goccy / go-zetasqlite

A database driver library that interprets ZetaSQL queries and runs them using SQLite3
MIT License
56 stars 29 forks source link

`CAST(string AS DATETIME)` incorrectly parses integer strings as date #175

Closed ohaibbq closed 8 months ago

ohaibbq commented 9 months ago

Expected

bq query --nouse_legacy_sql --format prettyjson --project_id recidiviz-bq-emulator-project 'SELECT CAST("20100317" AS DATETIME);'
Invalid datetime string "20100317"

Actual

When using the string in a literal, the analyzer correctly fails to process the statement:

bq query --nouse_legacy_sql --format prettyjson --project_id recidiviz-bq-emulator-project 'SELECT CAST("20100317" AS DATETIME);'
failed to analyze: INVALID_ARGUMENT: Could not cast literal "20100317" to type DATETIME [at 1:13]

However, when using CAST() on non-literals, the value is interpreted as epoch seconds:

bq query --api "http://0.0.0.0:9050" --nouse_legacy_sql --format prettyjson --project_id recidiviz-bq-emulator-project 'SELECT CAST(SPLIT("20100317", ",")[OFFSET(0)] AS DATETIME);'
[
  {
    "$col1": "1970-08-21T15:25:17"
  }
]
ohaibbq commented 9 months ago

This behavior was added to fix goccy/bigquery-emulator#90. We should move the formatting of input data into the bigquery-emulator insert handler instead.