to_timestamp(double) gives different results depending on scalar/vectorized call context

[findepi]

Describe the bug

We have this in SLT tests

query PPP
SELECT to_timestamp(1.1) as c1, ...
----
1970-01-01T00:00:01.100 ...

and indeed it works in CLI

> SELECT to_timestamp(1.1);
+----------------------------+
| to_timestamp(Float64(1.1)) |
+----------------------------+
| 1970-01-01T00:00:01.100    |
+----------------------------+

unless the value is not result of constant folding

> SELECT to_timestamp(x) FROM (VALUES (1.1), (NULL)) t(x);
+-------------------------------+
| to_timestamp(t.x)             |
+-------------------------------+
| 1970-01-01T00:00:00.000000001 |
| NULL                          |
+-------------------------------+

To Reproduce

No response

Expected behavior

Same result

Additional context

• Different result of double to timestamp(9) cast when source value is constant #16636
• Different result of decimal to timestamp cast when source value is constant #16531
• Ensure to_timestamp behaves consistently with PostgreSQL #13351

[dqkqd]

I cannot reproduce this in branch-50.

DataFusion CLI v50.0.0
> SELECT to_timestamp(1.1);
+----------------------------+
| to_timestamp(Float64(1.1)) |
+----------------------------+
| 1970-01-01T00:00:01.100    |
+----------------------------+
1 row(s) fetched.
Elapsed 0.008 seconds.

> SELECT to_timestamp(x) FROM (VALUES (1.1), (NULL)) t(x);
+-------------------------+
| to_timestamp(t.x)       |
+-------------------------+
| 1970-01-01T00:00:01.100 |
| NULL                    |
+-------------------------+
2 row(s) fetched.
Elapsed 0.006 seconds.

I think #16639 fixed this. I tried 4e32ab9.

DataFusion CLI v48.0.0
> SELECT to_timestamp(1.1);
+----------------------------+
| to_timestamp(Float64(1.1)) |
+----------------------------+
| 1970-01-01T00:00:01.100    |
+----------------------------+
1 row(s) fetched.
Elapsed 0.007 seconds.

> SELECT to_timestamp(x) FROM (VALUES (1.1), (NULL)) t(x);
+-------------------------+
| to_timestamp(t.x)       |
+-------------------------+
| 1970-01-01T00:00:01.100 |
| NULL                    |
+-------------------------+
2 row(s) fetched.
Elapsed 0.006 seconds.

[Jefffrey]

Thanks for checking this @dqkqd

Do you think you can check if we have an SLT test for this? If not could raise a PR with the test case so we can close this issue

[dqkqd]

@Jefffrey

I have checked. There is no test for to_timestamp with vectorized input.

However, after looking closely at the #16639 and the test, I found that to_timestamp(double) and cast(double as timestamp) return different results.

datafusion/sqllogictest/test_files/timestamps.slt:504

query PPP
SELECT to_timestamp(1.1) as c1, cast(1.1 as timestamp) as c2, 1.1::timestamp as c3;
----
1970-01-01T00:00:01.100 1970-01-01T00:00:00.000000001 1970-01-01T00:00:00.000000001

From https://github.com/apache/datafusion/pull/16639/files#r2213084931, I think what we want is to have to_timestamp(double) return something like this:

> SELECT to_timestamp(1.1);
+-------------------------------+
| to_timestamp(Float64(1.1))    |
+-------------------------------+
| 1970-01-01T00:00:00.000000001 |
+-------------------------------+

[dqkqd]

take

[dqkqd]

From https://github.com/apache/datafusion/pull/16639/files#r2213084931, I think what we want is to have to_timestamp(double) return something like this:

SELECT to_timestamp(1.1);
+-------------------------------+
| to_timestamp(Float64(1.1)) |
+-------------------------------+
| 1970-01-01T00:00:00.000000001 |
+-------------------------------+

I was wrong, actually to_timestamp should just cast as second instead of nanoseconds.

I will just add the testcase, no other changes needed.