[MDEV-25002] ON expressions cannot contain outer references Created: 2021-02-26  Updated: 2021-03-23  Resolved: 2021-03-19

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Blocker
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

Outside references in LEFT JOIN's ON expression produce "unknown column" error.

Create the test dataset (As far as I'm aware, there's nothing special about this choice of tables/columns):

create table t10 (
 
  pk int primary key,
 
  a int
 
);
 
insert into t10 values (1,1);
 
 
 
create table t11 (
 
  pk int primary key,
 
  b int
 
);
 
create table t12 (a int);
 
insert into t12 values (1),(2);

Run the query Q1:

select
 
  a,
 
  (select count(*)
 
   from 
     t10 left join t11 on t11.pk=t12.a
 
   where 
     t10.pk=1
 
   ) as SUBQ
 
from t12;


ERROR 1054 (42S22): Unknown column 't12.a' in 'on clause'

Wrapping the outside reference in a subquery helps: Q2:

select
 
  a,
 
  (select count(*)
 
   from 
     t10 left join t11 on t11.pk=(select t12.a from dual)
 
   where 
     t10.pk=1
 
   ) as SUBQ
 
from t12;


+------+------+
 
| a    | SUBQ |
 
+------+------+
 
|    1 |    1 |
 
|    2 |    1 |
 
+------+------+

I've tried Q1 on MySQL 8.0.22 or PostgreSQL and it worked in both.

Is it a bug MariaDB produces this error?

 Comments   
Comment by Igor Babaev [ 2021-03-05 ]

A similar query

select
 
  a,
 
  (select count(*)
 
   from 
     t10 join t11 on t11.pk=t12.a
 
   where 
     t10.pk=1
 
   ) as SUBQ
 
from t12;

with JOIN instead of LEFT JOIN also returns an error message:

MariaDB [test]> select
 
    ->   a,
 
    ->   (select count(*)
 
    ->    from 
    ->      t10 join t11 on t11.pk=t12.a
 
    ->    where 
    ->      t10.pk=1
 
    ->    ) as SUBQ
 
    -> from t12;
 
ERROR 1054 (42S22): Unknown column 't12.a' in 'on clause'

If we move the ON condition to WHERE

select
 
  a,
 
  (select count(*)
 
   from 
     t10 join t11 
   where 
    t11.pk=t12.a and t10.pk=1
 
   ) as SUBQ
 
from t12;

the query returns the expected result set:

MariaDB [test]> select
 
    ->   a,
 
    ->   (select count(*)
 
    ->    from 
    ->      t10 join t11 
    ->    where 
    ->     t11.pk=t12.a and t10.pk=1
 
    ->    ) as SUBQ
 
    -> from t12;
 
+------+------+
 
| a    | SUBQ |
 
+------+------+
 
|    1 |    0 |
 
|    2 |    0 |
 
+------+------+

Comment by Igor Babaev [ 2021-03-05 ]

Let's add a couple of rows to t11

insert into t11 values (1,1), (2,3);

and run the query

select a from t12 where a in (select t11.b from t10 join t11 where t11.pk=t12.a and t10.pk=1);

we get

MariaDB [test]> select a from t12 where a in (select t11.b from t10 join t11 where t11.pk=t12.a and t10.pk=1);
 
+------+
 
| a    |
 
+------+
 
|    1 |
 
+------+

However if in the subquery we move t11.pk=t12.a to the ON clause 

select a from t12 where a in (select t11.b from t10 join t11 on t11.pk=t12.a where t10.pk=1);

again we get an error message

MariaDB [test]> select a from t12 where a in (select t11.b from t10 join t11 on t11.pk=t12.a where t10.pk=1);
 
ERROR 1054 (42S22): Unknown column 't12.a' in 'on clause'

Comment by Igor Babaev [ 2021-03-05 ]

Apparently outer references in ON clauses cannot be identified.
Debugging shows that the problem appears because the outer context for Name resolution context of fields in ON clauses is set to 0.

Comment by Dmitry Shulga [ 2021-03-09 ]

OK to push

Comment by Igor Babaev [ 2021-03-09 ]

A fix for this bug was pushed into 10.2

Comment by Marko Mäkelä [ 2021-03-18 ]

igor or shulga, can you please provide a 10.4 version of this?
My attempt of merging this shows some test failures.

In my opinion, fixes should be tested at least in the oldest and newest major version branch to find this kind of problems in advance.

Comment by Marko Mäkelä [ 2021-03-19 ]

This is now merged up to 10.4 (with adjustment provided by igor) and 10.5.

Generated at Thu Feb 08 09:34:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.