Repository: duckdb/duckdb (language: python)

Issue:
Unexpected result when using `IN` with `DATE` values
### What happens?

Consider the following test cases. The first query should return `true` since the timestamp after casting is `2024-08-09` and the `IN` expression should be evaluated to `true`. Besides, if the `false` is expected, its negation query should not be evaluated as `false`.

It could be reproduced on both the newest source version and the nightly build.

### To Reproduce

```sql
CREATE TABLE t1(c0 TIMESTAMP) ;
INSERT INTO t1(c0) VALUES ('2024-08-09 14:48:00');

SELECT * FROM t1; -- 2024-08-09 14:48:00
SELECT (CAST(t1.c0 AS DATE) IN ('2024-08-09')) FROM t1; -- false (unexpected)
SELECT NOT (CAST(t1.c0 AS DATE) IN ('2024-08-09')) FROM t1; -- false
```

### OS:

ubuntu 22.04

### DuckDB Version:

v1.0.1-dev4052 f5ab7c167e

### DuckDB Client:

CLI

### 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/rule/in_clause_simplification_rule.cpp b/src/optimizer/rule/in_clause_simplification_rule.cpp
index 07e433773796..c17b0ea3b9e4 100644
--- a/src/optimizer/rule/in_clause_simplification_rule.cpp
+++ b/src/optimizer/rule/in_clause_simplification_rule.cpp
@@ -23,9 +23,12 @@ unique_ptr<Expression> InClauseSimplificationRule::Apply(LogicalOperator &op, ve
 	if (cast_expression.child->expression_class != ExpressionClass::BOUND_COLUMN_REF) {
 		return nullptr;
 	}
-	//! Here we check if we can apply the expression on the constant side
+	//! The goal here is to remove the cast from the probe expression
+	//! and apply a cast to the constant expressions. We can only do this
+	//! if the semantics do not change, which only happens when BOTH casts
+	//! are invertible.
 	auto target_type = cast_expression.source_type();
-	if (!BoundCastExpression::CastIsInvertible(cast_expression.return_type, target_type)) {
+	if (!BoundCastExpression::CastIsInvertible(target_type, cast_expression.return_type)) {
 		return nullptr;
 	}
 	vector<unique_ptr<BoundConstantExpression>> cast_list;
@@ -36,6 +39,9 @@ unique_ptr<Expression> InClauseSimplificationRule::Apply(LogicalOperator &op, ve
 		}
 		D_ASSERT(expr.children[i]->IsFoldable());
 		auto constant_value = ExpressionExecutor::EvaluateScalar(GetContext(), *expr.children[i]);
+		if (!BoundCastExpression::CastIsInvertible(constant_value.type(), target_type)) {
+			return nullptr;
+		}
 		auto new_constant = constant_value.DefaultTryCastAs(target_type);
 		if (!new_constant) {
 			return nullptr;


Test patch:
diff --git a/test/sql/optimizer/expression/test_in_clause_simplification.test b/test/sql/optimizer/expression/test_in_clause_simplification.test
new file mode 100644
index 000000000000..0b2b5e249ba6
--- /dev/null
+++ b/test/sql/optimizer/expression/test_in_clause_simplification.test
@@ -0,0 +1,29 @@
+# name: test/sql/optimizer/expression/test_in_clause_simplification.test
+# description: Test InClauseSimplificationRule
+# group: [expression]
+
+statement ok
+PRAGMA enable_verification
+
+# Removing the TIMESTAMP => DATE cast only works if the 
+# IN clause has TIMESTAMPs.
+statement ok
+CREATE TABLE issue13380(c0 TIMESTAMP);
+
+statement ok
+INSERT INTO issue13380(c0) VALUES ('2024-08-09 14:48:00');
+
+query I
+SELECT c0::DATE IN ('2024-08-09') d FROM issue13380; 
+----
+true
+
+query I
+SELECT NOT (c0::DATE IN ('2024-08-09')) FROM issue13380; 
+----
+false
+
+query I
+SELECT c0::DATE NOT IN ('2024-08-09') FROM issue13380; 
+----
+false