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)
|