Repository: duckdb/duckdb (language: python)

Issue:
INTERNAL Error: Failed to bind column reference
### What happens?

Here is a minimal example of my query:

```
-- Initializing DuckDB environment.
create schema if not exists logica_home;

-- Logica type: logicarecord383307722
drop type if exists logicarecord383307722 cascade; create type logicarecord383307722 as struct(a timestamp);
DROP TABLE IF EXISTS logica_home.QueryRequest;
CREATE TABLE logica_home.QueryRequest AS SELECT * FROM (
  
    SELECT
      ['A', 'B']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['A']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['B']::text[] AS query_request
  
) AS UNUSED_TABLE_NAME  ;

-- Interacting with table logica_home.QueryRequest

DROP TABLE IF EXISTS logica_home.ObjectAttributes;
CREATE TABLE logica_home.ObjectAttributes AS SELECT
  'zero' AS object_id,
  ['a']::text[] AS logica_value;

-- Interacting with table logica_home.ObjectAttributes

SELECT
  QueryRequest.query_request AS query_request,
  ObjectAttributes.object_id AS object_id,
  ObjectAttributes.logica_value AS object_attributes,
  ARRAY_AGG(DISTINCT x_9.unnested_pod) AS attributes_union
FROM
  logica_home.QueryRequest AS QueryRequest, logica_home.ObjectAttributes AS ObjectAttributes, (select unnest(((ObjectAttributes.logica_value) || (QueryRequest.query_request))) as unnested_pod) as x_9
WHERE
  (ObjectAttributes.object_id = 'zero')
GROUP BY QueryRequest.query_request, ObjectAttributes.object_id, ObjectAttributes.logica_value;
```

When I run it I get:

```
INTERNAL Error: Failed to bind column reference "query_request" [17.2] (bindings: {#[0.0], #[1.0], #[1.1]})
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
```

Looks like exactly the situation to file a bug :-)

Let me know if I can be of any help in understanding this error.



### To Reproduce

```
-- Initializing DuckDB environment.
create schema if not exists logica_home;

-- Logica type: logicarecord383307722
drop type if exists logicarecord383307722 cascade; create type logicarecord383307722 as struct(a timestamp);
DROP TABLE IF EXISTS logica_home.QueryRequest;
CREATE TABLE logica_home.QueryRequest AS SELECT * FROM (
  
    SELECT
      ['A', 'B']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['A']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['B']::text[] AS query_request
  
) AS UNUSED_TABLE_NAME  ;

-- Interacting with table logica_home.QueryRequest

DROP TABLE IF EXISTS logica_home.ObjectAttributes;
CREATE TABLE logica_home.ObjectAttributes AS SELECT
  'zero' AS object_id,
  ['a']::text[] AS logica_value;

-- Interacting with table logica_home.ObjectAttributes

SELECT
  QueryRequest.query_request AS query_request,
  ObjectAttributes.object_id AS object_id,
  ObjectAttributes.logica_value AS object_attributes,
  ARRAY_AGG(DISTINCT x_9.unnested_pod) AS attributes_union
FROM
  logica_home.QueryRequest AS QueryRequest, logica_home.ObjectAttributes AS ObjectAttributes, (select unnest(((ObjectAttributes.logica_value) || (QueryRequest.query_request))) as unnested_pod) as x_9
WHERE
  (ObjectAttributes.object_id = 'zero')
GROUP BY QueryRequest.query_request, ObjectAttributes.object_id, ObjectAttributes.logica_value;
```

### OS:

Google CoLab

### DuckDB Version:

whatever installs with pip install

### DuckDB Client:

Python, also your playground

### Hardware:

_No response_

### Full Name:

Evgeny Skvortsov

### Affiliation:

Google, working with duckdb within open source project Logica

### 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
INTERNAL Error: Failed to bind column reference
### What happens?

Here is a minimal example of my query:

```
-- Initializing DuckDB environment.
create schema if not exists logica_home;

-- Logica type: logicarecord383307722
drop type if exists logicarecord383307722 cascade; create type logicarecord383307722 as struct(a timestamp);
DROP TABLE IF EXISTS logica_home.QueryRequest;
CREATE TABLE logica_home.QueryRequest AS SELECT * FROM (
  
    SELECT
      ['A', 'B']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['A']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['B']::text[] AS query_request
  
) AS UNUSED_TABLE_NAME  ;

-- Interacting with table logica_home.QueryRequest

DROP TABLE IF EXISTS logica_home.ObjectAttributes;
CREATE TABLE logica_home.ObjectAttributes AS SELECT
  'zero' AS object_id,
  ['a']::text[] AS logica_value;

-- Interacting with table logica_home.ObjectAttributes

SELECT
  QueryRequest.query_request AS query_request,
  ObjectAttributes.object_id AS object_id,
  ObjectAttributes.logica_value AS object_attributes,
  ARRAY_AGG(DISTINCT x_9.unnested_pod) AS attributes_union
FROM
  logica_home.QueryRequest AS QueryRequest, logica_home.ObjectAttributes AS ObjectAttributes, (select unnest(((ObjectAttributes.logica_value) || (QueryRequest.query_request))) as unnested_pod) as x_9
WHERE
  (ObjectAttributes.object_id = 'zero')
GROUP BY QueryRequest.query_request, ObjectAttributes.object_id, ObjectAttributes.logica_value;
```

When I run it I get:

```
INTERNAL Error: Failed to bind column reference "query_request" [17.2] (bindings: {#[0.0], #[1.0], #[1.1]})
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
```

Looks like exactly the situation to file a bug :-)

Let me know if I can be of any help in understanding this error.



### To Reproduce

```
-- Initializing DuckDB environment.
create schema if not exists logica_home;

-- Logica type: logicarecord383307722
drop type if exists logicarecord383307722 cascade; create type logicarecord383307722 as struct(a timestamp);
DROP TABLE IF EXISTS logica_home.QueryRequest;
CREATE TABLE logica_home.QueryRequest AS SELECT * FROM (
  
    SELECT
      ['A', 'B']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['A']::text[] AS query_request
   UNION ALL
  
    SELECT
      ['B']::text[] AS query_request
  
) AS UNUSED_TABLE_NAME  ;

-- Interacting with table logica_home.QueryRequest

DROP TABLE IF EXISTS logica_home.ObjectAttributes;
CREATE TABLE logica_home.ObjectAttributes AS SELECT
  'zero' AS object_id,
  ['a']::text[] AS logica_value;

-- Interacting with table logica_home.ObjectAttributes

SELECT
  QueryRequest.query_request AS query_request,
  ObjectAttributes.object_id AS object_id,
  ObjectAttributes.logica_value AS object_attributes,
  ARRAY_AGG(DISTINCT x_9.unnested_pod) AS attributes_union
FROM
  logica_home.QueryRequest AS QueryRequest, logica_home.ObjectAttributes AS ObjectAttributes, (select unnest(((ObjectAttributes.logica_value) || (QueryRequest.query_request))) as unnested_pod) as x_9
WHERE
  (ObjectAttributes.object_id = 'zero')
GROUP BY QueryRequest.query_request, ObjectAttributes.object_id, ObjectAttributes.logica_value;
```

### OS:

Google CoLab

### DuckDB Version:

whatever installs with pip install

### DuckDB Client:

Python, also your playground

### Hardware:

_No response_

### Full Name:

Evgeny Skvortsov

### Affiliation:

Google, working with duckdb within open source project Logica

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

Fix patch:
diff --git a/src/optimizer/unnest_rewriter.cpp b/src/optimizer/unnest_rewriter.cpp
index 796a9338b946..e5f9ccf4bbaf 100644
--- a/src/optimizer/unnest_rewriter.cpp
+++ b/src/optimizer/unnest_rewriter.cpp
@@ -122,7 +122,7 @@ bool UnnestRewriter::RewriteCandidate(unique_ptr<LogicalOperator> &candidate) {
 	D_ASSERT(delim_join.type == LogicalOperatorType::LOGICAL_DELIM_JOIN);
 	GetDelimColumns(delim_join);
 
-	// LHS of the LOGICAL_DELIM_JOIN is a LOGICAL_WINDOW that contains a LOGICAL_PROJECTION
+	// LHS of the LOGICAL_DELIM_JOIN is a LOGICAL_WINDOW that contains a LOGICAL_PROJECTION/LOGICAL_CROSS_JOIN
 	// this lhs_proj later becomes the child of the UNNEST
 
 	idx_t delim_idx = delim_join.delim_flipped ? 1 : 0;
@@ -279,7 +279,7 @@ void UnnestRewriter::UpdateBoundUnnestBindings(UnnestRewriterPlanUpdater &update
 
 			if (delim_binding.table_index == unnest_binding.table_index) {
 				unnest_binding.table_index = overwritten_tbl_idx;
-				unnest_binding.column_index++;
+				unnest_binding.column_index = i;
 				updater.replace_bindings.emplace_back(unnest_binding, delim_binding);
 				unnest_cols.erase(unnest_it);
 				break;


Test patch:
diff --git a/test/issues/general/test_14540.test b/test/issues/general/test_14540.test
new file mode 100644
index 000000000000..222cd552b6ad
--- /dev/null
+++ b/test/issues/general/test_14540.test
@@ -0,0 +1,43 @@
+# name: test/issues/general/test_14540.test
+# description: Issue 14540 - INTERNAL Error: Failed to bind column reference(a unnest rewriter fail)
+# group: [general]
+
+statement ok
+PRAGMA enable_verification;
+
+statement ok
+CREATE TABLE QueryRequest AS SELECT * FROM (
+  
+    SELECT
+      ['A', 'B']::text[] AS query_request
+   UNION ALL
+  
+    SELECT
+      ['A']::text[] AS query_request
+   UNION ALL
+  
+    SELECT
+      ['B']::text[] AS query_request
+  
+) AS UNUSED_TABLE_NAME;
+
+statement ok
+CREATE TABLE ObjectAttributes AS SELECT
+  'zero' AS object_id,
+  ['a']::text[] AS logica_value;
+
+query IIII rowsort
+SELECT
+  QueryRequest.query_request AS query_request,
+  ObjectAttributes.object_id AS object_id,
+  ObjectAttributes.logica_value AS object_attributes,
+  count(DISTINCT x_9.unnested_pod) AS attributes_union
+FROM
+  QueryRequest AS QueryRequest, ObjectAttributes AS ObjectAttributes, (select unnest(((ObjectAttributes.logica_value) || (QueryRequest.query_request))) as unnested_pod) as x_9
+WHERE
+  (ObjectAttributes.object_id = 'zero')
+GROUP BY QueryRequest.query_request, ObjectAttributes.object_id, ObjectAttributes.logica_value;
+----
+[A, B]	zero	[a]	3
+[A]	zero	[a]	2
+[B]	zero	[a]	2