Repository: duckdb/duckdb (language: python)

Issue:
Unexpected result when casting `TIMESTAMP_S` to `TIME`
### 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

```sql
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 f0f78913a5

### 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?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have
Unexpected result when casting `TIMESTAMP_S` to `TIME`
### 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

```sql
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 f0f78913a5

### 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?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/optimizer/statistics/expression/propagate_cast.cpp b/src/optimizer/statistics/expression/propagate_cast.cpp
index 9cb590823b62..b5fcc8cedc9a 100644
--- a/src/optimizer/statistics/expression/propagate_cast.cpp
+++ b/src/optimizer/statistics/expression/propagate_cast.cpp
@@ -27,6 +27,9 @@ static unique_ptr<BaseStatistics> StatisticsNumericCastSwitch(const BaseStatisti
 	case LogicalTypeId::TIME: {
 		switch (input.GetType().id()) {
 		case LogicalTypeId::TIMESTAMP:
+		case LogicalTypeId::TIMESTAMP_SEC:
+		case LogicalTypeId::TIMESTAMP_MS:
+		case LogicalTypeId::TIMESTAMP_NS:
 		case LogicalTypeId::TIMESTAMP_TZ:
 			return nullptr;
 		default:


Test patch:
diff --git a/test/sql/types/timestamp/test_timestamp_types.test b/test/sql/types/timestamp/test_timestamp_types.test
index 297fa17b3d0e..436576c5346b 100644
--- a/test/sql/types/timestamp/test_timestamp_types.test
+++ b/test/sql/types/timestamp/test_timestamp_types.test
@@ -260,3 +260,15 @@ query I
 select '2008-01-01 00:00:11'::TIMESTAMP_NS = '2008-01-01 00:00:11'::TIMESTAMP_S
 ----
 true
+
+query I
+SELECT CAST(t0.c0 AS TIME)>=('12:34:56') FROM  values ('2030-01-01'::TIMESTAMP_S), ('1969-12-23 20:44:40'::TIMESTAMP_S) as t0(c0)
+----
+false
+true
+
+query I
+SELECT NOT CAST(t0.c0 AS TIME)>=('12:34:56') FROM  values ('2030-01-01'::TIMESTAMP_MS), ('1969-12-23 20:44:40'::TIMESTAMP_MS) as t0(c0)
+----
+true
+false