Wrong comparison for timestamp depending on if it's in a subquery or not

[binste]

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

/* -----------
Setup
----------- */
create or replace table table1 (
    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 as timestamp)
from table1
where cast(timestamp_str as timestamp) > cast('2024-05-03 01:00:00' as timestamp);


/* -----------
If I change the inserted values to include offsets of +00:00, I get 2 rows although
I 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 as timestamp)
from table1
where cast(timestamp_str as timestamp) > cast('2024-05-03 01:00:00' as timestamp);

-- Also does not work if I include the offset in the where statement
select timestamp_str, cast(timestamp_str as timestamp)
from table1
where cast(timestamp_str as timestamp) > cast('2024-05-03T01:00:00+00:00' as timestamp);

/* -----------
It works when I move the cast into a subquery
----------- */
select * from (
    select timestamp_str, cast(timestamp_str as timestamp) as timestamp_column
    from table1
)
where timestamp_column > cast('2024-05-03 01:00:00' as timestamp);

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

[AntiTopQuark]

I am quite interested in this problem.
Is anyone working on it?
If possible, could it be assigned to me?