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.

13 files changed, 1034 insertions, 23 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);
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 6bde221a55c..41342db5d49 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -78,6 +78,20 @@ create view v2_union as
   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);  
@@ -683,6 +697,58 @@ eval $query;
 eval explain $query;
 eval explain format=json $query;         
 
+--echo # using derived table with union of selects without aggregation
+--echo # extracted conjunctive predicate: pushing in WHERE of both selects
+let $query=    
+  select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
+eval $no_pushdown $query;
+eval $query;
+eval explain $query;
+eval explain format=json $query;  
+
+--echo # using derived table with union of selects without aggregation
+--echo # extracted conjunctive OR subformula: pushing in WHERE using equalities
+let $query=    
+  select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20));
+eval $no_pushdown $query;
+eval $query;
+eval explain $query;
+eval explain format=json $query;  
+
+--echo # using derived table with union of selects without aggregation
+--echo # extracted the whole condition: in WHERE of both selects
+let $query=    
+  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));
+eval $no_pushdown $query;
+eval $query;
+eval explain $query;
+eval explain format=json $query;  
+
+--echo # using derived table with union of 
+--echo # a select without aggregation and a select with aggregation
+--echo # extracted conjunctive predicate: pushing in WHERE of both selects
+let $query=    
+  select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20);
+eval $no_pushdown $query;
+eval $query;
+eval explain $query;
+eval explain format=json $query; 
+
+--echo # using derived table with union of 
+--echo # a select without aggregation and a select with aggregation
+--echo # extracted subformula: pushing in WHERE of one select
+--echo # extracted subformula: pushing in HAVING of the other select
+--echo # extracted sub-subformula: pushing in WHERE of the other select
+--echo # using an equality in all pushdowns
+let $query=    
+  select * from v4_union as v,t2 where 
+    (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500);
+eval $no_pushdown $query;
+eval $query;
+eval explain $query;
+eval explain format=json $query; 
+
 --echo # using embedded derived table : pushing the same conditions
 --echo # using several derived tables : pushing in all tables
 --echo # conjunctive subformula : pushing into WHERE
@@ -808,5 +874,7 @@ eval $query;
 eval explain $query;
 eval explain format=json $query;   
      
-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;    
\ No newline at end of file
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 3d9ab4e4477..8f7bdc03578 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -113,6 +113,7 @@ select * from t1 join v2 on f1=f2;
 show status like 'Handler_read%';
 
 explain extended select * from v1 join v4 on f1=f2;
+explain format=json select * from v1 join v4 on f1=f2; 
 select * from v1 join v4 on f1=f2;
 
 --echo merged derived in merged derived
@@ -136,15 +137,24 @@ select * from (select * from
 --echo materialized derived in materialized derived
 explain extended  select * from (select * from 
   (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by 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;
 select * from (select * from 
   (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz;
 
 --echo 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 
  (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
  on x.f1 = z.f1;
+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;
 
 flush status;
 select * from 
@@ -154,6 +164,7 @@ join
  on x.f1 = z.f1;
 show status like 'Handler_read%';
 flush status;
+set optimizer_switch=@save_optimizer_switch;
 
 --echo merged in merged derived join merged in merged derived
 explain extended  select * from 
@@ -181,6 +192,13 @@ 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 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;
 
 select * from 
  (select * from 
@@ -198,6 +216,8 @@ select * from (select * from v4 group by 1) tt;
 --echo materialized view in merged derived
 explain extended 
 select * from ( select * from v1 where f1 < 7) tt;
+explain format=json 
+select * from ( select * from v1 where f1 < 7) tt;
 select * from ( select * from v1 where f1 < 7) tt;
 
 --echo merged view in a merged view in a merged derived
diff --git a/sql/item.cc b/sql/item.cc
index f04ab32c734..0ba922eb6b6 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -6818,6 +6818,42 @@ Item *Item_field::derived_field_transformer_for_having(THD *thd, uchar *arg)
 
 Item *Item_field::derived_field_transformer_for_where(THD *thd, uchar *arg)
 {
+  Item *producing_item;
+  st_select_lex *sl= (st_select_lex *)arg;
+  List_iterator_fast<Item> li(sl->item_list);
+  table_map map= sl->master_unit()->derived->table->map;
+  if (used_tables() == map)
+  {
+    uint field_no= ((Item_field*) this)->field->field_index;
+    for (uint i= 0; i <= field_no; i++)
+      producing_item= li++;
+    return producing_item->build_clone(thd, thd->mem_root);
+  }
+  else if (((Item_field*)this)->item_equal)
+  {
+    Item_equal *cond= (Item_equal *) ((Item_field*)this)->item_equal;
+    Item_equal_fields_iterator it(*cond);
+    Item *item;
+    while ((item=it++))
+    {
+      if (item->used_tables() == map && item->type() == FIELD_ITEM)
+      {   
+	Item_field *field_item= (Item_field *) item;
+	li.rewind();
+        uint field_no= ((Item_field*) this)->field->field_index;
+        for (uint i= 0; i <= field_no; i++)
+          producing_item= li++;
+        return producing_item->build_clone(thd, thd->mem_root);
+      }
+    }
+  }
+  return this;
+}
+
+
+Item *Item_field::derived_grouping_field_transformer_for_where(THD *thd,
+                                                               uchar *arg)
+{
   st_select_lex *sl= (st_select_lex *)arg;
   List_iterator<Grouping_tmp_field> li(sl->grouping_tmp_fields);
   Grouping_tmp_field *field;
diff --git a/sql/item.h b/sql/item.h
index 7a189004531..5b8254837e8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1695,6 +1695,9 @@ public:
   { return this; }
   virtual Item *derived_field_transformer_for_where(THD *thd, uchar *arg)
   { return this; }
+  virtual Item *derived_grouping_field_transformer_for_where(THD *thd,
+                                                             uchar *arg)
+  { return this; }
   virtual bool expr_cache_is_needed(THD *) { return FALSE; }
   virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs);
   bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs)
@@ -2578,8 +2581,9 @@ public:
   Item_field *field_for_view_update() { return this; }
   int fix_outer_field(THD *thd, Field **field, Item **reference);
   virtual Item *update_value_transformer(THD *thd, uchar *select_arg);
-  virtual Item *derived_field_transformer_for_having(THD *thd, uchar *arg);
-  virtual Item *derived_field_transformer_for_where(THD *thd, uchar *arg);
+  Item *derived_field_transformer_for_having(THD *thd, uchar *arg);
+  Item *derived_field_transformer_for_where(THD *thd, uchar *arg);
+  Item *derived_grouping_field_transformer_for_where(THD *thd, uchar *arg);
   virtual void print(String *str, enum_query_type query_type);
   bool exclusive_dependence_on_table_processor(void *map);
   bool exclusive_dependence_on_grouping_fields_processor(void *arg);
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 4d24993f4b0..a5e73acdc47 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -480,8 +480,10 @@ unconditional_materialization:
   derived->set_materialized_derived();
   if (!derived->table || !derived->table->is_created())
     res= mysql_derived_create(thd, lex, derived);
+#if 0
   if (!res)
     res= mysql_derived_fill(thd, lex, derived);
+#endif
   goto exit_merge;
 }
 
@@ -1130,6 +1132,22 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
   if (!cond)
     return false;
 
+  st_select_lex_unit *unit= derived->get_unit();
+  st_select_lex *sl= unit->first_select();
+
+  /* Check whether any select of 'unit' allows condition pushdown */
+  bool any_select_allows_cond_pushdown= false;
+  for (; sl; sl= sl->next_select())
+  {
+    if (sl->cond_pushdown_is_allowed())
+    {
+      any_select_allows_cond_pushdown= true;
+      break;
+    }
+  }
+  if (!any_select_allows_cond_pushdown)
+    return false; 
+
   /* Do not push conditions into recursive with tables */
   if (derived->is_recursive_with_table())
     return false;
@@ -1150,11 +1168,11 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     return false;
   }
   /* Push extracted_cond into every select of the unit specifying 'derived' */
-  st_select_lex_unit *unit= derived->get_unit();
   st_select_lex *save_curr_select= thd->lex->current_select;
-  st_select_lex *sl= unit->first_select();
   for (; sl; sl= sl->next_select())
   {
+    if (!sl->cond_pushdown_is_allowed())
+      continue;
     thd->lex->current_select= sl;
     /*
       For each select of the unit except the last one
@@ -1164,7 +1182,32 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
                                extracted_cond->build_clone(thd, thd->mem_root);
     if (!extracted_cond_copy)
       continue;
+
+    if (!sl->join->group_list && !sl->with_sum_func)
+    {
+      /* extracted_cond_copy is pushed into where of sl */
+      extracted_cond_copy= extracted_cond_copy->transform(thd,
+                                 &Item::derived_field_transformer_for_where,
+                                 (uchar*) sl);
+      if (extracted_cond_copy)
+      {
+        /*
+          Create the conjunction of the existing where condition of sl
+          and the pushed condition, take it as the new where condition of sl
+          and fix this new condition
+        */
+        extracted_cond_copy->walk(&Item::cleanup_processor, 0, 0);
+        thd->change_item_tree(&sl->join->conds,
+                              and_conds(thd, sl->join->conds,
+                                        extracted_cond_copy));
+    
+        if (sl->join->conds->fix_fields(thd, &sl->join->conds))
+          goto err;
+      }      
   
+      continue;
+    }
+
     /*
       Figure out what can be extracted from the pushed condition
       that could be pushed into the where clause of sl
@@ -1182,8 +1225,8 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     */
     if (cond_over_grouping_fields)
       cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
-                                 &Item::derived_field_transformer_for_where,
-                                 (uchar*) sl);
+                         &Item::derived_grouping_field_transformer_for_where,
+                         (uchar*) sl);
      
     if (cond_over_grouping_fields)
     {
@@ -1200,16 +1243,16 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
       */
       cond_over_grouping_fields->walk(&Item::cleanup_processor, 0, 0);
       thd->change_item_tree(&sl->join->conds,
-                          and_conds(thd, sl->join->conds,
-                                    cond_over_grouping_fields));
+                            and_conds(thd, sl->join->conds,
+                                      cond_over_grouping_fields));
     
       if (sl->join->conds->fix_fields(thd, &sl->join->conds))
         goto err;
-    
+           
       if (!extracted_cond_copy)
         continue;
     }
- 
+    
     /*
       Transform the references to the 'derived' columns from the condition
       pushed into the having clause of sl to make them usable in the new context
@@ -1239,3 +1282,4 @@ err:
   thd->lex->current_select= save_curr_select;
   return true;
 }
+
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 97d1f001317..690dcbb2a5e 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1150,6 +1150,9 @@ public:
 
   bool have_window_funcs() const { return (window_funcs.elements !=0); }
 
+  bool cond_pushdown_is_allowed() const
+  { return !have_window_funcs() && !olap; }
+  
 private:
   bool m_non_agg_field_used;
   bool m_agg_func_used;