Description:
the column name change caused by DISTINCT,the first column name unexpectedly change from col_1 to col_2
mysql> WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT DISTINCT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC;
+-------+-------+
| col_2 | col_2 |
+-------+-------+
| 1 | 1 |
+-------+-------+
1 row in set (0.00 sec)
mysql> WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC;
+-------+-------+
| col_1 | col_2 |
+-------+-------+
| 1 | 1 |
+-------+-------+
1 row in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE t3 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 YEAR NOT NULL,
c5 TIME NULL,
c6 TINYINT NULL,
c7 SMALLINT NULL,
c8 MEDIUMINT NULL,
c9 BIGINT NULL,
c10 LONGTEXT NULL,
c11 VARCHAR(255) NULL,
c12 TINYTEXT NULL,
c13 TINYBLOB NULL,
c14 SET('x','y','z') NULL,
c15 TINYINT(1) NULL,
PRIMARY KEY (c1)
);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (8417, 9828, 4869, 2023, '2023-01-01 21:39:34', 14, 68, 41, 89, 'sample_etX2AhIU3JkLkyrEOwJTBqpBStqOrUPbskI8gL9NI0mwroTZBFHdHYljUuEHNlUbh5hyij8jKGC30KpbQ1LxgLF3m1cLuhezhgFbet0FnWTwJErW2Qd64AWovLqIOmfMKcJrXPMb405Gro9bXX0ewJlrXrzl383yHWXzgpu1Tb9SGfjW21HzuYBp50VLzsuEtkTKnFouWhIF6m4rT46enkICMZ8vOF9qUanlkKZ8aPFJSjPJX5O8pnDC1BiPX77tUJojj286KVV5Av6I9B9Itw8A0V2gZumzouD06KPaMkQlHsphSpBT7VpjPpneb3ebwLvj73Y15sCv5iwcbaDKFGTuZRdqtmCwx2l37zcGJgU6Ch8fbCNAgmFdYNJ0sveP7uiAvefZUWmlnFmQo6ASVSR5utAX2ha7bflNpNvwT6pbanbj49lcD00bY3DYwRWuMh2d8dVb1p9allRD9Tie2mJJO2wW5qmI3uHz9MzFBCRrkAwOhczPgT6gPtnleQXwtsdSU0zzeUjTYdu23ISbFdfCk9bgfRtIZkXa4faYbiRfy8jPkwjLNK9swCMcwxAtNe1LjK0DqRcGuVbTX5E2vpfDlfk60euiq1RO4TLEUuMaBpptJ7hAGQbTQrfD7rQr5s2QzKUNgM7jY3Pz0NtNRCDtousLLEzfvEmQkM4H95WGf4v24A564PdHfyY3xYflHU3pID5XyBUNi1XC2vga2Kq8ADaM5VKsE3o4BWIKpJcuESY0bOUcp1SOhSaqFiYjf3mmm9eUqo4elRwMqf37uOeNQ1TdvdvJlKRNHTi4umf6TYZr57zdtS4RuQ7HHW2NE5wQBBhmLcpoQyTquda7JCVSmmKo8G0h1C4VXe46bDTQvjJmrCw7JKMLncGbm7TEMsWMdiNxFPoXUDpxkXUSkjRAe0Uc9tPmeTUwq5uauperTQaR', 'sample_weZoLCxaYRuEbIZSAcw', 'sample_8eD325FzN79CIA1lx3hcxnIVFhKIlbppGhso8TzRvfqzVgovF4texBWt2e15D4TE8uXMELQHhSBEC2wHYIIOfKH8XCNMYbzkX06yuiY1u41mec1TesgBYKV3PW', X'D2A6D881052BECAA90E6BDA5DBAF542830D084', 'y,z', NULL);
WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT DISTINCT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC;
WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC;
© 2025, Oracle Corporation and/or its affiliates
Description: the column name change caused by DISTINCT,the first column name unexpectedly change from col_1 to col_2 mysql> WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT DISTINCT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC; +-------+-------+ | col_2 | col_2 | +-------+-------+ | 1 | 1 | +-------+-------+ 1 row in set (0.00 sec) mysql> WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC; +-------+-------+ | col_1 | col_2 | +-------+-------+ | 1 | 1 | +-------+-------+ 1 row in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE t3 ( c1 INT NOT NULL AUTO_INCREMENT, c2 INT NOT NULL, c3 INT NOT NULL, c4 YEAR NOT NULL, c5 TIME NULL, c6 TINYINT NULL, c7 SMALLINT NULL, c8 MEDIUMINT NULL, c9 BIGINT NULL, c10 LONGTEXT NULL, c11 VARCHAR(255) NULL, c12 TINYTEXT NULL, c13 TINYBLOB NULL, c14 SET('x','y','z') NULL, c15 TINYINT(1) NULL, PRIMARY KEY (c1) ); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (8417, 9828, 4869, 2023, '2023-01-01 21:39:34', 14, 68, 41, 89, 'sample_etX2AhIU3JkLkyrEOwJTBqpBStqOrUPbskI8gL9NI0mwroTZBFHdHYljUuEHNlUbh5hyij8jKGC30KpbQ1LxgLF3m1cLuhezhgFbet0FnWTwJErW2Qd64AWovLqIOmfMKcJrXPMb405Gro9bXX0ewJlrXrzl383yHWXzgpu1Tb9SGfjW21HzuYBp50VLzsuEtkTKnFouWhIF6m4rT46enkICMZ8vOF9qUanlkKZ8aPFJSjPJX5O8pnDC1BiPX77tUJojj286KVV5Av6I9B9Itw8A0V2gZumzouD06KPaMkQlHsphSpBT7VpjPpneb3ebwLvj73Y15sCv5iwcbaDKFGTuZRdqtmCwx2l37zcGJgU6Ch8fbCNAgmFdYNJ0sveP7uiAvefZUWmlnFmQo6ASVSR5utAX2ha7bflNpNvwT6pbanbj49lcD00bY3DYwRWuMh2d8dVb1p9allRD9Tie2mJJO2wW5qmI3uHz9MzFBCRrkAwOhczPgT6gPtnleQXwtsdSU0zzeUjTYdu23ISbFdfCk9bgfRtIZkXa4faYbiRfy8jPkwjLNK9swCMcwxAtNe1LjK0DqRcGuVbTX5E2vpfDlfk60euiq1RO4TLEUuMaBpptJ7hAGQbTQrfD7rQr5s2QzKUNgM7jY3Pz0NtNRCDtousLLEzfvEmQkM4H95WGf4v24A564PdHfyY3xYflHU3pID5XyBUNi1XC2vga2Kq8ADaM5VKsE3o4BWIKpJcuESY0bOUcp1SOhSaqFiYjf3mmm9eUqo4elRwMqf37uOeNQ1TdvdvJlKRNHTi4umf6TYZr57zdtS4RuQ7HHW2NE5wQBBhmLcpoQyTquda7JCVSmmKo8G0h1C4VXe46bDTQvjJmrCw7JKMLncGbm7TEMsWMdiNxFPoXUDpxkXUSkjRAe0Uc9tPmeTUwq5uauperTQaR', 'sample_weZoLCxaYRuEbIZSAcw', 'sample_8eD325FzN79CIA1lx3hcxnIVFhKIlbppGhso8TzRvfqzVgovF4texBWt2e15D4TE8uXMELQHhSBEC2wHYIIOfKH8XCNMYbzkX06yuiY1u41mec1TesgBYKV3PW', X'D2A6D881052BECAA90E6BDA5DBAF542830D084', 'y,z', NULL); WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT DISTINCT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC; WITH cte_953 AS (SELECT 1 AS col_1, 1 AS col_2, nmj99.c4 AS col_3 FROM t3 AS nmj99) SELECT cte.col_2 AS col_1, cte.col_2 AS col_2 FROM cte_953 AS cte WHERE NOT cte.col_1 BETWEEN 0 AND 0 ORDER BY cte.col_2 DESC;