SNOW-1510090: SNOW-1524103 Adding support for passing nil to variant columns

[sahilsalim99]

What is the current behavior?

My code
_, err = db.ExecContext(ctx, "INSERT INTO test_variant SELECT ?", nil)

The above results in

Error inserting into Snowflake: 002023 (22000): SQL compilation error:
Expression type does not match column data type, expecting VARIANT but got VARCHAR(16777216) for column VAR_COL

What is the desired behavior?

Being able to pass nil to variant columns while binding

How would this improve gosnowflake?

It would make it easier to work with variant columns.

The current workaround we're making use of involves wrapping it with a TRY_PARSE_JSON/PARSE_JSON :
_, err = db.ExecContext(ctx, "INSERT INTO test_variant SELECT TRY_PARSE_JSON(?)", nil)

[sfc-gh-dszmolka]

hi there - as an alternative, would it be possible to try something like this #831 (comment) ?

[sahilsalim99]

For our use case we do not have info on the data type of the columns we're trying to insert into. Hence it would be great if passing nil to a variant column would work as it does for other data types.

[sfc-gh-dszmolka]

tried one of the solutions for myself, which i suggested above

1. create table test_db.public.go1170 (c1 variant);

...
	insertQuery := "INSERT INTO test_db.public.go1170 (c1) SELECT (?)"
	
	fmt.Printf("Inserting VARIANT into table: %v\n", insertQuery)
	_, err = conn.ExecContext(ctx, insertQuery,
	//	nil, --> this is indeed mapped to TEXT
		sql.NullInt64{}, // --> this is not
	)
	if err != nil {
		log.Fatalf("failed to run the query. %v, err: %v", insertQuery, err)
	}

3. execute test script; relevant log snippets showing the input is not bound to TEXT (=VARCHAR) datatype anymore

INFO[0000]connection.go:301 gosnowflake.(*snowflakeConn).ExecContext Exec: "INSERT INTO test_db.public.go1170 (c1) SELECT (?)", [{ 1 {0 false}}] 
DEBU[0000]log.go:176 gosnowflake.(*defaultLogger).Debugf empty qcc                                    
INFO[0000]connection.go:118 gosnowflake.(*snowflakeConn).exec parameters: map[]                            
DEBU[0000]log.go:176 gosnowflake.(*defaultLogger).Debugf TYPE: sql.NullInt64, {0 false}               
INFO[0000]connection.go:127 gosnowflake.(*snowflakeConn).exec bindings: map[1:{FIXED <nil>  <nil>}]   

4. then query the table on Snowsight GUI

select * from test_db.public.go1170 ;

C1
--
<null>

I was able to insert null value into VARIANT column without needing to pass it to PARSE_JSON() first.
Would this help you ?

[sahilsalim99]

I was able to insert null value into VARIANT column without needing to pass it to PARSE_JSON() first. Would this help you ?

Currently we are inserting null in all other data types using nil.. would it be possible to replace nil with sql.NullInt64{} without running into any issues for all the other data types?

Also, are there plans to add support for nil binding for variant columns in the future?

[sfc-gh-pfus]

Hi @sahilsalim99 ! Variants are quite specific, because they can contain anything. It can be a string, an int or an object. If you just want to insert nil, we don't know what type is required.
We are planning to add support to binding nil variants, but this is still going to be something like:

sql.Exec("INSERT INTO test_variant VALUES (?)", DataTypeNilVariant, reflect.TypeOf(1))
sql.Exec("INSERT INTO test_variant VALUES (?)", DataTypeNilVariant, reflect.TypeOf(""))