Using clause.Not and clause.And result in erroneous simplification

[ezk84]

GORM Playground Link

go-gorm/playground#767

Description

When using clause.Not and clause.And to create query Where conditions and faulty simplification is applied:

NOT(col1 = val1 AND col2 = val2) gets turned into (col1 <> val1 AND col2 <> val2), which is wrong.

The right simplification is to turn the AND into an OR, by De Morgan's Laws, so (col1 <> OR val1 AND col2 <> val2)

This is a regression that was introduced into gorm in 1.25.6. Testing against 1.25.5 returns the correct condition and expected rows in the included tests (see gorm playground PR).

Test for completeness

func TestGORM(t *testing.T) {
	DB.Create(&User{Name: "jinzhu", Active: true})
	DB.Create(&User{Name: "ezequiel", Active: true})
	DB.Create(&User{Name: "jinzhu", Active: false})
	DB.Create(&User{Name: "ezequiel", Active: false})

	var results []User
	conds := clause.Not(clause.And(
		clause.Eq{
			Column: clause.Column{Name: "name"},
			Value:  "jinzhu",
		},
		clause.Eq{
			Column: clause.Column{Name: "active"},
			Value:  true,
		},
	))
	// Query with `NOT(name = 'jinzhu' AND active = true)` should return last three records
	// By De Morgan's Law this is equivalent to `name != 'jinzhu' OR active != true`
	// but current version (since 1.25.6) transforms this to
	// `name != 'jinzhu' AND active != true` which returns only the last record

	err := DB.
		Model(&User{}).
		Clauses(clause.Where{Exprs: []clause.Expression{conds}}).
		Find(&results).
		Error
	if err != nil {
		t.Errorf("Failed, got error: %v", err)
	}
	if len(results) != 3 {
		t.Errorf("Failed, expected 3 records, got %v", len(results))
	}
	for _, r := range results {
		if r.Name == "jinzhu" && r.Active {
			t.Errorf("Failed, unexpected record: %v", r)
		}
	}

	conds = clause.Not(clause.Or(
		clause.Eq{
			Column: clause.Column{Name: "name"},
			Value:  "jinzhu",
		},
		clause.Eq{
			Column: clause.Column{Name: "active"},
			Value:  true,
		},
	))
	// Query with `NOT(name = 'jinzhu' OR active = true)` should return last record
	err = DB.
		Model(&User{}).
		Clauses(clause.Where{Exprs: []clause.Expression{conds}}).
		Find(&results).
		Error
	if err != nil {
		t.Errorf("Failed, got error: %v", err)
	}
	if len(results) != 1 {
		t.Errorf("Failed, expected 1 records, got %v", len(results))
	}
	for _, r := range results {
		if r.Name == "jinzhu" || r.Active {
			t.Errorf("Failed, unexpected record: %v", r)
		}
	}

[ezk84]

I thought I had submitted this issue before, but must have missed submitting here in addition to the PR in gorm-playground.

[ezk84]

Have also seen a merged PR #6984 that is related, but the above test still fails for current master.