revision-id: 45dd56e287ff2ab7397ca00da53ccd5768bee43f (mariadb-10.5.2-1038-g45dd56e287f) parent(s): e801d6fd0075915e58999d7d48251b3f91674c8e author: Oleksandr Byelkin committer: Sergei Petrunia timestamp: 2021-06-18 00:43:45 +0300 message: Probably fix --- mysql-test/main/alias_in_where.result | 59 +++++++++++++++++++++++++++++++++-- mysql-test/main/alias_in_where.test | 32 +++++++++++++++++++ sql/item.cc | 6 ++-- sql/item.h | 1 + sql/sql_select.cc | 13 ++++++-- 5 files changed, 104 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/alias_in_where.result b/mysql-test/main/alias_in_where.result index b66b3ed169a..1fb7d33e14f 100644 --- a/mysql-test/main/alias_in_where.result +++ b/mysql-test/main/alias_in_where.result @@ -383,7 +383,7 @@ set sql_mode="ALIASES_IN_WHERE"; create view v1 as select a, (select t1.a from t1 limit 1) as cc from t1; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,(select `t__1__0__`.`a` from `t1` `t__1__0__` limit 1) AS `cc` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `v1`.`a` AS `a`,(select `t__1__0__`.`a` from `t1` `t__1__0__` limit 1) AS `cc` from `t1` latin1 latin1_swedish_ci select * from v1; a cc 1 1 @@ -404,7 +404,7 @@ insert into t1 values (1,1), (2,2), (10, 10); create view v1 as select a+1 as d, (select b from t1 where d=a) as cc from t1; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + 1 AS `d`,(select `t__1__0__`.`b` from `t1` `t__1__0__` where `t1`.`a` + 1 = `t__1__0__`.`a`) AS `cc` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `a` + 1 AS `d`,(select `t__1__0__`.`b` from `t1` `t__1__0__` where `a` + 1 = `t__1__0__`.`a`) AS `cc` from `t1` latin1 latin1_swedish_ci select * from v1; d cc 2 2 @@ -424,4 +424,59 @@ d cc set sql_mode="ALIASES_IN_WHERE"; drop view v1; drop table t1; +# +# outer reference as part of GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t +where t1.a < 2) group by c; +c d +1 1 +# shoud be the same as above +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +c d +1 1 +prepare stmt from "select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c"; +create view v1 as select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,min(`t1`.`b`) AS `d` from `t1` where 5 < (select max(`t`.`b`) from `t1` `t` where `t1`.`a` < 2) group by `t1`.`a` latin1 latin1_swedish_ci +create procedure p1() +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +select * from v1; +c d +1 1 +execute stmt; +c d +1 1 +execute stmt; +c d +1 1 +call p1; +c d +1 1 +call p1; +c d +1 1 +set sql_mode=""; +select * from v1; +c d +1 1 +execute stmt; +c d +1 1 +execute stmt; +c d +1 1 +call p1; +c d +1 1 +call p1; +c d +1 1 +drop procedure p1; +drop view v1; +drop table t1; set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/alias_in_where.test b/mysql-test/main/alias_in_where.test index 7638e96ecc3..4eb626f60e1 100644 --- a/mysql-test/main/alias_in_where.test +++ b/mysql-test/main/alias_in_where.test @@ -295,4 +295,36 @@ set sql_mode="ALIASES_IN_WHERE"; drop view v1; drop table t1; + +--echo # +--echo # outer reference as part of GROUP BY +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t +where t1.a < 2) group by c; +--echo # shoud be the same as above +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; + +prepare stmt from "select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c"; +create view v1 as select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +show create view v1; +create procedure p1() +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +select * from v1; +execute stmt; +execute stmt; +call p1; +call p1; +set sql_mode=""; +select * from v1; +execute stmt; +execute stmt; +call p1; +call p1; + +drop procedure p1; +drop view v1; +drop table t1; set @@sql_mode= @save_sql_mode; diff --git a/sql/item.cc b/sql/item.cc index eb3cc17378e..1314ab5cce5 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8253,11 +8253,11 @@ bool Item_ref_alias::fix_fields(THD *thd, Item **reference) Item_outer_ref *rf= new(thd->mem_root) Item_outer_ref(thd, context, ref, {0, 0}, name, true, true); - rf->base_flags&= ~item_base_t::FIXED; if (!rf) return TRUE; + rf->base_flags&= ~item_base_t::FIXED; thd->change_item_tree(reference, rf); - context->select_lex->inner_refs_list.push_back(rf, thd->mem_root); + outer_finding->select_lex->inner_refs_list.push_back(rf, thd->mem_root); rf->in_sum_func= thd->lex->in_sum_func; mark_as_dependent(thd, outer_finding->select_lex, context->select_lex, @@ -9266,7 +9266,7 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference) err= Item_direct_ref::fix_fields(thd, reference); if (!outer_ref) outer_ref= *ref; - if ((*ref)->type() == Item::FIELD_ITEM) + if ((*ref)->type() == Item::FIELD_ITEM && !outer_alias) table_name= ((Item_field*)outer_ref)->table_name; return err; } diff --git a/sql/item.h b/sql/item.h index 04de60460db..3411c88295b 100644 --- a/sql/item.h +++ b/sql/item.h @@ -6273,6 +6273,7 @@ class Item_outer_ref :public Item_direct_ref else Item_direct_ref::print(str, query_type); } + bool is_outer_alias() { return outer_alias; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0f7fa6a81e9..ed0144d40b2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -649,8 +649,17 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, for (ORDER *group= select->join->group_list; group; group= group->next) { (*group->item)->walk(&Item::check_inner_refs_processor, TRUE, &ref_it); - } - + while ((ref= ref_it++)) + { + if ((*group->item) == *(ref->ref)) + { + //DBUG_ASSERT(ref->is_outer_alias() || ref->found_in_group_by); + ref->found_in_group_by= TRUE; + } + } + ref_it.rewind(); + } + while ((ref= ref_it++)) { bool direct_ref= false;