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

Support derived tables / CTE with external references

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • 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

          is part of

          Task - A task that needs to be done. MDEV-28906 MySQL 8.0 desired compatibility

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            Description Currently the following query cannot be executed in MariaDB:
            {code:sql}
            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)
            {code}
            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.             		Currently the following query cannot be executed in MariaDB:
            {code:sql}
            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)
            {code}
            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:
            {code:sql}
            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)
            {code}
            cannot be executed in MariaDB while it is considered valid by SQL Standard and can be executed by MySQL 8.0.
            igor Igor Babaev (Inactive) made changes -
            Summary Support derived tables with external references Support derived tables / CTE with external references
            ralf.gebhardt Ralf Gebhardt made changes -
            NRE Projects RM_105_CANDIDATE
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 93595 ] MariaDB v4 [ 131029 ]
            AirFocus AirFocus made changes -
            Description Currently the following query cannot be executed in MariaDB:
            {code:sql}
            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)
            {code}
            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:
            {code:sql}
            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)
            {code}
            cannot be executed in MariaDB while it is considered valid by SQL Standard and can be executed by MySQL 8.0.
            		Currently the following query cannot be executed in MariaDB:

            {code:sql}
            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)
            {code}

            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:

            {code:sql}
            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)
            {code}

            cannot be executed in MariaDB while it is considered valid by SQL Standard and can be executed by MySQL 8.0.
            marko Marko Mäkelä made changes -
            abdel_eid Abdelrahman added a comment -

            is there any update for this, or any work around, Thanks

            abdel_eid Abdelrahman added a comment - is there any update for this, or any work around, Thanks
            Johnston Rex Johnston made changes -
            Assignee Igor Babaev [ igor ] Rex Johnston [ JIRAUSER52533 ]

            People

              Johnston Rex Johnston
              igor Igor Babaev (Inactive)
              Votes:
              5 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.