Emulator does not infer DATE & TIMESTAMP type for null values

[larkee]

Originally raised in googleapis/python-spanner-django#566, Spanner will infer the type of a null value if it is not provided but the emulator will throw an error for the same request. This has become an issue for Python ORMs as the suggestion to add this type inference logic to the DBAPI adapter is complicated and requires many changes.

Adding type inference for null values to the emulator to match the production behaviour would resolve the problem and allow us to confidently test against the emulator again.

[snehashah16]

Hello @larkee @olavloite

I was trying to debug this issue and would like some steps to reproduce this issue. I tried the following tests and have got a successful run from the emulator. Can u please provide the test case, logs ( --log_requests CLI argument to the emulator) and the error message.

Python Test:

1. Parameterized DML with some types defined: Passes

update_statement = (
            "UPDATE contacts SET email = @email " "WHERE contact_id = @contact_id;",
            {"contact_id": 1, "email": None},
            {"contact_id": Type(code=INT64),},
        )

Go Test: PASSES, except for DATES, TIMESTAMP

	tests := []struct {
		col  string
		val  interface{}
		want interface{}
	}{
		{col: "String", val: nil, want: NullString{}},
		{col: "StringArray", val: nil, want: []NullString(nil)},
		{col: "Bytes", val: nil, want: []byte(nil)},
		{col: "BytesArray", val: nil, want: [][]byte(nil)},
		{col: "Int64a", val: nil, want: NullInt64{}},
		{col: "Int64Array", val: nil, want: []NullInt64(nil)},
		{col: "Bool", val: nil, want: NullBool{}},
		{col: "BoolArray", val: nil, want: []NullBool(nil)},
		{col: "Float64", val: nil, want: NullFloat64{}},
		{col: "Float64Array", val: nil, want: []NullFloat64(nil)},
		// FAILS	{col: "Date", val: nil, want: NullDate{}},
		// FAILS	{col: "DateArray", val: nil, want: []NullDate(nil)},
		// FAILS	{col: "Timestamp", val: nil, want: NullTime{}},
		// FAILS	{col: "TimestampArray", val: nil, want: []NullTime(nil)},
	}
        statements := make([]Statement, 0)
	for i, test := range tests {
		stmt := NewStatement(fmt.Sprintf("INSERT INTO Types (RowId, `%s`) VALUES (@id, @value)", test.col))
		// Note: We are not setting the parameter type here to ensure that it
		// can be automatically recognized when it is actually needed.
		stmt.Params["id"] = i
		stmt.Params["value"] = test.val
		log.Println("Statement: %v", stmt)
		statements = append(statements, stmt)
	}
	_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, tx *ReadWriteTransaction) error {
		rowCounts, err := tx.BatchUpdate(ctx, statements)
        })

[olavloite]

@snehashah16 I think your conclusion that this only fails for DATE and TIMESTAMP is correct, and that the initial issue report missed that, simply because the example statements included these types and it was wrongly assumed that it applied to all types. I can confirm that untyped null parameter values work on the emulator for all types (including NUMERIC and JSON), except for DATE and TIMESTAMP.

Interestingly enough that also means that ARRAY<TIMESTAMP> and ARRAY<DATE> also work with untyped null values, so it is really only the base types DATE and TIMESTAMP that fail.

[snehashah16]

Thanks for confirming @olavloite .. Could we please uncomment the Golang tests for the untyped parameters for the emulator test run.

I will keep this bug open for DATE and TIMESTAMP.

[jakehilton]

I've just hit this issue as well. Seems less of an enhancement request and more of a straight up bug. Curious if any progress has been made on this front. Here's to hoping! Thanks!

[mohitg1580]

@jakehilton - we are looking into this issue, and will give you an ETA as soon as we have it.

[gauravpurohit06]

We have made the bug fixes related to this issue in the latest release. Please verify.

[olavloite]

The tests in googleapis/java-spanner#2388 verify that this issue has now been fixed in 1.5.3 of the emulator.