Repository: go-gorm/gorm
Pull request #4693 (2021-09-10 12:16:28)

fix (clause/expression): Allow sql stmt terminator

Example: select * from table_name where name == @name;

- [x] Do only one thing
- [x] Non breaking API changes
- [x] Tested

### What did this pull request do?

Allow the sql stmt terminator ";" at the end of a named parameter.

### User Case Description

Example: 
`select * from table_name where name == @name;`

Allow the stmt terminator `;` at the end of the stmt without a space between it and the named parameter.


Patch:
diff --git a/callbacks/create.go b/callbacks/create.go
index 8a3c593..a294431 100644
--- a/callbacks/create.go
+++ b/callbacks/create.go
@@ -227,6 +227,8 @@ func AfterCreate(db *gorm.DB) {
 
 // ConvertToCreateValues convert to create values
 func ConvertToCreateValues(stmt *gorm.Statement) (values clause.Values) {
+	curTime := stmt.DB.NowFunc()
+
 	switch value := stmt.Dest.(type) {
 	case map[string]interface{}:
 		values = ConvertMapToValuesForCreate(stmt, value)
@@ -240,7 +242,6 @@ func ConvertToCreateValues(stmt *gorm.Statement) (values clause.Values) {
 		var (
 			selectColumns, restricted = stmt.SelectAndOmitColumns(true, false)
 			_, updateTrackTime        = stmt.Get("gorm:update_track_time")
-			curTime                   = stmt.DB.NowFunc()
 			isZero                    bool
 		)
 		stmt.Settings.Delete("gorm:update_track_time")
@@ -352,13 +353,27 @@ func ConvertToCreateValues(stmt *gorm.Statement) (values clause.Values) {
 					if field := stmt.Schema.LookUpField(column.Name); field != nil {
 						if v, ok := selectColumns[field.DBName]; (ok && v) || (!ok && !restricted) {
 							if !field.PrimaryKey && (!field.HasDefaultValue || field.DefaultValueInterface != nil) && field.AutoCreateTime == 0 {
-								columns = append(columns, column.Name)
+								if field.AutoUpdateTime > 0 {
+									assignment := clause.Assignment{Column: clause.Column{Name: field.DBName}, Value: curTime}
+									switch field.AutoUpdateTime {
+									case schema.UnixNanosecond:
+										assignment.Value = curTime.UnixNano()
+									case schema.UnixMillisecond:
+										assignment.Value = curTime.UnixNano() / 1e6
+									case schema.UnixSecond:
+										assignment.Value = curTime.Unix()
+									}
+
+									onConflict.DoUpdates = append(onConflict.DoUpdates, assignment)
+								} else {
+									columns = append(columns, column.Name)
+								}
 							}
 						}
 					}
 				}
 
-				onConflict.DoUpdates = clause.AssignmentColumns(columns)
+				onConflict.DoUpdates = append(onConflict.DoUpdates, clause.AssignmentColumns(columns)...)
 
 				// use primary fields as default OnConflict columns
 				if len(onConflict.Columns) == 0 {
diff --git a/clause/expression.go b/clause/expression.go
index f7b93f4..e914b7b 100644
--- a/clause/expression.go
+++ b/clause/expression.go
@@ -121,7 +121,7 @@ func (expr NamedExpr) Build(builder Builder) {
 		if v == '@' && !inName {
 			inName = true
 			name = []byte{}
-		} else if v == ' ' || v == ',' || v == ')' || v == '"' || v == '\'' || v == '`' || v == '\n' {
+		} else if v == ' ' || v == ',' || v == ')' || v == '"' || v == '\'' || v == '`' || v == '\n' || v == ';' {
 			if inName {
 				if nv, ok := namedMap[string(name)]; ok {
 					builder.AddVar(builder, nv)
diff --git a/finisher_api.go b/finisher_api.go
index 34e1596..d273093 100644
--- a/finisher_api.go
+++ b/finisher_api.go
@@ -399,7 +399,7 @@ func (db *DB) Count(count *int64) (tx *DB) {
 
 				if tx.Statement.Distinct {
 					expr = clause.Expr{SQL: "COUNT(DISTINCT(?))", Vars: []interface{}{clause.Column{Name: dbName}}}
-				} else {
+				} else if dbName != "*" {
 					expr = clause.Expr{SQL: "COUNT(?)", Vars: []interface{}{clause.Column{Name: dbName}}}
 				}
 			}
@@ -506,7 +506,12 @@ func (db *DB) ScanRows(rows *sql.Rows, dest interface{}) error {
 	tx.Statement.Dest = dest
 	tx.Statement.ReflectValue = reflect.ValueOf(dest)
 	for tx.Statement.ReflectValue.Kind() == reflect.Ptr {
-		tx.Statement.ReflectValue = tx.Statement.ReflectValue.Elem()
+		elem := tx.Statement.ReflectValue.Elem()
+		if !elem.IsValid() {
+			elem = reflect.New(tx.Statement.ReflectValue.Type().Elem())
+			tx.Statement.ReflectValue.Set(elem)
+		}
+		tx.Statement.ReflectValue = elem
 	}
 	Scan(rows, tx, true)
 	return tx.Error
diff --git a/scan.go b/scan.go
index 2beecd4..4570380 100644
--- a/scan.go
+++ b/scan.go
@@ -97,11 +97,15 @@ func Scan(rows *sql.Rows, db *DB, initialized bool) {
 		}
 	default:
 		Schema := db.Statement.Schema
+		reflectValue := db.Statement.ReflectValue
+		if reflectValue.Kind() == reflect.Interface {
+			reflectValue = reflectValue.Elem()
+		}
 
-		switch db.Statement.ReflectValue.Kind() {
+		switch reflectValue.Kind() {
 		case reflect.Slice, reflect.Array:
 			var (
-				reflectValueType = db.Statement.ReflectValue.Type().Elem()
+				reflectValueType = reflectValue.Type().Elem()
 				isPtr            = reflectValueType.Kind() == reflect.Ptr
 				fields           = make([]*schema.Field, len(columns))
 				joinFields       [][2]*schema.Field
@@ -111,7 +115,7 @@ func Scan(rows *sql.Rows, db *DB, initialized bool) {
 				reflectValueType = reflectValueType.Elem()
 			}
 
-			db.Statement.ReflectValue.Set(reflect.MakeSlice(db.Statement.ReflectValue.Type(), 0, 20))
+			db.Statement.ReflectValue.Set(reflect.MakeSlice(reflectValue.Type(), 0, 20))
 
 			if Schema != nil {
 				if reflectValueType != Schema.ModelType && reflectValueType.Kind() == reflect.Struct {
@@ -186,13 +190,15 @@ func Scan(rows *sql.Rows, db *DB, initialized bool) {
 				}
 
 				if isPtr {
-					db.Statement.ReflectValue.Set(reflect.Append(db.Statement.ReflectValue, elem))
+					reflectValue = reflect.Append(reflectValue, elem)
 				} else {
-					db.Statement.ReflectValue.Set(reflect.Append(db.Statement.ReflectValue, elem.Elem()))
+					reflectValue = reflect.Append(reflectValue, elem.Elem())
 				}
 			}
+
+			db.Statement.ReflectValue.Set(reflectValue)
 		case reflect.Struct, reflect.Ptr:
-			if db.Statement.ReflectValue.Type() != Schema.ModelType {
+			if reflectValue.Type() != Schema.ModelType {
 				Schema, _ = schema.Parse(db.Statement.Dest, db.cacheStore, db.NamingStrategy)
 			}
 
@@ -220,11 +226,11 @@ func Scan(rows *sql.Rows, db *DB, initialized bool) {
 
 				for idx, column := range columns {
 					if field := Schema.LookUpField(column); field != nil && field.Readable {
-						field.Set(db.Statement.ReflectValue, values[idx])
+						field.Set(reflectValue, values[idx])
 					} else if names := strings.Split(column, "__"); len(names) > 1 {
 						if rel, ok := Schema.Relationships.Relations[names[0]]; ok {
 							if field := rel.FieldSchema.LookUpField(strings.Join(names[1:], "__")); field != nil && field.Readable {
-								relValue := rel.Field.ReflectValueOf(db.Statement.ReflectValue)
+								relValue := rel.Field.ReflectValueOf(reflectValue)
 								value := reflect.ValueOf(values[idx]).Elem()
 
 								if relValue.Kind() == reflect.Ptr && relValue.IsNil() {
diff --git a/schema/field.go b/schema/field.go
index ce0e3c1..f3189c7 100644
--- a/schema/field.go
+++ b/schema/field.go
@@ -21,9 +21,10 @@ type TimeType int64
 var TimeReflectType = reflect.TypeOf(time.Time{})
 
 const (
-	UnixSecond      TimeType = 1
-	UnixMillisecond TimeType = 2
-	UnixNanosecond  TimeType = 3
+	UnixTime        TimeType = 1
+	UnixSecond      TimeType = 2
+	UnixMillisecond TimeType = 3
+	UnixNanosecond  TimeType = 4
 )
 
 const (
@@ -251,7 +252,9 @@ func (schema *Schema) ParseField(fieldStruct reflect.StructField) *Field {
 	}
 
 	if v, ok := field.TagSettings["AUTOCREATETIME"]; ok || (field.Name == "CreatedAt" && (field.DataType == Time || field.DataType == Int || field.DataType == Uint)) {
-		if strings.ToUpper(v) == "NANO" {
+		if field.DataType == Time {
+			field.AutoCreateTime = UnixTime
+		} else if strings.ToUpper(v) == "NANO" {
 			field.AutoCreateTime = UnixNanosecond
 		} else if strings.ToUpper(v) == "MILLI" {
 			field.AutoCreateTime = UnixMillisecond
@@ -261,7 +264,9 @@ func (schema *Schema) ParseField(fieldStruct reflect.StructField) *Field {
 	}
 
 	if v, ok := field.TagSettings["AUTOUPDATETIME"]; ok || (field.Name == "UpdatedAt" && (field.DataType == Time || field.DataType == Int || field.DataType == Uint)) {
-		if strings.ToUpper(v) == "NANO" {
+		if field.DataType == Time {
+			field.AutoUpdateTime = UnixTime
+		} else if strings.ToUpper(v) == "NANO" {
 			field.AutoUpdateTime = UnixNanosecond
 		} else if strings.ToUpper(v) == "MILLI" {
 			field.AutoUpdateTime = UnixMillisecond
diff --git a/schema/schema.go b/schema/schema.go
index faba2e2..c425070 100644
--- a/schema/schema.go
+++ b/schema/schema.go
@@ -77,7 +77,11 @@ func Parse(dest interface{}, cacheStore *sync.Map, namer Namer) (*Schema, error)
 		return nil, fmt.Errorf("%w: %+v", ErrUnsupportedDataType, dest)
 	}
 
-	modelType := reflect.ValueOf(dest).Type()
+	modelType := reflect.Indirect(reflect.ValueOf(dest)).Type()
+	if modelType.Kind() == reflect.Interface {
+		modelType = reflect.Indirect(reflect.ValueOf(dest)).Elem().Type()
+	}
+
 	for modelType.Kind() == reflect.Slice || modelType.Kind() == reflect.Array || modelType.Kind() == reflect.Ptr {
 		modelType = modelType.Elem()
 	}


Test patch:
diff --git a/clause/expression_test.go b/clause/expression_test.go
index 0507486..eadd96e 100644
--- a/clause/expression_test.go
+++ b/clause/expression_test.go
@@ -89,6 +89,11 @@ func TestNamedExpr(t *testing.T) {
 		SQL:    "create table ? (? ?, ? ?)",
 		Vars:   []interface{}{},
 		Result: "create table ? (? ?, ? ?)",
+	}, {
+		SQL:          "name1 = @name AND name2 = @name;",
+		Vars:         []interface{}{sql.Named("name", "jinzhu")},
+		Result:       "name1 = ? AND name2 = ?;",
+		ExpectedVars: []interface{}{"jinzhu", "jinzhu"},
 	}}
 
 	for idx, result := range results {
diff --git a/tests/count_test.go b/tests/count_test.go
index dd25f8b..de06d0e 100644
--- a/tests/count_test.go
+++ b/tests/count_test.go
@@ -112,7 +112,7 @@ func TestCount(t *testing.T) {
 	if err := DB.Model(&User{}).Where("name in ?", []string{user1.Name, user2.Name, user3.Name}).Select(
 		"(CASE WHEN age=18 THEN 1 ELSE 2 END) as age", "name",
 	).Count(&count8).Find(&users).Error; err != nil || count8 != 3 {
-		t.Fatalf(fmt.Sprintf("Count should work, but got err %v", err))
+		t.Fatalf("Count should work, but got err %v", err)
 	}
 
 	expects = []User{User{Name: "count-1", Age: 1}, {Name: "count-2", Age: 1}, {Name: "count-3", Age: 1}}
@@ -123,9 +123,15 @@ func TestCount(t *testing.T) {
 	AssertEqual(t, users, expects)
 
 	var count9 int64
-	if err := DB.Debug().Scopes(func(tx *gorm.DB) *gorm.DB {
+	if err := DB.Scopes(func(tx *gorm.DB) *gorm.DB {
 		return tx.Table("users")
 	}).Where("name in ?", []string{user1.Name, user2.Name, user3.Name}).Count(&count9).Find(&users).Error; err != nil || count9 != 3 {
-		t.Fatalf(fmt.Sprintf("Count should work, but got err %v", err))
+		t.Fatalf("Count should work, but got err %v", err)
 	}
+
+	var count10 int64
+	if err := DB.Model(&User{}).Select("*").Where("name in ?", []string{user1.Name, user2.Name, user3.Name}).Count(&count10).Error; err != nil || count10 != 3 {
+		t.Fatalf("Count should be 3, but got count: %v err %v", count10, err)
+	}
+
 }
diff --git a/tests/go.mod b/tests/go.mod
index d7ab65a..c4e2702 100644
--- a/tests/go.mod
+++ b/tests/go.mod
@@ -7,10 +7,10 @@ require (
 	github.com/jinzhu/now v1.1.2
 	github.com/lib/pq v1.10.3
 	gorm.io/driver/mysql v1.1.2
-	gorm.io/driver/postgres v1.1.0
-	gorm.io/driver/sqlite v1.1.4
+	gorm.io/driver/postgres v1.1.1
+	gorm.io/driver/sqlite v1.1.5
 	gorm.io/driver/sqlserver v1.0.9
-	gorm.io/gorm v1.21.14
+	gorm.io/gorm v1.21.15
 )
 
 replace gorm.io/gorm => ../
diff --git a/tests/scan_test.go b/tests/scan_test.go
index 67d5f38..59fc6de 100644
--- a/tests/scan_test.go
+++ b/tests/scan_test.go
@@ -29,8 +29,9 @@ func TestScan(t *testing.T) {
 	}
 
 	var resPointer *result
-	DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Scan(&resPointer)
-	if res.ID != user3.ID || res.Name != user3.Name || res.Age != int(user3.Age) {
+	if err := DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Scan(&resPointer).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if resPointer.ID != user3.ID || resPointer.Name != user3.Name || resPointer.Age != int(user3.Age) {
 		t.Fatalf("Scan into struct should work, got %#v, should %#v", res, user3)
 	}
 
@@ -70,6 +71,46 @@ func TestScan(t *testing.T) {
 	if uint(id) != user2.ID {
 		t.Errorf("Failed to scan to customized data type")
 	}
+
+	var resInt interface{}
+	resInt = &User{}
+	if err := DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Find(&resInt).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if resInt.(*User).ID != user3.ID || resInt.(*User).Name != user3.Name || resInt.(*User).Age != user3.Age {
+		t.Fatalf("Scan into struct should work, got %#v, should %#v", resInt, user3)
+	}
+
+	var resInt2 interface{}
+	resInt2 = &User{}
+	if err := DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Scan(&resInt2).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if resInt2.(*User).ID != user3.ID || resInt2.(*User).Name != user3.Name || resInt2.(*User).Age != user3.Age {
+		t.Fatalf("Scan into struct should work, got %#v, should %#v", resInt2, user3)
+	}
+
+	var resInt3 interface{}
+	resInt3 = []User{}
+	if err := DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Find(&resInt3).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if rus := resInt3.([]User); len(rus) == 0 || rus[0].ID != user3.ID || rus[0].Name != user3.Name || rus[0].Age != user3.Age {
+		t.Fatalf("Scan into struct should work, got %#v, should %#v", resInt3, user3)
+	}
+
+	var resInt4 interface{}
+	resInt4 = []User{}
+	if err := DB.Table("users").Select("id, name, age").Where("id = ?", user3.ID).Scan(&resInt4).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if rus := resInt4.([]User); len(rus) == 0 || rus[0].ID != user3.ID || rus[0].Name != user3.Name || rus[0].Age != user3.Age {
+		t.Fatalf("Scan into struct should work, got %#v, should %#v", resInt4, user3)
+	}
+
+	var resInt5 interface{}
+	resInt5 = []User{}
+	if err := DB.Table("users").Select("id, name, age").Where("id IN ?", []uint{user1.ID, user2.ID, user3.ID}).Find(&resInt5).Error; err != nil {
+		t.Fatalf("Failed to query with pointer of value, got error %v", err)
+	} else if rus := resInt5.([]User); len(rus) != 3 {
+		t.Fatalf("Scan into struct should work, got %+v, len %v", resInt5, len(rus))
+	}
 }
 
 func TestScanRows(t *testing.T) {
diff --git a/tests/upsert_test.go b/tests/upsert_test.go
index 867110d..0e247ca 100644
--- a/tests/upsert_test.go
+++ b/tests/upsert_test.go
@@ -66,6 +66,26 @@ func TestUpsert(t *testing.T) {
 			t.Errorf("Table with escape character, got %v", r.Statement.SQL.String())
 		}
 	}
+
+	var user = *GetUser("upsert_on_conflict", Config{})
+	user.Age = 20
+	if err := DB.Create(&user).Error; err != nil {
+		t.Errorf("failed to create user, got error %v", err)
+	}
+
+	var user2 User
+	DB.First(&user2, user.ID)
+	user2.Age = 30
+	time.Sleep(time.Second)
+	if err := DB.Clauses(clause.OnConflict{UpdateAll: true}).Create(&user2).Error; err != nil {
+		t.Fatalf("failed to onconflict create user, got error %v", err)
+	} else {
+		var user3 User
+		DB.First(&user3, user.ID)
+		if user3.UpdatedAt.UnixNano() == user2.UpdatedAt.UnixNano() {
+			t.Fatalf("failed to update user's updated_at, old: %v, new: %v", user2.UpdatedAt, user3.UpdatedAt)
+		}
+	}
 }
 
 func TestUpsertSlice(t *testing.T) {
@@ -152,29 +172,29 @@ func TestUpsertWithSave(t *testing.T) {
 		}
 	}
 
-	// lang := Language{Code: "upsert-save-3", Name: "Upsert-save-3"}
-	// if err := DB.Save(&lang).Error; err != nil {
-	// 	t.Errorf("Failed to create, got error %v", err)
-	// }
-
-	// var result Language
-	// if err := DB.First(&result, "code = ?", lang.Code).Error; err != nil {
-	// 	t.Errorf("Failed to query lang, got error %v", err)
-	// } else {
-	// 	AssertEqual(t, result, lang)
-	// }
-
-	// lang.Name += "_new"
-	// if err := DB.Save(&lang).Error; err != nil {
-	// 	t.Errorf("Failed to create, got error %v", err)
-	// }
-
-	// var result2 Language
-	// if err := DB.First(&result2, "code = ?", lang.Code).Error; err != nil {
-	// 	t.Errorf("Failed to query lang, got error %v", err)
-	// } else {
-	// 	AssertEqual(t, result2, lang)
-	// }
+	lang := Language{Code: "upsert-save-3", Name: "Upsert-save-3"}
+	if err := DB.Save(&lang).Error; err != nil {
+		t.Errorf("Failed to create, got error %v", err)
+	}
+
+	var result Language
+	if err := DB.First(&result, "code = ?", lang.Code).Error; err != nil {
+		t.Errorf("Failed to query lang, got error %v", err)
+	} else {
+		AssertEqual(t, result, lang)
+	}
+
+	lang.Name += "_new"
+	if err := DB.Save(&lang).Error; err != nil {
+		t.Errorf("Failed to create, got error %v", err)
+	}
+
+	var result2 Language
+	if err := DB.First(&result2, "code = ?", lang.Code).Error; err != nil {
+		t.Errorf("Failed to query lang, got error %v", err)
+	} else {
+		AssertEqual(t, result2, lang)
+	}
 }
 
 func TestFindOrInitialize(t *testing.T) {
diff --git a/utils/tests/dummy_dialecter.go b/utils/tests/dummy_dialecter.go
index b8452ef..84fdd2b 100644
--- a/utils/tests/dummy_dialecter.go
+++ b/utils/tests/dummy_dialecter.go
@@ -31,9 +31,51 @@ func (DummyDialector) BindVarTo(writer clause.Writer, stmt *gorm.Statement, v in
 }
 
 func (DummyDialector) QuoteTo(writer clause.Writer, str string) {
-	writer.WriteByte('`')
-	writer.WriteString(str)
-	writer.WriteByte('`')
+	var (
+		underQuoted, selfQuoted bool
+		continuousBacktick      int8
+		shiftDelimiter          int8
+	)
+
+	for _, v := range []byte(str) {
+		switch v {
+		case '`':
+			continuousBacktick++
+			if continuousBacktick == 2 {
+				writer.WriteString("``")
+				continuousBacktick = 0
+			}
+		case '.':
+			if continuousBacktick > 0 || !selfQuoted {
+				shiftDelimiter = 0
+				underQuoted = false
+				continuousBacktick = 0
+				writer.WriteString("`")
+			}
+			writer.WriteByte(v)
+			continue
+		default:
+			if shiftDelimiter-continuousBacktick <= 0 && !underQuoted {
+				writer.WriteByte('`')
+				underQuoted = true
+				if selfQuoted = continuousBacktick > 0; selfQuoted {
+					continuousBacktick -= 1
+				}
+			}
+
+			for ; continuousBacktick > 0; continuousBacktick -= 1 {
+				writer.WriteString("``")
+			}
+
+			writer.WriteByte(v)
+		}
+		shiftDelimiter++
+	}
+
+	if continuousBacktick > 0 && !selfQuoted {
+		writer.WriteString("``")
+	}
+	writer.WriteString("`")
 }
 
 func (DummyDialector) Explain(sql string, vars ...interface{}) string {