Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.2(EOL)
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' |
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
Activity
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?
Disclaimer: I didn't check whether it works correctly in PostgreSQL, only that it does not return an error.
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'
|
Works in PostgreSQL.