[Commits] bfa9d76: This is a modification of the first patch committed for mdev-13369

IgorBabaev igor at mariadb.com
Fri Aug 11 00:25:19 EEST 2017


revision-id: bfa9d7613ee266d4d2713beaeef4fbcd86ad0a95 (mariadb-10.2.2-571-gbfa9d76)
parent(s): 1710dd2bf286f9a240f380925a0067c57c1b29f9
author: Igor Babaev
committer: Igor Babaev
timestamp: 2017-08-10 14:25:19 -0700
message:

This is a modification of the first patch committed for mdev-13369
developed to cover the case of mdev-13389: "Optimization for equi-joins
of derived tables with window functions".

---
 mysql-test/r/derived_cond_pushdown.result | 176 +++++++++++++++++++++++++-----
 mysql-test/r/selectivity.result           |   4 +-
 mysql-test/r/selectivity_innodb.result    |   4 +-
 mysql-test/t/derived_cond_pushdown.test   |  19 +++-
 sql/sql_derived.cc                        |  13 ++-
 sql/sql_select.cc                         |  23 +++-
 sql/sql_select.h                          |   3 +-
 7 files changed, 199 insertions(+), 43 deletions(-)

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 51fc633..028a112 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -8785,6 +8785,8 @@ DROP TABLE t1,t2;
 #
 # 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
+#             (Splitting derived tables / views with window functions)
 #
 create table t1 (a int);
 insert into t1 values
@@ -9105,49 +9107,27 @@ drop index idx on t2;
 create index idx on t2(b);
 create index idx on t3(a);
 create index idx2 on t4(c);
-insert into t3 select * from t3;
-insert into t3 select * from t3;
-insert into t4 select * from t4;
+insert into t3 select a+1, concat(c,'f') from t3;
+insert into t3 select a+1, concat(c,'h')  from t3;
+insert into t4 select a+1, b+10, concat(c,'h') from t4;
 set statement optimizer_switch='split_grouping_derived=off' for select t2.a,t2.b,t3.c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
 where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
 a	b	c	max	min
 7	82	aa	77	15
 7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
 2	90	aa	77	15
 2	90	aa	77	15
+2	90	bbh	92	22
 select t2.a,t2.b,t3.c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
 where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
 a	b	c	max	min
 7	82	aa	77	15
 7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-7	82	aa	77	15
-7	82	bb	82	12
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	aa	77	15
 2	90	aa	77	15
 2	90	aa	77	15
+2	90	bbh	92	22
 explain extended select t2.a,t2.b,t3.c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
 where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
@@ -9219,4 +9199,146 @@ EXPLAIN
     }
   }
 }
+set statement optimizer_switch='split_grouping_derived=off' for select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+a	b	a	c	c	b	sum(b) over (partition by c)
+7	82	7	aa	aa	77	177
+7	82	7	aa	aa	50	177
+7	82	7	aa	aa	15	177
+7	82	7	aa	aa	15	177
+7	82	7	aa	aa	20	177
+7	82	7	bb	bb	40	219
+7	82	7	bb	bb	32	219
+7	82	7	bb	bb	12	219
+7	82	7	bb	bb	82	219
+7	82	7	bb	bb	30	219
+7	82	7	bb	bb	23	219
+2	90	2	aa	aa	77	177
+2	90	2	aa	aa	50	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	20	177
+2	90	2	aa	aa	77	177
+2	90	2	aa	aa	50	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	20	177
+2	90	2	bbh	bbh	50	279
+2	90	2	bbh	bbh	42	279
+2	90	2	bbh	bbh	22	279
+2	90	2	bbh	bbh	92	279
+2	90	2	bbh	bbh	40	279
+2	90	2	bbh	bbh	33	279
+select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+a	b	a	c	c	b	sum(b) over (partition by c)
+7	82	7	aa	aa	77	177
+7	82	7	aa	aa	50	177
+7	82	7	aa	aa	15	177
+7	82	7	aa	aa	15	177
+7	82	7	aa	aa	20	177
+7	82	7	bb	bb	40	219
+7	82	7	bb	bb	32	219
+7	82	7	bb	bb	12	219
+7	82	7	bb	bb	82	219
+7	82	7	bb	bb	30	219
+7	82	7	bb	bb	23	219
+2	90	2	aa	aa	77	177
+2	90	2	aa	aa	50	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	20	177
+2	90	2	aa	aa	77	177
+2	90	2	aa	aa	50	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	15	177
+2	90	2	aa	aa	20	177
+2	90	2	bbh	bbh	50	279
+2	90	2	bbh	bbh	42	279
+2	90	2	bbh	bbh	22	279
+2	90	2	bbh	bbh	92	279
+2	90	2	bbh	bbh	40	279
+2	90	2	bbh	bbh	33	279
+explain extended select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	range	idx	idx	5	NULL	5	100.00	Using index condition; Using where
+1	PRIMARY	t3	ref	idx	idx	5	test.t2.a	3	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	19	test.t3.c	4	100.00	
+2	LATERAL DERIVED	t4	ref	idx2	idx2	19	test.t3.c	5	100.00	Using temporary
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`c` AS `c`,`t`.`b` AS `b`,`t`.`sum(b) over (partition by c)` AS `sum(b) over (partition by c)` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,`test`.`t4`.`b` AS `b`,sum(`test`.`t4`.`b`) over ( partition by `test`.`t4`.`c`) AS `sum(b) over (partition by c)` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` > 50
+explain format=json select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "range",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["b"],
+      "rows": 5,
+      "filtered": 100,
+      "index_condition": "t2.b > 50",
+      "attached_condition": "t2.a is not null"
+    },
+    "table": {
+      "table_name": "t3",
+      "access_type": "ref",
+      "possible_keys": ["idx"],
+      "key": "idx",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "t3.c is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "19",
+      "used_key_parts": ["c"],
+      "ref": ["test.t3.c"],
+      "rows": 4,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "outer_ref_condition": "t3.c is not null",
+          "window_functions_computation": {
+            "sorts": {
+              "filesort": {
+                "sort_key": "t4.c"
+              }
+            },
+            "temporary_table": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ref",
+                "possible_keys": ["idx2"],
+                "key": "idx2",
+                "key_length": "19",
+                "used_key_parts": ["c"],
+                "ref": ["test.t3.c"],
+                "rows": 5,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 drop table t1,t2,t3,t4;
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 8b447f8..79b4618 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -141,7 +141,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
+3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	75.00	Using where
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 Warnings:
@@ -162,7 +162,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
+3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 Warnings:
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 8128edb..7165728 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -144,7 +144,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
+3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	75.11	Using where
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	229	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 Warnings:
@@ -165,7 +165,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
+3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	228	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 Warnings:
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 1ecc566..5520448 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1552,6 +1552,8 @@ DROP TABLE t1,t2;
 --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
+--echo #             (Splitting derived tables / views with window functions)
 --echo #
 
 let
@@ -1629,9 +1631,9 @@ drop index idx on t2;
 create index idx on t2(b);
 create index idx on t3(a);
 create index idx2 on t4(c);
-insert into t3 select * from t3;
-insert into t3 select * from t3;
-insert into t4 select * from t4;
+insert into t3 select a+1, concat(c,'f') from t3;
+insert into t3 select a+1, concat(c,'h')  from t3;
+insert into t4 select a+1, b+10, concat(c,'h') from t4;
 
 let $q5=
 select t2.a,t2.b,t3.c,t.max,t.min
@@ -1643,5 +1645,14 @@ eval $q5;
 eval explain extended $q5;
 eval explain format=json $q5;
 
-drop table t1,t2,t3,t4;
+let $q6=
+select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
 
+eval $no_splitting $q6;
+eval $q6;
+eval explain extended $q6;
+eval explain format=json $q6;
+
+drop table t1,t2,t3,t4;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index e274e20..5690936 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1055,11 +1055,20 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
   select_unit *derived_result= derived->derived_result;
   SELECT_LEX *save_current_select= lex->current_select;
 
-  if (!derived_is_recursive && (unit->uncacheable & UNCACHEABLE_DEPENDENT))
+  if (unit->executed && !derived_is_recursive &&
+      (unit->uncacheable & UNCACHEABLE_DEPENDENT))
   {
     if ((res= derived->table->file->ha_delete_all_rows()))
       goto err;
-    unit->first_select()->join->first_record= false;
+    JOIN *join= unit->first_select()->join;
+    join->first_record= false;
+    for (uint i= join->top_join_tab_count;
+         i < join->top_join_tab_count + join->aggr_tables;
+         i++)
+    { 
+      if ((res= join->join_tab[i].table->file->ha_delete_all_rows()))
+        goto err;
+    }   
   }
   
   if (derived_is_recursive)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4f3251e..2826ede 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8866,6 +8866,7 @@ bool key_can_be_used_to_split_by_fields(KEY *key_info, uint used_key_parts,
 
 bool JOIN::check_for_splittable_grouping_derived(THD *thd)
 {
+  partition_list= 0;
   st_select_lex_unit *unit= select_lex->master_unit();
   TABLE_LIST *derived= unit->derived;
   if (!optimizer_flag(thd, OPTIMIZER_SWITCH_SPLIT_GROUPING_DERIVED))
@@ -8878,15 +8879,26 @@ bool JOIN::check_for_splittable_grouping_derived(THD *thd)
     return false;
   if (derived->is_recursive_with_table())
     return false;
-  if (!group_list)
+  if (group_list)
+  {
+    if (!select_lex->have_window_funcs())
+      partition_list= group_list;
+  }
+  else if (select_lex->have_window_funcs() &&
+           select_lex->window_specs.elements == 1)
+  {
+    partition_list=
+      select_lex->window_specs.head()->partition_list->first;
+  }
+  if (!partition_list)
     return false;
-
+ 
   ORDER *ord;
   TABLE *table= 0;
   key_map ref_keys;
   uint group_fields= 0;
   ref_keys.set_all();
-  for (ord= group_list; ord; ord= ord->next, group_fields++)
+  for (ord= partition_list; ord; ord= ord->next, group_fields++)
   {
     Item *ord_item= *ord->item;
     if (ord_item->real_item()->type() != Item::FIELD_ITEM)
@@ -8905,7 +8917,7 @@ bool JOIN::check_for_splittable_grouping_derived(THD *thd)
   List<Field> grouping_fields;
   List<Field> splitting_fields;
   List_iterator<Item> li(fields_list);
-  for (ord= group_list; ord; ord= ord->next)
+  for (ord= partition_list; ord; ord= ord->next)
   {
     Item *item;
     i= 0;
@@ -8981,7 +8993,8 @@ Item *JOIN_TAB::get_splitting_cond_for_grouping_derived(THD *thd)
   KEY_PART_INFO *end= start + table->splitting_fields.elements;
   List_iterator_fast<Field> li(table->splitting_fields);
   Field *fld= li++;
-  for (ORDER *ord= sel->join->group_list; ord; ord= ord->next, fld= li++)  
+  for (ORDER *ord= sel->join->partition_list; ord;
+       ord= ord->next, fld= li++)  
   {
     Item *left_item= (*ord->item)->build_clone(thd, thd->mem_root);
     uint i= 0;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 08c5887..6350cd7 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1411,7 +1411,8 @@ class JOIN :public Sql_alloc
   bool group_sent;
 
   bool is_for_splittable_grouping_derived;
-  bool with_two_phase_optimization; 
+  bool with_two_phase_optimization;
+  ORDER *partition_list; 
 
   JOIN_TAB *sort_and_group_aggr_tab;
 


More information about the commits mailing list