Bug #120918 MySQL returns the wrong column alias for an empty result query

• Submitted: 14 Jul 10:56
• Modified: 18 Sep 11:50
• Reporter: cl hl
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[14 Jul 10:56] cl hl

Description:
On MySQL 9.5.0, a SELECT statement explicitly aliases its third output column
as col_3, but the result-set metadata reports the third column name as col_4.

The query returns zero rows, so this is not a row-value mismatch. It is a result
metadata correctness issue. The output column aliases written in the query are:

    col_1, col_2, col_3, col_4, col_5

However, MySQL returns the following column names:

    col_1, col_2, col_4, col_4, col_5

The third output expression is:

    t20.col_1 AS col_3

so the third column name should be col_3, not col_4.

How to repeat:
these SQL can easily be used to reproduce the bug
 
DROP DATABASE IF EXISTS rift_pair3935_min;
CREATE DATABASE rift_pair3935_min;
USE rift_pair3935_min;

CREATE TABLE t1 (
  c1 INT NOT NULL,
  c5 DATE NOT NULL,
  c6 VARCHAR(10) NOT NULL
);

INSERT INTO t1 VALUES (50, '2026-01-01', 'x');

-- Expected column names: a, b
-- Observed on MySQL 9.5.0: b, b
WITH cte_1 AS (
  SELECT
    (SELECT c6 FROM t1 WHERE c1 > 43 LIMIT 1) AS col_1,
    c5 AS col_2
  FROM t1
  WHERE c1 > 42
    AND NOT c5 IN (SELECT c5 FROM t1)
)
SELECT
  t20.col_1 AS a,
  t20.col_1 AS b
FROM cte_1 AS t20
ORDER BY t20.col_2;

[15 Jul 5:25] Chaithra Marsur Gopala Reddy

Hi cl hl,

Thank you for the test case. Verified as described.

[18 Sep 11:50] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 26.10.0 release notes:

Queries exposing the same derived-table expression under different output
aliases could report both result columns with the later alias even though
their values were correct. This affected populated results ordered by
RAND() and empty results using the optimizer. The hypergraph optimizer was
not affected.