search

goccy / bigquery-emulator

BigQuery emulator server implemented in Go
MIT License
743 stars 86 forks source link

Queries on TIMESTAMP columns are not handled correctly when no timezone is specified #279

Open prismec opened 2 months ago

prismec commented 2 months ago

What happened?

Given that table my_table with a TIMESTAMP column t, the query SELECT * FROM my_table WHERE t = "<timestamp value>" returns no results if <timestamp value> does not contain a timezone, e.g. 2023-08-01T01:00:00.000000.

Using a timezone, e.g. 2023-08-01T01:00:00.000000Z works as expected.

What did you expect to happen?

<timestamp value> 2023-08-01T01:00:00.000000 is equal to 2023-08-01T01:00:00.000000Z.

See also canonical format of the TIMESTAMP datatype: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type

_[timezone]: String representing the time zone. When a time zone is not explicitly specified, the default time zone, UTC, is used.

How can we reproduce it (as minimally and precisely as possible)?

  1. INSERT INTO my_table (t) VALUES (2023-08-01T01:00:00.000000Z`)
  2. SELECT * FROM my_table WHERE t= "2023-08-01T01:00:00.000000"

Anything else we need to know?

No response

ohaibbq commented 1 month ago

It seems that this will require a change to the Go ZetaSQL bindings. I'm not familiar with that process, @goccy could you take a look? https://github.com/goccy/go-zetasql/issues/27