revision-id: 0ce31b93c4b85d17e467c922c8359949c18da7cb (mariadb-10.2.31-777-g0ce31b9) parent(s): 7759991a06d54630214f19eaa0ec39bd21bf09df author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-04 23:02:47 -0800 message: MDEV-25002 ON expressions cannot contain outer references 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/r/subselect.result | 38 +++++++++++++++++++++++++++ mysql-test/r/subselect_no_exists_to_in.result | 38 +++++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 38 +++++++++++++++++++++++++++ mysql-test/r/subselect_no_opts.result | 38 +++++++++++++++++++++++++++ mysql-test/r/subselect_no_scache.result | 38 +++++++++++++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 38 +++++++++++++++++++++++++++ mysql-test/t/subselect.test | 35 ++++++++++++++++++++++++ sql/sql_parse.cc | 2 ++ 8 files changed, 265 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index d4d61eb..a03a2cf 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7331,4 +7331,42 @@ 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 diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index fa9ab8b..f7da3fd 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -7331,6 +7331,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 set optimizer_switch=default; select @@optimizer_switch like '%exists_to_in=off%'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index c4d5b28..6ab304c 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7324,6 +7324,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 set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index fd1c527..338ddd5 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7322,5 +7322,43 @@ 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 set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 48d5bc9..741b070 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7337,6 +7337,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 set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 2df8073..ebabafb 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7322,6 +7322,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 # # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 7e16321..57ac431 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6188,4 +6188,39 @@ 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/sql/sql_parse.cc b/sql/sql_parse.cc index 7f75624..e8ab9a2 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8774,6 +8774,8 @@ 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(); + 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); }