[Commits] de5aeee3dae: MDEV-20129: Equality propagation for ORDER BY items do not work with expressions

varun varunraiko1803 at gmail.com
Wed Sep 30 17:53:35 EEST 2020


revision-id: de5aeee3daef05f39889b5df073261cb78a04e6e (mariadb-10.5.2-272-gde5aeee3dae)
parent(s): 3b72b35a776b473c15df5afa5846b859797d9473
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-09-30 20:13:28 +0530
message:

MDEV-20129: Equality propagation for ORDER BY items do not work with expressions

Equality propagation for ORDER BY clause should be allowed for
expression. This would allow the expression in the ORDER BY clause
to be substitute with the best field.

Also moved the function make_sortorder from execution to optimization
stage.

---
 mysql-test/main/order_by.result | 103 ++++++++++++++++++++++++++++++++++++++++
 mysql-test/main/order_by.test   |  52 ++++++++++++++++++++
 sql/filesort.cc                 |   2 +-
 sql/filesort.h                  |   1 +
 sql/item.cc                     |   4 ++
 sql/sql_delete.cc               |   1 +
 sql/sql_select.cc               |  24 +++++-----
 sql/sql_table.cc                |   2 +
 sql/sql_update.cc               |   1 +
 sql/sql_window.cc               |   5 ++
 10 files changed, 183 insertions(+), 12 deletions(-)

diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 29a31a9c28c..3b9dc758817 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -4260,4 +4260,107 @@ a	group_concat(t1.b)
 96	2
 58	1
 DROP TABLE t1, t2;
+#
+# MDEV-20129: Equality propagation for ORDER BY items do not work with expressions
+#
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 select A.a + B.a* 10, A.a + B.a* 10 from ten A, ten B;
+create table t2(a int, b int, key(a));
+insert into t2 select A.a + B.a* 10, A.a+B.a*10 from ten A, ten B;
+should have Using  Filesort only
+explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
+select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a;
+a	b	a	b
+96	96	96	96
+97	97	97	97
+98	98	98	98
+99	99	99	99
+should have Using  Filesort only with expressions too
+explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a+t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
+select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a+t1.b;
+a	b	a	b
+96	96	96	96
+97	97	97	97
+98	98	98	98
+99	99	99	99
+drop table t1,t2,ten;
+CREATE TABLE t1(a int, b int);
+CREATE TABLE t2(a int, b int, key(a));
+INSERT INTO t1 VALUES (1,1), (2,2);
+INSERT INTO t2 VALUES (1,4), (1,5), (2,4), (2,5);
+INSERT INTO t2 VALUES (1,6), (1,7), (2,6), (2,7);
+INSERT INTO t2 VALUES (1,9), (1,8), (2,8), (2,9);
+CREATE VIEW v1 AS
+SELECT t1.a as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a;
+# should have Using Filesort only
+explain SELECT c,d FROM v1 ORDER BY c DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	
+SELECT c,d FROM v1 ORDER BY c DESC;
+c	d
+2	4
+2	5
+2	6
+2	7
+2	8
+2	9
+1	4
+1	5
+1	6
+1	7
+1	9
+1	8
+CREATE VIEW v2 AS
+SELECT t1.b+t2.a as c, abs(t2.a) as d, t2.b as e FROM t1,t2 WHERE t1.a=t2.a;
+views use Item_direct_view_ref that are expressions
+should have Using Filesort only
+explain SELECT c,e FROM v2 ORDER BY c DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	
+SELECT c,e FROM v2 ORDER BY c DESC;
+c	e
+4	4
+4	5
+4	6
+4	7
+4	8
+4	9
+2	4
+2	5
+2	6
+2	7
+2	9
+2	8
+views use Item_direct_view_ref that are expressions
+should have Using Filesort only
+explain SELECT d,e FROM v2 ORDER BY d DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	2	
+SELECT d,e FROM v2 ORDER BY d DESC;
+d	e
+2	4
+2	5
+2	6
+2	7
+2	8
+2	9
+1	4
+1	5
+1	6
+1	7
+1	9
+1	8
+DROP TABLE t1,t2;
+DROP VIEW v1,v2;
 # End of 10.6 tests
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index fd87f54cc3d..4c4c22fbf66 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2591,4 +2591,56 @@ eval EXPLAIN FORMAT=JSON $query;
 eval $query;
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-20129: Equality propagation for ORDER BY items do not work with expressions
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1(a int, b int);
+insert into t1 select A.a + B.a* 10, A.a + B.a* 10 from ten A, ten B;
+create table t2(a int, b int, key(a));
+insert into t2 select A.a + B.a* 10, A.a+B.a*10 from ten A, ten B;
+
+--echo should have Using  Filesort only
+explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a;
+select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a;
+
+--echo should have Using  Filesort only with expressions too
+explain select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a+t1.b;
+select * from t1,t2 where t1.a > 95  and t1.a=t2.a order by t2.a+t1.b;
+drop table t1,t2,ten;
+
+CREATE TABLE t1(a int, b int);
+CREATE TABLE t2(a int, b int, key(a));
+INSERT INTO t1 VALUES (1,1), (2,2);
+INSERT INTO t2 VALUES (1,4), (1,5), (2,4), (2,5);
+INSERT INTO t2 VALUES (1,6), (1,7), (2,6), (2,7);
+INSERT INTO t2 VALUES (1,9), (1,8), (2,8), (2,9);
+CREATE VIEW v1 AS
+SELECT t1.a as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a;
+
+--echo # should have Using Filesort only
+let $query= SELECT c,d FROM v1 ORDER BY c DESC;
+eval explain $query;
+eval $query;
+
+CREATE VIEW v2 AS
+SELECT t1.b+t2.a as c, abs(t2.a) as d, t2.b as e FROM t1,t2 WHERE t1.a=t2.a;
+--echo views use Item_direct_view_ref that are expressions
+--echo should have Using Filesort only
+let $query= SELECT c,e FROM v2 ORDER BY c DESC;
+eval explain $query;
+eval $query;
+
+
+--echo views use Item_direct_view_ref that are expressions
+--echo should have Using Filesort only
+let $query= SELECT d,e FROM v2 ORDER BY d DESC;
+eval explain $query;
+eval $query;
+
+DROP TABLE t1,t2;
+DROP VIEW v1,v2;
+
 --echo # End of 10.6 tests
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 2a713ecf97b..180a69baa79 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -208,7 +208,7 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort,
 
   DBUG_ENTER("filesort");
 
-  if (!(sort_keys= filesort->make_sortorder(thd, join, first_table_bit)))
+  if (!(sort_keys= filesort->get_sort_keys()))
     DBUG_RETURN(NULL);  /* purecov: inspected */
 
   s_length= static_cast<uint>(sort_keys->size());
diff --git a/sql/filesort.h b/sql/filesort.h
index 29ae5e20cc6..af3af3ca4a5 100644
--- a/sql/filesort.h
+++ b/sql/filesort.h
@@ -90,6 +90,7 @@ class Filesort: public Sql_alloc
   Sort_keys* make_sortorder(THD *thd, JOIN *join, table_map first_table_bit);
   /* Unpack temp table columns to base table columns*/
   void (*unpack)(TABLE *);
+  Sort_keys* get_sort_keys() { return sort_keys; }
 
 private:
   void cleanup();
diff --git a/sql/item.cc b/sql/item.cc
index 52274380cd1..f3c673492ae 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9138,7 +9138,11 @@ Item *Item_direct_view_ref::propagate_equal_fields(THD *thd,
 {
   Item *field_item= real_item();
   if (field_item->type() != FIELD_ITEM)
+  {
+    Item *res= field_item->propagate_equal_fields(thd, ctx, cond);
+    DBUG_ASSERT(res == field_item);
     return this;
+  }
   Item *item= field_item->propagate_equal_fields(thd, ctx, cond);
   set_item_equal(field_item->get_item_equal());
   field_item->set_item_equal(NULL);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 7280236e43f..0c679205db6 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -630,6 +630,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   {
     {
       Filesort fsort(order, HA_POS_ERROR, true, select);
+      fsort.make_sortorder(thd, NULL, table->map);
       DBUG_ASSERT(query_plan.index == MAX_KEY);
 
       Filesort_tracker *fs_tracker=
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 420a64ba827..db79e4cbd05 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3864,6 +3864,8 @@ JOIN::add_sorting_to_table(JOIN_TAB *tab, ORDER *order)
   if (!tab->filesort)
     return true;
 
+  tab->filesort->make_sortorder(thd, this, tab->table->map);
+
   TABLE *table= tab->table;
   if ((tab == join_tab + const_tables) &&
        table->pos_in_table_list &&
@@ -14253,7 +14255,6 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
           bool can_subst_to_first_table= false;
           if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_ORDERBY_EQ_PROP) &&
               first_is_base_table &&
-              order->item[0]->real_item()->type() == Item::FIELD_ITEM &&
               join->cond_equal)
           {
             table_map first_table_bit=
@@ -14273,16 +14274,17 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
                                                     Value_source::
                                                     Context_identity(),
                                                     join->cond_equal);
-            Item_equal *item_eq;
-            if ((item_eq= res->get_item_equal()))
-            {
-              Item *first= item_eq->get_first(NO_PARTICULAR_TAB, NULL);
-              if (first->const_item() || first->used_tables() ==
-                                         first_table_bit)
-              {
-                can_subst_to_first_table= true;
-              }
-            }
+
+            res=
+            substitute_for_best_equal_field(join->thd, NO_PARTICULAR_TAB,
+                                            res,
+                                            join->cond_equal,join->map2table,
+                                            true);
+            res->update_used_tables();
+
+            if (!(res->used_tables() & ~ first_table_bit))
+              can_subst_to_first_table= true;
+
           }
 
           if (!can_subst_to_first_table)
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 15d190c3139..396d27eb652 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -11224,6 +11224,8 @@ copy_data_between_tables(THD *thd, TABLE *from, TABLE *to,
                       &tables, fields, all_fields, order))
         goto err;
 
+      fsort.make_sortorder(thd, NULL, from->map);
+
       if (!(file_sort= filesort(thd, from, &fsort, &dummy_tracker)))
         goto err;
     }
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index fccc2a426c4..9b997a5401f 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -769,6 +769,7 @@ int mysql_update(THD *thd,
         NOTE: filesort will call table->prepare_for_position()
       */
       Filesort fsort(order, limit, true, select);
+      fsort.make_sortorder(thd, NULL, table->map);
 
       Filesort_tracker *fs_tracker= 
         thd->lex->explain->get_upd_del_plan()->filesort_tracker;
diff --git a/sql/sql_window.cc b/sql/sql_window.cc
index af6a73006a8..60b186c1679 100644
--- a/sql/sql_window.cc
+++ b/sql/sql_window.cc
@@ -3076,6 +3076,11 @@ bool Window_funcs_sort::setup(THD *thd, SQL_SELECT *sel,
   }
   filesort= new (thd->mem_root) Filesort(sort_order, HA_POS_ERROR, true, NULL);
 
+  if (!filesort)
+    return true;
+
+  filesort->make_sortorder(thd, join_tab->join, join_tab->table->map);
+
   /* Apply the same condition that the subsequent sort has. */
   filesort->select= sel;
 


More information about the commits mailing list