Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.31, 10.3.33, 10.2(EOL), 10.3(EOL)
Description
Looks like we get unexpected "ERROR 1046 (3D000): No database selected" when we refer to CTEs in SELECT with UNION clause used to define the table with CREATE OR REPLACE TEMPORARY TABLE AS. Consider the following test case:
openxs@ao756:~/dbs/maria10.3$ bin/mysql --socket=/tmp/mariadb.sock test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 8
|
Server version: 10.3.33-MariaDB MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [test]> with cte1 as (select 1 as a), cte2 as (select 2 as a) select * from cte1 union select * from cte2;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
+---+
|
2 rows in set (0.001 sec)
|
So, we have simple SELECT referring to CTEs in UNION that works and produced expected result. Now if we try to define a table based on it, we get the error:
MariaDB [test]> create or replace temporary table tmp as with cte1 as (select 1 as a), cte2 as (select 2 as a) select * from cte1 union select * from cte2;
|
ERROR 1046 (3D000): No database selected
|
Surely we can hide UNION in yet another CTE as a workaround:
MariaDB [test]> create or replace temporary table tmp as with cte1 as (select 1 as a), cte2 as (select 2 as a), cte3 as (select * from cte1 union select * from cte2) select * from cte3;
|
Query OK, 2 rows affected (0.021 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select * from tmp;
|
+---+
|
| a |
|
+---+
|
| 1 |
|
| 2 |
|
+---+
|
2 rows in set (0.001 sec)
|
and everything works as expected. But older 10.3.x versions (like 10.3.23) do NOT have this problem, and so I think we have a regression bug here.