Merge 10.2 into 10.3

1 files changed, 428 insertions, 0 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 326e56b222f..867da5bbbf3 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -12778,6 +12778,434 @@ 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;
+#
+# MDEV-15765: pushing condition with IN subquery defined with constants
+#             using substitution
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT * FROM
+(
+SELECT DISTINCT * FROM t1
+) der_tab
+WHERE (a>0 AND a<2 OR a IN (2,3)) AND
+(a=2 OR 0);
+a
+2
+DROP TABLE t1;
+#
 # MDEV-10855: Pushdown into derived with window functions 
 #
 set @save_optimizer_switch= @@optimizer_switch;