Repository: go-gorm/gorm
Pull request #4618 (2021-08-16 12:12:29)

Fix extra 'AND' when len(values) == 0 ON IN.NegationBuild()
<!--
Make sure these boxes checked before submitting your pull request.

For significant changes, please open an issue to make an agreement on an implementation design/plan first before starting it.
-->

- [x] Do only one thing
- [x] Non breaking API changes
- [x] Tested

### What did this pull request do?

<!--
provide a general description of the code changes in your pull request
-->
Three reasons for this PR:   
1. When `DB.Not(map[string]interface{}{"name": []string{}}).Where("age = ?", 10)`, Generate sql `SELECT * FROM ``users`` WHERE   AND ``age`` = 10;` , it is a bad sql.
2. In some database, like postgresql, when exec ` ``name`` NOT IN ('jinzhu')`, the result dose not contain the row where `name` == NULL, so when `is not` in an empty array,  to some extent, it can be considered that `name` IS NOT NULL. 
3. NegationBuild() should be the inverse of Build(), but when len(values)==0, the Build() return the row where `name`==NULL, so NegationBuild() should not return the row where `name`==NULL. 

### User Case Description

<!-- Your use case -->
```go
DB.Not(map[string]interface{}{"name": []string{}}).Where("age = ?", 10).Find(&User{})`   
```

Thanks!

secake: 
### Suggestion 
In other ORM, like django, when use `User.objects.filter(name__in=[])`, the result dose not contain the row where `name`==NULL, when use `User.objects.excludes(name__in=[])`, the result return all rows, also contains the row where `name`==NULL, as follows:
select id,name from users;
| id  |  name  |
| --  |  ----     |
| 1   |  jinzhu |
| 2   |  NULL  |
```py
User.objects.filter(name__in=[]).count()  // 0
User.objects.exclude(name__in=[]).count()` // 2
```

So I suggest the following implementation，if you think this suggestion works, I can implement it : 
```go
func (in IN) Build(builder Builder) {
	builder.WriteQuoted(in.Column)

	switch len(in.Values) {
	case 0:
		builder.WriteString(" 1=2")
	case 1:
		if _, ok := in.Values[0].([]interface{}); !ok {
			builder.WriteString(" = ")
			builder.AddVar(builder, in.Values[0])
			break
		}

		fallthrough
	default:
		builder.WriteString(" IN (")
		builder.AddVar(builder, in.Values...)
		builder.WriteByte(')')
	}
}

func (in IN) NegationBuild(builder Builder) {
	builder.WriteQuoted(in.Column)
	switch len(in.Values) {
	case 0:
		builder.WriteString(" 1=1")
	case 1:
		if _, ok := in.Values[0].([]interface{}); !ok {
			builder.WriteString(" <> ")
			builder.AddVar(builder, in.Values[0])
			break
		}

		fallthrough
	default:
		builder.WriteString(" NOT IN (")
		builder.AddVar(builder, in.Values...)
		builder.WriteByte(')')
	}
}
```
 

Patch:
diff --git a/clause/expression.go b/clause/expression.go
index a177c5d..f7b93f4 100644
--- a/clause/expression.go
+++ b/clause/expression.go
@@ -210,11 +210,12 @@ func (in IN) Build(builder Builder) {
 }
 
 func (in IN) NegationBuild(builder Builder) {
+	builder.WriteQuoted(in.Column)
 	switch len(in.Values) {
 	case 0:
+		builder.WriteString(" IS NOT NULL")
 	case 1:
 		if _, ok := in.Values[0].([]interface{}); !ok {
-			builder.WriteQuoted(in.Column)
 			builder.WriteString(" <> ")
 			builder.AddVar(builder, in.Values[0])
 			break
@@ -222,7 +223,6 @@ func (in IN) NegationBuild(builder Builder) {
 
 		fallthrough
 	default:
-		builder.WriteQuoted(in.Column)
 		builder.WriteString(" NOT IN (")
 		builder.AddVar(builder, in.Values...)
 		builder.WriteByte(')')
diff --git a/finisher_api.go b/finisher_api.go
index 537c955..34e1596 100644
--- a/finisher_api.go
+++ b/finisher_api.go
@@ -390,7 +390,7 @@ func (db *DB) Count(count *int64) (tx *DB) {
 		if len(tx.Statement.Selects) == 1 {
 			dbName := tx.Statement.Selects[0]
 			fields := strings.FieldsFunc(dbName, utils.IsValidDBNameChar)
-			if len(fields) == 1 || (len(fields) == 3 && strings.ToUpper(fields[1]) == "AS") {
+			if len(fields) == 1 || (len(fields) == 3 && (strings.ToUpper(fields[1]) == "AS" || fields[1] == ".")) {
 				if tx.Statement.Parse(tx.Statement.Model) == nil {
 					if f := tx.Statement.Schema.LookUpField(dbName); f != nil {
 						dbName = f.DBName
diff --git a/statement.go b/statement.go
index 8b682c8..93b78c1 100644
--- a/statement.go
+++ b/statement.go
@@ -129,6 +129,8 @@ func (stmt *Statement) QuoteTo(writer clause.Writer, field interface{}) {
 			stmt.QuoteTo(writer, d)
 		}
 		writer.WriteByte(')')
+	case clause.Expr:
+		v.Build(stmt)
 	case string:
 		stmt.DB.Dialector.QuoteTo(writer, v)
 	case []string:


Test patch:
diff --git a/clause/expression_test.go b/clause/expression_test.go
index 0ccd077..0507486 100644
--- a/clause/expression_test.go
+++ b/clause/expression_test.go
@@ -156,6 +156,18 @@ func TestExpression(t *testing.T) {
 		},
 		ExpectedVars: []interface{}{"a", "b"},
 		Result:       "`column-name` NOT IN (?,?)",
+	}, {
+		Expressions: []clause.Expression{
+			clause.Eq{Column: clause.Expr{SQL: "SUM(?)", Vars: []interface{}{clause.Column{Name: "id"}}}, Value: 100},
+		},
+		ExpectedVars: []interface{}{100},
+		Result:       "SUM(`id`) = ?",
+	}, {
+		Expressions: []clause.Expression{
+			clause.Gte{Column: clause.Expr{SQL: "SUM(?)", Vars: []interface{}{clause.Column{Table: "users", Name: "id"}}}, Value: 100},
+		},
+		ExpectedVars: []interface{}{100},
+		Result:       "SUM(`users`.`id`) >= ?",
 	}}
 
 	for idx, result := range results {
diff --git a/tests/go.mod b/tests/go.mod
index b623b36..278ad5b 100644
--- a/tests/go.mod
+++ b/tests/go.mod
@@ -6,11 +6,11 @@ require (
 	github.com/google/uuid v1.3.0
 	github.com/jinzhu/now v1.1.2
 	github.com/lib/pq v1.10.2
-	gorm.io/driver/mysql v1.1.1
+	gorm.io/driver/mysql v1.1.2
 	gorm.io/driver/postgres v1.1.0
 	gorm.io/driver/sqlite v1.1.4
 	gorm.io/driver/sqlserver v1.0.7
-	gorm.io/gorm v1.21.12
+	gorm.io/gorm v1.21.13
 )
 
 replace gorm.io/gorm => ../
diff --git a/tests/query_test.go b/tests/query_test.go
index 36046ae..8a47659 100644
--- a/tests/query_test.go
+++ b/tests/query_test.go
@@ -436,6 +436,11 @@ func TestNot(t *testing.T) {
 		t.Fatalf("Build NOT condition, but got %v", result.Statement.SQL.String())
 	}
 
+	result = dryDB.Not(map[string]interface{}{"name": []string{}}).Find(&User{})
+	if !regexp.MustCompile("SELECT \\* FROM .*users.* WHERE .*name.* IS NOT NULL").MatchString(result.Statement.SQL.String()) {
+		t.Fatalf("Build NOT condition, but got %v", result.Statement.SQL.String())
+	}
+	
 	result = dryDB.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&User{})
 	if !regexp.MustCompile("SELECT \\* FROM .*users.* WHERE .*name.* NOT IN \\(.+,.+\\)").MatchString(result.Statement.SQL.String()) {
 		t.Fatalf("Build NOT condition, but got %v", result.Statement.SQL.String())