MATERIALIZED causes Binder Error: table has duplicate column name

[tanner-lai]

What happens?

An unexpected error occurs when a CTE is MATERIALIZED: Error: Binder Error: table "CTE" has duplicate column name "A1"

To Reproduce

CREATE TABLE T0(C1 INT);
CREATE TABLE T1(C1 INT);

INSERT INTO T0(C1) VALUES (1);
INSERT INTO T1(C1) VALUES (1);

-- no problem
WITH CTE AS (
SELECT A1, * FROM T0
  LEFT JOIN (
    SELECT C1 AS A1 FROM T1
  ) ON T0.C1 = A1
) SELECT A1 FROM CTE;

-- binder error
WITH CTE AS MATERIALIZED (
SELECT A1, * FROM T0
  LEFT JOIN (
    SELECT C1 AS A1 FROM T1
  ) ON T0.C1 = A1
) SELECT A1 FROM CTE;

OS:

macOS 14.2.1 (23C71)

DuckDB Version:

v0.9.2

DuckDB Client:

CLI

Full Name:

Tanner Lai

Affiliation:

Appian

Have you tried this on the latest main branch?

I have tested with a release build (and could not test with a main build)

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[kryonix]

This is expected behavior. Your query produces column name A1 twice:

SELECT A1, * FROM T0
  LEFT JOIN (
    SELECT C1 AS A1 FROM T1
  ) ON T0.C1 = A1

┌───────┬───────┬───────┐
│  A1   │  C1   │  A1   │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │     1 │     1 │
└───────┴───────┴───────┘

Either rename one of the occurences SELECT A1 AS "A1.1", * ..., or remove A1 completely, as you select it with * anyways.

[tanner-lai]

Hmm, I would expect that materializing a CTE should not affect the correctness of the results or whether or not there would be results at all (e.g. in this case where it fails to bind).

WITH CTE AS /* MATERIALIZED */ (
SELECT A1, * FROM T0
  LEFT JOIN (
    SELECT C1 AS A1 FROM T1
  ) ON T0.C1 = A1
) SELECT * FROM CTE;

Like you said A1 is selected twice, but the column is automatically realiased. I think it's not unreasonable to expect materialize to maintain this behavior.

┌───────┬───────┬───────┐
│  A1   │  C1   │ A1:1  │
│ int32 │ int32 │ int32 │
├───────┼───────┼───────┤
│     1 │     1 │     1 │
└───────┴───────┴───────┘

[kryonix]

Alright, that makes sense. See PR #10386.

[Mytherin]

Fixed in #10386