revision-id: 0f649474adac7f0a7f1f5d1b932ca620c4ea3aa4 (mariadb-10.5.2-486-g0f649474ada) parent(s): cbc2296a8c53d67685da97041c391a8701c34291 author: Igor Babaev committer: Sergei Petrunia timestamp: 2021-04-04 22:04:57 +0300 message: MDEV-25002 ON expressions cannot contain outer references (Cherry-pick into 10.6-json_table) A bogus error message was issued for any outer references occurred in ON expressions used in subqueries. This prevented execution of queries containing subqueries as soon as they used outer references in their ON clauses. This happened because the Name_resolution_context structure created for any ON expression erroneously had the field outer_context set to NULL. The fields select_lex of this structure was not set correctly either. The idea of the fix was taken from mysql code of the function push_new_name_resolution_context(). Approved by dmitry.shulga@mariadb.com --- mysql-test/main/subselect.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect.test | 35 ++++++++++++++++++++++ mysql-test/main/subselect_no_exists_to_in.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_mat.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_opts.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_scache.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_semijoin.result | 38 ++++++++++++++++++++++++ sql/sql_parse.cc | 4 ++- 8 files changed, 266 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 1ee4718ef10..167382fc328 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7342,6 +7342,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index f6333c22684..a58a08e1a58 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6169,6 +6169,41 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; +--echo # +--echo # MDEV-25002: Outer reference in ON clause of subselect +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=myisam; +insert into t1 values (1,1), (2,2); + +create table t2 ( + pk int primary key, + b int +) engine=myisam; +insert into t2 values (1,1), (2,3); + +create table t3 (a int); +insert into t3 values (1),(2); + +select a, + (select count(*) from t1, t2 + where t2.pk=t3.a and t1.pk=1) as sq +from t3; +select a, + (select count(*) from t1 join t2 on t2.pk=t3.a + where t1.pk=1) as sq +from t3; + +select a from t3 + where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +select a from t3 + where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); + +drop table t1,t2,t3; + --echo # End of 10.2 tests diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 1436f98d9f8..70add4d1740 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7342,6 +7342,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 8100c1da7e5..f0c7033803c 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 86c89f9bd5f..8ff79c1790b 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7333,6 +7333,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 5dc8040698f..37ca0d4c030 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7348,6 +7348,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index f9bbf2e00c4..1abdde5ff7f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7333,6 +7333,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index ed2bcb46949..a0853f13836 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -9002,7 +9002,9 @@ push_new_name_resolution_context(THD *thd, left_op->first_leaf_for_name_resolution(); on_context->last_name_resolution_table= right_op->last_leaf_for_name_resolution(); - return thd->lex->push_context(on_context); + on_context->select_lex = thd->lex->current_select; + on_context->outer_context = thd->lex->current_context()->outer_context; + return thd->lex->push_context(on_context, thd->mem_root); }