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

Bogus error message for query using recursive CTE

    XMLWordPrintable

    Details

      Description

      If a recursive CTE refers to a materialized view/derived table then the query that uses
      this CTE returns a bogus error message:

       
      MariaDB [test]> create table t1 (a int);
      Query OK, 0 rows affected (0.06 sec)
       
      MariaDB [test]> insert into t1 values
          ->   (0), (1), (2), (3), (4);
      Query OK, 5 rows affected (0.01 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create table t2 (a int);
      Query OK, 0 rows affected (0.05 sec)
       
      MariaDB [test]> insert into t2 values
          ->   (1), (2), (3), (4), (5);
      Query OK, 5 rows affected (0.01 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create view v1 as
          ->   select a from t2 where a < 3
          ->   union
          ->   select a from t2 where a > 4; 
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> with recursive
          -> t1 as
          -> (
          -> select a from v1 where a=1 
          -> union
          -> select v1.a from t1,v1 where t1.a+1=v1.a
          -> )
          -> select * from t1;
      ERROR 4007 (HY000): Reference to recursive WITH table 't1' in materialized derived
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: