Repository: duckdb/duckdb (language: python)

Issue:
Incorrect results for IN clause
### What happens?

When compared to PostgreSQL , DuckDB is giving incorrect results. Spark SQL and PostgreSQL give 2 rows and DuckDB gives 3 rows.
PostgreSQL and Spark SQL results
t1b,8
t1c,8

DuckDB results
t1e,10
t1b,8
t1c,8

### To Reproduce

-- Create table t1
CREATE TABLE t1 (
    t1a TEXT,
    t1b SMALLINT,
    t1c INTEGER,
    t1d BIGINT,
    t1e REAL,
    t1f DOUBLE PRECISION,
    t1g NUMERIC(10,2),
    t1h TIMESTAMP,
    t1i DATE
);

INSERT INTO t1 (t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i) VALUES
('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:00:00', '2014-04-04'),
('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1a', 16, 12, 21, 15.0, 20, 20.00, '2014-06-04 01:02:00.001', '2014-06-04'),
('t1a', 16, 12, 10, 15.0, 20, 20.00, '2014-07-04 01:01:00', '2014-07-04'),
('t1c', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:02:00.001', '2014-05-05'),
('t1d', NULL, 16, 22, 17.0, 25, 26.00, '2014-06-04 01:01:00', NULL),
('t1d', NULL, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:02:00.001', NULL),
('t1e', 10, NULL, 25, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-04'),
('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:02:00.001', '2014-09-04'),
('t1d', 10, NULL, 12, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:02:00.001', '2014-04-04'),
('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04');

-- Create table t2
CREATE TABLE t2 (
    t2a TEXT,
    t2b SMALLINT,
    t2c INTEGER,
    t2d BIGINT,
    t2e REAL,
    t2f DOUBLE PRECISION,
    t2g NUMERIC(10,2),
    t2h TIMESTAMP,
    t2i DATE
);

INSERT INTO t2 (t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i) VALUES
('t2a', 6, 12, 14, 15.0, 20, 20.00, '2014-04-04 01:01:00', '2014-04-04'),
('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1b', 8, 16, 119, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
('t1c', 12, 16, 219, 17.0, 25, 26.00, '2016-05-04 01:01:00', '2016-05-04'),
('t1b', NULL, 16, 319, 17.0, 25, 26.00, '2017-05-04 01:01:00', NULL),
('t2e', 8, NULL, 419, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
('t1f', 19, NULL, 519, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:01:00', '2014-07-04'),
('t1c', 12, 16, 19, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-05'),
('t1e', 8, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:01:00', '2014-09-04'),
('t1f', 19, NULL, 19, 17.0, 25, 26.00, '2014-10-04 01:01:00', '2014-10-04'),
('t1b', NULL, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', NULL);


SELECT t1a,
       t1b
FROM   t1
WHERE  t1c IN (SELECT t2c
               FROM   t2
               WHERE  t1a = t2a)
GROUP  BY t1a,
          t1b;

### OS:

x86_64

### DuckDB Version:

1.1.1

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Rishab C

### Affiliation:

Student

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

I have tested with a stable release

### 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
Incorrect results for IN clause
### What happens?

When compared to PostgreSQL , DuckDB is giving incorrect results. Spark SQL and PostgreSQL give 2 rows and DuckDB gives 3 rows.
PostgreSQL and Spark SQL results
t1b,8
t1c,8

DuckDB results
t1e,10
t1b,8
t1c,8

### To Reproduce

-- Create table t1
CREATE TABLE t1 (
    t1a TEXT,
    t1b SMALLINT,
    t1c INTEGER,
    t1d BIGINT,
    t1e REAL,
    t1f DOUBLE PRECISION,
    t1g NUMERIC(10,2),
    t1h TIMESTAMP,
    t1i DATE
);

INSERT INTO t1 (t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i) VALUES
('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:00:00', '2014-04-04'),
('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1a', 16, 12, 21, 15.0, 20, 20.00, '2014-06-04 01:02:00.001', '2014-06-04'),
('t1a', 16, 12, 10, 15.0, 20, 20.00, '2014-07-04 01:01:00', '2014-07-04'),
('t1c', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:02:00.001', '2014-05-05'),
('t1d', NULL, 16, 22, 17.0, 25, 26.00, '2014-06-04 01:01:00', NULL),
('t1d', NULL, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:02:00.001', NULL),
('t1e', 10, NULL, 25, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-04'),
('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:02:00.001', '2014-09-04'),
('t1d', 10, NULL, 12, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:02:00.001', '2014-04-04'),
('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04');

-- Create table t2
CREATE TABLE t2 (
    t2a TEXT,
    t2b SMALLINT,
    t2c INTEGER,
    t2d BIGINT,
    t2e REAL,
    t2f DOUBLE PRECISION,
    t2g NUMERIC(10,2),
    t2h TIMESTAMP,
    t2i DATE
);

INSERT INTO t2 (t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i) VALUES
('t2a', 6, 12, 14, 15.0, 20, 20.00, '2014-04-04 01:01:00', '2014-04-04'),
('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1b', 8, 16, 119, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
('t1c', 12, 16, 219, 17.0, 25, 26.00, '2016-05-04 01:01:00', '2016-05-04'),
('t1b', NULL, 16, 319, 17.0, 25, 26.00, '2017-05-04 01:01:00', NULL),
('t2e', 8, NULL, 419, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
('t1f', 19, NULL, 519, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:01:00', '2014-07-04'),
('t1c', 12, 16, 19, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-05'),
('t1e', 8, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:01:00', '2014-09-04'),
('t1f', 19, NULL, 19, 17.0, 25, 26.00, '2014-10-04 01:01:00', '2014-10-04'),
('t1b', NULL, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', NULL);


SELECT t1a,
       t1b
FROM   t1
WHERE  t1c IN (SELECT t2c
               FROM   t2
               WHERE  t1a = t2a)
GROUP  BY t1a,
          t1b;

### OS:

x86_64

### DuckDB Version:

1.1.1

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Rishab C

### Affiliation:

Student

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

I have tested with a stable release

### 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:
Thanks for opening this issue! Based on our automated check, it seems that your post contains some code but it does not use [code blocks](https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks) to format it.
          
Please double-check your post and revise it if necessary. To employ syntax highlighting, it's recommended to use code blocks with triple backticks, e.g.:
````
```sql
SELECT ...
```
````
If this is a false positive, feel free to disregard this comment.

Semantically correct query when changed to Join gives correct results
SELECT t1a,
       t1b
FROM   t1 , t2
WHERE  t1c = t2c
               and t1a = t2a
GROUP  BY t1a,
          t1b;
Thanks for opening this issue! Based on our automated check, it seems that your post contains some code but it does not use [code blocks](https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks) to format it.
          
Please double-check your post and revise it if necessary. To employ syntax highlighting, it's recommended to use code blocks with triple backticks, e.g.:
````
```sql
SELECT ...
```
````
If this is a false positive, feel free to disregard this comment.

Semantically correct query when changed to Join gives correct results
SELECT t1a,
       t1b
FROM   t1 , t2
WHERE  t1c = t2c
               and t1a = t2a
GROUP  BY t1a,
          t1b;

Fix patch:
diff --git a/src/optimizer/deliminator.cpp b/src/optimizer/deliminator.cpp
index 2771222e7af5..805e56609742 100644
--- a/src/optimizer/deliminator.cpp
+++ b/src/optimizer/deliminator.cpp
@@ -81,13 +81,6 @@ unique_ptr<LogicalOperator> Deliminator::Optimize(unique_ptr<LogicalOperator> op
 		if (candidate.joins.size() == candidate.delim_get_count && all_removed) {
 			delim_join.type = LogicalOperatorType::LOGICAL_COMPARISON_JOIN;
 			delim_join.duplicate_eliminated_columns.clear();
-			if (all_equality_conditions) {
-				for (auto &cond : delim_join.conditions) {
-					if (IsEqualityJoinCondition(cond)) {
-						cond.comparison = ExpressionType::COMPARE_NOT_DISTINCT_FROM;
-					}
-				}
-			}
 		}
 
 		// Only DelimJoins are ever created as SINGLE joins,


Test patch:
diff --git a/test/issues/general/test_14232.test b/test/issues/general/test_14232.test
new file mode 100644
index 000000000000..2c6664ccbfb5
--- /dev/null
+++ b/test/issues/general/test_14232.test
@@ -0,0 +1,73 @@
+# name: test/issues/general/test_14232.test
+# description: Issue 14232 - Incorrect results for IN clause
+# group: [general]
+
+statement ok
+CREATE TABLE t1 (
+t1a TEXT,
+t1b SMALLINT,
+t1c INTEGER,
+t1d BIGINT,
+t1e REAL,
+t1f DOUBLE PRECISION,
+t1g NUMERIC(10,2),
+t1h TIMESTAMP,
+t1i DATE
+)
+
+statement ok
+INSERT INTO t1 (t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i) VALUES
+('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:00:00', '2014-04-04'),
+('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
+('t1a', 16, 12, 21, 15.0, 20, 20.00, '2014-06-04 01:02:00.001', '2014-06-04'),
+('t1a', 16, 12, 10, 15.0, 20, 20.00, '2014-07-04 01:01:00', '2014-07-04'),
+('t1c', 8, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:02:00.001', '2014-05-05'),
+('t1d', NULL, 16, 22, 17.0, 25, 26.00, '2014-06-04 01:01:00', NULL),
+('t1d', NULL, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:02:00.001', NULL),
+('t1e', 10, NULL, 25, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-04'),
+('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:02:00.001', '2014-09-04'),
+('t1d', 10, NULL, 12, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
+('t1a', 6, 8, 10, 15.0, 20, 20.00, '2014-04-04 01:02:00.001', '2014-04-04'),
+('t1e', 10, NULL, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04')
+
+statement ok
+CREATE TABLE t2 (
+t2a TEXT,
+t2b SMALLINT,
+t2c INTEGER,
+t2d BIGINT,
+t2e REAL,
+t2f DOUBLE PRECISION,
+t2g NUMERIC(10,2),
+t2h TIMESTAMP,
+t2i DATE
+)
+
+statement ok
+INSERT INTO t2 (t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i) VALUES
+('t2a', 6, 12, 14, 15.0, 20, 20.00, '2014-04-04 01:01:00', '2014-04-04'),
+('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
+('t1b', 8, 16, 119, 17.0, 25, 26.00, '2015-05-04 01:01:00', '2015-05-04'),
+('t1c', 12, 16, 219, 17.0, 25, 26.00, '2016-05-04 01:01:00', '2016-05-04'),
+('t1b', NULL, 16, 319, 17.0, 25, 26.00, '2017-05-04 01:01:00', NULL),
+('t2e', 8, NULL, 419, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
+('t1f', 19, NULL, 519, 17.0, 25, 26.00, '2014-05-04 01:01:00', '2014-05-04'),
+('t1b', 10, 12, 19, 17.0, 25, 26.00, '2014-06-04 01:01:00', '2014-06-04'),
+('t1b', 8, 16, 19, 17.0, 25, 26.00, '2014-07-04 01:01:00', '2014-07-04'),
+('t1c', 12, 16, 19, 17.0, 25, 26.00, '2014-08-04 01:01:00', '2014-08-05'),
+('t1e', 8, NULL, 19, 17.0, 25, 26.00, '2014-09-04 01:01:00', '2014-09-04'),
+('t1f', 19, NULL, 19, 17.0, 25, 26.00, '2014-10-04 01:01:00', '2014-10-04'),
+('t1b', NULL, 16, 19, 17.0, 25, 26.00, '2014-05-04 01:01:00', NULL)
+
+query II
+SELECT t1a,
+t1b
+FROM t1
+WHERE t1c IN (SELECT t2c
+FROM t2
+WHERE t1a = t2a)
+GROUP BY t1a,
+t1b
+----
+t1b	8
+t1c	8
\ No newline at end of file