diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 40 | ||||
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 40 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 50 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 31 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 4 |
5 files changed, 164 insertions, 1 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index fdc36b74f63..1995640638a 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2682,4 +2682,44 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 6a6b1007866..58df4203c17 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2690,6 +2690,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch_jcl6; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index c5b2c985ff7..9ad2c48bfca 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -2190,4 +2190,54 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests +--echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + SET optimizer_switch=@org_optimizer_switch; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index be5905da683..5a85b7ea7e3 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -563,6 +563,32 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) /* + @brief + Prevent name resolution out of context of ON expressions in derived tables + + @param + join_list list of tables used in from list of a derived + + @details + The function sets the Name_resolution_context::outer_context to NULL + for all ON expressions contexts in the given join list. It does this + recursively for all nested joins the list contains. +*/ + +static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + while (TABLE_LIST *table= li++) + { + if (table->on_context) + table->on_context->outer_context= NULL; + if (table->nested_join) + nullify_outer_context_for_on_clauses(table->nested_join->join_list); + } +} + + +/* Create temporary table structure (but do not fill it) @param thd Thread handle @@ -695,7 +721,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) { + // Prevent it for the WHERE clause sl->context.outer_context= 0; + + // And for ON clauses, if there are any + nullify_outer_context_for_on_clauses(*sl->join_list); + if (!derived->is_with_table_recursive_reference() || (!derived->with->with_anchor && !derived->with->is_with_prepared_anchor())) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 573df24cb33..9436e111043 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -7418,6 +7418,7 @@ mysql_new_select(LEX *lex, bool move_down) DBUG_RETURN(1); select_lex->select_number= ++thd->lex->stmt_lex->current_select_number; select_lex->parent_lex= lex; /* Used in init_query. */ + Name_resolution_context *curr_context= lex->context_stack.head(); select_lex->init_query(); select_lex->init_select(); lex->nest_level++; @@ -7448,7 +7449,8 @@ mysql_new_select(LEX *lex, bool move_down) By default we assume that it is usual subselect and we have outer name resolution context, if no we will assign it to 0 later */ - select_lex->context.outer_context= &select_lex->outer_select()->context; + + select_lex->context.outer_context= curr_context; } else { |