Fixed a flaw in the implementation of condition push-down

for materialized views and derived tables: there were no
push-down if the view was defined as union of selects
without aggregation. Added test cases with such unions.

Adjusted result files after the merge of the code for mdev-9197.

7 files changed, 848 insertions, 12 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 3acf1965323..069041cc707 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -65,6 +65,18 @@ where t1.b>10 group by a,b having c < 300
 union
 select a, b, avg(c) as c from t1
 where t1.c>300 group by a,b having c < 707;
+create view v3_union as 
+select a, b, (a+1) as c from t1
+where t1.a<10
+union
+select a, b, c from t1
+where t1.b>10 and t1.c>100;
+create view v4_union as 
+select a, b, max(c)-100 as c from t1
+where t1.a<10 group by a,b having c > 109
+union
+select a, b, (c+100) as c from t1
+where t1.b>10;
 create view v_double as
 select a, avg(a/4) as avg_a, b, c from t1_double
 where (b>12.2) group by b,c having (avg_a<22.333);
@@ -5262,6 +5274,540 @@ EXPLAIN
     }
   }
 }
+# using derived table with union of selects without aggregation
+# extracted conjunctive predicate: pushing in WHERE of both selects
+set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
+a	b	c	a	b	c	d
+1	21	123	1	21	909	12
+1	21	101	1	21	909	12
+1	21	104	1	21	909	12
+1	33	988	1	21	909	12
+1	19	107	1	21	909	12
+1	21	500	1	21	909	12
+1	21	345	1	21	909	12
+7	11	708	7	13	312	406
+7	11	8	7	13	312	406
+8	33	404	8	64	248	107
+8	33	123	8	64	248	107
+8	33	114	8	64	248	107
+8	33	9	8	64	248	107
+6	20	214	6	20	315	279
+6	20	315	6	20	315	279
+6	20	309	6	20	315	279
+6	20	7	6	20	315	279
+1	21	123	1	19	203	107
+1	21	101	1	19	203	107
+1	21	104	1	19	203	107
+1	33	988	1	19	203	107
+1	19	107	1	19	203	107
+1	21	500	1	19	203	107
+1	21	345	1	19	203	107
+8	33	404	8	80	800	314
+8	33	123	8	80	800	314
+8	33	114	8	80	800	314
+8	33	9	8	80	800	314
+6	20	214	6	23	303	909
+6	20	315	6	23	303	909
+6	20	309	6	23	303	909
+6	20	7	6	23	303	909
+select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
+a	b	c	a	b	c	d
+1	21	123	1	21	909	12
+1	21	101	1	21	909	12
+1	21	104	1	21	909	12
+1	33	988	1	21	909	12
+1	19	107	1	21	909	12
+1	21	500	1	21	909	12
+1	21	345	1	21	909	12
+7	11	708	7	13	312	406
+7	11	8	7	13	312	406
+8	33	404	8	64	248	107
+8	33	123	8	64	248	107
+8	33	114	8	64	248	107
+8	33	9	8	64	248	107
+6	20	214	6	20	315	279
+6	20	315	6	20	315	279
+6	20	309	6	20	315	279
+6	20	7	6	20	315	279
+1	21	123	1	19	203	107
+1	21	101	1	19	203	107
+1	21	104	1	19	203	107
+1	33	988	1	19	203	107
+1	19	107	1	19	203	107
+1	21	500	1	19	203	107
+1	21	345	1	19	203	107
+8	33	404	8	80	800	314
+8	33	123	8	80	800	314
+8	33	114	8	80	800	314
+8	33	9	8	80	800	314
+6	20	214	6	23	303	909
+6	20	315	6	23	303	909
+6	20	309	6	23	303	909
+6	20	7	6	23	303	909
+explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 9,
+      "filtered": 100,
+      "attached_condition": "(t2.a is not null)"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "(v.c > 6)",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.a < 10) and ((t1.a + 1) > 6))"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.b > 10) and (t1.c > 100) and (t1.c > 6))"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+# using derived table with union of selects without aggregation
+# extracted conjunctive OR subformula: pushing in WHERE using equalities
+set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
+a	b	c	a	b	c	d
+1	19	107	1	21	909	12
+1	19	2	1	21	909	12
+7	11	708	7	13	312	406
+7	11	8	7	13	312	406
+8	33	404	8	64	248	107
+8	33	123	8	64	248	107
+8	33	114	8	64	248	107
+8	33	9	8	64	248	107
+6	20	214	6	20	315	279
+6	20	315	6	20	315	279
+6	20	309	6	20	315	279
+6	20	7	6	20	315	279
+1	19	107	1	19	203	107
+1	19	2	1	19	203	107
+8	33	404	8	80	800	314
+8	33	123	8	80	800	314
+8	33	114	8	80	800	314
+8	33	9	8	80	800	314
+6	20	214	6	23	303	909
+6	20	315	6	23	303	909
+6	20	309	6	23	303	909
+6	20	7	6	23	303	909
+select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
+a	b	c	a	b	c	d
+1	19	107	1	21	909	12
+1	19	2	1	21	909	12
+7	11	708	7	13	312	406
+7	11	8	7	13	312	406
+8	33	404	8	64	248	107
+8	33	123	8	64	248	107
+8	33	114	8	64	248	107
+8	33	9	8	64	248	107
+6	20	214	6	20	315	279
+6	20	315	6	20	315	279
+6	20	309	6	20	315	279
+6	20	7	6	20	315	279
+1	19	107	1	19	203	107
+1	19	2	1	19	203	107
+8	33	404	8	80	800	314
+8	33	123	8	80	800	314
+8	33	114	8	80	800	314
+8	33	9	8	80	800	314
+6	20	214	6	23	303	909
+6	20	315	6	23	303	909
+6	20	309	6	23	303	909
+6	20	7	6	23	303	909
+explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 9,
+      "filtered": 100,
+      "attached_condition": "(t2.a is not null)"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "((t2.a > 1) or (v.b < 20))",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.a < 10) and ((t1.a > 1) or (t1.b < 20)))"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.b > 10) and (t1.c > 100) and ((t1.a > 1) or (t1.b < 20)))"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+# using derived table with union of selects without aggregation
+# extracted the whole condition: in WHERE of both selects
+set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from v3_union as v,t2 where 
+(v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600));
+a	b	c	a	b	c	d
+1	19	2	1	21	909	12
+1	21	2	1	21	909	12
+1	19	2	1	19	203	107
+1	21	2	1	19	203	107
+select * from v3_union as v,t2 where 
+(v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600));
+a	b	c	a	b	c	d
+1	19	2	1	21	909	12
+1	21	2	1	21	909	12
+1	19	2	1	19	203	107
+1	21	2	1	19	203	107
+explain select * from v3_union as v,t2 where 
+(v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json select * from v3_union as v,t2 where 
+(v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600));
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 9,
+      "filtered": 100,
+      "attached_condition": "(t2.a is not null)"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "(((v.b = 19) or (v.b = 21)) and ((v.c < 3) or (v.c > 600)))",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.a < 10) and ((t1.b = 19) or (t1.b = 21)) and (((t1.a + 1) < 3) or ((t1.a + 1) > 600)))"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.b > 10) and (t1.c > 100) and ((t1.b = 19) or (t1.b = 21)) and ((t1.c < 3) or (t1.c > 600)))"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+# using derived table with union of 
+# a select without aggregation and a select with aggregation
+# extracted conjunctive predicate: pushing in WHERE of both selects
+set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
+a	b	c	a	b	c	d
+1	19	207	1	21	909	12
+7	11	808	7	13	312	406
+7	11	608	7	13	312	406
+1	19	207	1	19	203	107
+select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
+a	b	c	a	b	c	d
+1	19	207	1	21	909	12
+7	11	808	7	13	312	406
+7	11	608	7	13	312	406
+1	19	207	1	19	203	107
+explain select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 9,
+      "filtered": 100,
+      "attached_condition": "(t2.a is not null)"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "(v.b < 20)",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "having_condition": "(c > 109)",
+                  "filesort": {
+                    "sort_key": "t1.a, t1.b",
+                    "temporary_table": {
+                      "table": {
+                        "table_name": "t1",
+                        "access_type": "ALL",
+                        "rows": 20,
+                        "filtered": 100,
+                        "attached_condition": "((t1.a < 10) and (t1.b < 20))"
+                      }
+                    }
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.b > 10) and (t1.b < 20))"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+# using derived table with union of 
+# a select without aggregation and a select with aggregation
+# extracted subformula: pushing in WHERE of one select
+# extracted subformula: pushing in HAVING of the other select
+# extracted sub-subformula: pushing in WHERE of the other select
+# using an equality in all pushdowns
+set statement optimizer_switch='condition_pushdown_for_derived=off' for select * from v4_union as v,t2 where 
+(v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
+a	b	c	a	b	c	d
+1	33	1088	1	21	909	12
+1	21	600	1	21	909	12
+1	33	888	1	21	909	12
+7	11	808	7	13	312	406
+7	11	608	7	13	312	406
+8	33	504	8	64	248	107
+1	33	1088	1	19	203	107
+1	21	600	1	19	203	107
+1	33	888	1	19	203	107
+8	33	504	8	80	800	314
+select * from v4_union as v,t2 where 
+(v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
+a	b	c	a	b	c	d
+1	33	1088	1	21	909	12
+1	21	600	1	21	909	12
+1	33	888	1	21	909	12
+7	11	808	7	13	312	406
+7	11	608	7	13	312	406
+8	33	504	8	64	248	107
+1	33	1088	1	19	203	107
+1	21	600	1	19	203	107
+1	33	888	1	19	203	107
+8	33	504	8	80	800	314
+explain select * from v4_union as v,t2 where 
+(v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	4	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; Using temporary; Using filesort
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json select * from v4_union as v,t2 where 
+(v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 9,
+      "filtered": 100,
+      "attached_condition": "(t2.a is not null)"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 4,
+      "filtered": 100,
+      "attached_condition": "(((t2.a < 3) or (v.b < 40)) and (v.c > 500))",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "having_condition": "((c > 109) and (c > 500))",
+                  "filesort": {
+                    "sort_key": "t1.a, t1.b",
+                    "temporary_table": {
+                      "table": {
+                        "table_name": "t1",
+                        "access_type": "ALL",
+                        "rows": 20,
+                        "filtered": 100,
+                        "attached_condition": "((t1.a < 10) and ((t1.a < 3) or (t1.b < 40)))"
+                      }
+                    }
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ALL",
+                    "rows": 20,
+                    "filtered": 100,
+                    "attached_condition": "((t1.b > 10) and ((t1.a < 3) or (t1.b < 40)) and ((t1.c + 100) > 500))"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
 # using embedded derived table : pushing the same conditions
 # using several derived tables : pushing in all tables
 # conjunctive subformula : pushing into WHERE
@@ -6432,5 +6978,7 @@ EXPLAIN
     }
   }
 }
-drop view v1,v2,v3,v4,v_union,v2_union,v_double,v_char,v_decimal;
+drop view v1,v2,v3,v4;
+drop view v_union,v2_union,v3_union,v4_union;
+drop view v_double,v_char,v_decimal;
 drop table t1,t2,t1_double,t2_double,t1_char,t2_char,t1_decimal,t2_decimal;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 5783247b673..963fcf8570f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -363,9 +363,51 @@ explain extended select * from v1 join v4 on f1=f2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.f2	2	100.00	
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using temporary; Using filesort
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` in (2,3)))
+explain format=json select * from v1 join v4 on f1=f2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "attached_condition": "((t2.f2 in (2,3)) and (t2.f2 is not null))"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["f1"],
+      "ref": ["test.t2.f2"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(t1.f1 in (2,3))"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 select * from v1 join v4 on f1=f2;
 f1	f11	f2	f22
 3	3	3	3
@@ -389,7 +431,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2)
+Note	1003	select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2)) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2)
 select * from (select * from 
 (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz;
 f1	f11
@@ -416,13 +458,63 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `zz`
+Note	1003	select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2)) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `zz`
+explain format=json  select * from (select * from 
+(select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "tt.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "<derived3>",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(tt.f1 > 2)",
+                "materialized": {
+                  "query_block": {
+                    "select_id": 3,
+                    "filesort": {
+                      "sort_key": "t1.f1",
+                      "temporary_table": {
+                        "table": {
+                          "table_name": "t1",
+                          "access_type": "ALL",
+                          "rows": 11,
+                          "filtered": 100,
+                          "attached_condition": "((t1.f1 < 7) and (t1.f1 > 2))"
+                        }
+                      }
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 select * from (select * from 
 (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
 f1	f11
 3	3
 5	5
 mat in merged derived join mat in merged derived
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='condition_pushdown_for_derived=off';
 explain extended  select * from 
 (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
 join 
@@ -435,6 +527,69 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` join (select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `t1`.`f1`) `tt` where ((`tt`.`f1` = `tt`.`f1`) and (`tt`.`f1` > 2) and (`tt`.`f1` > 2))
+explain format=json  select * from 
+(select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
+join 
+(select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
+on x.f1 = z.f1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "attached_condition": "((tt.f1 > 2) and (tt.f1 > 2) and (tt.f1 is not null))",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "filesort": {
+            "sort_key": "t1.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(t1.f1 < 7)"
+              }
+            }
+          }
+        }
+      }
+    },
+    "table": {
+      "table_name": "<derived5>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["f1"],
+      "ref": ["tt.f1"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 5,
+          "filesort": {
+            "sort_key": "t1.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(t1.f1 < 7)"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 flush status;
 select * from 
 (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x
@@ -455,6 +610,7 @@ Handler_read_rnd	8
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	39
 flush status;
+set optimizer_switch=@save_optimizer_switch;
 merged in merged derived join merged in merged derived
 explain extended  select * from 
 (select * from 
@@ -495,7 +651,106 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
-Note	1003	select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `x` join (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `z` where (`z`.`f1` = `x`.`f1`)
+Note	1003	select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2)) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `x` join (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where ((`t1`.`f1` < 7) and (`t1`.`f1` > 2)) group by `t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `z` where (`z`.`f1` = `x`.`f1`)
+explain format=json select * from 
+(select * from 
+(select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x
+join 
+(select * from 
+(select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z
+on x.f1 = z.f1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "attached_condition": "(x.f1 is not null)",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "tt.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "<derived3>",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(tt.f1 > 2)",
+                "materialized": {
+                  "query_block": {
+                    "select_id": 3,
+                    "filesort": {
+                      "sort_key": "t1.f1",
+                      "temporary_table": {
+                        "table": {
+                          "table_name": "t1",
+                          "access_type": "ALL",
+                          "rows": 11,
+                          "filtered": 100,
+                          "attached_condition": "((t1.f1 < 7) and (t1.f1 > 2))"
+                        }
+                      }
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    },
+    "table": {
+      "table_name": "<derived4>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["f1"],
+      "ref": ["x.f1"],
+      "rows": 2,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 4,
+          "filesort": {
+            "sort_key": "tt.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "<derived5>",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(tt.f1 > 2)",
+                "materialized": {
+                  "query_block": {
+                    "select_id": 5,
+                    "filesort": {
+                      "sort_key": "t1.f1",
+                      "temporary_table": {
+                        "table": {
+                          "table_name": "t1",
+                          "access_type": "ALL",
+                          "rows": 11,
+                          "filtered": 100,
+                          "attached_condition": "((t1.f1 < 7) and (t1.f1 > 2))"
+                        }
+                      }
+                    }
+                  }
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 select * from 
 (select * from 
 (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x
@@ -523,9 +778,41 @@ explain extended
 select * from ( select * from v1 where f1 < 7) tt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
-3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using temporary; Using filesort
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 Warnings:
 Note	1003	select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` where (`v1`.`f1` < 7)
+explain format=json 
+select * from ( select * from v1 where f1 < 7) tt;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 11,
+      "filtered": 100,
+      "attached_condition": "(v1.f1 < 7)",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "filesort": {
+            "sort_key": "t1.f1",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 11,
+                "filtered": 100,
+                "attached_condition": "(t1.f1 < 7)"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
 select * from ( select * from v1 where f1 < 7) tt;
 f1	f11
 1	1
@@ -2354,7 +2641,7 @@ ON p.id = g.p_random
 ORDER BY gallery_name ASC 
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	Using filesort
 1	PRIMARY	p	eq_ref	PRIMARY	PRIMARY	4	g.p_random	1	Using where
 2	DERIVED	gal	ALL	NULL	NULL	NULL	NULL	10	
 3	DEPENDENT SUBQUERY	pi	ref	gallery_id	gallery_id	4	test.gal.id	4	Using temporary; Using filesort
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index e2a7c462efd..6a67b5d0baf 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -918,7 +918,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	Using where; Using temporary
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.i1	9	100.00	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	Using where
 Warnings:
 Note	1003	select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3))
 set join_buffer_size=1024;
diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result
index af5d1b800aa..ff0bc163a33 100644
--- a/mysql-test/r/explain_json.result
+++ b/mysql-test/r/explain_json.result
@@ -485,6 +485,7 @@ EXPLAIN
       "materialized": {
         "query_block": {
           "select_id": 2,
+          "having_condition": "(cnt > 0)",
           "filesort": {
             "sort_key": "t1.a",
             "temporary_table": {
diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result
index a857e2a21ff..6fbe7514483 100644
--- a/mysql-test/r/index_merge_myisam.result
+++ b/mysql-test/r/index_merge_myisam.result
@@ -292,7 +292,7 @@ set optimizer_switch='derived_merge=off,derived_with_keys=off';
 explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DERIVED	t1	index_merge	i1,i2	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
+2	DERIVED	t1	index_merge	i1,i2,i8	i1,i2	4,4	NULL	2	Using union(i1,i2); Using where
 set optimizer_switch=@tmp_optimizer_switch;
 create table t3 like t0;
 insert into t3 select * from t0;
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 536fd4ab97f..de5fabbd90c 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1491,7 +1491,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
 Note	1003	select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where ((`v2`.`i2` = NULL) and (`v2`.`a` < `v2`.`b`))
 DROP VIEW v2;
diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result
index 79bca388181..7cfce6dd006 100644
--- a/mysql-test/r/subselect_extra_no_semijoin.result
+++ b/mysql-test/r/subselect_extra_no_semijoin.result
@@ -436,7 +436,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
-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);
@@ -451,7 +451,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	<derived3>	ref	key1	key1	8	const,const	0	Using where
-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);