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

CTE with correlated subquery does not recognize column names from outer tables

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.2
    • N/A
    • Optimizer - CTE
    • None

    Description

      I don't know if it is supposed to work; if it is not, please feel free to close as not a bug.

      DROP TABLE IF EXISTS t1, t2;
       
      CREATE TABLE t1 (a INT);
      CREATE TABLE t2 (b INT);
       
      # This query works of course
      SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a );
       
      # But this does not
      SELECT * FROM t1 WHERE EXISTS ( WITH cte AS ( SELECT * FROM t2 WHERE b = a ) SELECT * FROM cte );
      

      MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a );
      Empty set (0.01 sec)
       
      MariaDB [test]> SELECT * FROM t1 WHERE EXISTS ( WITH cte AS ( SELECT * FROM t2 WHERE b = a ) SELECT * FROM cte );
      ERROR 1054 (42S22): Unknown column 'a' in 'where clause'
      

      The example is oversimplified and probably does not make much sense, it's just meant to demonstrate the problem, but I guess there might more realistic cases affected by it.

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.