[Commits] ce96028: MDEV-16088: Pushdown into materialized views/derived tables doesn't

8 May 2018

revision-id: ce96028f690db1a4371e3846dbfb584c399486cb (mariadb-10.2.14-79-gce96028)
parent(s): bd1d152d05ba75bd1bdd2d9bc0358d8508df307a
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2018-05-08 18:22:38 +0200
message:

MDEV-16088: Pushdown into materialized views/derived tables doesn't
            work in the IN subqueries

The pushdown into the materialized derived table/view wasn't done because
optimize() for the derived was called before any conditions that can
be pushed down were extracted. So optimize() in
convert_join_subqueries_to_semijoins() method is called too early and is
unnecessary. The second optimize() call in mysql_handle_single_derived()
is enough.

---
 mysql-test/r/derived_cond_pushdown.result | 413 ++++++++++++++++++++++++++++++
 mysql-test/r/derived_view.result          |   4 +-
 mysql-test/r/subselect_extra.result       |   4 +-
 mysql-test/t/derived_cond_pushdown.test   |  83 ++++++
 sql/opt_subselect.cc                      |   2 -
 5 files changed, 500 insertions(+), 6 deletions(-)

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 8e74e09..09b3b52 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -9045,3 +9045,416 @@ select * from (select  date('2018-01-01') as d) as t
 where t.d between date ('2017-01-01') and date ('2019-01-01');
 d
 2018-01-01
+#
+# MDEV-16088: pushdown into derived defined in the IN subquery
+#
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (e INT, f INT, g INT);
+INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24);
+INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1);
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.e>1
+)
+;
+a	b
+2	32
+3	24
+EXPLAIN SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.e>1
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.e>1
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["e", "max_f"],
+      "ref": ["func", "func"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "<derived3>",
+            "access_type": "ALL",
+            "rows": 5,
+            "filtered": 100,
+            "attached_condition": "d_tab.e > 1",
+            "materialized": {
+              "query_block": {
+                "select_id": 3,
+                "having_condition": "max_f > 18",
+                "filesort": {
+                  "sort_key": "t2.e",
+                  "temporary_table": {
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 5,
+                      "filtered": 100,
+                      "attached_condition": "t2.e > 1"
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.max_f<25
+)
+;
+a	b
+1	19
+3	24
+EXPLAIN SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.max_f<25
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	5	Using where
+3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e,d_tab.max_f
+FROM (
+SELECT t2.e, MAX(t2.f) AS max_f
+FROM t2
+GROUP BY t2.e
+HAVING max_f>18
+) as d_tab
+WHERE d_tab.max_f<25
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["e", "max_f"],
+      "ref": ["func", "func"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "<derived3>",
+            "access_type": "ALL",
+            "rows": 5,
+            "filtered": 100,
+            "attached_condition": "d_tab.max_f < 25",
+            "materialized": {
+              "query_block": {
+                "select_id": 3,
+                "having_condition": "max_f > 18 and max_f < 25",
+                "filesort": {
+                  "sort_key": "t2.e",
+                  "temporary_table": {
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 5,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.e>1
+GROUP BY d_tab.g
+)
+;
+a	b
+2	32
+EXPLAIN SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.e>1
+GROUP BY d_tab.g
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.b	1	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary
+3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary; Using filesort
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.e>1
+GROUP BY d_tab.g
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "t1.a is not null and t1.b is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["e", "max_f"],
+      "ref": ["test.t1.a", "test.t1.b"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "<derived3>",
+              "access_type": "ALL",
+              "rows": 5,
+              "filtered": 100,
+              "attached_condition": "d_tab.e > 1",
+              "materialized": {
+                "query_block": {
+                  "select_id": 3,
+                  "filesort": {
+                    "sort_key": "t2.e",
+                    "temporary_table": {
+                      "table": {
+                        "table_name": "t2",
+                        "access_type": "ALL",
+                        "rows": 5,
+                        "filtered": 100,
+                        "attached_condition": "t2.e > 1"
+                      }
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.max_f>20
+GROUP BY d_tab.g
+)
+;
+a	b
+2	32
+EXPLAIN SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.max_f>20
+GROUP BY d_tab.g
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.b	1	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	5	Using where; Using temporary
+3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+(
+SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+FROM (
+SELECT t2.e, MAX(t2.f) as max_f, t2.g
+FROM t2
+GROUP BY t2.e
+) as d_tab
+WHERE d_tab.max_f>20
+GROUP BY d_tab.g
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "t1.a is not null and t1.b is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["e", "max_f"],
+      "ref": ["test.t1.a", "test.t1.b"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "<derived3>",
+              "access_type": "ALL",
+              "rows": 5,
+              "filtered": 100,
+              "attached_condition": "d_tab.max_f > 20",
+              "materialized": {
+                "query_block": {
+                  "select_id": 3,
+                  "having_condition": "max_f > 20",
+                  "filesort": {
+                    "sort_key": "t2.e",
+                    "temporary_table": {
+                      "table": {
+                        "table_name": "t2",
+                        "access_type": "ALL",
+                        "rows": 5,
+                        "filtered": 100
+                      }
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index df6ba08..d2804ce 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1841,7 +1841,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; End temporary
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
@@ -1856,7 +1856,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<derived3>	ref	key1	key1	8	const,const	0	Start temporary
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 73642c0..a3a0f1f 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -434,7 +434,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t3); Using join buffer (flat, BNL join)
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
@@ -449,7 +449,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<derived3>	ref	key1	key1	8	const,const	0	Start temporary
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 0b87738..e8f6e9c 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1646,3 +1646,86 @@ select * from (select  date('2018-01-01') as d
 
 select * from (select  date('2018-01-01') as d) as t
   where t.d between date ('2017-01-01') and date ('2019-01-01');
+
+--echo #
+--echo # MDEV-16088: pushdown into derived defined in the IN subquery
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (e INT, f INT, g INT);
+INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24);
+INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1);
+
+LET $query=
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+  (
+    SELECT d_tab.e,d_tab.max_f
+    FROM (
+      SELECT t2.e, MAX(t2.f) AS max_f
+      FROM t2
+      GROUP BY t2.e
+      HAVING max_f>18
+    ) as d_tab
+    WHERE d_tab.e>1
+  )
+;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+  (
+    SELECT d_tab.e,d_tab.max_f
+    FROM (
+      SELECT t2.e, MAX(t2.f) AS max_f
+      FROM t2
+      GROUP BY t2.e
+      HAVING max_f>18
+    ) as d_tab
+    WHERE d_tab.max_f<25
+  )
+;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+  (
+    SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+    FROM (
+      SELECT t2.e, MAX(t2.f) as max_f, t2.g
+      FROM t2
+      GROUP BY t2.e
+    ) as d_tab
+    WHERE d_tab.e>1
+    GROUP BY d_tab.g
+  )
+;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM t1
+WHERE (t1.a,t1.b) IN
+  (
+    SELECT d_tab.e, MAX(d_tab.max_f) AS max_f
+    FROM (
+      SELECT t2.e, MAX(t2.f) as max_f, t2.g
+      FROM t2
+      GROUP BY t2.e
+    ) as d_tab
+    WHERE d_tab.max_f>20
+    GROUP BY d_tab.g
+  )
+;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1,t2;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index b28adae..5765278 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1092,8 +1092,6 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
   while ((in_subq= li++))
   {
     SELECT_LEX *subq_sel= in_subq->get_select_lex();
-    if (subq_sel->handle_derived(thd->lex, DT_OPTIMIZE))
-      DBUG_RETURN(1);
     if (subq_sel->handle_derived(thd->lex, DT_MERGE))
       DBUG_RETURN(TRUE);
     subq_sel->update_used_tables();

    

[Commits] ce96028: MDEV-16088: Pushdown into materialized views/derived tables doesn't

galina.shalygina＠mariadb.com