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

Bogus error for query with two usage of CTE referring another CTE

    XMLWordPrintable

Details

    Description

      When a double encapsulated CTE query calls a function which reads a table which has been aliased in the CTE query, the optimizer cannot find the table anymore and ends with an error 1146 Table db.t1 doesn't exist.

      Script to reproduce:

      CREATE TABLE t1 (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY);
      /* Generate 10'000 entries */
      INSERT INTO t1(id) SELECT @row := @row + 1 AS id FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3, 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4, 
      (SELECT @row:=0) numbers;
       
      CREATE OR REPLACE FUNCTION check_my_id(in_id INT) RETURNS INTEGER
      READS SQL DATA
      RETURN (SELECT id FROM t1 WHERE t1.id = in_id);
       
       
      WITH c1 AS (SELECT id	FROM t1 a WHERE check_my_id(id) = id GROUP BY id),
      	   c2 AS (SELECT pt.id FROM c1 pt GROUP BY pt.id)
      SELECT id FROM c2 GROUP BY id
      UNION ALL
      SELECT id FROM c2;
      

      If the table alias (a) is removed in the CTE c1, the query works.

      When I try on my real query (and not in the abbreviated example above), I sometimes get the error message and sometimes mysqld crashes with the error log attached.

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              vdg@plan-bee.ch Walter van der Geest
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.