Details
Blocker 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?
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Oleksandr Byelkin [ sanja ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Igor Babaev [ igor ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
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.
| Assignee | Igor Babaev [ igor ] | Dmitry Shulga [ JIRAUSER47315 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.38 [ 25207 ] | |
| Fix Version/s | 10.3.29 [ 25206 ] | |
| Fix Version/s | 10.4.19 [ 25205 ] | |
| Fix Version/s | 10.5.10 [ 25204 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
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.
| Resolution | Fixed [ 1 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.5.10 [ 25204 ] | |
| Fix Version/s | 10.4.19 [ 25205 ] | |
| Priority | Major [ 3 ] | Blocker [ 1 ] |
This is now merged up to 10.4 (with adjustment provided by igor) and 10.5.
| issue.field.resolutiondate | 2021-03-19 11:15:05.0 | 2021-03-19 11:15:05.287 |
| Fix Version/s | 10.4.19 [ 25205 ] | |
| Fix Version/s | 10.5.10 [ 25204 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Assignee | Dmitry Shulga [ JIRAUSER47315 ] | Igor Babaev [ igor ] |
| Workflow | MariaDB v3 [ 119573 ] | MariaDB v4 [ 158969 ] |
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 |+------+------+