question: Can't cast NULL TIMESTAMP with dialect="bigquery"

[LennartKloppenburg]

What happened?

>>> ibis.to_sql(ibis.literal(None).cast("timestamp"), dialect='bigquery')
SQLString('SELECT\n  CAST(NULL AS DATETIME) AS `Cast_None_timestamp`')
>>> ibis.to_sql(ibis.null().cast("timestamp"), dialect='bigquery')
SQLString('SELECT\n  CAST(NULL AS DATETIME) AS `Cast_None_timestamp`')

I would expect the following statements to output a CAST(NULL AS TIMESTAMP). I found issues related to BigQuery's TIMESTAMP and DATETIME columns but I really need a CAST(NULL AS TIMESTAMP) because I'm using it in a .union(...) where the types need to match.

What version of ibis are you using?

10.0.0.dev459

What backend(s) are you using, if any?

BigQuery

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Thanks for the issue!

1. timestamp is a timestamp without a timezone, which corresponds to BigQuery's DATETIME type.
2. timestamp("UTC") is a timestamp with the UTC timezone, which corresponds to BigQuery's TIMESTAMP type.

Does that help?

[cpcloud]

Here are some examples of what you're trying to do:

In [1]: import ibis

In [2]: ibis.to_sql(ibis.literal(None).cast("timestamp"), dialect='bigquery')
Out[2]:
SELECT
  CAST(NULL AS DATETIME) AS `Cast_None_timestamp`

In [3]: ibis.to_sql(ibis.literal(None).cast("timestamp('UTC')"), dialect='bigquery')
Out[3]:
SELECT
  CAST(NULL AS TIMESTAMP) AS `Cast_None_timestamp_'UTC'`

In [4]: ibis.to_sql(ibis.null().cast("timestamp"), dialect='bigquery')
Out[4]:
SELECT
  CAST(NULL AS DATETIME) AS `Cast_None_timestamp`

In [5]: ibis.to_sql(ibis.null().cast("timestamp('UTC')"), dialect='bigquery')
Out[5]:
SELECT
  CAST(NULL AS TIMESTAMP) AS `Cast_None_timestamp_'UTC'`

[cpcloud]

Closing this out for now!

[LennartKloppenburg]

@cpcloud Cheers Phillip! That did it :) Thanks for coming back to me so quickly.