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

CTE table should not belong to database, that is in use

    Details

      Description

      CTE table formally does not belong to database, but its behavior is different from derived table.
      testcase:

      WITH cte AS (SELECT 1) SELECT * from cte; # returns error if database is not selected
      USE test;
      WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte; #works, even if database test does not exist
      CREATE DATABASE test;
      USE test;
      WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte; # works
      SELECT test.d1.a FROM (SELECT 1 AS a) d1;		# error, derived table does not belong to database test;
      SELECT d1.a FROM (SELECT 1 AS a) d1;	# works
      

      MariaDB [(none)]> WITH cte AS (SELECT 1) SELECT * from cte;
      ERROR 1046 (3D000): No database selected
       
      MariaDB [(none)]> USE test;
      ERROR 1049 (42000): Unknown database 'test'
       
      MariaDB [(none)]> WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte;
      +---+
      | a |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
       
      MariaDB [(none)]> CREATE DATABASE test;
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [(none)]> USE test;
      Database changed
       
      MariaDB [test]> WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte; 
      +---+
      | a |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT test.d1.a FROM (SELECT 1 AS a) d1;
      ERROR 1054 (42S22): Unknown column 'test.d1.a' in 'field list'
       
      MariaDB [test]> SELECT d1.a FROM (SELECT 1 AS a) d1;
      +---+
      | a |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
      
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                alice Alice Sherepa
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: