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

CTE, referencing another CTE, that is declared after, does not return error

    XMLWordPrintable

    Details

      Description

      CREATE TABLE t1 (i int);
      INSERT INTO t1 VALUES (1),(2),(3);
       
      WITH c1 AS (SELECT * FROM c2),
           c2 AS (SELECT * FROM t1)
      SELECT * FROM c1;
       
      WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3),
           c2 AS (SELECT * FROM t1)
      SELECT * FROM c1;
      

      MariaDB [test]> CREATE TABLE t1 (i int);
      Query OK, 0 rows affected (0.16 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (1),(2),(3);
      Query OK, 3 rows affected (0.06 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> WITH c1 AS (SELECT * FROM c2),
          ->      c2 AS (SELECT * FROM t1)
          -> SELECT * FROM c1;
      ERROR 1146 (42S02): Table 'test.c2' doesn't exist
       
      MariaDB [test]> WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3),
          ->      c2 AS (SELECT * FROM t1)
          -> SELECT * FROM c1;
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.00 sec)
      

      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                      |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | with c1 as (with c3 as (select `test`.`t1`.`i` AS `i` from `test`.`t1`)select `test`.`t1`.`i` AS `i` from `test`.`t1`), c2 as (select `test`.`t1`.`i` AS `i` from `test`.`t1`)select `test`.`t1`.`i` AS `i` from `test`.`t1` |
      +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

        Attachments

          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: