Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27086

"No database selected" when using UNION of CTEs to define table

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.