Weird equality comparison when TIMESTAMP_NS stored in tables, subqueries and CTEs is casted to DATE

[Matts966]

What happens?

• DATE values converted from TIMESTAMP_NS stored in tables, subqueries, and CTEs are different from DATE literal even though the values look the same.
• This does not happen when TIMESTAMP_NS values are literal instantiated in the clause.
• This is one of the realistic usages of DuckDB because Pandas DateTime values are converted to TIMESTAMP_NS and comparing date values of timestamps stored in tables is natural. I found this case when filtering a dataset with a date.

WITH t AS (
    SELECT
        '2020/09/13 00:30:00'::TIMESTAMP_NS AS a,
        a::DATE as c,
)
SELECT
    version(),
    '2020/09/13 00:30:00'::TIMESTAMP_NS AS b,
    a::DATE = DATE '2020/09/13',
    b::DATE = DATE '2020/09/13',
    c = DATE '2020/09/13',
    a::DATE, b::DATE, c, DATE '2020/09/13',
FROM t;

returns

version()	b	(CAST(a AS DATE) = CAST('2020/09/13' AS DATE))	(CAST(b AS DATE) = CAST('2020/09/13' AS DATE))	(c = CAST('2020/09/13' AS DATE))	CAST(a AS DATE)	CAST(b AS DATE)	c	CAST('2020/09/13' AS DATE)
v0.10.1	2020-09-13 00:30:00	False	True	True	2020-09-13	2020-09-13	2020-09-13	2020-09-13

To Reproduce

Use the SQL above

OS:

Linux x86

DuckDB Version:

v0.10.1

DuckDB Client:

Python

Full Name:

Masahiro Matsui

Affiliation:

Treasure Data

Have you tried this on the latest nightly build?

I have tested with a release build (and could not test with a nightly build)

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[szarnyasg]

Thanks, we'll look into this