[MDEV-27086] "No database selected" when using UNION of CTEs to define table Created: 2021-11-19  Updated: 2022-02-24  Resolved: 2021-11-21

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.31, 10.3.33, 10.2, 10.3
Fix Version/s: 10.2.42, 10.3.33

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression


 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.

 Comments   
Comment by Igor Babaev [ 2021-11-19 ]

In a general case CREATE TABLE ... AS has the same problem in 10.2, 10.3:

MariaDB [test]> create table t1 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

Comment by Oleksandr Byelkin [ 2021-11-20 ]

8388a825ccd6c5ce093c79108e1111e1139cef4a OK to push

Comment by Igor Babaev [ 2021-11-21 ]

A fix of this bug was pushed into 10.2. It is to be merged to 10.3 only.

Generated at Thu Feb 08 09:50:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.