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

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); } }