Internal error in CAST from Timestamp[us]

[ghost]

Describe the bug
The following error occurs in cast of timestamp.

Exception: Internal error: Unsupported CAST from Timestamp(Microsecond, None) to Int32. This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker

To Reproduce

import datafusion
ctx = datafusion.SessionContext()
ctx.register_parquet('nyc_taxi', 'nyc_taxi.parquet')
sql = '''
WITH a AS
   (SELECT
      tpep_pickup_datetime,
      tpep_dropoff_datetime,
      CAST(tpep_pickup_datetime AS INTEGER) AS pickup,
      CAST(tpep_dropoff_datetime AS INTEGER) AS dropoff
   FROM nyc_taxi)
SELECT
  CAST(AVG(dropoff - pickup) AS NUMERIC(5, 2))
FROM a
'''
df = ctx.sql(sql)
df.show()

---

Exception Traceback (most recent call last)
Input In [5], in
4 sql = '''
5 WITH a AS
6 (SELECT
(...)
14 FROM a
15 '''
16 df = ctx.sql(sql)
---> 17 df.show()

Exception: Internal error: Unsupported CAST from Timestamp(Microsecond, None) to Int32. This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker

Additional context
Latest version : datafusion 0.6.0 (python)

[waitingkuo]

there's no implementaton for casting timestamp to int32 in the current version. you can cast it to int64 for now (bigint)

[alamb]

The fact this is reported as an internal error is not ideal. I will change the error type

[alamb]

The root cause is that the arrow cast kernel doesn't support converting from timestamp --> int32 (as @waitingkuo mentions)

To fix this we çould extend the support support in https://github.com/apache/arrow-rs/blob/c7f7606/arrow/src/compute/kernels/cast.rs#L253-L254

[waitingkuo]

@alamb i think int32 isn't large enough for microsecond scale.
casting timestamp[us] to integer returns number of microseconds from 1970, but 2^32-1 microseonds is just too small.

❯ select to_timestamp_micros(4294967295);
+--------------------------------------+
| totimestampmicros(Int64(4294967295)) |
+--------------------------------------+
| 1970-01-01 01:11:34.967295           |
+--------------------------------------+
1 row in set. Query took 0.000 seconds.

[alamb]

So maybe a better error message is the best solution here

[ghost]

Thank you all.
I understand that CAST does not support timestamp to INT32/INT64.
The internal error message is misleading.

[waitingkuo]

@ike560 Cast does support timestamp to INT64, you can try CAST(tpep_pickup_datetime AS BIGINT)

[ghost]

Thanks @waitingkuo
I tried it, CAST to BIGINT was OK.

[ghost]

Close