[Commits] 45dd56e287f: Probably fix

psergey sergey at mariadb.com
Fri Jun 18 00:43:45 EEST 2021


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;


More information about the commits mailing list