Repository: duckdb/duckdb (language: python)

Issue:
Unexpected result when using `IN` with `TIME` values
### What happens?

Consider the following test cases. The second query should return `true` since the value after casting is `12:34:56` and the IN expression should be evaluated to true. Besides, if the `false` is expected, its negation query should not be evaluated as `false`.


### To Reproduce

```sql
DROP TABLE IF EXISTS t1;
CREATE   TABLE  t1(c0 TIME WITH TIME ZONE);
INSERT INTO t1(c0) VALUES ('12:34:56');

SELECT t1.c0 FROM t1; -- 12:34:56+08

SELECT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false (unexpected)
SELECT NOT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false

```

### OS:

ubuntu 22.04

### DuckDB Version:

v1.0.1-dev5313 64bacde85e

### 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 nightly 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:
Hi, thanks for reporting this! Interestingly, it doesn't reproduce on macOS:

```sql
v1.0.1-dev5313 64bacde85e
D DROP TABLE IF EXISTS t1;
D CREATE   TABLE  t1(c0 TIME WITH TIME ZONE);
D INSERT INTO t1(c0) VALUES ('12:34:56');
D
D SELECT t1.c0 FROM t1; -- 12:34:56+08
┌─────────────────────┐
│         c0          │
│ time with time zone │
├─────────────────────┤
│ 12:34:56+00         │
└─────────────────────┘
D
D SELECT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false (unexpected)
┌───────────────────────────────────────┐
│ (CAST(t1.c0 AS TIME) IN ('12:34:56')) │
│                boolean                │
├───────────────────────────────────────┤
│ true                                  │
└───────────────────────────────────────┘
D SELECT NOT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false
┌───────────────────────────────────────────┐
│ (CAST(t1.c0 AS TIME) NOT IN ('12:34:56')) │
│                  boolean                  │
├───────────────────────────────────────────┤
│ false                                     │
└───────────────────────────────────────────┘
```

I will test it on Ubuntu in a moment.
Have just tested on my M2 Mac by install through this [link](https://duckdb.org/docs/installation/?version=main&environment=cli&platform=macos&download_method=direct). Looks like it's an old version (perhaps we should also update the mac install [link](https://github.com/duckdb/duckdb/actions/runs/10587966032/artifacts/1863098716)), but I could also reproduce it.
```sql
./duckdb
v1.0.1-dev4917 45787e5f9f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D DROP TABLE IF EXISTS t1;
D CREATE   TABLE  t1(c0 TIME WITH TIME ZONE);
D INSERT INTO t1(c0) VALUES ('12:34:56');
D
D SELECT t1.c0 FROM t1; -- 12:34:56+08
┌─────────────────────┐
│         c0          │
│ time with time zone │
├─────────────────────┤
│ 12:34:56+08         │
└─────────────────────┘
D
D SELECT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false (unexpected)
┌───────────────────────────────────────┐
│ (CAST(t1.c0 AS TIME) IN ('12:34:56')) │
│                boolean                │
├───────────────────────────────────────┤
│ false                                 │
└───────────────────────────────────────┘
D SELECT NOT (CAST(t1.c0 AS TIME) IN ('12:34:56')) FROM t1; -- false
┌───────────────────────────────────────────┐
│ (CAST(t1.c0 AS TIME) NOT IN ('12:34:56')) │
│                  boolean                  │
├───────────────────────────────────────────┤
│ false                                     │
└───────────────────────────────────────────┘
D
```

So I guess it's about `timezone`. When I change the timezone to `UTC`, there's not issue:

```sql
SET TimeZone = 'UTC';
```

I guess you can try this:
```sql
SET TimeZone='Asia/Singapore';
```
It's an `icu` issue! I also tried to set the timezone which failed due to `icu` not being available in my (default) build. I rebuilt DuckDB with a few core extension and now it reproduces the problem.

```bash
CORE_EXTENSIONS='autocomplete;icu;parquet;json' GEN=ninja make
```

Thanks for helping with the troubleshooting!

Fix patch:
diff --git a/src/planner/expression/bound_cast_expression.cpp b/src/planner/expression/bound_cast_expression.cpp
index 0c355d49980c..1c8dc9518723 100644
--- a/src/planner/expression/bound_cast_expression.cpp
+++ b/src/planner/expression/bound_cast_expression.cpp
@@ -170,6 +170,7 @@ bool BoundCastExpression::CastIsInvertible(const LogicalType &source_type, const
 		break;
 	case LogicalTypeId::VARCHAR:
 	case LogicalTypeId::BIT:
+	case LogicalTypeId::TIME_TZ:
 		return false;
 	default:
 		break;


Test patch:
diff --git a/test/sql/types/time/test_time_tz_icu.test b/test/sql/types/time/test_time_tz_icu.test
new file mode 100644
index 000000000000..a308c1d56cc4
--- /dev/null
+++ b/test/sql/types/time/test_time_tz_icu.test
@@ -0,0 +1,39 @@
+# name: test/sql/types/time/test_time_tz_icu.test
+# description: Test TIMETZ cat invertilbility
+# group: [time]
+
+require icu
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+SET Calendar='gregorian';
+
+statement ok
+SET TimeZone='Asia/Singapore';
+
+statement ok
+CREATE OR REPLACE TABLE single(c0 TIME WITH TIME ZONE);
+
+statement ok
+INSERT INTO single(c0) VALUES ('12:34:56');
+
+query III
+SELECT 
+	c0, 
+	c0::TIME AS t,
+	c0::TIME::TIMETZ AS tz,
+FROM single;
+----
+12:34:56+08	12:34:56	12:34:56+00
+
+query IIII
+SELECT 
+	(c0::TIME = '12:34:56') AS e,
+	(c0::TIME <> '12:34:56') AS u,
+	(c0::TIME IN ('12:34:56')) AS i,
+	(c0::TIME NOT IN ('12:34:56')) AS n,
+FROM single;
+----
+1	0	1	0