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

Support derived tables / CTE with external references

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Currently the following query cannot be executed in MariaDB:

      SELECT * FROM t1
        WHERE t1.a IN (SELECT t.c FROM (SELECT t2.a, count(t2.b) AS c
                                                                  WHERE t2.a < t1.b GROUP BY t2.a) AS t)

      cannot be executed in MariaDB because the specification of the derived table t contains an external reference t1.b.

      SQL standard considers this query as valid.

      MySQL 8.0 supports such queries.

      Similarly the query:

      SELECT * FROM t1
        WHERE t1.a IN (WITH t(c) AS  (SELECT t2.a, count(t2.b) AS c
                                                            WHERE t2.a < t1.b GROUP BY t2.a)
                                 SELECT t.c FROM t)

      cannot be executed in MariaDB while it is considered valid by SQL Standard and can be executed by MySQL 8.0.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
              Votes:
              4 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.