No way to add a row with different typed columns through gosnowflake driver

[madisonchamberlain]

Hello, I am trying to add a row to a table with different column types. It doesn't seem to work. I believe the issue is due to the fact that if you want to specify a type, all of the types like DataTypeText for example are just bytes under the hood, and if no type is specified the driver will assume that all variables are of the same type. Here is a test which can be used to reproduce:

const (
	// define these out here so they can be used through the test
	ID_COL_NAME       = "ID"
	SEQ_NUM_COL_NAME  = "SEQ_NUM"
	TEXT_COL_NAME     = "TEXT_COL"
)

// specify your test db, schema and some test table name
func TestSnowflakeSupportsAddingOneRowWithDifferentTypes(t *testing.T, databaseName string, schemaName string, tableName string) {
	ctx := context.Background()

	tablePath := strings.Join([]string{databaseName, schemaName, tableName}, ".")

	// Do what you need to do to get a sql connection here... this is what we need to do 
	config := readTestConfig(t, "snowflake")
	poolCache := wh.NewPoolCache()

	c, err := config.NewConnector(ctx, poolCache)
	require.NoError(t, err)
	sfConnector, isSfConnector := c.(*connector)
	require.True(t, isSfConnector, "connector is not a snowflake connector ")

	conn, err := sfConnector.pool.AcquireConn(ctx)
	require.NoError(t, err)

	// create a table with the initial two columns having binary types 
	createTableQuery := fmt.Sprintf(
		"CREATE TABLE IF NOT EXISTS %s ("+
			"%s BINARY(16) NOT NULL PRIMARY KEY, "+ // 16-byte UUID
			"%s BINARY NOT NULL"+ // variable-length binary string
			")",
		tablePath,
		ID_COL_NAME,
		SEQ_NUM_COL_NAME,
	)
	
	// execute the query 
	_, err = conn.Exec(ctx, query.NewQuery(createTableQuery), nil)
	require.NoError(t, err, "error creating initial table")

	// Add another column which is of type text 
	columnType := "string"
	addColQuery := fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s %s", tablePath, TEXT_COL_NAME, columnType)

	// execute the query 
	_, err = conn.Exec(ctx, query.NewQuery(addColQuery), nil)
	require.NoError(t, err, "error adding string column")

	sql, args :=  generateQueryWithNoTypes(tablePath)
	_, err = conn.Exec(ctx, sql, args...) 
	if err != nil {
		sql, args := generateQueryWithWithTypes(tablePath)
		_, newErr := conn.Exec(ctx, sql, args...) 
		require.NoError(t, newErr, "both options failed :(, other error is %s", err)
	}

}

func mkUuidBytes() []byte {
	u := uuid.New()
	return u[:]
}


func generateQueryWithNoTypes(tablePath string) (string, []interface{}) {
	colNames := []string{
		ID_COL_NAME,
		SEQ_NUM_COL_NAME,
		TEXT_COL_NAME,
	}

	// This will be passed directly to the query, so it will contain both type args and values
	colArgs := []interface{}{
		mkUuidBytes(),    // data to add to ID row
		[]byte{byte(10)}, // data to add to seqNumRow
		"0 2",            // data to add to text col
	}

	colQuestionMarks := make([]string, len(colNames))
	for i := range colNames {
		colQuestionMarks[i] = "?"
	}

	sql := fmt.Sprintf(
		"INSERT INTO %s (%s) VALUES (%s)",
		tablePath,
		strings.Join(colNames, ","),
		strings.Join(colQuestionMarks, ","),
	)

	return sql, colArgs
}

func generateQueryWithWithTypes(tablePath string) (string, []interface{}) {
	colNames := []string{
		ID_COL_NAME,
		SEQ_NUM_COL_NAME,
		TEXT_COL_NAME,
	}

	// This will be passed directly to the query, so it will contain both type args and values
	colArgs := []interface{}{
		gosnowflake.DataTypeBinary,
		mkUuidBytes(),    // data to add to ID row
		gosnowflake.DataTypeBinary,
		[]byte{byte(10)}, // data to add to seqNumRow
		gosnowflake.DataTypeText,
		"0 2",            // data to add to text col
	}

	colQuestionMarks := make([]string, len(colNames))
	for i := range colNames {
		colQuestionMarks[i] = "?"
	}

	sql := fmt.Sprintf(
		"INSERT INTO %s (%s) VALUES (%s)",
		tablePath,
		strings.Join(colNames, ","),
		strings.Join(colQuestionMarks, ","),
	)

	return sql, colArgs
}

I think that what would work as a solution is if you wrapped all of the publicly exposed dataTypes in datatype.go with something like this

// SnowflakeDataType is the type used by clients to explicitly indicate the type
// of an argument to Exec/QueryContext. We use a separate public-facing
// type rather than a Go primitive type so that we can always differentiate
// between args that indicate type and args that are values. Otherwise snowflake
// will default to thinking the data is actually bytes
type SnowflakeDataType []byte

var (
	// DataTypeFixed is a FIXED datatype.
	DataTypeFixed = SnowflakeDataType{fixedType.Byte()}
	// DataTypeReal is a REAL datatype.
	DataTypeReal = SnowflakeDataType{realType.Byte()}
	// DataTypeText is a TEXT datatype.
	DataTypeText = SnowflakeDataType{textType.Byte()}
	// DataTypeDate is a Date datatype.
	DataTypeDate = SnowflakeDataType{dateType.Byte()}
	// DataTypeVariant is a TEXT datatype.
	DataTypeVariant = SnowflakeDataType{variantType.Byte()}
	// DataTypeTimestampLtz is a TIMESTAMP_LTZ datatype.
	DataTypeTimestampLtz = SnowflakeDataType{timestampLtzType.Byte()}
	// DataTypeTimestampNtz is a TIMESTAMP_NTZ datatype.
	DataTypeTimestampNtz = SnowflakeDataType{timestampNtzType.Byte()}
	// DataTypeTimestampTz is a TIMESTAMP_TZ datatype.
	DataTypeTimestampTz = SnowflakeDataType{timestampTzType.Byte()}
	// DataTypeObject is a OBJECT datatype.
	DataTypeObject = SnowflakeDataType{objectType.Byte()}
	// DataTypeArray is a ARRAY datatype.
	DataTypeArray = SnowflakeDataType{arrayType.Byte()}
	// DataTypeBinary is a BINARY datatype.
	DataTypeBinary = SnowflakeDataType{binaryType.Byte()}
	// DataTypeTime is a TIME datatype.
	DataTypeTime = SnowflakeDataType{timeType.Byte()}
	// DataTypeBoolean is a BOOLEAN datatype.
	DataTypeBoolean = SnowflakeDataType{booleanType.Byte()}
	// DataTypeNull is a NULL datatype.
	DataTypeNull = SnowflakeDataType{nullType.Byte()}
)

Then if there were a function like this

func clientTypeToInternal(cType SnowflakeDataType) (iType snowflakeType, err error) {
	if cType != nil {
		switch {
		case cType.Equals(DataTypeFixed):
			iType = fixedType
		case cType.Equals(DataTypeReal):
			iType = realType
		case cType.Equals(DataTypeText):
			iType = textType
		case cType.Equals(DataTypeDate):
			iType = dateType
		case cType.Equals(DataTypeVariant):
			iType = variantType
		case cType.Equals(DataTypeTimestampLtz):
			iType = timestampLtzType
		case cType.Equals(DataTypeTimestampNtz):
			iType = timestampNtzType
		case cType.Equals(DataTypeTimestampTz):
			iType = timestampTzType
		case cType.Equals(DataTypeObject):
			iType = objectType
		case cType.Equals(DataTypeArray):
			iType = arrayType
		case cType.Equals(DataTypeBinary):
			iType = binaryType
		case cType.Equals(DataTypeTime):
			iType = timeType
		case cType.Equals(DataTypeBoolean):
			iType = booleanType
		case cType.Equals(DataTypeNull):
			iType = nullType
		default:
			return nullType, fmt.Errorf(errMsgInvalidByteArray, ([]byte)(cType))
		}
	} else {
		return nullType, fmt.Errorf(errMsgInvalidByteArray, nil)
	}
	return iType, nil
}

which could be called in goTypeToSnowflake I think it would do the trick. Maybe there is a work around, but I cant find any way to do this

[sfc-gh-dszmolka]

(this is in progress on a different forum, will update with the outcome)

[madisonchamberlain]

Closing this one, the issue turns out to be an issue dealing with [][]byte. I will open a new issue