You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When using a timestamp to filter a table on a timestamp column, I get a different result depending on if a timestamp conversion happened in a subquery or not. I hope the example below clarifies it. Let me know if you need more infos!
/* -----------Setup----------- */create or replacetabletable1 (
timestamp_str varchar
);
/* -----------The code below returns exactly one row as expected----------- */insert into table1 values ('2024-05-03 01:00:00'), ('2024-05-03 01:00:02');
select timestamp_str, cast(timestamp_str astimestamp)
from table1
where cast(timestamp_str astimestamp) > cast('2024-05-03 01:00:00'astimestamp);
/* -----------If I change the inserted values to include offsets of +00:00, I get 2 rows althoughI would expect only one row to be returned as above.----------- */
truncate table table1;
insert into table1 values ('2024-05-03T01:00:00+00:00'), ('2024-05-03T01:00:02+00:00');
select timestamp_str, cast(timestamp_str astimestamp)
from table1
where cast(timestamp_str astimestamp) > cast('2024-05-03 01:00:00'astimestamp);
-- Also does not work if I include the offset in the where statementselect timestamp_str, cast(timestamp_str astimestamp)
from table1
where cast(timestamp_str astimestamp) > cast('2024-05-03T01:00:00+00:00'astimestamp);
/* -----------It works when I move the cast into a subquery----------- */select*from (
select timestamp_str, cast(timestamp_str astimestamp) as timestamp_column
from table1
)
where timestamp_column > cast('2024-05-03 01:00:00'astimestamp);
OS:
Linux
DuckDB Version:
0.10.2
DuckDB Client:
CLI and Python
Full Name:
Stefan Binder
Affiliation:
Energy Infrastructure Partners AG
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release and the nightly release which was available on 2024-05-03.
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
Yes, I have
The text was updated successfully, but these errors were encountered:
What happens?
When using a timestamp to filter a table on a timestamp column, I get a different result depending on if a timestamp conversion happened in a subquery or not. I hope the example below clarifies it. Let me know if you need more infos!
I found #11377 which sounds a bit similar.
To Reproduce
OS:
Linux
DuckDB Version:
0.10.2
DuckDB Client:
CLI and Python
Full Name:
Stefan Binder
Affiliation:
Energy Infrastructure Partners AG
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release and the nightly release which was available on 2024-05-03.
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
The text was updated successfully, but these errors were encountered: