[MDEV-10061] Subquery with CTE does not see tables from the outer query Created: 2016-05-12  Updated: 2019-04-26  Resolved: 2017-01-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE, Parser
Affects Version/s: 10.2
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: CTE

Issue Links:
Duplicate
is duplicated by MDEV-19337 CTEs not working with outer references Closed
Relates

 Description   

Note: I'm not sure whether it's supposed to work, but it looks suspicious.

create table t1 (i int);
 
create table t2 (j int);
 
 
 
# This of course works:
 
select * from t1 where exists ( select * from t2 where j = i );
 
# Empty set (0.01 sec)
 
 
 
# But this does not:
 
select * from t1 where exists ( with cte as ( select * from t2 where j = i ) select * from cte );
 
# ERROR 1054 (42S22): Unknown column 'i' in 'where clause'



 Comments   
Comment by Elena Stepanova [ 2016-09-07 ]

Works in PostgreSQL.

Comment by Sergei Petrunia [ 2016-09-07 ]

Adding a bit of formatting to the query:

select * from t1 
where exists ( with 
                cte as ( select * from t2 
                          where t2.j = t1.i ) 
              select * from cte 
             );

so, the CTE definition has a reference to t1.i, to a field in a parent select.

This is hard to implement in MySQL or MariaDB, because both of these assume that derived tables cannot be "correlated", i.e. cannot depend on outer tables.

Perhaps, PostreSQL is able to handle this due to their support for "LATERAL" queries?

Comment by Elena Stepanova [ 2016-09-07 ]

Disclaimer: I didn't check whether it works correctly in PostgreSQL, only that it does not return an error.

Comment by Igor Babaev [ 2017-01-30 ]

This is not bug.
We do not allow outer references in CTE as we do not allow them in derived tables:

MariaDB [test]> select * from t1 where exists (select * from (select * from t2 where j = i) as cte);
 
ERROR 1054 (42S22): Unknown column 'i' in 'where clause'

Generated at Thu Feb 08 07:39:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.