Repository: snowflakedb/gosnowflake
Pull request #629 (2022-07-28 01:06:57)

SNOW-590709 Support for using interface slice []interface{} in array binding
### Description
SNOW-590709 (Simba incident 00383422)

The driver currently supports inserting NULL one by one but not in batch (i.e. array binding). Also, NULL cannot be inserted in arrays like []int and []string, so user will need to use the interface slice []interface{}.

This PR has added support for using interface slice []interface{} in array binding so that NULL can be inserted in batch.
Data types supported: INTEGER, FLOAT, STRING, BOOLEAN, BINARY
Not supported: DATE, TIME, TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ

### 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 1cb9fb8..9894fbf 100644
--- a/bind_uploader.go
+++ b/bind_uploader.go
@@ -10,6 +10,7 @@ import (
 	"reflect"
 	"strconv"
 	"strings"
+	"time"
 )
 
 const (
@@ -126,13 +127,17 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 	_, column := snowflakeArrayToString(&columns[0], true)
 	numRows := len(column)
 	csvRows := make([][]byte, 0)
-	rows := make([][]string, 0)
+	rows := make([][]interface{}, 0)
 	for rowIdx := 0; rowIdx < numRows; rowIdx++ {
-		rows = append(rows, make([]string, numColumns))
+		rows = append(rows, make([]interface{}, numColumns))
 	}
 
 	for rowIdx := 0; rowIdx < numRows; rowIdx++ {
-		rows[rowIdx][0] = *column[rowIdx]
+		if column[rowIdx] == nil {
+			rows[rowIdx][0] = column[rowIdx]
+		} else {
+			rows[rowIdx][0] = *column[rowIdx]
+		}
 	}
 	for colIdx := 1; colIdx < numColumns; colIdx++ {
 		_, column = snowflakeArrayToString(&columns[colIdx], true)
@@ -145,7 +150,12 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 			}).exceptionTelemetry(bu.sc)
 		}
 		for rowIdx := 0; rowIdx < numRows; rowIdx++ {
-			rows[rowIdx][colIdx] = *column[rowIdx] // length of column = number of rows
+			// length of column = number of rows
+			if column[rowIdx] == nil {
+				rows[rowIdx][colIdx] = column[rowIdx]
+			} else {
+				rows[rowIdx][colIdx] = *column[rowIdx]
+			}
 		}
 	}
 	for _, row := range rows {
@@ -154,14 +164,19 @@ func (bu *bindUploader) buildRowsAsBytes(columns []driver.NamedValue) ([][]byte,
 	return csvRows, nil
 }
 
-func (bu *bindUploader) createCSVRecord(data []string) []byte {
+func (bu *bindUploader) createCSVRecord(data []interface{}) []byte {
 	var b strings.Builder
 	b.Grow(1024)
 	for i := 0; i < len(data); i++ {
 		if i > 0 {
 			b.WriteString(",")
 		}
-		b.WriteString(escapeForCSV(data[i]))
+		value, ok := data[i].(string)
+		if ok {
+			b.WriteString(escapeForCSV(value))
+		} else if !reflect.ValueOf(data[i]).IsNil() {
+			logger.Debugf("Cannot convert value to string in createCSVRecord. value: %v", data[i])
+		}
 	}
 	b.WriteString("\n")
 	return []byte(b.String())
@@ -276,6 +291,28 @@ func supportedArrayBind(nv *driver.NamedValue) bool {
 		return false
 	default:
 		// TODO SNOW-176486 variant, object, array
+
+		// 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
+						}
+					}
+				}
+				return true
+			}
+		}
+
 		return false
 	}
 }
diff --git a/converter.go b/converter.go
index 94716a8..f334013 100644
--- a/converter.go
+++ b/converter.go
@@ -748,7 +748,78 @@ func snowflakeArrayToString(nv *driver.NamedValue, stream bool) (snowflakeType,
 			arr = append(arr, &v)
 		}
 	default:
-		return unSupportedType, nil
+		// 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)
+			}
+		} else {
+			return unSupportedType, nil
+		}
+	}
+	return t, arr
+}
+
+func interfaceSliceToString(interfaceSlice reflect.Value, interfaceSliceLen int, stream bool) (snowflakeType, []*string) {
+	var t snowflakeType
+	var arr []*string
+
+	for i := 0; i < interfaceSliceLen; i++ {
+		val := interfaceSlice.Index(i)
+		if val.CanInterface() {
+			switch val.Interface().(type) {
+			case int:
+				t = fixedType
+				x := val.Interface().(int)
+				v := strconv.Itoa(x)
+				arr = append(arr, &v)
+			case int32:
+				t = fixedType
+				x := val.Interface().(int32)
+				v := strconv.Itoa(int(x))
+				arr = append(arr, &v)
+			case int64:
+				t = fixedType
+				x := val.Interface().(int64)
+				v := strconv.FormatInt(x, 10)
+				arr = append(arr, &v)
+			case float32:
+				t = realType
+				x := val.Interface().(float32)
+				v := fmt.Sprintf("%g", x)
+				arr = append(arr, &v)
+			case float64:
+				t = realType
+				x := val.Interface().(float64)
+				v := fmt.Sprintf("%g", x)
+				arr = append(arr, &v)
+			case bool:
+				t = booleanType
+				x := val.Interface().(bool)
+				v := strconv.FormatBool(x)
+				arr = append(arr, &v)
+			case string:
+				t = textType
+				x := val.Interface().(string)
+				arr = append(arr, &x)
+			case []byte:
+				t = binaryType
+				x := val.Interface().([]byte)
+				v := hex.EncodeToString(x)
+				arr = append(arr, &v)
+			case time.Time:
+				// Support for bulk array binding insertion using []interface{}
+				return unSupportedType, nil
+			default:
+				if val.Interface() != nil {
+					return unSupportedType, nil
+				}
+
+				arr = append(arr, nil)
+			}
+		}
 	}
 	return t, arr
 }
diff --git a/doc.go b/doc.go
index cfc9fc5..37688c7 100644
--- a/doc.go
+++ b/doc.go
@@ -414,6 +414,36 @@ binds arrays to the parameters in the INSERT statement.
 	// Insert the data from the arrays and wrap in an Array() function into the table.
 	_, err = db.Exec("insert into my_table values (?, ?, ?, ?)", Array(&intArray), Array(&fltArray), Array(&boolArray), Array(&strArray))
 
+If the array contains SQL NULL values, use slice []interface{}, which allows Golang nil values.
+This feature is available in version 1.6.12 (and later) of the driver. For exmaple,
+
+ 	// Define the arrays containing the data to insert.
+ 	strArray := make([]interface{}, 3)
+	strArray[0] = "test1"
+	strArray[1] = "test2"
+	strArray[2] = nil // This line is optional as nil is the default value.
+	...
+	// Create a table and insert the data from the array as shown above.
+	_, err = db.Exec("create or replace table my_table(c1 string)")
+	_, err = db.Exec("insert into my_table values (?)", Array(&strArray))
+	...
+	// Use sql.NullString to fetch the string column that contains NULL values.
+	var s sql.NullString
+	rows, _ := db.Query("select * from my_table")
+	for rows.Next() {
+		err := rows.Scan(&s)
+		if err != nil {
+			log.Fatalf("Failed to scan. err: %v", err)
+		}
+		if s.Valid {
+			fmt.Println("Retrieved value:", s.String)
+		} else {
+			fmt.Println("Retrieved value: NULL")
+		}
+	}
+
+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.
+
 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 182d75b..95f8cf3 100644
--- a/bindings_test.go
+++ b/bindings_test.go
@@ -5,6 +5,7 @@ package gosnowflake
 import (
 	"bytes"
 	"context"
+	"database/sql"
 	"fmt"
 	"math/big"
 	"math/rand"
@@ -20,6 +21,18 @@ const (
 	deleteTableSQL = "drop table if exists TEST_PREP_STATEMENT"
 	insertSQL      = "insert into TEST_PREP_STATEMENT values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
 	selectAllSQL   = "select * from TEST_PREP_STATEMENT ORDER BY 1"
+
+	createTableSQLBulkArray = `create or replace table test_bulk_array(c1 INTEGER,
+		c2 FLOAT, c3 BOOLEAN, c4 STRING, C5 BINARY)`
+	deleteTableSQLBulkArray = "drop table if exists test_bulk_array"
+	insertSQLBulkArray      = "insert into test_bulk_array values(?, ?, ?, ?, ?)"
+	selectAllSQLBulkArray   = "select * from test_bulk_array ORDER BY 1"
+
+	createTableSQLBulkArrayDateTimeTimestamp = `create or replace table test_bulk_array_DateTimeTimestamp(
+		C1 TIMESTAMP_NTZ, C2 TIMESTAMP_LTZ, C3 TIMESTAMP_TZ, C4 DATE, C5 TIME)`
+	deleteTableSQLBulkArrayDateTimeTimestamp = "drop table if exists test_bulk_array_DateTimeTimestamp"
+	insertSQLBulkArrayDateTimeTimestamp      = "insert into test_bulk_array_DateTimeTimestamp values(?, ?, ?, ?, ?)"
+	selectAllSQLBulkArrayDateTimeTimestamp   = "select * from test_bulk_array_DateTimeTimestamp ORDER BY 1"
 )
 
 func TestBindingFloat64(t *testing.T) {
@@ -246,6 +259,179 @@ func TestBindingInterfaceString(t *testing.T) {
 	})
 }
 
+func TestBulkArrayBindingInterfaceNil(t *testing.T) {
+	nilArray := make([]interface{}, 1)
+
+	runTests(t, dsn, func(dbt *DBTest) {
+		dbt.mustExec(createTableSQLBulkArray)
+		defer dbt.mustExec(deleteTableSQLBulkArray)
+
+		dbt.mustExec(insertSQLBulkArray, Array(&nilArray), Array(&nilArray),
+			Array(&nilArray), Array(&nilArray), Array(&nilArray))
+		rows := dbt.mustQuery(selectAllSQLBulkArray)
+		defer rows.Close()
+
+		var v0 sql.NullInt32
+		var v1 sql.NullFloat64
+		var v2 sql.NullBool
+		var v3 sql.NullString
+		var v4 []byte
+
+		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 {
+				t.Fatalf("failed to fetch the INTEGER 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)
+			}
+			if v2.Valid {
+				t.Fatalf("failed to fetch the BOOLEAN column. 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)
+			}
+			if v4 != nil {
+				t.Fatalf("failed to fetch the BINARY column. expected %v, got: %v", nilArray[i], v4)
+			}
+			cnt++
+		}
+		if cnt != len(nilArray) {
+			t.Fatal("failed to query")
+		}
+	})
+}
+
+func TestBulkArrayBindingInterface(t *testing.T) {
+	intArray := make([]interface{}, 3)
+	intArray[0] = int32(100)
+	intArray[1] = int32(200)
+
+	fltArray := make([]interface{}, 3)
+	fltArray[0] = float64(0.1)
+	fltArray[2] = float64(5.678)
+
+	boolArray := make([]interface{}, 3)
+	boolArray[1] = false
+	boolArray[2] = true
+
+	strArray := make([]interface{}, 3)
+	strArray[2] = "test3"
+
+	byteArray := make([]interface{}, 3)
+	byteArray[0] = []byte{0x01, 0x02, 0x03}
+	byteArray[2] = []byte{0x07, 0x08, 0x09}
+
+	runTests(t, dsn, func(dbt *DBTest) {
+		dbt.mustExec(createTableSQLBulkArray)
+		defer dbt.mustExec(deleteTableSQLBulkArray)
+
+		dbt.mustExec(insertSQLBulkArray, Array(&intArray), Array(&fltArray),
+			Array(&boolArray), Array(&strArray), Array(&byteArray))
+		rows := dbt.mustQuery(selectAllSQLBulkArray)
+		defer rows.Close()
+
+		var v0 sql.NullInt32
+		var v1 sql.NullFloat64
+		var v2 sql.NullBool
+		var v3 sql.NullString
+		var v4 []byte
+
+		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.Int32 != intArray[i] {
+					t.Fatalf("failed to fetch. 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)
+			}
+			if v1.Valid {
+				if v1.Float64 != fltArray[i] {
+					t.Fatalf("failed to fetch. 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)
+			}
+			if v2.Valid {
+				if v2.Bool != boolArray[i] {
+					t.Fatalf("failed to fetch. 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)
+			}
+			if v3.Valid {
+				if v3.String != strArray[i] {
+					t.Fatalf("failed to fetch. 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)
+			}
+			if byteArray[i] != nil {
+				if !bytes.Equal(v4, byteArray[i].([]byte)) {
+					t.Fatalf("failed to fetch. expected %v, got: %v", byteArray[i], v4)
+				}
+			} else if v4 != nil {
+				t.Fatalf("failed to fetch. expected %v, got: %v", byteArray[i], v4)
+			}
+			cnt++
+		}
+		if cnt != len(intArray) {
+			t.Fatal("failed to query")
+		}
+	})
+}
+
+func TestBulkArrayBindingInterfaceDateTimeTimestamp(t *testing.T) {
+	tz := time.Now()
+	createDSN(PSTLocation)
+
+	now := time.Now()
+	loc, err := time.LoadLocation(PSTLocation)
+	if err != nil {
+		t.Error(err)
+	}
+	ntzArray := make([]interface{}, 3)
+	ntzArray[0] = now
+	ntzArray[1] = now.Add(1)
+
+	ltzArray := make([]interface{}, 3)
+	ltzArray[1] = now.Add(2).In(loc)
+	ltzArray[2] = now.Add(3).In(loc)
+
+	tzArray := make([]interface{}, 3)
+	tzArray[0] = tz.Add(4).In(loc)
+	tzArray[2] = tz.Add(5).In(loc)
+
+	dtArray := make([]interface{}, 3)
+	dtArray[0] = tz.Add(6).In(loc)
+	dtArray[1] = now.Add(7).In(loc)
+
+	tmArray := make([]interface{}, 3)
+	tmArray[1] = now.Add(8).In(loc)
+	tmArray[2] = now.Add(9).In(loc)
+
+	runTests(t, dsn, func(dbt *DBTest) {
+		dbt.mustExec(createTableSQLBulkArrayDateTimeTimestamp)
+		defer dbt.mustExec(deleteTableSQLBulkArrayDateTimeTimestamp)
+
+		_, err := dbt.db.Exec(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{}")
+		}
+	})
+	createDSN("UTC")
+}
+
 // TestBindingArray tests basic array binding via the usage of the Array
 // function that converts the passed Golang slice to a Snowflake array type
 func TestBindingArray(t *testing.T) {
@@ -456,3 +642,64 @@ func TestBulkArrayMultiPartBindingInt(t *testing.T) {
 		dbt.mustExec("DROP TABLE binding_test")
 	})
 }
+
+func TestBulkArrayMultiPartBindingWithNull(t *testing.T) {
+	runTests(t, dsn, func(dbt *DBTest) {
+		dbt.mustExec("create or replace table binding_test (c1 integer, c2 string)")
+		startNum := 1000000
+		endNum := 2000000
+		numRows := endNum - startNum
+
+		// Define the integer and string arrays
+		intArr := make([]interface{}, numRows)
+		stringArr := make([]interface{}, numRows)
+		for i := startNum; i < endNum; i++ {
+			intArr[i-startNum] = i
+			stringArr[i-startNum] = fmt.Sprint(i)
+		}
+
+		// Set some of the rows to NULL
+		intArr[numRows-1] = nil
+		intArr[numRows-2] = nil
+		intArr[numRows-3] = nil
+		stringArr[1] = nil
+		stringArr[2] = nil
+		stringArr[3] = nil
+
+		_, err := dbt.db.Exec("insert into binding_test values (?, ?)", Array(&intArr), Array(&stringArr))
+		if err != nil {
+			t.Errorf("Should have succeeded to insert. err: %v", err)
+		}
+
+		rows := dbt.mustQuery("select * from binding_test order by c1,c2")
+		cnt := startNum
+		var i sql.NullInt32
+		var s sql.NullString
+		for rows.Next() {
+			if err := rows.Scan(&i, &s); err != nil {
+				t.Fatal(err)
+			}
+			// Verify integer column c1
+			if i.Valid {
+				if int(i.Int32) != intArr[cnt-startNum] {
+					t.Fatalf("expected: %v, got: %v", cnt, int(i.Int32))
+				}
+			} else if !(cnt == startNum+numRows-1 || cnt == startNum+numRows-2 || cnt == startNum+numRows-3) {
+				t.Fatalf("expected NULL in column c1 at index: %v", cnt-startNum)
+			}
+			// Verify string column c2
+			if s.Valid {
+				if s.String != stringArr[cnt-startNum] {
+					t.Fatalf("expected: %v, got: %v", cnt, s.String)
+				}
+			} else if !(cnt == startNum+1 || cnt == startNum+2 || cnt == startNum+3) {
+				t.Fatalf("expected NULL in column c2 at index: %v", cnt-startNum)
+			}
+			cnt++
+		}
+		if cnt != endNum {
+			t.Fatalf("expected %v rows, got %v", numRows, (cnt - startNum))
+		}
+		dbt.mustExec("DROP TABLE binding_test")
+	})
+}
diff --git a/converter_test.go b/converter_test.go
index 728bfd2..ee97c32 100644
--- a/converter_test.go
+++ b/converter_test.go
@@ -65,6 +65,12 @@ func TestGoTypeToSnowflake(t *testing.T) {
 		{in: time.Now(), tmode: timestampTzType, out: timestampTzType},
 		{in: time.Now(), tmode: timestampLtzType, out: timestampLtzType},
 		{in: []byte{1, 2, 3}, tmode: binaryType, out: binaryType},
+		{in: []int{1}, tmode: nullType, out: sliceType},
+		{in: Array([]interface{}{int64(123)}), tmode: nullType, out: sliceType},
+		{in: Array([]interface{}{float64(234.56)}), tmode: nullType, out: sliceType},
+		{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},
 		// negative
 		{in: 123, tmode: nullType, out: unSupportedType},
 		{in: int8(12), tmode: nullType, out: unSupportedType},
@@ -74,7 +80,11 @@ 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: []int{1}, 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)