[Commits] f9cbc58: MDEV-12387 Push conditions into materialized subqueries

shagalla galina.shalygina at mariadb.com
Sun Apr 29 21:55:52 EEST 2018


revision-id: f9cbc5873429543fc489907938a378f4c91470b2 (mariadb-10.3.4-56-gf9cbc58)
parent(s): 1bfad1ffbd716b78e338f3c6b2d21c8b5e9a591f
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2018-04-29 20:55:51 +0200
message:

MDEV-12387 Push conditions into materialized subqueries

Post review changes: comments changed, tests changed

---
 mysql-test/r/in_subq_cond_pushdown.result | 1963 +++++++++++++++++++++--------
 mysql-test/t/in_subq_cond_pushdown.test   |  611 +++++----
 sql/item.cc                               |  127 +-
 sql/item.h                                |   35 +-
 sql/item_cmpfunc.h                        |    2 +
 sql/item_subselect.h                      |    2 +-
 sql/opt_subselect.cc                      |  376 +++---
 sql/sql_derived.cc                        |  207 ++-
 sql/sql_lex.cc                            |  144 ++-
 sql/sql_lex.h                             |    5 +
 sql/sql_select.cc                         |    2 +-
 11 files changed, 2340 insertions(+), 1134 deletions(-)

diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result
index 6676d3b..ebb6265 100644
--- a/mysql-test/r/in_subq_cond_pushdown.result
+++ b/mysql-test/r/in_subq_cond_pushdown.result
@@ -1,44 +1,44 @@
-create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
-create table t2 (t2_e int, t2_f int, t2_g int);
-create table t3 (t3_x int, t3_y int);
-insert into t1 values
+CREATE TABLE t1 (t1_a INt, t1_b INt, t1_c INt, t1_d INt);
+CREATE TABLE t2 (t2_e INt, t2_f INt, t2_g INt);
+CREATE TABLE t3 (t3_x INt, t3_y INt);
+INSERT INTO t1 VALUES
 (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
 (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
 (5,5,65,1), (2,3,70,3),  (1,4,35,3), (2,3,25,3);
-insert into t2 values
+INSERT INTO t2 VALUES
 (1,2,38), (2,3,15), (1,3,40), (1,4,35),
 (2,2,70), (3,4,23), (5,5,12), (5,4,17),
 (3,3,17), (4,2,24), (2,5,25), (5,1,65);
-insert into t3 values
+INSERT INTO t3 VALUES
 (1,25), (1,18), (2,15), (4,24),
 (1,35), (3,23), (3,17), (2,15);
-create view v1 as
+CREATE VIEW v1 AS
 (
-select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
+SELECT t3_x AS v1_x, t3_y AS v1_y FROM t3 WHERE t3_x<=3
 );
 # conjunctive subformula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_c<25 and
-(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_c<25 AND
+(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e);
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
-select * from t1
-where t1_c<25 and
-(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+SELECT * FROM t1
+WHERE t1_c<25 AND
+(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e);
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
-explain select * from t1
-where t1_c<25 and
-(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+EXPLAIN SELECT * FROM t1
+WHERE t1_c<25 AND
+(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where t1_c<25 and
-(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_c<25 AND
+(t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e);
 EXPLAIN
 {
   "query_block": {
@@ -56,7 +56,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -64,7 +64,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(t2_g)` < 25",
+          "having_condition": "`MAX(t2_g)` < 25",
           "temporary_table": {
             "table": {
               "table_name": "t2",
@@ -79,53 +79,53 @@ EXPLAIN
     }
   }
 }
-# extracted and formula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_c>55 and t1_b<4 and
-(t1_a,t1_b,t1_c) in
+# extracted AND formula : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_c>55 AND t1_b<4 AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	3	70	3
-select * from t1
-where t1_c>55 and t1_b<4 and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE t1_c>55 AND t1_b<4 AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	3	70	3
-explain select * from t1
-where t1_c>55 and t1_b<4 and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_c>55 AND t1_b<4 AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where t1_c>55 and t1_b<4 and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_c>55 AND t1_b<4 AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -145,7 +145,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -153,7 +153,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(t2_g)` > 55 and t2.t2_f < 4",
+          "having_condition": "`MAX(t2_g)` > 55 and t2.t2_f < 4",
           "temporary_table": {
             "table": {
               "table_name": "t2",
@@ -168,55 +168,55 @@ EXPLAIN
     }
   }
 }
-# extracted or formula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (t1_c>60 or t1_c<25) and
-(t1_a,t1_b,t1_c) in
+# extracted OR formula : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_c>60 OR t1_c<25) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 2	3	70	3
-select * from t1
-where (t1_c>60 or t1_c<25) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE (t1_c>60 OR t1_c<25) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 2	3	70	3
-explain select * from t1
-where (t1_c>60 or t1_c<25) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE (t1_c>60 OR t1_c<25) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where (t1_c>60 or t1_c<25) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_c>60 OR t1_c<25) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -236,7 +236,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -244,7 +244,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(t2_g)` > 60 or `max(t2_g)` < 25",
+          "having_condition": "`MAX(t2_g)` > 60 or `MAX(t2_g)` < 25",
           "temporary_table": {
             "table": {
               "table_name": "t2",
@@ -259,53 +259,53 @@ EXPLAIN
     }
   }
 }
-# extracted and-or formula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_c>60 or t1_c<25) and t1_b>2) and
-(t1_a,t1_b,t1_c) in
+# extracted AND-or formula : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	3	70	3
-select * from t1
-where ((t1_c>60 or t1_c<25) and t1_b>2) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	3	70	3
-explain select * from t1
-where ((t1_c>60 or t1_c<25) and t1_b>2) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_c>60 or t1_c<25) and t1_b>2) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -325,7 +325,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -333,7 +333,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "(`max(t2_g)` > 60 or `max(t2_g)` < 25) and t2.t2_f > 2",
+          "having_condition": "(`MAX(t2_g)` > 60 or `MAX(t2_g)` < 25) and t2.t2_f > 2",
           "temporary_table": {
             "table": {
               "table_name": "t2",
@@ -349,54 +349,54 @@ EXPLAIN
   }
 }
 # conjunctive subformula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_a<2 or t1_d>3) and t1_b>1) and
-(t1_a,t1_b,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 1	2	40	2
-select * from t1
-where ((t1_a<2 or t1_d>3) and t1_b>1) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 1	2	40	2
-explain select * from t1
-where ((t1_a<2 or t1_d>3) and t1_b>1) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_a<2 or t1_d>3) and t1_b>1) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -416,7 +416,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -439,53 +439,53 @@ EXPLAIN
     }
   }
 }
-# using view in subquery definition : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_c>20 and
-(t1_a,t1_c) in
+# using view IN subquery defINition : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_c>20 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 3	2	23	1
-select * from t1
-where t1_c>20 and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_c>20 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 3	2	23	1
-explain select * from t1
-where t1_c>20 and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_c>20 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
-explain format=json select * from t1
-where t1_c>20 and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_c>20 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 EXPLAIN
@@ -505,7 +505,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["v1_x", "max(v1_y)"],
+      "used_key_parts": ["v1_x", "MAX(v1_y)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -513,7 +513,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(v1_y)` > 20",
+          "having_condition": "`MAX(v1_y)` > 20",
           "temporary_table": {
             "table": {
               "table_name": "t3",
@@ -529,38 +529,38 @@ EXPLAIN
   }
 }
 # using equality : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where t1_c>20 and t1_c=v1_y and
-(t1_a,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1
+WHERE t1_c>20 AND t1_c=v1_y AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 3	2	23	1	3	23
-select * from t1,v1
-where t1_c>20 and t1_c=v1_y and
-(t1_a,t1_c) in
+SELECT * FROM t1,v1
+WHERE t1_c>20 AND t1_c=v1_y AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 3	2	23	1	3	23
-explain select * from t1,v1
-where t1_c>20 and t1_c=v1_y and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1,v1
+WHERE t1_c>20 AND t1_c=v1_y AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -568,14 +568,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t3.t3_y	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1,v1
-where t1_c>20 and t1_c=v1_y and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1,v1
+WHERE t1_c>20 AND t1_c=v1_y AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -607,7 +607,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t3.t3_y"],
       "rows": 1,
       "filtered": 100,
@@ -615,7 +615,7 @@ EXPLAIN
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(t2_g)` > 20",
+          "having_condition": "`MAX(t2_g)` > 20",
           "temporary_table": {
             "table": {
               "table_name": "t2",
@@ -631,54 +631,54 @@ EXPLAIN
   }
 }
 # conjunctive subformula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_a<2 and
-(t1_a,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_a<2 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 1	2	40	2
-select * from t1
-where t1_a<2 and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a<2 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 1	2	40	2
-explain select * from t1
-where t1_a<2 and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_a<2 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where t1_a<2 and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_a<2 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -698,7 +698,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -720,55 +720,55 @@ EXPLAIN
     }
   }
 }
-# extracted and formula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_a>2 and t1_a<5 and
-(t1_a,t1_c) in
+# extracted AND formula : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_a>2 AND t1_a<5 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
-select * from t1
-where t1_a>2 and t1_a<5 and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a>2 AND t1_a<5 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
-explain select * from t1
-where t1_a>2 and t1_a<5 and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_a>2 AND t1_a<5 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where t1_a>2 and t1_a<5 and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_a>2 AND t1_a<5 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -788,7 +788,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -810,57 +810,57 @@ EXPLAIN
     }
   }
 }
-# extracted or formula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (t1_a<2 or t1_a>=4) and
-(t1_a,t1_c) in
+# extracted OR formula : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a<2 OR t1_a>=4) AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 4	2	24	4
 1	2	40	2
-select * from t1
-where (t1_a<2 or t1_a>=4) and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE (t1_a<2 OR t1_a>=4) AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 4	2	24	4
 1	2	40	2
-explain select * from t1
-where (t1_a<2 or t1_a>=4) and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a<2 OR t1_a>=4) AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where (t1_a<2 or t1_a>=4) and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a<2 OR t1_a>=4) AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -880,7 +880,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -902,53 +902,53 @@ EXPLAIN
     }
   }
 }
-# extracted and-or formula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+# extracted AND-or formula : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	4	35	3
-select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	4	35	3
-explain select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 EXPLAIN
@@ -968,7 +968,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -990,53 +990,53 @@ EXPLAIN
     }
   }
 }
-# extracted and-or formula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+# extracted AND-or formula : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	4	35	3
-select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	4	35	3
-explain select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e,t2_f
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
 )
 ;
 EXPLAIN
@@ -1056,7 +1056,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1079,52 +1079,52 @@ EXPLAIN
   }
 }
 # conjunctive subformula : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_b<3 or t1_d>2) and t1_a<2) and
-(t1_a,t1_b,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	2	40	2
-select * from t1
-where ((t1_b<3 or t1_d>2) and t1_a<2) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	2	40	2
-explain select * from t1
-where ((t1_b<3 or t1_d>2) and t1_a<2) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_b<3 or t1_d>2) and t1_a<2) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -1144,7 +1144,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1167,52 +1167,52 @@ EXPLAIN
   }
 }
 # using equalities : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_d=1 and t1_a=t1_d and
-(t1_a,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_d=1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
-select * from t1
-where t1_d=1 and t1_a=t1_d and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_d=1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	3	40	1
-explain select * from t1
-where t1_d=1 and t1_a=t1_d and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_d=1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	const,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where
-explain format=json select * from t1
-where t1_d=1 and t1_a=t1_d and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_d=1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -1232,7 +1232,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["const", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1253,52 +1253,52 @@ EXPLAIN
   }
 }
 # using equality : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_d>1 and t1_a=t1_d and
-(t1_a,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_d>1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
-select * from t1
-where t1_d>1 and t1_a=t1_d and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_d>1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 4	2	24	4
-explain select * from t1
-where t1_d>1 and t1_a=t1_d and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_d>1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where t1_d>1 and t1_a=t1_d and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_d>1 AND t1_a=t1_d AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -1318,7 +1318,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1340,53 +1340,53 @@ EXPLAIN
     }
   }
 }
-# using view in subquery definition : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where t1_a<3 and
-(t1_a,t1_c) in
+# using view IN subquery defINition : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE t1_a<3 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	1	15	4
-select * from t1
-where t1_a<3 and
-(t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a<3 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 2	1	15	4
-explain select * from t1
-where t1_a<3 and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE t1_a<3 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
-explain format=json select * from t1
-where t1_a<3 and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE t1_a<3 AND
+(t1_a,t1_c) IN
 (
-select v1_x,max(v1_y)
-from v1
-where v1_x>1
-group by v1_x
+SELECT v1_x,MAX(v1_y)
+FROM v1
+WHERE v1_x>1
+GROUP BY v1_x
 )
 ;
 EXPLAIN
@@ -1406,7 +1406,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["v1_x", "max(v1_y)"],
+      "used_key_parts": ["v1_x", "MAX(v1_y)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1429,40 +1429,40 @@ EXPLAIN
   }
 }
 # using equality : pushing into WHERE
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where t1_a=v1_x and v1_x<2 and v1_y>30 and
-(t1_a,t1_c) in
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1,v1
+WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 1	3	40	1	1	35
 1	2	40	2	1	35
-select * from t1,v1
-where t1_a=v1_x and v1_x<2 and v1_y>30 and
-(t1_a,t1_c) in
+SELECT * FROM t1,v1
+WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 1	3	40	1	1	35
 1	2	40	2	1	35
-explain select * from t1,v1
-where t1_a=v1_x and v1_x<2 and v1_y>30 and
-(t1_a,t1_c) in
+EXPLAIN SELECT * FROM t1,v1
+WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1470,14 +1470,14 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t3.t3_x,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1,v1
-where t1_a=v1_x and v1_x<2 and v1_y>30 and
-(t1_a,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1,v1
+WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND
+(t1_a,t1_c) IN
 (
-select t2_e,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -1509,7 +1509,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
       "ref": ["test.t3.t3_x", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1532,53 +1532,53 @@ EXPLAIN
   }
 }
 # conjunctive subformula : pushing into WHERE
-# extracted or formula : pushing into HAVING
-set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((t1_b<3 or t1_b=4) and t1_a<3) and
-(t1_a,t1_b,t1_c) in
+# extracted OR formula : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	2	40	2
-select * from t1
-where ((t1_b<3 or t1_b=4) and t1_a<3) and
-(t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 t1_a	t1_b	t1_c	t1_d
 1	2	40	2
-explain select * from t1
-where ((t1_b<3 or t1_b=4) and t1_a<3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
-explain format=json select * from t1
-where ((t1_b<3 or t1_b=4) and t1_a<3) and
-(t1_a,t1_b,t1_c) in
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND
+(t1_a,t1_b,t1_c) IN
 (
-select t2_e,t2_f,max(t2_g)
-from t2
-where t2_e<5
-group by t2_e
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
 )
 ;
 EXPLAIN
@@ -1598,7 +1598,7 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
       "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
@@ -1621,5 +1621,904 @@ EXPLAIN
     }
   }
 }
-drop table t1,t2,t3;
-drop view v1;
+# conjunctive subformula using addition : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a+t1_c>41) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+SELECT * FROM t1
+WHERE (t1_a+t1_c>41) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a+t1_c>41) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a+t1_c>41) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_a + t1.t1_c > 41 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "t2.t2_e + `MAX(t2_g)` > 41",
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using substitution : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_c-t1_a<35) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+3	2	23	1
+SELECT * FROM t1
+WHERE (t1_c-t1_a<35) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+3	2	23	1
+EXPLAIN SELECT * FROM t1
+WHERE (t1_c-t1_a<35) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_c-t1_a<35) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_c - t1.t1_a < 35 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "`MAX(t2_g)` - t2.t2_e < 35",
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using multiplication : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_c*t1_a>100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+SELECT * FROM t1
+WHERE (t1_c*t1_a>100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_c*t1_a>100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_c*t1_a>100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_c * t1.t1_a > 100 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "`MAX(t2_g)` * t2.t2_e > 100",
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using division : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_c/t1_a>30) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+1	2	40	2
+2	3	70	3
+SELECT * FROM t1
+WHERE (t1_c/t1_a>30) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+1	2	40	2
+2	3	70	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_c/t1_a>30) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_c/t1_a>30) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_c / t1.t1_a > 30 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "`MAX(t2_g)` / t2.t2_e > 30",
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using BETWEEN : pushing into HAVING
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_c BETWEEN 50 AND 100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+SELECT * FROM t1
+WHERE (t1_c BETWEEN 50 AND 100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+2	3	70	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_c BETWEEN 50 AND 100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_c BETWEEN 50 AND 100) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_c between 50 and 100 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "`MAX(t2_g)` between 50 and 100",
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using addition : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a+t1_b > 5) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+SELECT * FROM t1
+WHERE (t1_a+t1_b > 5) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a+t1_b > 5) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a+t1_b > 5) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_a + t1.t1_b > 5 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "12",
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e + t2.t2_f > 5"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using substitution : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a-t1_b > 0) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+SELECT * FROM t1
+WHERE (t1_a-t1_b > 0) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a-t1_b > 0) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a-t1_b > 0) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_a - t1.t1_b > 0 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "12",
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e - t2.t2_f > 0"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using multiplication : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a*t1_b > 6) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+SELECT * FROM t1
+WHERE (t1_a*t1_b > 6) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a*t1_b > 6) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a*t1_b > 6) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_a * t1.t1_b > 6 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "12",
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e * t2.t2_f > 6"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using division : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_b/t1_a > 2) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+1	4	35	3
+SELECT * FROM t1
+WHERE (t1_b/t1_a > 2) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+1	4	35	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_b/t1_a > 2) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_b/t1_a > 2) AND
+(t1_a,t1_b,t1_c) IN
+(
+SELECT t2_e,t2_f,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e,t2_f
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_b / t1.t1_a > 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "12",
+      "used_key_parts": ["t2_e", "t2_f", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5 and t2.t2_f / t2.t2_e > 2"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+# conjunctive subformula using BETWEEN : pushing into WHERE
+SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1
+WHERE (t1_a BETWEEN 1 AND 3) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+3	2	23	1
+1	2	40	2
+2	3	70	3
+SELECT * FROM t1
+WHERE (t1_a BETWEEN 1 AND 3) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+t1_a	t1_b	t1_c	t1_d
+1	3	40	1
+3	2	23	1
+1	2	40	2
+2	3	70	3
+EXPLAIN SELECT * FROM t1
+WHERE (t1_a BETWEEN 1 AND 3) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
+EXPLAIN FORMAT=JSON SELECT * FROM t1
+WHERE (t1_a BETWEEN 1 AND 3) AND
+(t1_a,t1_c) IN
+(
+SELECT t2_e,MAX(t2_g)
+FROM t2
+WHERE t2_e<5
+GROUP BY t2_e
+)
+;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 12,
+      "filtered": 100,
+      "attached_condition": "t1.t1_a between 1 and 3 and t1.t1_a is not null and t1.t1_c is not null"
+    },
+    "table": {
+      "table_name": "<subquery2>",
+      "access_type": "eq_ref",
+      "possible_keys": ["distinct_key"],
+      "key": "distinct_key",
+      "key_length": "8",
+      "used_key_parts": ["t2_e", "MAX(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
+      "rows": 1,
+      "filtered": 100,
+      "materialized": {
+        "unique": 1,
+        "query_block": {
+          "select_id": 2,
+          "temporary_table": {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e between 1 and 3"
+            }
+          }
+        }
+      }
+    }
+  }
+}
+DROP TABLE t1,t2,t3;
+DROP VIEW v1;
diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test
index a40ba1a..1aae56b 100644
--- a/mysql-test/t/in_subq_cond_pushdown.test
+++ b/mysql-test/t/in_subq_cond_pushdown.test
@@ -1,345 +1,526 @@
-let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_subquery=off' for; 
+LET $no_pushdown=
+  SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR;
 
-create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
-create table t2 (t2_e int, t2_f int, t2_g int);
-create table t3 (t3_x int, t3_y int);
+CREATE TABLE t1 (t1_a INt, t1_b INt, t1_c INt, t1_d INt);
+CREATE TABLE t2 (t2_e INt, t2_f INt, t2_g INt);
+CREATE TABLE t3 (t3_x INt, t3_y INt);
 
-insert into t1 values
+INSERT INTO t1 VALUES
 (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
 (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
 (5,5,65,1), (2,3,70,3),  (1,4,35,3), (2,3,25,3);
 
-insert into t2 values
+INSERT INTO t2 VALUES
 (1,2,38), (2,3,15), (1,3,40), (1,4,35),
 (2,2,70), (3,4,23), (5,5,12), (5,4,17),
 (3,3,17), (4,2,24), (2,5,25), (5,1,65);
 
-insert into t3 values
+INSERT INTO t3 VALUES
 (1,25), (1,18), (2,15), (4,24),
 (1,35), (3,23), (3,17), (2,15);
 
-create view v1 as
+CREATE VIEW v1 AS
 (
-  select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
+  SELECT t3_x AS v1_x, t3_y AS v1_y FROM t3 WHERE t3_x<=3
 );
 
 --echo # conjunctive subformula : pushing into HAVING
 let $query=
-select * from t1
-where t1_c<25 and
-  (t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+SELECT * FROM t1
+WHERE t1_c<25 AND
+  (t1_a,t1_c) IN (SELECT t2_e,MAX(t2_g) FROM t2 WHERE t2_e<5 GROUP BY t2_e);
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted and formula : pushing into HAVING
+--echo # extracted AND formula : pushing into HAVING
 let $query=
-select * from t1
-where t1_c>55 and t1_b<4 and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE t1_c>55 AND t1_b<4 AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted or formula : pushing into HAVING
+--echo # extracted OR formula : pushing into HAVING
 let $query=
-select * from t1
-where (t1_c>60 or t1_c<25) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE (t1_c>60 OR t1_c<25) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted and-or formula : pushing into HAVING
+--echo # extracted AND-or formula : pushing into HAVING
 let $query=
-select * from t1
-where ((t1_c>60 or t1_c<25) and t1_b>2) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_c>60 OR t1_c<25) AND t1_b>2) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # conjunctive subformula : pushing into HAVING
 let $query=
-select * from t1
-where ((t1_a<2 or t1_d>3) and t1_b>1) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_d>3) AND t1_b>1) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # using view in subquery definition : pushing into HAVING
+--echo # using view IN subquery defINition : pushing into HAVING
 let $query=
-select * from t1
-where t1_c>20 and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_c>20 AND
+  (t1_a,t1_c) IN
   (
-    select v1_x,max(v1_y)
-    from v1
-    where v1_x>1
-    group by v1_x
+    SELECT v1_x,MAX(v1_y)
+    FROM v1
+    WHERE v1_x>1
+    GROUP BY v1_x
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # using equality : pushing into WHERE
 let $query=
-select * from t1,v1
-where t1_c>20 and t1_c=v1_y and
-  (t1_a,t1_c) in
+SELECT * FROM t1,v1
+WHERE t1_c>20 AND t1_c=v1_y AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # conjunctive subformula : pushing into WHERE
 let $query=
-select * from t1
-where t1_a<2 and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a<2 AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted and formula : pushing into WHERE
+--echo # extracted AND formula : pushing into WHERE
 let $query=
-select * from t1
-where t1_a>2 and t1_a<5 and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a>2 AND t1_a<5 AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted or formula : pushing into WHERE
+--echo # extracted OR formula : pushing into WHERE
 let $query=
-select * from t1
-where (t1_a<2 or t1_a>=4) and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE (t1_a<2 OR t1_a>=4) AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted and-or formula : pushing into WHERE
+--echo # extracted AND-or formula : pushing into WHERE
 let $query=
-select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e,t2_f
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # extracted and-or formula : pushing into WHERE
+--echo # extracted AND-or formula : pushing into WHERE
 let $query=
-select * from t1
-where ((t1_a<2 or t1_a=5) and t1_b>3) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_a<2 OR t1_a=5) AND t1_b>3) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e,t2_f
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # conjunctive subformula : pushing into WHERE
 let $query=
-select * from t1
-where ((t1_b<3 or t1_d>2) and t1_a<2) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_d>2) AND t1_a<2) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # using equalities : pushing into WHERE
 let $query=
-select * from t1
-where t1_d=1 and t1_a=t1_d and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_d=1 AND t1_a=t1_d AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # using equality : pushing into WHERE
 let $query=
-select * from t1
-where t1_d>1 and t1_a=t1_d and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_d>1 AND t1_a=t1_d AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
---echo # using view in subquery definition : pushing into WHERE
+--echo # using view IN subquery defINition : pushing into WHERE
 let $query=
-select * from t1
-where t1_a<3 and
-  (t1_a,t1_c) in
+SELECT * FROM t1
+WHERE t1_a<3 AND
+  (t1_a,t1_c) IN
   (
-    select v1_x,max(v1_y)
-    from v1
-    where v1_x>1
-    group by v1_x
+    SELECT v1_x,MAX(v1_y)
+    FROM v1
+    WHERE v1_x>1
+    GROUP BY v1_x
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # using equality : pushing into WHERE
 let $query=
-select * from t1,v1
-where t1_a=v1_x and v1_x<2 and v1_y>30 and
-  (t1_a,t1_c) in
+SELECT * FROM t1,v1
+WHERE t1_a=v1_x AND v1_x<2 AND v1_y>30 AND
+  (t1_a,t1_c) IN
   (
-    select t2_e,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
 --echo # conjunctive subformula : pushing into WHERE
---echo # extracted or formula : pushing into HAVING
+--echo # extracted OR formula : pushing into HAVING
 let $query=
-select * from t1
-where ((t1_b<3 or t1_b=4) and t1_a<3) and
-  (t1_a,t1_b,t1_c) in
+SELECT * FROM t1
+WHERE ((t1_b<3 OR t1_b=4) AND t1_a<3) AND
+  (t1_a,t1_b,t1_c) IN
   (
-    select t2_e,t2_f,max(t2_g)
-    from t2
-    where t2_e<5
-    group by t2_e
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
   )
 ;
 
-eval $no_pushdown $query;
-eval $query;
-eval explain $query;
-eval explain format=json $query;
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
 
-drop table t1,t2,t3;
-drop view v1;
+--echo # conjunctive subformula using addition : pushing into HAVING
+let $query=
+SELECT * FROM t1
+WHERE (t1_a+t1_c>41) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using substitution : pushing into HAVING
+let $query=
+SELECT * FROM t1
+WHERE (t1_c-t1_a<35) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using multiplication : pushing into HAVING
+let $query=
+SELECT * FROM t1
+WHERE (t1_c*t1_a>100) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using division : pushing into HAVING
+let $query=
+SELECT * FROM t1
+WHERE (t1_c/t1_a>30) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using BETWEEN : pushing into HAVING
+let $query=
+SELECT * FROM t1
+WHERE (t1_c BETWEEN 50 AND 100) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using addition : pushing into WHERE
+let $query=
+SELECT * FROM t1
+WHERE (t1_a+t1_b > 5) AND
+  (t1_a,t1_b,t1_c) IN
+  (
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using substitution : pushing into WHERE
+let $query=
+SELECT * FROM t1
+WHERE (t1_a-t1_b > 0) AND
+  (t1_a,t1_b,t1_c) IN
+  (
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using multiplication : pushing into WHERE
+let $query=
+SELECT * FROM t1
+WHERE (t1_a*t1_b > 6) AND
+  (t1_a,t1_b,t1_c) IN
+  (
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using division : pushing into WHERE
+let $query=
+SELECT * FROM t1
+WHERE (t1_b/t1_a > 2) AND
+  (t1_a,t1_b,t1_c) IN
+  (
+    SELECT t2_e,t2_f,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e,t2_f
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+--echo # conjunctive subformula using BETWEEN : pushing into WHERE
+let $query=
+SELECT * FROM t1
+WHERE (t1_a BETWEEN 1 AND 3) AND
+  (t1_a,t1_c) IN
+  (
+    SELECT t2_e,MAX(t2_g)
+    FROM t2
+    WHERE t2_e<5
+    GROUP BY t2_e
+  )
+;
+
+EVAL $no_pushdown $query;
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1,t2,t3;
+DROP VIEW v1;
diff --git a/sql/item.cc b/sql/item.cc
index 7a6879e..113a56e 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7532,34 +7532,34 @@ Item *Item_field::update_value_transformer(THD *thd, uchar *select_arg)
 
 /**
   @brief
-  Mark subformulas of a condition unusable for the pushed condition
+    Prepare AND/OR formula for extraction of a pushable condition
 
-  @param tab_map    bitmap of tables used by derived table
-  @param subq_pred  subquery
+  @param checker  the checker callback function to be applied to the nodes
+                  of the tree of the object
+  @param arg      parameter to be passed to the checker
 
   @details
-
-    This method is called for both condition pushdown optimizations:
-    pushdown into the materialized views/derived tables (1) and
-    pushdown into the IN subqueries (2).
-    (1) :
-    When the method is called for the pushdown into the materialized views
-    or derived tables (object) the subq_pred parameter is set to 0.
-
-    (2) :
-    When the method is called for the pushdown into the IN subqueries (object) the
-    tab_map parameter is set to 0.
-
-    This method recursively traverses the AND-OR condition cond and for each subformula
-    of the condition it checks whether it can be usable for the extraction of a condition
-    that can be pushed into the object. The subformulas that are not usable are
-    marked with the flag NO_EXTRACTION_FL.
+    This method recursively traverses this AND/OR condition and for each
+    subformula of the condition it checks whether it can be usable for the
+    extraction of a pushable condition. The criteria of pushability of
+    a subformula is checked by the callback function 'checker' with one
+    parameter arg. The subformulas that are not usable are marked with
+    the flag NO_EXTRACTION_FL.
   @note
     This method is called before any call of build_pushable_cond.
-    The flag NO_EXTRACTION_FL set in a subformula allows to avoid building clone
-    for the subformula when extracting the pushable condition.
-**/
-void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred)
+    The flag NO_EXTRACTION_FL set in a subformula allows to avoid building
+    clones for the subformulas that are not used in the pushable condition.
+  @note
+    This method is called for pushdown conditions into materialized
+    derived tables/views optimization.
+    Item::pushable_cond_checker_for_derived() is passed as the actual callback
+    function.
+    Also it is called for pushdown conditions in materialized IN subqueries.
+    Item::pushable_cond_checker_for_subquery is passed as the actual
+    callback function.
+*/
+
+void Item::check_pushable_cond(Pushdown_checker checker, uchar *arg)
 {
   clear_extraction_flag();
   if (type() == Item::COND_ITEM)
@@ -7570,7 +7570,7 @@ void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred)
     Item *item;
     while ((item=li++))
     {
-      item->check_pushable_cond(tab_map, subq_pred);
+      item->check_pushable_cond(checker, arg);
       if (item->get_extraction_flag() !=  NO_EXTRACTION_FL)
         count++;
       else if (!and_cond)
@@ -7585,56 +7585,54 @@ void Item::check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred)
         item->clear_extraction_flag();
     }
   }
-  else if ((tab_map && !excl_dep_on_table(tab_map)) ||
-           (subq_pred && !excl_dep_on_in_subq_left_part(subq_pred)))
+  else if (!((this->*checker) (arg)))
     set_extraction_flag(NO_EXTRACTION_FL);
 }
 
 
 /**
   @brief
-  Build condition extractable from the given one for the pushdown
+    Build condition extractable from this condition for pushdown
 
-  @param thd        the thread handle
-  @param tab_map    bitmap of tables used by derived table
-  @param subq_pred  subquery
+  @param thd      the thread handle
+  @param checker  the checker callback function to be applied to the
+                  equal items of multiple equality items
+  @param arg      parameter to be passed to the checker
 
   @details
-    This method is called for both condition pushdown optimizations:
-    pushdown into the materialized views/derived tables (1) and
-    pushdown into the IN subqueries (2).
-    (1) :
-    When the method is called for the pushdown into the materialized views
-    or derived tables the subq_pred parameter is set to 0. The condition c1
-    is the check of whether the item depends only on the tab_map of the
-    derived table.
-
-    (2) :
-    When the method is called for the pushdown into the IN subqueries the
-    tab_map parameter is set to 0. The condition c1 is the check of whether
-    the item depends only on the fields from the left part of the IN subquery.
-
-    For the given condition cond this method finds out what condition for which
-    c1 is satisfied can be extracted from cond. If such condition C exists
-    the method builds the item for it.
-    The method uses the flag NO_EXTRACTION_FL set by the preliminary call of
-    the method check_pushable_cond to figure out whether c1 is satisfied for the
-    subformula or not.
+    This method finds out what condition that can be pushed down can be
+    extracted from this condition. If such condition C exists the
+    method builds the item for it. The method uses the flag NO_EXTRACTION_FL
+    set by the preliminary call of the method check_pushable_cond() to figure
+    out whether a subformula is pushable or not.
+    In the case when this item is a multiple equality a checker method is
+    called to find the equal fields to build a new equality that can be
+    pushed down.
   @note
     The built condition C is always implied by the condition cond
     (cond => C). The method tries to build the most restrictive such
     condition (i.e. for any other condition C' such that cond => C'
     we have C => C').
-   @note
+  @note
     The build item is not ready for usage: substitution for the field items
     has to be done and it has to be re-fixed.
+  @note
+    This method is called for pushdown conditions into materialized
+    derived tables/views optimization.
+    Item::pushable_equality_checker_for_derived() is passed as the actual
+    callback function.
+    Also it is called for pushdown conditions into materialized IN subqueries.
+    Item::pushable_equality_checker_for_subquery() is passed as the actual
+    callback function.
 
  @retval
-    the built condition pushable into this table if such a condition exists
+    the built condition pushable into if such a condition exists
     NULL if there is no such a condition
-**/
-Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
-                                Item_in_subselect *subq_pred)
+*/
+
+Item *Item::build_pushable_cond(THD *thd,
+                                Pushdown_checker checker,
+                                uchar *arg)
 {
   bool is_multiple_equality= type() == Item::FUNC_ITEM &&
   ((Item_func*) this)->functype() == Item_func::MULT_EQUAL_FUNC;
@@ -7649,7 +7647,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
     if (((Item_cond*) this)->functype() == Item_func::COND_AND_FUNC)
     {
       cond_and= true;
-      new_cond=new (thd->mem_root) Item_cond_and(thd);
+      new_cond= new (thd->mem_root) Item_cond_and(thd);
     }
     else
       new_cond= new (thd->mem_root) Item_cond_or(thd);
@@ -7666,12 +7664,13 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
           return 0;
         continue;
       }
-      Item *fix= item->build_pushable_cond(thd, tab_map, subq_pred);
+      Item *fix= item->build_pushable_cond(thd, checker, arg);
       if (!fix && !cond_and)
         return 0;
       if (!fix)
         continue;
-      new_cond->argument_list()->push_back(fix, thd->mem_root);
+      if (new_cond->argument_list()->push_back(fix, thd->mem_root))
+        return 0;
     }
 
     switch (new_cond->argument_list()->elements)
@@ -7686,8 +7685,6 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
   }
   else if (is_multiple_equality)
   {
-    if (tab_map && !(used_tables() & tab_map))
-      return 0;
     Item *new_cond= NULL;
     int i= 0;
     Item_equal *item_equal= (Item_equal *) this;
@@ -7699,8 +7696,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
     {
       while ((item=it++))
       {
-        left_item= ((tab_map && (item->used_tables() == tab_map)) ||
-                    (subq_pred && item->find_field(subq_pred))) ? item : NULL;
+        left_item= ((item->*checker) (arg)) ? item : NULL;
         if (left_item)
           break;
       }
@@ -7709,8 +7705,7 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
       return 0;
     while ((item=it++))
     {
-      right_item= ((tab_map && (item->used_tables() == tab_map)) ||
-                   (subq_pred && item->find_field(subq_pred))) ? item : NULL;
+      right_item= ((item->*checker) (arg)) ? item : NULL;
       if (!right_item)
         continue;
       Item_func_eq *eq= 0;
@@ -7741,8 +7736,8 @@ Item *Item::build_pushable_cond(THD *thd, table_map tab_map,
         }
       }
     }
-    if (new_cond)
-      new_cond->fix_fields(thd, &new_cond);
+    if (new_cond && new_cond->fix_fields(thd, &new_cond))
+      return 0;
     return new_cond;
   }
   else if (get_extraction_flag() != NO_EXTRACTION_FL)
diff --git a/sql/item.h b/sql/item.h
index faeb4ae..056e592 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -531,6 +531,7 @@ typedef bool (Item::*Item_processor) (void *arg);
 typedef bool (Item::*Item_analyzer) (uchar **argp);
 typedef Item* (Item::*Item_transformer) (THD *thd, uchar *arg);
 typedef void (*Cond_traverser) (const Item *item, void *arg);
+typedef bool (Item::*Pushdown_checker) (uchar *arg);
 
 struct st_cond_statistic;
 
@@ -1787,6 +1788,8 @@ class Item: public Value_source,
   { return this; }
   virtual Item *grouping_field_transformer_for_where(THD *thd, uchar *arg)
   { return this; }
+  virtual Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg)
+  { return this; }
   virtual Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg)
   { return this; }
   virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg)
@@ -1970,10 +1973,30 @@ class Item: public Value_source,
   {
     marker &= ~EXTRACTION_MASK;
   }
-  bool find_field(Item_in_subselect *subq_pred);
-  Item *build_pushable_cond(THD *thd, table_map tab_map,
-                            Item_in_subselect *subq_pred);
-  void check_pushable_cond(table_map tab_map, Item_in_subselect *subq_pred);
+  void check_pushable_cond(Pushdown_checker excl_dep_func, uchar *arg);
+  bool pushable_cond_checker_for_derived(uchar *arg)
+  {
+    return excl_dep_on_table(*((table_map *)arg));
+  }
+  bool pushable_cond_checker_for_subquery(uchar *arg)
+  {
+    return excl_dep_on_in_subq_left_part((Item_in_subselect *)arg);
+  }
+  Item *get_corresponding_field_in_insubq(Item_in_subselect *subq_pred);
+  Item *build_pushable_cond(THD *thd,
+                            Pushdown_checker checker,
+                            uchar *arg);
+  /*
+    Checks if this item depends only on the tables used in arg
+  */
+  bool pushable_equality_checker_for_derived(uchar *arg)
+  {
+    return (used_tables() == *((table_map *)arg));
+  }
+  bool pushable_equality_checker_for_subquery(uchar *arg)
+  {
+    return get_corresponding_field_in_insubq((Item_in_subselect *)arg);
+  }
 };
 
 MEM_ROOT *get_thd_memroot(THD *thd);
@@ -2959,6 +2982,7 @@ class Item_field :public Item_ident
   Item *derived_field_transformer_for_having(THD *thd, uchar *arg);
   Item *derived_field_transformer_for_where(THD *thd, uchar *arg);
   Item *grouping_field_transformer_for_where(THD *thd, uchar *arg);
+  Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg);
   Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg);
   virtual void print(String *str, enum_query_type query_type);
   bool excl_dep_on_table(table_map tab_map);
@@ -5091,6 +5115,7 @@ class Item_direct_view_ref :public Item_direct_ref
   Item *derived_field_transformer_for_having(THD *thd, uchar *arg);
   Item *derived_field_transformer_for_where(THD *thd, uchar *arg);
   Item *grouping_field_transformer_for_where(THD *thd, uchar *arg);
+  Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg);
   Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg);
 
   void save_val(Field *to)
@@ -5999,6 +6024,8 @@ class Item_cache: public Item_basic_constant,
   { return convert_to_basic_const_item(thd); }
   Item *grouping_field_transformer_for_where(THD *thd, uchar *arg)
   { return convert_to_basic_const_item(thd); }
+  Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg)
+  { return convert_to_basic_const_item(thd); }
   Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg)
   { return convert_to_basic_const_item(thd); }
 };
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index f565f3a..38f1698 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1262,6 +1262,8 @@ class Item_func_nullif :public Item_func_case_expression
   { reset_first_arg_if_needed(); return this; }
   Item *grouping_field_transformer_for_where(THD *thd, uchar *arg)
   { reset_first_arg_if_needed(); return this; }
+  Item *in_subq_field_transformer_for_where(THD *thd, uchar *arg)
+  { reset_first_arg_if_needed(); return this; }
   Item *in_subq_field_transformer_for_having(THD *thd, uchar *arg)
   { reset_first_arg_if_needed(); return this; }
 };
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 49dc582..301f11c 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -585,7 +585,7 @@ class Item_in_subselect :public Item_exists_subselect
   */
   bool is_registered_semijoin;
   
-  List<In_subq_field> comparable_fields;
+  List<In_subq_field> corresponding_fields;
 
   /*
     Used to determine how this subselect item is represented in the item tree,
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 49747e4..e56801b 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5560,8 +5560,9 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
     bool is_mult_eq= (cond && cond->type() == Item::FUNC_ITEM &&
         ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC);
 
-    if (cond && !is_mult_eq)
-      new_conds_list.push_back(cond, thd->mem_root);
+    if (cond && !is_mult_eq &&
+        new_conds_list.push_back(cond, thd->mem_root))
+      return NULL;
     if (new_conds.elements > 0)
     {
       li.rewind();
@@ -5578,7 +5579,8 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       {
         li.rewind();
         item= li++;
-        new_conds_list.push_back(item, thd->mem_root);
+        if (new_conds_list.push_back(item, thd->mem_root))
+          return NULL;
       }
     }
 
@@ -5720,10 +5722,8 @@ bool execute_degenerate_jtbm_semi_join(THD *thd,
         new (thd->mem_root) Item_func_eq(thd,
                                          subq_pred->left_expr->element_index(i),
                                          new_sink->row[i]);
-      if (!eq_cond)
+      if (!eq_cond || eq_list.push_back(eq_cond, thd->mem_root))
         DBUG_RETURN(TRUE);
-
-      eq_list.push_back(eq_cond, thd->mem_root);
     }
   }
   else
@@ -5805,7 +5805,9 @@ setup_degenerate_jtbm_semi_joins(JOIN *join,
     }
     if ((nested_join= table->nested_join))
     {
-      if (setup_degenerate_jtbm_semi_joins(join, &nested_join->join_list, eq_list))
+      if (setup_degenerate_jtbm_semi_joins(join,
+                                           &nested_join->join_list,
+                                           eq_list))
           DBUG_RETURN(TRUE);
     }
   }
@@ -5905,7 +5907,10 @@ bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
         List_iterator<Item> li(*hash_sj_engine->semi_join_conds->argument_list());
         Item *item;
         while ((item=li++))
-          eq_list.push_back(item, thd->mem_root);
+        {
+          if (eq_list.push_back(item, thd->mem_root))
+            DBUG_RETURN(TRUE);
+        }
       }
       table->table->maybe_null= MY_TEST(join->mixed_implicit_grouping);
     }
@@ -6307,38 +6312,33 @@ bool JOIN::choose_tableless_subquery_plan()
 }
 
 
-/**
-  @brief
-    Check if item exists in the list of fields from the left part of subquery
-
-  @param item    the search field
-  @param fields  list of In_subq_fields
-
-  @retval TRUE   if the item was found in the list
-  @retval FALSE  otherwise
+/*
+  Check if the item exists in the fields list of the left part of
+  the IN subquery predicate subq_pred and returns its corresponding
+  item from the select of the right part of subq_pred.
 */
-bool Item::find_field(Item_in_subselect *subq_pred)
+Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred)
 {
   DBUG_ASSERT(type() == Item::FIELD_ITEM ||
               (type() == Item::REF_ITEM &&
                ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF));
 
-  List_iterator<In_subq_field> it(subq_pred->comparable_fields);
+  List_iterator<In_subq_field> it(subq_pred->corresponding_fields);
   In_subq_field *ret;
   Item_field *field_item= (Item_field *) (real_item());
   while ((ret= it++))
   {
     if (field_item->field ==
         ((Item_field *) (ret->left_it->real_item()))->field)
-      return true;
+      return ret->right_it;
   }
-  return false;
+  return NULL;
 }
 
 
 bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
 {
-  if (((Item *)this)->find_field(subq_pred))
+  if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred))
     return true;
   if (item_equal)
   {
@@ -6347,9 +6347,9 @@ bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
     while ((equal_item= it++))
     {
       if (equal_item->const_item())
-	continue;
-      if (equal_item->find_field(subq_pred))
-	return true;
+        continue;
+      if (equal_item->get_corresponding_field_in_insubq(subq_pred))
+        return true;
     }
   }
   return false;
@@ -6361,7 +6361,8 @@ bool Item_direct_view_ref::excl_dep_on_in_subq_left_part(Item_in_subselect *subq
   if (item_equal)
   {
     DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM);
-    return ((Item *)this)->find_field(subq_pred);
+    if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred))
+      return true;
   }
   return (*ref)->excl_dep_on_in_subq_left_part(subq_pred);
 }
@@ -6396,37 +6397,35 @@ bool Item_equal::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
 
 /**
   @brief
-    Transforms item so it can be pushed into the IN subquery HAVING clause
+    Get corresponding item from the select of the right part of IN subquery
 
   @param thd        the thread handle
-  @param in_item    the item for which pushable item should be created
+  @param item       the item from the left part of subq_pred for which
+                    corresponding item should be find
   @param subq_pred  the IN subquery predicate
 
   @details
-    This method traverses the fields of the select of the IN subquery predicate
-    subq_pred trying to find the corresponding item 'new_item' for in_item.
-    If in_item has equal items it traverses the fields of the select of
-    subq_pred for each equal item trying to find corresponding item 'new_item'.
-    If 'new_item' is found, a shell for this item is created. This shell can be
-    pushed into the HAVING part of subq_pred select.
-
-  @retval <item*>  reference to the created corresonding item shell for in_item
-  @retval NULL     if mistake occurs
+    This method looks through the fields of the select of the right part of
+    the IN subquery predicate subq_pred trying to find the corresponding
+    item 'new_item' for item. If item has equal items it looks through
+    the fields of the select of the right part of subq_pred for each equal
+    item trying to find the corresponding item.
+    The method assumes that the given item is either a field item or
+    a reference to a field item.
+
+  @retval <item*>  reference to the corresonding item
+  @retval NULL     if item was not found
 */
-
 static
-Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item,
-                                                Item_in_subselect *subq_pred)
+Item *get_corresponding_item(THD *thd, Item *item,
+                             Item_in_subselect *subq_pred)
 {
-  DBUG_ASSERT(in_item->type() == Item::FIELD_ITEM ||
-              (in_item->type() == Item::REF_ITEM &&
-              ((Item_ref *) in_item)->ref_type() == Item_ref::VIEW_REF));
+  DBUG_ASSERT(item->type() == Item::FIELD_ITEM ||
+              (item->type() == Item::REF_ITEM &&
+              ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF));
 
-  In_subq_field *fi;
-  Item *new_item;
-  List_iterator<In_subq_field> li(subq_pred->comparable_fields);
-  Item_field *field_item= (Item_field *) (in_item->real_item());
-  Item_equal *item_equal= in_item->get_item_equal();
+  Item *corresonding_item;
+  Item_equal *item_equal= item->get_item_equal();
 
   if (item_equal)
   {
@@ -6434,32 +6433,64 @@ Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item,
     Item *equal_item;
     while ((equal_item= it++))
     {
-      field_item= (Item_field *) (equal_item->real_item());
-      li.rewind();
-      while ((fi= li++))
-      {
-        if (field_item->field ==
-            ((Item_field *) (fi->left_it->real_item()))->field)
-        {
-          new_item= fi->right_it;
-          break;
-        }
-      }
+      corresonding_item=
+        equal_item->get_corresponding_field_in_insubq(subq_pred);
+      if (corresonding_item)
+        return corresonding_item;
     }
+    return NULL;
   }
   else
+    return item->get_corresponding_field_in_insubq(subq_pred);
+}
+
+
+Item *Item_field::in_subq_field_transformer_for_where(THD *thd, uchar *arg)
+{
+  Item_in_subselect *subq_pred= (Item_in_subselect *)arg;
+  Item *producing_item= get_corresponding_item(thd, this, subq_pred);
+  if (producing_item)
+    return producing_item->build_clone(thd);
+  return this;
+}
+
+Item *Item_direct_view_ref::in_subq_field_transformer_for_where(THD *thd,
+                                                                uchar *arg)
+{
+  if (item_equal)
   {
-    li.rewind();
-    while ((fi= li++))
-    {
-      if (field_item->field ==
-        ((Item_field *) (fi->left_it->real_item()))->field)
-      {
-        new_item= fi->right_it;
-        break;
-      }
-    }
+    Item_in_subselect *subq_pred= (Item_in_subselect *)arg;
+    Item *producing_item= get_corresponding_item(thd, this, subq_pred);
+    DBUG_ASSERT (producing_item != NULL);
+    return producing_item->build_clone(thd);
   }
+  return this;
+}
+
+
+/**
+  @brief
+    Transforms item so it can be pushed into the IN subquery HAVING clause
+
+  @param thd        the thread handle
+  @param in_item    the item for which pushable item should be created
+  @param subq_pred  the IN subquery predicate
+
+  @details
+    This method finds for in_item that is a field from the left part of the
+    IN subquery predicate subq_pred its corresponding item from the right part
+    of subq_pred.
+    If corresponding item is found, a shell for this item is created.
+    This shell can be pushed into the HAVING part of subq_pred select.
+
+  @retval <item*>  reference to the created corresonding item shell for in_item
+  @retval NULL     if mistake occurs
+*/
+static Item*
+get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item,
+                                          Item_in_subselect *subq_pred)
+{
+  Item *new_item= get_corresponding_item(thd, in_item, subq_pred);
 
   if (new_item)
   {
@@ -6471,7 +6502,6 @@ Item *get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item,
     return ref;
   }
   DBUG_ASSERT(0);
-  return NULL;
 }
 
 
@@ -6503,11 +6533,12 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd,
     sel and saves them with their corresponding items from fields.
 */
 
-void grouping_fields_in_the_in_subq_left_part(THD *thd,
+bool grouping_fields_in_the_in_subq_left_part(THD *thd,
                                               st_select_lex *sel,
                                               List<In_subq_field> *fields,
                                               ORDER *grouping_list)
 {
+  DBUG_ENTER("grouping_fields_in_the_in_subq_left_part");
   sel->grouping_tmp_fields.empty();
   List_iterator<In_subq_field> it(*fields);
   In_subq_field *item;
@@ -6521,54 +6552,62 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd,
           ((Item_field *)(item->left_it->real_item()))->field;
         Grouping_tmp_field *grouping_tmp_field=
           new Grouping_tmp_field(field, item->right_it);
-        sel->grouping_tmp_fields.push_back(grouping_tmp_field);
+        if (sel->grouping_tmp_fields.push_back(grouping_tmp_field,
+                                               thd->mem_root))
+          DBUG_RETURN(TRUE);
       }
     }
   }
+  DBUG_RETURN(FALSE);
 }
 
 
 /**
   @brief
-    Extract the condition that depends only on the fields from the
-    left part of the IN subquery and push it into the select of
-    the right part of the IN subquery
+    Extract condition that can be pushed into select of this IN subquery
 
   @param thd   the thread handle
   @param cond  current condition
 
   @details
-    How the pushdown is made on the example:
-
-    select * from t1
-    where a>3 and b>10 and
-          (a,b) in (select x,max(y) from t2 group by x);
-
-    -->
-
-    select * from t1
-    where a>3 and b>10 and
-          (a,b) in (select x,max(y)
-                    from t2
-                    where x>3
-                    group by x
-                    having max(y)>10);
-
-    The implementation scheme:
-
-    1. It extracts the condition extr from condition cond that depends only on
-       the fields of the left part of the IN subquery (left_part).
-    2. Finds fields F_group in the right part of the IN subquery (right_part)
-       that are used in the GROUP BY.
-    3. Extracts from the extr condition cond_where that depends only on the
-       those fields from the left_part that stay at the same places in the left_part
-       (have the same indexes) as the F_group fields in the projection of the
-       right_part.
-    4. Transforms cond_where so it can be pushed into the WHERE clause of the
-       right_part and delete cond_where from the extr.
-    5. Transforms extr so it can be pushed into the HAVING clause of the right_part
+    This functiom builds the least restrictive condition depending only on
+    the list of fields of the left part of this IN subquery predicate that
+    can be extracted from the given condition cond and pushes it into
+    this IN subquery.
+
+    Example of the transformation:
+
+    SELECT * FROM t1
+    WHERE a>3 AND b>10 AND
+          (a,b) IN (SELECT x,MAX(y) FROM t2 GROUP BY x);
+
+    =>
+
+    SELECT * FROM t1
+    WHERE a>3 AND b>10 AND
+          (a,b) IN (SELECT x,max(y)
+                    FROM t2
+                    WHERE x>3
+                    GROUP BY x
+                    HAVING MAX(y)>10);
+
+
+    In details:
+    1. Check what pushable formula can be extracted from cond
+    2. Build a clone PC of the formula that can be extracted
+    3. If there is no HAVING clause prepare PC to be conjuncted with
+       WHERE clause of this subquery. Otherwise do 4-7.
+    4. Check what formula PC_where can be extracted from PC to be pushed
+       into the WHERE clause of the subquery
+    5. Build PC_where and if PC_where is a conjunct of PC remove it from PC
+       getting PC_having
+    6. Prepare PC_where to be conjuncted with the WHERE clause of
+       the IN subquery
+    7. Prepare PC_having to be conjuncted with the HAVING clause of
+       the IN subquery
+
   @note
-    This method is similar with pushdown_cond_for_derived()
+    This method is similar to pushdown_cond_for_derived()
 
   @retval TRUE   if an error occurs
   @retval FALSE  otherwise
@@ -6577,6 +6616,8 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd,
 bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
 {
   DBUG_ENTER("Item_in_subselect::pushdown_cond_for_in_subquery");
+  Item *remaining_cond= NULL;
+
   if (!cond)
     DBUG_RETURN(FALSE);
 
@@ -6589,13 +6630,14 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
     DBUG_RETURN(FALSE);
 
   /*
-    Create the list of In_subq_field items for this IN subquery:
-    it consists of the pairs of fields from the left part of the IN subquery
-    'left_part' and the respective fields from the select of the right part of
-    the IN subquery 'right_part' (fields that stay on the same places as fields
-    of left_part in the projection list of right_part).
+    Create a list of In_subq_field items for this IN subquery.
+    It consists of the pairs of fields from the left part of this IN subquery
+    predicate 'left_part' and the respective fields from the select of the
+    right part of the IN subquery 'sel' (the field from left_part with the
+    corresponding field from the sel projection list).
+    Attach this list to the IN subquery.
   */
-  comparable_fields.empty();
+  corresponding_fields.empty();
   List_iterator_fast<Item> it(sel->join->fields_list);
   Item *item;
   for (uint i= 0; i < left_expr->cols(); i++)
@@ -6606,104 +6648,66 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
     if (elem->real_item()->type() != Item::FIELD_ITEM)
       continue;
 
-    comparable_fields.push_back(new In_subq_field(elem, item));
+    if (corresponding_fields.push_back(new In_subq_field(elem, item)))
+      DBUG_RETURN(TRUE);
   }
 
-  /*
-    Build the new condition from cond that can be pushed into sel
-  */
+  /*  1. Check what pushable formula can be extracted from cond  */
   Item *extracted_cond;
-  cond->check_pushable_cond(0, this);
-  extracted_cond= cond->build_pushable_cond(thd, 0, this);
-  /*
-    Nothing to push
-  */
+  cond->check_pushable_cond(&Item::pushable_cond_checker_for_subquery,
+                            (uchar *)this);
+  /*  2. Build a clone PC of the formula that can be extracted  */
+  extracted_cond=
+    cond->build_pushable_cond(thd,
+                              &Item::pushable_equality_checker_for_subquery,
+                              (uchar *)this);
+  /*  Nothing to push  */
   if (!extracted_cond)
   {
     DBUG_RETURN(FALSE);
   }
 
+  /*  Collect fields that are used in the GROUP BY of sel  */
   st_select_lex *save_curr_select= thd->lex->current_select;
-  thd->lex->current_select= sel;
-
   if (sel->have_window_funcs())
   {
     if (sel->group_list.first || sel->join->implicit_grouping)
       goto exit;
     ORDER *common_partition_fields=
-	     sel->find_common_window_func_partition_fields(thd);
+       sel->find_common_window_func_partition_fields(thd);
     if (!common_partition_fields)
       goto exit;
 
-    Item *cond_over_partition_fields;
-    grouping_fields_in_the_in_subq_left_part(thd, sel, &comparable_fields,
-					     common_partition_fields);
-
-    sel->check_cond_extraction_for_grouping_fields(extracted_cond);
-    cond_over_partition_fields=
-      sel->build_cond_for_grouping_fields(thd, extracted_cond, true);
-
-    if (cond_over_partition_fields)
-      cond_over_partition_fields= cond_over_partition_fields->transform(thd,
-        &Item::grouping_field_transformer_for_where, (uchar*) sel);
-
-    if (cond_over_partition_fields)
-    {
-      cond_over_partition_fields->walk(
-	      &Item::cleanup_excluding_const_fields_processor, 0, 0);
-      sel->cond_pushed_into_where= cond_over_partition_fields;
-    }
-    goto exit;
+    if (grouping_fields_in_the_in_subq_left_part(thd, sel, &corresponding_fields,
+                                                 common_partition_fields))
+      DBUG_RETURN(TRUE);
   }
+  else if (grouping_fields_in_the_in_subq_left_part(thd, sel,
+                                                    &corresponding_fields,
+                                                    sel->group_list.first))
+    DBUG_RETURN(TRUE);
 
+  /*  Do 4-6.  */
+  sel->pushdown_cond_into_where_clause(thd, extracted_cond,
+                                    &remaining_cond,
+                                    &Item::in_subq_field_transformer_for_where,
+                                    (uchar *) this);
+  if (!remaining_cond)
+    goto exit;
   /*
-    Checks what can be pushed into the WHERE clause of sel from the
-    extracted condition
-  */
-  Item *cond_over_grouping_fields;
-  grouping_fields_in_the_in_subq_left_part(thd, sel, &comparable_fields,
-                                           sel->group_list.first);
-  sel->check_cond_extraction_for_grouping_fields(extracted_cond);
-  cond_over_grouping_fields=
-    sel->build_cond_for_grouping_fields(thd, extracted_cond, true);
-
-  /*
-    Transforms the references to the left_part fields so they can be pushed
-    into sel of the WHERE clause.
-  */
-  if (cond_over_grouping_fields)
-      cond_over_grouping_fields=
-        cond_over_grouping_fields->transform(thd,
-                                   &Item::grouping_field_transformer_for_where,
-                                   (uchar*) sel);
-
-  if (cond_over_grouping_fields)
-  {
-    /*
-      Removes from extracted_cond all parts that can be pushed into the
-      WHERE clause
-    */
-    extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond);
-
-    cond_over_grouping_fields->walk(
-      &Item::cleanup_excluding_const_fields_processor, 0, 0);
-    sel->cond_pushed_into_where= cond_over_grouping_fields;
-
-    if (!extracted_cond)
-      goto exit;
-  }
-  /*
-    Transforms the references to the left_part fields so the transformed
-    condition can be pushed into sel of the HAVING clause
+    7. Prepare PC_having to be conjuncted with the HAVING clause of
+       the IN subquery
   */
-  extracted_cond= extracted_cond->transform(thd,
-                         &Item::in_subq_field_transformer_for_having,
-                         (uchar*) this);
-  if (!extracted_cond)
+  remaining_cond=
+    remaining_cond->transform(thd,
+                              &Item::in_subq_field_transformer_for_having,
+                              (uchar *)this);
+  if (!remaining_cond)
     goto exit;
 
-  extracted_cond->walk(&Item::cleanup_excluding_const_fields_processor, 0, 0);
-  sel->cond_pushed_into_having= extracted_cond;
+  remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor,
+                       0, 0);
+  sel->cond_pushed_into_having= remaining_cond;
 
 exit:
   thd->lex->current_select= save_curr_select;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 1c62a38..dd7f93e 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1208,25 +1208,59 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
 
 /**
   @brief
-  Extract the condition depended on derived table/view and pushed it there 
+    Extract condition that can be pushed into a derived table/view
    
-  @param thd       The thread handle
-  @param cond      The condition from which to extract the pushed condition 
-  @param derived   The reference to the derived table/view
+  @param thd       the thread handle
+  @param cond      current condition
+  @param derived   the reference to the derived table/view
 
   @details
-   This functiom builds the most restrictive condition depending only on
-   the derived table/view that can be extracted from the condition cond. 
-   The built condition is pushed into the having clauses of the
-   selects contained in the query specifying the derived table/view.
-   The function also checks for each select whether any condition depending
-   only on grouping fields can be extracted from the pushed condition.
-   If so, it pushes the condition over grouping fields into the where
-   clause of the select.
-  
-  @retval
-    true    if an error is reported 
-    false   otherwise
+    This functiom builds the least restrictive condition depending only on
+    the derived table/view that can be extracted from the given condition
+    cond and pushes it into the derived table/view.
+
+    Example of the transformation:
+
+    SELECT *
+    FROM t1,
+    (
+      SELECT x,MAX(y) AS max_y
+      FROM t2
+      GROUP BY x
+    ) AS d_tab
+    WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+    =>
+
+    SELECT *
+    FROM t1,
+    (
+      SELECT x,z,MAX(y) AS max_y
+      FROM t2
+      WHERE x>1
+      HAVING max_y<30
+      GROUP BY x
+    ) AS d_tab
+    WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+    In details:
+    1. Check what pushable formula can be extracted from cond
+    2. Build a clone PC of the formula that can be extracted
+
+    Do for every select specifying derived table/view:
+    3. If there is no HAVING clause prepare PC to be conjuncted with
+       WHERE clause of the select. Otherwise do 4-7.
+    4. Check what formula PC_where can be extracted from PC to be pushed
+       into the WHERE clause of the select
+    5. Build PC_where and if PC_where is a conjunct of PC remove it from PC
+       getting PC_having
+    6. Prepare PC_where to be conjuncted with the WHERE clause of the select
+    7. Prepare PC_having to be conjuncted with the HAVING clause of the select
+  @note
+    This method is similar to pushdown_cond_for_in_subquery()
+
+  @retval TRUE   if an error occurs
+  @retval FALSE  otherwise
 */
 
 bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
@@ -1266,62 +1300,25 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
   if (!some_select_allows_cond_pushdown)
     DBUG_RETURN(false);
 
-  /*
-    Build the most restrictive condition extractable from 'cond'
-    that can be pushed into the derived table 'derived'.
-    All subexpressions of this condition are cloned from the
-    subexpressions of 'cond'.
-    This condition has to be fixed yet.
-  */
+  /*  1. Check what pushable formula can be extracted from cond  */
   Item *extracted_cond;
-  cond->check_pushable_cond(derived->table->map, 0);
-  extracted_cond= cond->build_pushable_cond(thd, derived->table->map, 0);
+  cond->check_pushable_cond(&Item::pushable_cond_checker_for_derived,
+                            (uchar *)(&derived->table->map));
+  /*  2. Build a clone PC of the formula that can be extracted  */
+  extracted_cond=
+    cond->build_pushable_cond(thd,
+                              &Item::pushable_equality_checker_for_derived,
+                              ((uchar *)&derived->table->map));
   if (!extracted_cond)
   {
     /* Nothing can be pushed into the derived table */
     DBUG_RETURN(false);
   }
-  /* Push extracted_cond into every select of the unit specifying 'derived' */
+
   st_select_lex *save_curr_select= thd->lex->current_select;
   for (; sl; sl= sl->next_select())
   {
     Item *extracted_cond_copy;
-    if (!sl->cond_pushdown_is_allowed())
-      continue;
-    thd->lex->current_select= sl;
-    if (sl->have_window_funcs())
-    {
-      if (sl->join->group_list || sl->join->implicit_grouping)
-        continue;
-      ORDER *common_partition_fields= 
-	       sl->find_common_window_func_partition_fields(thd);           
-      if (!common_partition_fields)
-        continue;
-      extracted_cond_copy= !sl->next_select() ?
-                           extracted_cond :
-                           extracted_cond->build_clone(thd);
-      if (!extracted_cond_copy)
-        continue;
-
-      Item *cond_over_partition_fields;; 
-      sl->collect_grouping_fields(thd, common_partition_fields);
-      sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy);
-      cond_over_partition_fields=
-        sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true);
-      if (cond_over_partition_fields)
-        cond_over_partition_fields= cond_over_partition_fields->transform(thd,
-                                  &Item::grouping_field_transformer_for_where,
-                                  (uchar*) sl);
-      if (cond_over_partition_fields)
-      {
-        cond_over_partition_fields->walk(
-          &Item::cleanup_excluding_const_fields_processor, 0, 0);
-        sl->cond_pushed_into_where= cond_over_partition_fields;     
-      }
-
-      continue;
-    }
-
     /*
       For each select of the unit except the last one
       create a clone of extracted_cond
@@ -1332,72 +1329,44 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     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)
-      {
-        extracted_cond_copy->walk(
-          &Item::cleanup_excluding_const_fields_processor, 0, 0);
-        sl->cond_pushed_into_where= extracted_cond_copy;
-      }      
-  
-      continue;
-    }
-
-    /*
-      Figure out what can be extracted from the pushed condition
-      that could be pushed into the where clause of sl
-    */
-    Item *cond_over_grouping_fields;
-    sl->collect_grouping_fields(thd, sl->join->group_list);
-    sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy);
-    cond_over_grouping_fields=
-      sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true);
-  
-    /*
-      Transform the references to the 'derived' columns from the condition
-      pushed into the where clause of sl to make them usable in the new context
-    */
-    if (cond_over_grouping_fields)
-      cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
-                              &Item::grouping_field_transformer_for_where,
-                              (uchar*) sl);
-     
-    if (cond_over_grouping_fields)
+    /* Collect fields that are used in the GROUP BY of sl */
+    if (sl->have_window_funcs())
     {
-      /*
-        In extracted_cond_copy remove top conjuncts that
-        has been pushed into the where clause of sl
-      */
-      extracted_cond_copy= remove_pushed_top_conjuncts(thd, extracted_cond_copy);
-
-      cond_over_grouping_fields->walk(
-        &Item::cleanup_excluding_const_fields_processor, 0, 0);
-      sl->cond_pushed_into_where= cond_over_grouping_fields;
-
-      if (!extracted_cond_copy)
+      if (sl->join->group_list || sl->join->implicit_grouping)
+        continue;
+      ORDER *common_partition_fields=
+         sl->find_common_window_func_partition_fields(thd);
+      if (!common_partition_fields)
         continue;
+      sl->collect_grouping_fields(thd, common_partition_fields);
     }
+    else
+      sl->collect_grouping_fields(thd, sl->join->group_list);
+
+    Item *remaining_cond= NULL;
+    /*  Do 4-6  */
+    sl->pushdown_cond_into_where_clause(thd, extracted_cond_copy,
+                                    &remaining_cond,
+                                    &Item::derived_field_transformer_for_where,
+                                    (uchar *) sl);
     
+    if (!remaining_cond)
+      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
+       7. Prepare PC_having to be conjuncted with the HAVING clause of
+          the select
     */
-    extracted_cond_copy= extracted_cond_copy->transform(thd,
-                         &Item::derived_field_transformer_for_having,
-                         (uchar*) sl);
-    if (!extracted_cond_copy)
+    remaining_cond=
+      remaining_cond->transform(thd,
+                                &Item::derived_field_transformer_for_having,
+                                (uchar *) sl);
+    if (!remaining_cond)
       continue;
 
-    extracted_cond_copy->walk(&Item::cleanup_excluding_const_fields_processor,
-                              0, 0);
-    sl->cond_pushed_into_having= extracted_cond_copy;
+    remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor,
+                         0, 0);
+    sl->cond_pushed_into_having= remaining_cond;
   }
   thd->lex->current_select= save_curr_select;
   DBUG_RETURN(false);
 }
-
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ddb0aa5..4e01447 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -7224,7 +7224,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond)
     to figure out whether a subformula depends only on these fields or not.
   @note
     The built condition C is always implied by the condition cond
-    (cond => C). The method tries to build the most restictive such
+    (cond => C). The method tries to build the least restictive such
     condition (i.e. for any other condition C' such that cond => C'
     we have C => C').
   @note
@@ -7237,7 +7237,7 @@ st_select_lex::check_cond_extraction_for_grouping_fields(Item *cond)
 */ 
 
 Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond,
-						    bool no_top_clones)
+                                                    bool no_top_clones)
 {
   if (cond->get_extraction_flag() == FULL_EXTRACTION_FL)
   {
@@ -7265,17 +7265,17 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond,
     {
       if (item->get_extraction_flag() == NO_EXTRACTION_FL)
       {
-	DBUG_ASSERT(cond_and);
-	item->clear_extraction_flag();
-	continue;
+        DBUG_ASSERT(cond_and);
+        item->clear_extraction_flag();
+        continue;
       }
       Item *fix= build_cond_for_grouping_fields(thd, item,
-						no_top_clones & cond_and);
+                                                no_top_clones & cond_and);
       if (!fix)
       {
-	if (cond_and)
-	  continue;
-	break;
+        if (cond_and)
+          continue;
+        break;
       }
       new_cond->argument_list()->push_back(fix, thd->mem_root);
     }
@@ -7283,7 +7283,7 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond,
     if (!cond_and && item)
     {
       while((item= li++))
-	item->clear_extraction_flag();
+        item->clear_extraction_flag();
       return 0;
     }
     switch (new_cond->argument_list()->elements) 
@@ -7496,3 +7496,127 @@ bool SELECT_LEX::vers_push_field(THD *thd, TABLE_LIST *table, const LEX_CSTRING
   return false;
 }
 
+
+/**
+  @brief
+    Extract from given item a condition pushable into WHERE clause
+
+  @param thd             the thread handle
+  @param cond            the item to extract a condition to be pushed
+                         into WHERE
+  @param remaining_cond  the condition that will remain of cond after
+                         the pushdown of its parts into the WHERE clause
+  @param transformer     the transformer callback function to be
+                         applied to the condition so it can be pushed
+                         down into the WHERE clause of this select
+  @param arg             parameter to be passed to the transformer
+
+  @details
+    This method checks if cond entirely or its parts can be
+    pushed into the WHERE clause of this select and prepares it for pushing.
+
+    First it checks wherever this select doesn't have any aggregation function
+    in its projection and GROUP BY clause. If so cond can be entirely
+    pushed into the WHERE clause of this select but before its fields should
+    be transformed with transformer_for_where to make it pushable.
+
+    Otherwise the method checks wherever any condition depending only on
+    grouping fields can be extracted from cond. If there is any it prepares it
+    for pushing using grouping_field_transformer_for_where and if it happens to
+    be a conjunct of cond it removes it from cond. It saves the result of
+    removal in remaining_cond.
+    The extracted condition is saved in cond_pushed_into_where of this select.
+
+  @note
+    When looking for pushable condition the method considers only the grouping
+    fields from the list grouping_tmp_fields whose elements are of the type
+    Grouping_tmp_field. This list must be prepared before the call of the
+    function.
+
+  @note
+    This method is called for pushdown conditions into materialized
+    derived tables/views optimization.
+    Item::derived_field_transformer_for_where is passed as the actual
+    callback function.
+    Also it is called for pushdown conditions into materialized IN subqueries.
+    Item::in_subq_field_transformer_for_where is passed as the actual
+    callback function.
+*/
+
+void st_select_lex::pushdown_cond_into_where_clause(THD *thd, Item *cond,
+                                                    Item **remaining_cond,
+                                                    Item_transformer transformer,
+                                                    uchar *arg)
+{
+  if (!cond_pushdown_is_allowed())
+    return;
+  thd->lex->current_select= this;
+  if (have_window_funcs())
+  {
+    Item *cond_over_partition_fields;
+    check_cond_extraction_for_grouping_fields(cond);
+    cond_over_partition_fields=
+      build_cond_for_grouping_fields(thd, cond, true);
+    if (cond_over_partition_fields)
+      cond_over_partition_fields= cond_over_partition_fields->transform(thd,
+                                &Item::grouping_field_transformer_for_where,
+                                (uchar*) this);
+    if (cond_over_partition_fields)
+    {
+      cond_over_partition_fields->walk(
+        &Item::cleanup_excluding_const_fields_processor, 0, 0);
+      cond_pushed_into_where= cond_over_partition_fields;
+    }
+
+    return;
+  }
+
+  if (!join->group_list && !with_sum_func)
+  {
+    cond=
+      cond->transform(thd, transformer, arg);
+    if (cond)
+    {
+      cond->walk(
+        &Item::cleanup_excluding_const_fields_processor, 0, 0);
+      cond_pushed_into_where= cond;
+    }
+
+    return;
+  }
+
+  /*
+    Figure out what can be extracted from cond
+    that could be pushed into the WHERE clause of this select
+  */
+  Item *cond_over_grouping_fields;
+  check_cond_extraction_for_grouping_fields(cond);
+  cond_over_grouping_fields=
+    build_cond_for_grouping_fields(thd, cond, true);
+
+  /*
+    Transform the references to the columns from the cond
+    pushed into the WHERE clause of this select to make them usable in
+    the new context
+  */
+  if (cond_over_grouping_fields)
+    cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
+                            &Item::grouping_field_transformer_for_where,
+                            (uchar*) this);
+
+  if (cond_over_grouping_fields)
+  {
+
+    /*
+      In cond remove top conjuncts that has been pushed into the WHERE
+      clause of this select
+    */
+    cond= remove_pushed_top_conjuncts(thd, cond);
+
+    cond_over_grouping_fields->walk(
+      &Item::cleanup_excluding_const_fields_processor, 0, 0);
+    cond_pushed_into_where= cond_over_grouping_fields;
+  }
+
+  *remaining_cond= cond;
+}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index e44453a..57f7972 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1276,6 +1276,11 @@ class st_select_lex: public st_select_lex_node
   bool cond_pushdown_is_allowed() const
   { return !olap && !explicit_limit && !tvc; }
   
+  void pushdown_cond_into_where_clause(THD *thd, Item *extracted_cond,
+                                       Item **remaining_cond,
+                                       Item_transformer transformer,
+                                       uchar *arg);
+
 private:
   bool m_non_agg_field_used;
   bool m_agg_func_used;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 58f7a0e..5b7940a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1781,7 +1781,7 @@ JOIN::optimize_inner()
     while ((tbl= li++))
       if (tbl->jtbm_subselect)
       {
-	if (tbl->jtbm_subselect->pushdown_cond_for_in_subquery(thd, conds))
+        if (tbl->jtbm_subselect->pushdown_cond_for_in_subquery(thd, conds))
           DBUG_RETURN(1);
       }
   }


More information about the commits mailing list