Repository: duckdb/duckdb (language: python)

Issue:
Correlated subquery related? Failed to bind column reference "once_state2" [35.0] (bindings: {#[1.0], #[33.0]})
### What happens?

See the reproduction. The difference between the first query that succeeds, and the second that gives an error is only that a column is referenced in the where clause in the second query. This column is generated from a exists correlated subquery "self join".

### To Reproduce

```
$ ./duckdb 
v1.0.0 1f98600c2c
```
```sql
select version();
```
```text
┌───────────┐
│ version() │
│  varchar  │
├───────────┤
│ v1.0.0    │
└───────────┘
```
```sql
create table stuff (
      ts timestamp,
      item_id int,
      type text
    );
```
```sql
with
    unique_items as (
       select distinct item_id from stuff
    ),
    state_stuff as (
      select
        item_id,
        exists (select 1 from stuff c where s.item_id = c.item_id
                and type = 'state1') as once_state1,
        exists (select 1 from stuff c where s.item_id = c.item_id
                and type = 'state2') as once_state2
      from unique_items s)
    select * from state_stuff;
```
```text
┌─────────┬─────────────┬─────────────┐
│ item_id │ once_state1 │ once_state2 │
│  int32  │   boolean   │   boolean   │
├─────────┴─────────────┴─────────────┤
│               0 rows                │
└─────────────────────────────────────┘
```
```sql
with
    unique_items as (
       select distinct item_id from stuff
    ),
    state_stuff as (
      select
        item_id,
        exists (select 1 from stuff c where s.item_id = c.item_id
                and type = 'state1') as once_state1,
        exists (select 1 from stuff c where s.item_id = c.item_id
                and type = 'state2') as once_state2
      from unique_items s)
    select * from state_stuff where not once_state2;
```
```console
INTERNAL Error: Failed to bind column reference "once_state2" [35.0] (bindings: {#[1.0], #[33.0]})
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
```

### OS:

x64 Debian GNU/Linux 12

### DuckDB Version:

1.0.0

### DuckDB Client:

duckdb cli

### Full Name:

Jake Wheat

### Affiliation:

n/a

### 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:
Thanks, I could reproduce this!
This bug will likely be fixed with PR https://github.com/duckdb/duckdb/pull/13404. I tested it, and with my local version, both queries work.
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

Fix patch:
diff --git a/src/optimizer/filter_pushdown.cpp b/src/optimizer/filter_pushdown.cpp
index d789394be56b..3440e657e720 100644
--- a/src/optimizer/filter_pushdown.cpp
+++ b/src/optimizer/filter_pushdown.cpp
@@ -14,6 +14,7 @@ using Filter = FilterPushdown::Filter;
 
 void FilterPushdown::CheckMarkToSemi(LogicalOperator &op, unordered_set<idx_t> &table_bindings) {
 	switch (op.type) {
+	case LogicalOperatorType::LOGICAL_DELIM_JOIN:
 	case LogicalOperatorType::LOGICAL_COMPARISON_JOIN: {
 		auto &join = op.Cast<LogicalComparisonJoin>();
 		if (join.join_type != JoinType::MARK) {
@@ -31,7 +32,6 @@ void FilterPushdown::CheckMarkToSemi(LogicalOperator &op, unordered_set<idx_t> &
 	case LogicalOperatorType::LOGICAL_PROJECTION: {
 		// when we encounter a projection, replace the table_bindings with
 		// the tables in the projection
-		auto plan_bindings = op.GetColumnBindings();
 		auto &proj = op.Cast<LogicalProjection>();
 		auto proj_bindings = proj.GetColumnBindings();
 		unordered_set<idx_t> new_table_bindings;
@@ -40,8 +40,8 @@ void FilterPushdown::CheckMarkToSemi(LogicalOperator &op, unordered_set<idx_t> &
 			auto &expr = proj.expressions.at(col_index);
 			vector<ColumnBinding> bindings_to_keep;
 			ExpressionIterator::EnumerateExpression(expr, [&](Expression &child) {
-				if (expr->expression_class == ExpressionClass::BOUND_COLUMN_REF) {
-					auto &col_ref = expr->Cast<BoundColumnRefExpression>();
+				if (child.expression_class == ExpressionClass::BOUND_COLUMN_REF) {
+					auto &col_ref = child.Cast<BoundColumnRefExpression>();
 					bindings_to_keep.push_back(col_ref.binding);
 				}
 			});


Test patch:
diff --git a/test/issues/general/test_13542.test b/test/issues/general/test_13542.test
new file mode 100644
index 000000000000..8d22a4ae4474
--- /dev/null
+++ b/test/issues/general/test_13542.test
@@ -0,0 +1,45 @@
+# name: test/issues/general/test_13542.test
+# description: Correlated subquery related? Failed to bind column reference "once_state2" [35.0] (bindings: {#[1.0], #[33.0]})
+# group: [general]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+create table stuff (
+      ts timestamp,
+      item_id int,
+      type text
+    );
+
+query III
+with
+    unique_items as (
+       select distinct item_id from stuff
+    ),
+    state_stuff as (
+      select
+        item_id,
+        exists (select 1 from stuff c where s.item_id = c.item_id
+                and type = 'state1') as once_state1,
+        exists (select 1 from stuff c where s.item_id = c.item_id
+                and type = 'state2') as once_state2
+      from unique_items s)
+    select * from state_stuff;
+----
+
+query III
+with
+    unique_items as (
+       select distinct item_id from stuff
+    ),
+    state_stuff as (
+      select
+        item_id,
+        exists (select 1 from stuff c where s.item_id = c.item_id
+                and type = 'state1') as once_state1,
+        exists (select 1 from stuff c where s.item_id = c.item_id
+                and type = 'state2') as once_state2
+      from unique_items s)
+    select * from state_stuff where not once_state2;
+----