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

        1. error.log
          11 kB
          Walter van der Geest

        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.