Unexpected result when casting `TIMESTAMP_S` to `TIME`

[suyZhong]

What happens?

Consider the following test cases. The second and the third queries are unexpected since the expression and its negation cannot be evaluated as true at the same time. I could reproduce it using TIMESTAMP_S, TIMESTAMP_NS, and TIMESTAMP_MS. Things work well for TIMESTAMP.

To Reproduce

CREATE TABLE  t0(c0 TIMESTAMP_S); -- also for TIMESTAMP_NS and TIMESTAMP_MS
INSERT INTO t0(c0) VALUES ('2030-01-01');
INSERT INTO t0( c0) VALUES ('1969-12-23 20:44:40');

SELECT * FROM t0; -- 2030-01-01 00:00:00, 1969-12-23 20:44:40
-- Unexpected that both query returns the same result
SELECT (CAST(t0.c0 AS TIME)>=('12:34:56')) FROM t0; -- true, true
SELECT NOT (CAST(t0.c0 AS TIME)>=('12:34:56')) FROM t0; -- true, true


-- original query found by TLP
SELECT * FROM t0 WHERE (CAST(t0.c0 AS TIME)>=('12:34:56')) UNION ALL SELECT * FROM t0 WHERE (NOT (CAST(t0.c0 AS TIME)>=('12:34:56'))) UNION ALL SELECT * FROM t0 WHERE (((CAST(t0.c0 AS TIME)>=('12:34:56'))) IS NULL);
-- 4 rows (unexpected)

OS:

Ubuntu 22.04

DuckDB Version:

v1.1.1-dev238 f0f7891

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Suyang Zhong

Affiliation:

NUS

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

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