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,
(selectcount(*)
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 |
+------+------+
Igor Babaev (Inactive)
added a comment - 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 |
+------+------+
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'
Igor Babaev (Inactive)
added a comment - 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'
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.
Igor Babaev (Inactive)
added a comment - 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.
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.
Marko Mäkelä
added a comment - 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.
Blocker
A similar query
selecta,t10.pk=1with 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
selecta,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 |+------+------+