[MDEV-19077] Support derived tables / CTE with external references - Jira

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.

is part of

MDEV-28906 MySQL 8.0 desired compatibility

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

MariaDB Server