Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.12, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
The bug described in MDEV-15478 is not fully resolved. When the CTE is used again with UNION in the main statement, it gives error #1054 - Unknown column 'col1' in 'field list'
WITH cte (col1) AS |
(
|
SELECT 1 |
UNION |
SELECT 2 |
)
|
SELECT col1 FROM cte |
-- union statement added to example in MDEV-15478
|
UNION
|
SELECT col1 FROM cte |
This can currently be worked around with:
WITH cte (col1) AS |
(
|
SELECT 1 AS col1 -- alias required |
UNION |
SELECT 2 |
)
|
SELECT col1 FROM cte |
UNION
|
SELECT col1 FROM cte |
Attachments
Issue Links
- relates to
-
-
- Closed
-
Thanks for the report!
MariaDB [test]> WITH cte (col1) AS (SELECT 1 UNION SELECT 2) SELECT col1 FROM cte UNION SELECT col1 FROM cte;ERROR 1054 (42S22): Unknown column 'col1' in 'field list'MariaDB [test]> WITH cte (col1) AS (SELECT 1 UNION SELECT 2) SELECT col1 FROM cte UNION SELECT * FROM cte;+------+| col1 |+------+| 1 || 2 |+------+2 rows in set (0.000 sec)MariaDB [test]> explain extended WITH cte (col1) AS (SELECT 1 UNION SELECT 2) SELECT col1 FROM cte UNION SELECT * FROM cte;+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | || 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | || 4 | UNION | <derived5> | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | || 5 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || 6 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || NULL | UNION RESULT | <union5,6> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | || NULL | UNION RESULT | <union1,4> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |+------+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+9 rows in set, 1 warning (0.001 sec)Note (Code 1003): with cte(`col1`) as (/* select#2 */ select 1 AS `col1` union /* select#3 */ select 2 AS `2`)/* select#1 */ select `cte`.`col1` AS `col1` from `cte` union /* select#4 */ select `cte`.`1` AS `1` from (/* select#5 */ select 1 AS `col1` union /* select#6 */ select 2 AS `2`) `cte`MariaDB [test]> WITH cte (col1) AS (SELECT 1 UNION SELECT 2) SELECT col1 FROM cte UNION SELECT `1` FROM cte;+------+| col1 |+------+| 1 || 2 |+------+2 rows in set (0.001 sec)