Repository: snowflakedb/gosnowflake
Pull request #642 (2022-08-10 01:04:06)

SNOW-590709 Support time.Time types in slice []interface{} for array binding
### Description
SNOW-590709 (Simba incident 00383422)

Part 2 for #629 to support the DATE, TIME, TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ data types to be used in slice []interface{} for array binding.

### Checklist
- [x] Code compiles correctly
- [x] Run ``make fmt`` to fix inconsistent formats
- [x] Run ``make lint`` to get lint errors and fix all of them
- [x] Created tests which fail without the change (if possible)
- [x] All tests passing
- [x] Extended the README / documentation, if necessary



Patch:
diff --git a/bind_uploader.go b/bind_uploader.go
index 9894fbf..d3e8b69 100644
--- a/bind_uploader.go
+++ b/bind_uploader.go
@@ -10,7 +10,6 @@ import (
 	"reflect"
 	"strconv"
 	"strings"
-	"time"
 )
 
 const (
@@ -295,24 +294,11 @@ func supportedArrayBind(nv *driver.NamedValue) bool {
 		// Support for bulk array binding insertion using []interface{}
 		nvValue := reflect.ValueOf(nv)
 		if nvValue.Kind() == reflect.Ptr {
-			interfaceSlice := reflect.Indirect(reflect.ValueOf(nv.Value))
-			if interfaceSlice.Kind() == reflect.Slice {
-				for i := 0; i < interfaceSlice.Len(); i++ {
-					val := interfaceSlice.Index(i)
-					if val.CanInterface() {
-						switch val.Interface().(type) {
-						case time.Time:
-							// TODO support date, time, timestamps
-							return false
-						default:
-							continue
-						}
-					}
-				}
+			value := reflect.Indirect(reflect.ValueOf(nv.Value))
+			if value.Kind() == reflect.Slice || value.Kind() == reflect.Struct {
 				return true
 			}
 		}
-
 		return false
 	}
 }
diff --git a/converter.go b/converter.go
index f334013..50f1bfa 100644
--- a/converter.go
+++ b/converter.go
@@ -37,6 +37,11 @@ const (
 	TimeType
 )
 
+type timezoneTypeArrayBinding struct {
+	tzType            timezoneType
+	timezoneTypeArray interface{}
+}
+
 // goTypeToSnowflake translates Go data type to Snowflake data type.
 func goTypeToSnowflake(v driver.Value, tsmode snowflakeType) snowflakeType {
 	switch t := v.(type) {
@@ -629,6 +634,13 @@ func Array(a interface{}, typ ...timezoneType) interface{} {
 		default:
 			return a
 		}
+	case []interface{}, *[]interface{}:
+		// Support for bulk array binding insertion using []interface{}
+		if len(typ) < 1 {
+			return a
+		}
+
+		return timezoneTypeArrayBinding{typ[0], a}
 	default:
 		return a
 	}
@@ -751,22 +763,30 @@ func snowflakeArrayToString(nv *driver.NamedValue, stream bool) (snowflakeType,
 		// Support for bulk array binding insertion using []interface{}
 		nvValue := reflect.ValueOf(nv)
 		if nvValue.Kind() == reflect.Ptr {
-			interfaceSlice := reflect.Indirect(reflect.ValueOf(nv.Value))
-			if interfaceSlice.Kind() == reflect.Slice {
-				t, arr = interfaceSliceToString(interfaceSlice, interfaceSlice.Len(), stream)
+			value := reflect.Indirect(reflect.ValueOf(nv.Value))
+			if value.Kind() == reflect.Slice {
+				return interfaceSliceToString(value, stream)
+			} else if value.Kind() == reflect.Struct {
+				timeStruct, ok := value.Interface().(timezoneTypeArrayBinding)
+				if ok {
+					timeInterfaceSlice := reflect.Indirect(reflect.ValueOf(timeStruct.timezoneTypeArray))
+					if timeInterfaceSlice.Kind() == reflect.Slice {
+						timeArray := reflect.Indirect(reflect.ValueOf(timeInterfaceSlice.Interface()))
+						return interfaceSliceToString(timeArray, stream, timeStruct.tzType)
+					}
+				}
 			}
-		} else {
-			return unSupportedType, nil
 		}
+		return unSupportedType, nil
 	}
 	return t, arr
 }
 
-func interfaceSliceToString(interfaceSlice reflect.Value, interfaceSliceLen int, stream bool) (snowflakeType, []*string) {
+func interfaceSliceToString(interfaceSlice reflect.Value, stream bool, tzType ...timezoneType) (snowflakeType, []*string) {
 	var t snowflakeType
 	var arr []*string
 
-	for i := 0; i < interfaceSliceLen; i++ {
+	for i := 0; i < interfaceSlice.Len(); i++ {
 		val := interfaceSlice.Index(i)
 		if val.CanInterface() {
 			switch val.Interface().(type) {
@@ -810,8 +830,50 @@ func interfaceSliceToString(interfaceSlice reflect.Value, interfaceSliceLen int,
 				v := hex.EncodeToString(x)
 				arr = append(arr, &v)
 			case time.Time:
-				// Support for bulk array binding insertion using []interface{}
-				return unSupportedType, nil
+				if len(tzType) < 1 {
+					return unSupportedType, nil
+				}
+
+				x := val.Interface().(time.Time)
+				switch tzType[0] {
+				case TimestampNTZType:
+					t = timestampNtzType
+					v := strconv.FormatInt(x.UnixNano(), 10)
+					arr = append(arr, &v)
+				case TimestampLTZType:
+					t = timestampLtzType
+					v := strconv.FormatInt(x.UnixNano(), 10)
+					arr = append(arr, &v)
+				case TimestampTZType:
+					t = timestampTzType
+					var v string
+					if stream {
+						v = x.Format(format)
+					} else {
+						_, offset := x.Zone()
+						v = fmt.Sprintf("%v %v", x.UnixNano(), offset/60+1440)
+					}
+					arr = append(arr, &v)
+				case DateType:
+					t = dateType
+					_, offset := x.Zone()
+					x = x.Add(time.Second * time.Duration(offset))
+					v := fmt.Sprintf("%d", x.Unix()*1000)
+					arr = append(arr, &v)
+				case TimeType:
+					t = timeType
+					var v string
+					if stream {
+						v = x.Format(format[11:19])
+					} else {
+						h, m, s := x.Clock()
+						tm := int64(h)*int64(time.Hour) + int64(m)*int64(time.Minute) + int64(s)*int64(time.Second) + int64(x.Nanosecond())
+						v = strconv.FormatInt(tm, 10)
+					}
+					arr = append(arr, &v)
+				default:
+					return unSupportedType, nil
+				}
 			default:
 				if val.Interface() != nil {
 					return unSupportedType, nil
diff --git a/doc.go b/doc.go
index 37688c7..fbf5d7e 100644
--- a/doc.go
+++ b/doc.go
@@ -442,7 +442,11 @@ This feature is available in version 1.6.12 (and later) of the driver. For exmap
 		}
 	}
 
-Currently, the driver does not support the DATE, TIME, TIMESTAMP_LTZ, TIMESTAMP_NTZ and TIMESTAMP_TZ data types when slice []interface{} is used in array binding.
+For slices []interface{} containing time.Time values, a binding parameter flag is required for the preceding array variable in the Array() function.
+This feature is available in version 1.6.13 (and later) of the driver. For exmaple,
+
+	_, err = db.Exec("create or replace table my_table(c1 timestamp_ntz, c2 timestamp_ltz)")
+	_, err = db.Exec("insert into my_table values (?,?)", Array(&ntzArray, sf.TimestampNTZType), Array(&ltzArray, sf.TimestampLTZType))
 
 Note: For alternative ways to load data into the Snowflake database (including bulk loading using the COPY command), see
 Loading Data into Snowflake (https://docs.snowflake.com/en/user-guide-data-load.html).


Test patch:
diff --git a/bindings_test.go b/bindings_test.go
index 95f8cf3..bc6d7bb 100644
--- a/bindings_test.go
+++ b/bindings_test.go
@@ -263,12 +263,15 @@ func TestBulkArrayBindingInterfaceNil(t *testing.T) {
 	nilArray := make([]interface{}, 1)
 
 	runTests(t, dsn, func(dbt *DBTest) {
-		dbt.mustExec(createTableSQLBulkArray)
-		defer dbt.mustExec(deleteTableSQLBulkArray)
+		dbt.mustExec(createTableSQL)
+		defer dbt.mustExec(deleteTableSQL)
 
-		dbt.mustExec(insertSQLBulkArray, Array(&nilArray), Array(&nilArray),
-			Array(&nilArray), Array(&nilArray), Array(&nilArray))
-		rows := dbt.mustQuery(selectAllSQLBulkArray)
+		dbt.mustExec(insertSQL, Array(&nilArray), Array(&nilArray),
+			Array(&nilArray), Array(&nilArray), Array(&nilArray),
+			Array(&nilArray, TimestampNTZType), Array(&nilArray, TimestampTZType),
+			Array(&nilArray, TimestampTZType), Array(&nilArray, DateType),
+			Array(&nilArray, TimeType))
+		rows := dbt.mustQuery(selectAllSQL)
 		defer rows.Close()
 
 		var v0 sql.NullInt32
@@ -276,26 +279,42 @@ func TestBulkArrayBindingInterfaceNil(t *testing.T) {
 		var v2 sql.NullBool
 		var v3 sql.NullString
 		var v4 []byte
+		var v5, v6, v7, v8, v9 sql.NullTime
 
 		cnt := 0
 		for i := 0; rows.Next(); i++ {
-			if err := rows.Scan(&v0, &v1, &v2, &v3, &v4); err != nil {
+			if err := rows.Scan(&v0, &v1, &v2, &v3, &v4, &v5, &v6, &v7, &v8, &v9); err != nil {
 				t.Fatal(err)
 			}
 			if v0.Valid {
-				t.Fatalf("failed to fetch the INTEGER column V0. expected %v, got: %v", nilArray[i], v0)
+				t.Fatalf("failed to fetch the sql.NullInt32 column v0. expected %v, got: %v", nilArray[i], v0)
 			}
 			if v1.Valid {
-				t.Fatalf("failed to fetch the FLOAT column. expected %v, got: %v", nilArray[i], v1)
+				t.Fatalf("failed to fetch the sql.NullFloat64 column v1. expected %v, got: %v", nilArray[i], v1)
 			}
 			if v2.Valid {
-				t.Fatalf("failed to fetch the BOOLEAN column. expected %v, got: %v", nilArray[i], v2)
+				t.Fatalf("failed to fetch the sql.NullBool column v2. expected %v, got: %v", nilArray[i], v2)
 			}
 			if v3.Valid {
-				t.Fatalf("failed to fetch the STRING column. expected %v, got: %v", nilArray[i], v3)
+				t.Fatalf("failed to fetch the sql.NullString column v3. expected %v, got: %v", nilArray[i], v3)
 			}
 			if v4 != nil {
-				t.Fatalf("failed to fetch the BINARY column. expected %v, got: %v", nilArray[i], v4)
+				t.Fatalf("failed to fetch the []byte column v4. expected %v, got: %v", nilArray[i], v4)
+			}
+			if v5.Valid {
+				t.Fatalf("failed to fetch the sql.NullTime column v5. expected %v, got: %v", nilArray[i], v5)
+			}
+			if v6.Valid {
+				t.Fatalf("failed to fetch the sql.NullTime column v6. expected %v, got: %v", nilArray[i], v6)
+			}
+			if v7.Valid {
+				t.Fatalf("failed to fetch the sql.NullTime column v7. expected %v, got: %v", nilArray[i], v7)
+			}
+			if v8.Valid {
+				t.Fatalf("failed to fetch the sql.NullTime column v8. expected %v, got: %v", nilArray[i], v8)
+			}
+			if v9.Valid {
+				t.Fatalf("failed to fetch the sql.NullTime column v9. expected %v, got: %v", nilArray[i], v9)
 			}
 			cnt++
 		}
@@ -347,38 +366,38 @@ func TestBulkArrayBindingInterface(t *testing.T) {
 			}
 			if v0.Valid {
 				if v0.Int32 != intArray[i] {
-					t.Fatalf("failed to fetch. expected %v, got: %v", intArray[i], v0.Int32)
+					t.Fatalf("failed to fetch the sql.NullInt32 column v0. expected %v, got: %v", intArray[i], v0.Int32)
 				}
 			} else if intArray[i] != nil {
-				t.Fatalf("failed to fetch. expected %v, got: %v", intArray[i], v0)
+				t.Fatalf("failed to fetch the sql.NullInt32 column v0. expected %v, got: %v", intArray[i], v0)
 			}
 			if v1.Valid {
 				if v1.Float64 != fltArray[i] {
-					t.Fatalf("failed to fetch. expected %v, got: %v", fltArray[i], v1.Float64)
+					t.Fatalf("failed to fetch the sql.NullFloat64 column v1. expected %v, got: %v", fltArray[i], v1.Float64)
 				}
 			} else if fltArray[i] != nil {
-				t.Fatalf("failed to fetch. expected %v, got: %v", fltArray[i], v1)
+				t.Fatalf("failed to fetch the sql.NullFloat64 column v1. expected %v, got: %v", fltArray[i], v1)
 			}
 			if v2.Valid {
 				if v2.Bool != boolArray[i] {
-					t.Fatalf("failed to fetch. expected %v, got: %v", boolArray[i], v2.Bool)
+					t.Fatalf("failed to fetch the sql.NullBool column v2. expected %v, got: %v", boolArray[i], v2.Bool)
 				}
 			} else if boolArray[i] != nil {
-				t.Fatalf("failed to fetch. expected %v, got: %v", boolArray[i], v2)
+				t.Fatalf("failed to fetch the sql.NullBool column v2. expected %v, got: %v", boolArray[i], v2)
 			}
 			if v3.Valid {
 				if v3.String != strArray[i] {
-					t.Fatalf("failed to fetch. expected %v, got: %v", strArray[i], v3.String)
+					t.Fatalf("failed to fetch the sql.NullString column v3. expected %v, got: %v", strArray[i], v3.String)
 				}
 			} else if strArray[i] != nil {
-				t.Fatalf("failed to fetch. expected %v, got: %v", strArray[i], v3)
+				t.Fatalf("failed to fetch the sql.NullString column v3. expected %v, got: %v", strArray[i], v3)
 			}
 			if byteArray[i] != nil {
 				if !bytes.Equal(v4, byteArray[i].([]byte)) {
-					t.Fatalf("failed to fetch. expected %v, got: %v", byteArray[i], v4)
+					t.Fatalf("failed to fetch the []byte column v4. expected %v, got: %v", byteArray[i], v4)
 				}
 			} else if v4 != nil {
-				t.Fatalf("failed to fetch. expected %v, got: %v", byteArray[i], v4)
+				t.Fatalf("failed to fetch the []byte column v4. expected %v, got: %v", byteArray[i], v4)
 			}
 			cnt++
 		}
@@ -421,12 +440,64 @@ func TestBulkArrayBindingInterfaceDateTimeTimestamp(t *testing.T) {
 		dbt.mustExec(createTableSQLBulkArrayDateTimeTimestamp)
 		defer dbt.mustExec(deleteTableSQLBulkArrayDateTimeTimestamp)
 
-		_, err := dbt.db.Exec(insertSQLBulkArrayDateTimeTimestamp,
+		dbt.mustExec(insertSQLBulkArrayDateTimeTimestamp,
 			Array(&ntzArray, TimestampNTZType), Array(&ltzArray, TimestampLTZType),
 			Array(&tzArray, TimestampTZType), Array(&dtArray, DateType),
 			Array(&tmArray, TimeType))
-		if err == nil {
-			t.Fatal("Date, time and timestamp are not supported in array binding using []interface{}")
+
+		rows := dbt.mustQuery(selectAllSQLBulkArrayDateTimeTimestamp)
+		defer rows.Close()
+
+		var v0, v1, v2, v3, v4 sql.NullTime
+
+		cnt := 0
+		for i := 0; rows.Next(); i++ {
+			if err := rows.Scan(&v0, &v1, &v2, &v3, &v4); err != nil {
+				t.Fatal(err)
+			}
+			if v0.Valid {
+				if v0.Time.UnixNano() != ntzArray[i].(time.Time).UnixNano() {
+					t.Fatalf("failed to fetch the column v0. expected %v, got: %v", ntzArray[i], v0)
+				}
+			} else if ntzArray[i] != nil {
+				t.Fatalf("failed to fetch the column v0. expected %v, got: %v", ntzArray[i], v0)
+			}
+			if v1.Valid {
+				if v1.Time.UnixNano() != ltzArray[i].(time.Time).UnixNano() {
+					t.Fatalf("failed to fetch the column v1. expected %v, got: %v", ltzArray[i], v1)
+				}
+			} else if ltzArray[i] != nil {
+				t.Fatalf("failed to fetch the column v1. expected %v, got: %v", ltzArray[i], v1)
+			}
+			if v2.Valid {
+				if v2.Time.UnixNano() != tzArray[i].(time.Time).UnixNano() {
+					t.Fatalf("failed to fetch the column v2. expected %v, got: %v", tzArray[i], v2)
+				}
+			} else if tzArray[i] != nil {
+				t.Fatalf("failed to fetch the column v2. expected %v, got: %v", tzArray[i], v2)
+			}
+			if v3.Valid {
+				if v3.Time.Year() != dtArray[i].(time.Time).Year() ||
+					v3.Time.Month() != dtArray[i].(time.Time).Month() ||
+					v3.Time.Day() != dtArray[i].(time.Time).Day() {
+					t.Fatalf("failed to fetch the column v3. expected %v, got: %v", dtArray[i], v3)
+				}
+			} else if dtArray[i] != nil {
+				t.Fatalf("failed to fetch the column v3. expected %v, got: %v", dtArray[i], v3)
+			}
+			if v4.Valid {
+				if v4.Time.Hour() != tmArray[i].(time.Time).Hour() ||
+					v4.Time.Minute() != tmArray[i].(time.Time).Minute() ||
+					v4.Time.Second() != tmArray[i].(time.Time).Second() {
+					t.Fatalf("failed to fetch the column v4. expected %v, got: %v", tmArray[i], v4)
+				}
+			} else if tmArray[i] != nil {
+				t.Fatalf("failed to fetch the column v4. expected %v, got: %v", tmArray[i], v4)
+			}
+			cnt++
+		}
+		if cnt != len(ntzArray) {
+			t.Fatal("failed to query")
 		}
 	})
 	createDSN("UTC")
diff --git a/converter_test.go b/converter_test.go
index ee97c32..cef5e4f 100644
--- a/converter_test.go
+++ b/converter_test.go
@@ -71,6 +71,11 @@ func TestGoTypeToSnowflake(t *testing.T) {
 		{in: Array([]interface{}{true}), tmode: nullType, out: sliceType},
 		{in: Array([]interface{}{"teststring"}), tmode: nullType, out: sliceType},
 		{in: Array([]interface{}{[]byte{1, 2, 3}}), tmode: nullType, out: sliceType},
+		{in: Array([]interface{}{time.Now()}), tmode: timestampNtzType, out: sliceType},
+		{in: Array([]interface{}{time.Now()}), tmode: timestampTzType, out: sliceType},
+		{in: Array([]interface{}{time.Now()}), tmode: timestampLtzType, out: sliceType},
+		{in: Array([]interface{}{time.Now()}), tmode: dateType, out: sliceType},
+		{in: Array([]interface{}{time.Now()}), tmode: timeType, out: sliceType},
 		// negative
 		{in: 123, tmode: nullType, out: unSupportedType},
 		{in: int8(12), tmode: nullType, out: unSupportedType},
@@ -80,11 +85,6 @@ func TestGoTypeToSnowflake(t *testing.T) {
 		{in: uint64(456), tmode: nullType, out: unSupportedType},
 		{in: []byte{100}, tmode: nullType, out: unSupportedType},
 		{in: nil, tmode: nullType, out: unSupportedType},
-		{in: Array([]interface{}{time.Now()}), tmode: timestampNtzType, out: unSupportedType},
-		{in: Array([]interface{}{time.Now()}), tmode: timestampTzType, out: unSupportedType},
-		{in: Array([]interface{}{time.Now()}), tmode: timestampLtzType, out: unSupportedType},
-		{in: Array([]interface{}{time.Now()}), tmode: dateType, out: unSupportedType},
-		{in: Array([]interface{}{time.Now()}), tmode: timeType, out: unSupportedType},
 	}
 	for _, test := range testcases {
 		a := goTypeToSnowflake(test.in, test.tmode)