[MDEV-19077] Support derived tables / CTE with external references Created: 2019-03-28  Updated: 2023-08-01

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Unresolved Votes: 5
Labels: None

Issue Links:
PartOf
is part of MDEV-28906 MySQL 8.0 desired compatibility Open

 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.

 Comments   
Comment by Abdelrahman [ 2022-10-20 ]

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

Generated at Thu Feb 08 08:48:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.