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;