Bug #121152 CTE merge incorrectly loses the first alias when a BIT column is projected twice with different aliases

• Submitted: 22 Aug 6:09
• Reporter: cl hl
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.7.1
• OS: Any
• CPU Architecture: Any

[22 Aug 6:09] cl hl

Description:
When the same `BIT` column from a CTE is projected twice with different aliases inside a `DISTINCT` derived table, MySQL incorrectly assigns the second alias to both output columns.

The derived table should expose columns `a` and `b`, but with `derived_merge=on`, it exposes `b` and `b`. Referencing `d.a` consequently raises error 1054.

Disabling `derived_merge` or adding `NO_MERGE(i)` makes the query execute correctly. The result therefore changes solely because of an optimizer transformation.

Expected result:

```text
a
0x07
```

Actual result:

```text
ERROR 1054 (42S22): Unknown column 'd.a' in 'field list'
```

Selecting all derived-table columns demonstrates the incorrect metadata:

```sql
WITH c AS (
    SELECT x FROM bug_t
)
SELECT d.*
FROM (
    SELECT DISTINCT i.x AS a, i.x AS b
    FROM c AS i
) AS d;
```

Expected column names:

```text
a, b
```

Actual column names:

```text
b, b
```

How to repeat:
```sql
CREATE TABLE bug_t (
    x BIT(8)
);

INSERT INTO bug_t VALUES (b'00000111');

WITH c AS (
    SELECT x FROM bug_t
)
SELECT d.a
FROM (
    SELECT DISTINCT i.x AS a, i.x AS b
    FROM c AS i
) AS d;
```

The query returns:

```text
ERROR 1054 (42S22): Unknown column 'd.a' in 'field list'
```

Confirm that the query succeeds when CTE merging is prevented:

```sql
WITH c AS (
    SELECT x FROM bug_t
)
SELECT d.a
FROM (
    SELECT /*+ NO_MERGE(i) */
           DISTINCT i.x AS a, i.x AS b
    FROM c AS i
) AS d;
```

Result:

```text
a
0x07
```

The following session-level workaround also succeeds:

```sql
SET optimizer_switch = 'derived_merge=off';
```