diff options
| -rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 463 | ||||
| -rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 74 | ||||
| -rw-r--r-- | sql/sql_derived.cc | 42 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 5 | ||||
| -rw-r--r-- | sql/sql_lex.h | 4 | ||||
| -rw-r--r-- | sql/sql_select.h | 10 |
6 files changed, 584 insertions, 14 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 028a1120419..a196709dad1 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -8783,6 +8783,469 @@ EXPLAIN DROP VIEW v2; DROP TABLE t1,t2; # +# MDEV-10855: Pushdown into derived with window functions +# +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='split_grouping_derived=off'; +create table t1 (a int, c varchar(16)); +insert into t1 values +(8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'), +(7,'aa'), (2,'aa'), (7,'bb'); +create table t2 (a int, b int, c varchar(16), index idx(a,c)); +insert into t2 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'bb'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'bb'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from (select a, c, sum(b) over (partition by a,c) from t2) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +a c sum(b) over (partition by a,c) +7 cc 28 +7 cc 28 +3 aa 92 +7 bb 126 +4 aa 15 +7 bb 126 +7 bb 126 +3 bb 40 +3 aa 92 +select * from (select a, c, sum(b) over (partition by a,c) from t2) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +a c sum(b) over (partition by a,c) +7 cc 28 +7 cc 28 +3 aa 92 +7 bb 126 +4 aa 15 +7 bb 126 +7 bb 126 +3 bb 40 +3 aa 92 +explain select * from (select a, c, sum(b) over (partition by a,c) from t2) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 Using where +2 DERIVED t2 ALL idx NULL NULL NULL 20 Using where; Using temporary +explain format=json select * from (select a, c, sum(b) over (partition by a,c) from t2) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t.a > 2 and t.c in ('aa','bb','cc')", + "materialized": { + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a, t2.c" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2 and t2.c in ('aa','bb','cc')" + } + } + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +n a c s +1 7 cc 28 +1 7 cc 28 +1 3 aa 92 +1 7 bb 126 +1 4 aa 15 +1 7 bb 126 +1 7 bb 126 +1 3 bb 40 +1 3 aa 92 +2 7 cc 154 +2 7 cc 154 +2 3 aa 132 +2 7 bb 154 +2 4 aa 139 +2 7 bb 154 +2 7 bb 154 +2 3 bb 132 +2 3 aa 132 +select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +n a c s +1 7 cc 28 +1 7 cc 28 +1 3 aa 92 +1 7 bb 126 +1 4 aa 15 +1 7 bb 126 +1 7 bb 126 +1 3 bb 40 +1 3 aa 92 +2 7 cc 154 +2 7 cc 154 +2 3 aa 132 +2 7 bb 154 +2 4 aa 139 +2 7 bb 154 +2 7 bb 154 +2 3 bb 132 +2 3 aa 132 +explain select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 32 Using where +2 DERIVED t2 ALL idx NULL NULL NULL 20 Using where; Using temporary +3 UNION t2 ALL idx NULL NULL NULL 20 Using where; Using temporary +explain format=json select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 32, + "filtered": 100, + "attached_condition": "t.a > 2 and t.c in ('aa','bb','cc')", + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a, t2.c" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2 and t2.c in ('aa','bb','cc')" + } + } + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2" + } + } + } + } + } + ] + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_for_derived=off' for select * +from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 +where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); +a c s a c +1 bb 30 1 bb +7 bb 126 7 bb +7 bb 126 7 bb +7 bb 126 7 bb +select * +from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 +where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); +a c s a c +1 bb 30 1 bb +7 bb 126 7 bb +7 bb 126 7 bb +7 bb 126 7 bb +explain select * +from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 +where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived2> ref key0 key0 24 test.t1.a,test.t1.c 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 20 Using where; Using temporary +explain format=json select * +from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 +where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t1.c in ('aa','bb','cc') and t1.a is not null and t1.c is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "24", + "used_key_parts": ["a", "c"], + "ref": ["test.t1.a", "test.t1.c"], + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a, t2.c" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 20, + "filtered": 100, + "attached_condition": "t2.c in ('aa','bb','cc')" + } + } + } + } + } + } + } +} +set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +union all +select 3 as n, a, c, sum(b) as s from t2 group by a +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +n a c s +1 7 cc 28 +1 7 cc 28 +1 3 aa 92 +1 7 bb 126 +1 4 aa 15 +1 7 bb 126 +1 7 bb 126 +1 3 bb 40 +1 3 aa 92 +2 7 cc 154 +2 7 cc 154 +2 3 aa 132 +2 7 bb 154 +2 4 aa 139 +2 7 bb 154 +2 7 bb 154 +2 3 bb 132 +2 3 aa 132 +3 3 aa 132 +3 7 cc 154 +select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +union all +select 3 as n, a, c, sum(b) as s from t2 group by a +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +n a c s +1 7 cc 28 +1 7 cc 28 +1 3 aa 92 +1 7 bb 126 +1 4 aa 15 +1 7 bb 126 +1 7 bb 126 +1 3 bb 40 +1 3 aa 92 +2 7 cc 154 +2 7 cc 154 +2 3 aa 132 +2 7 bb 154 +2 4 aa 139 +2 7 bb 154 +2 7 bb 154 +2 3 bb 132 +2 3 aa 132 +3 3 aa 132 +3 7 cc 154 +explain select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +union all +select 3 as n, a, c, sum(b) as s from t2 group by a +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 48 Using where +2 DERIVED t2 ALL idx NULL NULL NULL 20 Using where; Using temporary +3 UNION t2 ALL idx NULL NULL NULL 20 Using where; Using temporary +4 UNION t2 ALL idx NULL NULL NULL 20 Using where; Using temporary; Using filesort +explain format=json select * from +( +select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 +union all +select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +union all +select 3 as n, a, c, sum(b) as s from t2 group by a +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 48, + "filtered": 100, + "attached_condition": "t.a > 2 and t.c in ('aa','bb','cc')", + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,3,4>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a, t2.c" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2 and t2.c in ('aa','bb','cc')" + } + } + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.a" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2" + } + } + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "having_condition": "t2.c in ('aa','bb','cc')", + "filesort": { + "sort_key": "t2.a", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "possible_keys": ["idx"], + "rows": 20, + "filtered": 80, + "attached_condition": "t2.a > 2" + } + } + } + } + } + ] + } + } + } + } + } +} +drop table t1,t2; +set optimizer_switch= @save_optimizer_switch; +# # MDEV-13369: Optimization for equi-joins of grouping derived tables # (Splitting derived tables / views with GROUP BY) # MDEV-13389: Optimization for equi-joins of derived tables with WF diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 552044823bc..f82b23a9aa1 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1550,6 +1550,78 @@ DROP VIEW v2; DROP TABLE t1,t2; --echo # +--echo # MDEV-10855: Pushdown into derived with window functions +--echo # + +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch='split_grouping_derived=off'; + +create table t1 (a int, c varchar(16)); +insert into t1 values +(8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'), +(7,'aa'), (2,'aa'), (7,'bb'); + +create table t2 (a int, b int, c varchar(16), index idx(a,c)); +insert into t2 values + (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), + (4,71,'xx'), (3,15,'aa'), (7,82,'bb'), (8,12,'dd'), (4,15,'aa'), + (11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'bb'), + (8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); + +let $q1= +select * from (select a, c, sum(b) over (partition by a,c) from t2) as t + where t.a > 2 and t.c in ('aa','bb','cc'); + +eval $no_pushdown $q1; +eval $q1; +eval explain $q1; +eval explain format=json $q1; + +let $q2= +select * from +( + select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 + union all + select 2 as n, a, c, sum(b) over (partition by a) as s from t2 +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); + +eval $no_pushdown $q2; +eval $q2; +eval explain $q2; +eval explain format=json $q2; + +let $q3= +select * +from (select a, c, sum(b) over (partition by a,c) as s from t2) as t, t1 + where t1.a=t.a and t1.c=t.c and t1.c in ('aa','bb','cc'); + +eval $no_pushdown $q3; +eval $q3; +eval explain $q3; +eval explain format=json $q3; + +let $q4= +select * from +( + select 1 as n, a, c, sum(b) over (partition by a,c) as s from t2 + union all + select 2 as n, a, c, sum(b) over (partition by a) as s from t2 + union all + select 3 as n, a, c, sum(b) as s from t2 group by a +) as t +where t.a > 2 and t.c in ('aa','bb','cc'); + +eval $no_pushdown $q4; +eval $q4; +eval explain $q4; +eval explain format=json $q4; + +drop table t1,t2; + +set optimizer_switch= @save_optimizer_switch; + +--echo # --echo # MDEV-13369: Optimization for equi-joins of grouping derived tables --echo # (Splitting derived tables / views with GROUP BY) --echo # MDEV-13389: Optimization for equi-joins of derived tables with WF @@ -1596,7 +1668,6 @@ insert into t3 values (8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'), (7,'aa'), (2,'aa'), (7,'bb'); - create table t4 (a int, b int, c varchar(16), index idx(a,c)); insert into t4 values (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), @@ -1604,7 +1675,6 @@ insert into t4 values (11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'bb'), (8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); - let $q3= select t3.a,t3.c,t.max,t.min from t3 join diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index c278c5d5aa9..ad18e1c4686 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1243,15 +1243,51 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) st_select_lex *save_curr_select= thd->lex->current_select; for (; sl; sl= sl->next_select()) { + Item *extracted_cond_copy; if (!sl->cond_pushdown_is_allowed()) continue; thd->lex->current_select= sl; + if (sl->have_window_funcs()) + { + if (sl->join->group_list || sl->join->implicit_grouping) + continue; + if (!(sl->window_specs.elements == 1 && + sl->window_specs.head()->partition_list)) + continue; + extracted_cond_copy= !sl->next_select() ? + extracted_cond : + extracted_cond->build_clone(thd, thd->mem_root); + if (!extracted_cond_copy) + continue; + + Item *cond_over_partition_fields; + ORDER *grouping_list= sl->window_specs.head()->partition_list->first; + sl->collect_grouping_fields(thd, grouping_list); + sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy, + derived); + cond_over_partition_fields= + sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true); + if (cond_over_partition_fields) + cond_over_partition_fields= cond_over_partition_fields->transform(thd, + &Item::derived_grouping_field_transformer_for_where, + (uchar*) sl); + if (cond_over_partition_fields) + { + cond_over_partition_fields->walk( + &Item::cleanup_excluding_const_fields_processor, 0, 0); + sl->cond_pushed_into_where= cond_over_partition_fields; + } + + continue; + } + /* For each select of the unit except the last one create a clone of extracted_cond */ - Item *extracted_cond_copy= !sl->next_select() ? extracted_cond : - extracted_cond->build_clone(thd, thd->mem_root); + extracted_cond_copy= !sl->next_select() ? + extracted_cond : + extracted_cond->build_clone(thd, thd->mem_root); if (!extracted_cond_copy) continue; @@ -1276,7 +1312,7 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) that could be pushed into the where clause of sl */ Item *cond_over_grouping_fields; - sl->collect_grouping_fields(thd); + sl->collect_grouping_fields(thd, sl->join->group_list); sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy, derived); cond_over_grouping_fields= diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f9c335e04bd..2980420bf34 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -6878,14 +6878,15 @@ void binlog_unsafe_map_init() st_select_lex and saves this fields. */ -void st_select_lex::collect_grouping_fields(THD *thd) +void st_select_lex::collect_grouping_fields(THD *thd, + ORDER *grouping_list) { grouping_tmp_fields.empty(); List_iterator<Item> li(join->fields_list); Item *item= li++; for (uint i= 0; i < master_unit()->derived->table->s->fields; i++, (item=li++)) { - for (ORDER *ord= join->group_list; ord; ord= ord->next) + for (ORDER *ord= grouping_list; ord; ord= ord->next) { if ((*ord->item)->eq((Item*)item, 0)) { diff --git a/sql/sql_lex.h b/sql/sql_lex.h index e404af4afd9..24773719562 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1223,7 +1223,7 @@ public: With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); bool check_subqueries_with_recursive_references(); - void collect_grouping_fields(THD *thd); + void collect_grouping_fields(THD *thd, ORDER *grouping_list); void check_cond_extraction_for_grouping_fields(Item *cond, TABLE_LIST *derived); Item *build_cond_for_grouping_fields(THD *thd, Item *cond, @@ -1248,7 +1248,7 @@ public: bool have_window_funcs() const { return (window_funcs.elements !=0); } bool cond_pushdown_is_allowed() const - { return !have_window_funcs() && !olap && !explicit_limit; } + { return !olap && !explicit_limit; } private: bool m_non_agg_field_used; diff --git a/sql/sql_select.h b/sql/sql_select.h index 6350cd73189..3592752698d 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1409,6 +1409,11 @@ public: bool set_group_rpa; /** Exec time only: TRUE <=> current group has been sent */ bool group_sent; + /** + TRUE if the query contains an aggregate function but has no GROUP + BY clause. + */ + bool implicit_grouping; bool is_for_splittable_grouping_derived; bool with_two_phase_optimization; @@ -1701,11 +1706,6 @@ private: */ void optimize_distinct(); - /** - TRUE if the query contains an aggregate function but has no GROUP - BY clause. - */ - bool implicit_grouping; void cleanup_item_list(List<Item> &items) const; bool make_aggr_tables_info(); |