Closed
Labels
bugSomething isn't workingSomething isn't working
Description
Describe the bug
Timestamp literal conversion fails to be created from extreme values.
To Reproduce
❯ SELECT to_timestamp(-62125747200);
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Compute error: Overflow happened on: -62125747200 * 1000000000
❯ select to_timestamp(1926632005177);
Optimizer rule 'simplify_expressions' failed
caused by
Arrow error: Compute error: Overflow happened on: 1926632005177 * 1000000000
Expected behavior
The cast should happen
PG returns
0001-04-25T00:00:00 +63022-07-16T12:59:37
respectfully
Additional context
Part of #8282
Metadata
Metadata
Assignees
Labels
bugSomething isn't workingSomething isn't working
Activity
comphead commentedon Nov 27, 2023
The overflow happens because Datafusion treats underlying
i64as nanoseconds which is obviously not big enough.Other sql engines like PG/Spark/DuckDB treat
i64as microsecond.Parquet was using deprecated
i96datatype to fit the value.Some evident options are:
i64as microsecond, the similar way PG, Spark, DuckDB works. This might involve huge work, as all timestamp conversions, functions expects nanoseconds.i128instead ofi64by modifying the code or by conditional compilation.@alamb @waitingkuo @viirya @tustvold would be nice to hear from you
tustvold commentedon Nov 27, 2023
I'm confused by this statement, DataFusion follows the arrow data model which has various different timestamp precisions. There is no hard coded mapping to nanoseconds, rather anything from seconds to nanoseconds is supported
comphead commentedon Nov 27, 2023
Right, I can see there are bunch of hardcodes to nanoseconds, like cast from string to timestamp will come as nanoseconds, read from parquet, coercions, etc.
alamb commentedon Nov 28, 2023
Another option is "do nothing"
You can get the expected answer by using
to_timestamp_seconds:I think the core issue is that the SQL type
TIMESTAMPmapping (as documented in https://arrow.apache.org/datafusion/user-guide/sql/data_types.html#date-time-types) is:TIMESTAMPTimestamp(Nanosecond, None)There is no type in Arrow that can represent nanosecond precision over the same range of values as a i64 second timestamp. So if we switched to something like
TIMESTAMPTimestamp(Second, None)That would result in lower precision timestamps (can't represent nanosecond precision)
So basically I suggest we do nothing about this particular issue -- there is a workaround and I think it is a fairly uncommon corner case
comphead commentedon Nov 28, 2023
Thanks @alamb @tustvold for your input. as part of this issue I will document behavior with timestamp limits in the user manual, specifying which range is acceptable. We can return to this later if the real user case scenario arises