[Commits] 83e0b03: Post review changes. Tests added. Comments changed.

revision-id: 83e0b03e0529e6e43254ec0bf57a314d86daf28c (mariadb-10.3.4-56-g83e0b03) parent(s): 6c83e2097819828e8cc6cecfc8c0c1c10dcab443 author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-05-05 17:37:30 +0200 message: Post review changes. Tests added. Comments changed. --- mysql-test/r/in_subq_cond_pushdown.result | 2877 +++++++++++++++++++++-------- mysql-test/t/in_subq_cond_pushdown.test | 571 ++++-- sql/item.cc | 16 +- sql/item.h | 6 +- sql/item_subselect.h | 21 +- sql/opt_subselect.cc | 134 +- sql/sql_derived.cc | 22 +- sql/sql_lex.cc | 8 +- sql/sql_lex.h | 25 +- sql/sql_select.cc | 11 +- sql/sql_select.h | 5 +- 11 files changed, 2601 insertions(+), 1095 deletions(-) diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result index ebb6265..b9e5522 100644 --- a/mysql-test/r/in_subq_cond_pushdown.result +++ b/mysql-test/r/in_subq_cond_pushdown.result @@ -1,10 +1,11 @@ -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 (a INT, b INT, c INT, d INT); +CREATE TABLE t2 (e INT, f INT, g INT); +CREATE TABLE t3 (x INT, y INT); INSERT INTO t1 VALUES -(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), +(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,3,40,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); +(5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3), +(2,2,40,4), (1,4,55,1), (5,3,72,4), (1,2,70,5); 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), @@ -14,31 +15,38 @@ INSERT INTO t3 VALUES (1,35), (3,23), (3,17), (2,15); 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 +); +CREATE VIEW v2 AS +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.e +HAVING max_g>25 ); # 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); -t1_a t1_b t1_c t1_d +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); +a b c 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); -t1_a t1_b t1_c t1_d +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); +a b c 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); +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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); +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": { @@ -46,9 +54,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_c < 25 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -56,22 +64,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "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", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -81,51 +89,51 @@ 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 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 SELECT * FROM t1 -WHERE t1_c>55 AND t1_b<4 AND -(t1_a,t1_b,t1_c) IN +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE t1.c>55 AND t1.b<4 AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -135,9 +143,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_c > 55 and t1.t1_b < 4 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -145,22 +153,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "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.f < 4", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -170,53 +178,53 @@ 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 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE (t1.c>60 OR t1.c<25) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -226,9 +234,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -236,22 +244,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "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", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -259,53 +267,53 @@ EXPLAIN } } } -# extracted AND-or formula : pushing into HAVING +# 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -315,9 +323,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_b > 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -325,22 +333,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "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.f > 2", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -350,53 +358,53 @@ 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -406,9 +414,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_a < 2 or t1.t1_d > 3) and t1.t1_b > 1 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.a < 2 or t1.d > 3) and t1.b > 1 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -416,22 +424,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "t2.t2_f > 1", + "having_condition": "t2.f > 1", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -441,8 +449,8 @@ 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 +WHERE t1.c>20 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -450,11 +458,11 @@ WHERE v1_x>1 GROUP BY v1_x ) ; -t1_a t1_b t1_c t1_d +a b c d 3 2 23 1 SELECT * FROM t1 -WHERE t1_c>20 AND -(t1_a,t1_c) IN +WHERE t1.c>20 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -462,11 +470,11 @@ WHERE v1_x>1 GROUP BY v1_x ) ; -t1_a t1_b t1_c t1_d +a b c d 3 2 23 1 EXPLAIN SELECT * FROM t1 -WHERE t1_c>20 AND -(t1_a,t1_c) IN +WHERE t1.c>20 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -475,12 +483,12 @@ 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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.c>20 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -495,9 +503,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_c > 20 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -506,7 +514,7 @@ EXPLAIN "key": "distinct_key", "key_length": "8", "used_key_parts": ["v1_x", "MAX(v1_y)"], - "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -520,7 +528,7 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3" + "attached_condition": "t3.x > 1 and t3.x <= 3" } } } @@ -530,52 +538,52 @@ 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 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d v1_x v1_y +a b c 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 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d v1_x v1_y +a b c 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 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where -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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.c>20 AND t1.c=v1_y AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -587,19 +595,19 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.t3_y > 20 and t3.t3_x <= 3 and t3.t3_y is not null" + "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t1.t1_c = t3.t3_y and t1.t1_a is not null" + "attached_condition": "t1.c = t3.y and t1.a is not null" }, "table": { "table_name": "<subquery2>", @@ -607,22 +615,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "MAX(t2_g)"], - "ref": ["test.t1.t1_a", "test.t3.t3_y"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t3.y"], "rows": 1, "filtered": 100, "materialized": { "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", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -632,53 +640,53 @@ 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 +WHERE t1.a<2 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 1 3 40 1 1 2 40 2 SELECT * FROM t1 -WHERE t1_a<2 AND -(t1_a,t1_c) IN +WHERE t1.a<2 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 1 3 40 1 1 2 40 2 EXPLAIN SELECT * FROM t1 -WHERE t1_a<2 AND -(t1_a,t1_c) IN +WHERE t1.a<2 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.a<2 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -688,9 +696,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_a < 2 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -698,8 +706,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -712,7 +720,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2" + "attached_condition": "t2.e < 5 and t2.e < 2" } } } @@ -722,53 +730,53 @@ 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 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.a>2 AND t1.a<5 AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -778,9 +786,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_a > 2 and t1.t1_a < 5 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -788,8 +796,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -802,7 +810,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e > 2 and t2.t2_e < 5" + "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" } } } @@ -812,55 +820,55 @@ 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 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.a<2 OR t1.a>=4) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -870,9 +878,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_a < 2 or t1.t1_a >= 4) and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -880,8 +888,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -894,7 +902,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e >= 4)" + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" } } } @@ -902,53 +910,53 @@ EXPLAIN } } } -# extracted AND-or formula : pushing into WHERE +# 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -958,9 +966,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -968,8 +976,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -982,7 +990,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3" + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" } } } @@ -990,53 +998,53 @@ EXPLAIN } } } -# extracted AND-or formula : pushing into WHERE +# 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -1046,9 +1054,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1056,8 +1064,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1070,7 +1078,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3" + "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" } } } @@ -1080,51 +1088,51 @@ 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1134,9 +1142,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_b < 3 or t1.t1_d > 2) and 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" + "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1144,8 +1152,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1158,7 +1166,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2" + "attached_condition": "t2.e < 5 and t2.e < 2" } } } @@ -1168,51 +1176,51 @@ 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 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 1 3 40 1 SELECT * FROM t1 -WHERE t1_d=1 AND t1_a=t1_d AND -(t1_a,t1_c) IN +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 1 3 40 1 EXPLAIN SELECT * FROM t1 -WHERE t1_d=1 AND t1_a=t1_d AND -(t1_a,t1_c) IN +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.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 +WHERE t1.d=1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1222,9 +1230,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_a = 1 and t1.t1_d = 1 and t1.t1_c is not null" + "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1232,8 +1240,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "MAX(t2_g)"], - "ref": ["const", "test.t1.t1_c"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["const", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1245,7 +1253,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e = 1" + "attached_condition": "t2.e = 1" } } } @@ -1254,51 +1262,51 @@ 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 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 SELECT * FROM t1 -WHERE t1_d>1 AND t1_a=t1_d AND -(t1_a,t1_c) IN +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 EXPLAIN SELECT * FROM t1 -WHERE t1_d>1 AND t1_a=t1_d AND -(t1_a,t1_c) IN +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.d>1 AND t1.a=t1.d AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1308,9 +1316,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_d = t1.t1_a and t1.t1_a > 1 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1318,8 +1326,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1332,7 +1340,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e > 1" + "attached_condition": "t2.e < 5 and t2.e > 1" } } } @@ -1340,10 +1348,10 @@ EXPLAIN } } } -# using view IN subquery defINition : pushing into WHERE +# 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 +WHERE t1.a<3 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -1351,11 +1359,10 @@ WHERE v1_x>1 GROUP BY v1_x ) ; -t1_a t1_b t1_c t1_d -2 1 15 4 +a b c d SELECT * FROM t1 -WHERE t1_a<3 AND -(t1_a,t1_c) IN +WHERE t1.a<3 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -1363,11 +1370,10 @@ WHERE v1_x>1 GROUP BY v1_x ) ; -t1_a t1_b t1_c t1_d -2 1 15 4 +a b c d EXPLAIN SELECT * FROM t1 -WHERE t1_a<3 AND -(t1_a,t1_c) IN +WHERE t1.a<3 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -1376,12 +1382,12 @@ 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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE t1.a<3 AND +(t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -1396,9 +1402,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "t1.t1_a < 3 and t1.t1_a is not null and t1.t1_c is not null" + "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1407,7 +1413,7 @@ EXPLAIN "key": "distinct_key", "key_length": "8", "used_key_parts": ["v1_x", "MAX(v1_y)"], - "ref": ["test.t1.t1_a", "test.t1.t1_c"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1420,7 +1426,7 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3 and t3.t3_x < 3" + "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" } } } @@ -1430,54 +1436,54 @@ 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 +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) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d v1_x v1_y +a b c 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 +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) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d v1_x v1_y +a b c 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 +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) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where -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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.x,test.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 +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) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1489,19 +1495,19 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.t3_x < 2 and t3.t3_y > 30 and t3.t3_x <= 3 and t3.t3_x is not null" + "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t1.t1_a = t3.t3_x and t1.t1_c is not null" + "attached_condition": "t1.a = t3.x and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1509,8 +1515,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["t2_e", "MAX(t2_g)"], - "ref": ["test.t3.t3_x", "test.t1.t1_c"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t3.x", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -1523,7 +1529,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e <= 3" + "attached_condition": "t2.e < 5 and t2.e <= 3" } } } @@ -1534,51 +1540,51 @@ 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +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) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1588,9 +1594,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "filtered": 100, - "attached_condition": "(t1.t1_b < 3 or t1.t1_b = 4) and t1.t1_a < 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null" + "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1598,22 +1604,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "t2.t2_f < 3 or t2.t2_f = 4", + "having_condition": "t2.f < 3 or t2.f = 4", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e < 3" + "attached_condition": "t2.e < 5 and t2.e < 3" } } } @@ -1623,51 +1629,51 @@ EXPLAIN } # 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 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 SELECT * FROM t1 -WHERE (t1_a+t1_c>41) AND -(t1_a,t1_c) IN +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 EXPLAIN SELECT * FROM t1 -WHERE (t1_a+t1_c>41) AND -(t1_a,t1_c) IN +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.a+t1.c>41) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1677,9 +1683,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.a + t1.c > 41 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1687,22 +1693,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "t2.t2_e + `MAX(t2_g)` > 41", + "having_condition": "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" + "attached_condition": "t2.e < 5" } } } @@ -1712,53 +1718,53 @@ EXPLAIN } # 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 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 3 2 23 1 SELECT * FROM t1 -WHERE (t1_c-t1_a<35) AND -(t1_a,t1_c) IN +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.c-t1.a<35) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1768,9 +1774,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.c - t1.a < 35 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1778,22 +1784,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`MAX(t2_g)` - t2.t2_e < 35", + "having_condition": "`MAX(t2.g)` - t2.e < 35", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -1803,51 +1809,51 @@ EXPLAIN } # 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 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 SELECT * FROM t1 -WHERE (t1_c*t1_a>100) AND -(t1_a,t1_c) IN +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 EXPLAIN SELECT * FROM t1 -WHERE (t1_c*t1_a>100) AND -(t1_a,t1_c) IN +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.c*t1.a>100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1857,9 +1863,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.c * t1.a > 100 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1867,22 +1873,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`MAX(t2_g)` * t2.t2_e > 100", + "having_condition": "`MAX(t2.g)` * t2.e > 100", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -1892,55 +1898,55 @@ EXPLAIN } # 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 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.c/t1.a>30) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -1950,9 +1956,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.c / t1.a > 30 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -1960,22 +1966,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`MAX(t2_g)` / t2.t2_e > 30", + "having_condition": "`MAX(t2.g)` / t2.e > 30", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5" + "attached_condition": "t2.e < 5" } } } @@ -1985,51 +1991,51 @@ EXPLAIN } # 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 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 SELECT * FROM t1 -WHERE (t1_c BETWEEN 50 AND 100) AND -(t1_a,t1_c) IN +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c d 2 3 70 3 EXPLAIN SELECT * FROM t1 -WHERE (t1_c BETWEEN 50 AND 100) AND -(t1_a,t1_c) IN +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.c BETWEEN 50 AND 100) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -2039,9 +2045,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.c between 50 and 100 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2049,22 +2055,22 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`MAX(t2_g)` between 50 and 100", + "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" + "attached_condition": "t2.e < 5" } } } @@ -2074,51 +2080,51 @@ EXPLAIN } # 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 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 SELECT * FROM t1 -WHERE (t1_a+t1_b > 5) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 EXPLAIN SELECT * FROM t1 -WHERE (t1_a+t1_b > 5) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE (t1.a+t1.b > 5) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -2128,9 +2134,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.a + t1.b > 5 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2138,8 +2144,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -2152,7 +2158,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e + t2.t2_f > 5" + "attached_condition": "t2.e < 5 and t2.e + t2.f > 5" } } } @@ -2162,51 +2168,51 @@ EXPLAIN } # 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 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 SELECT * FROM t1 -WHERE (t1_a-t1_b > 0) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 EXPLAIN SELECT * FROM t1 -WHERE (t1_a-t1_b > 0) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE (t1.a-t1.b > 0) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -2216,9 +2222,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.a - t1.b > 0 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2226,8 +2232,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -2240,7 +2246,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e - t2.t2_f > 0" + "attached_condition": "t2.e < 5 and t2.e - t2.f > 0" } } } @@ -2250,51 +2256,51 @@ EXPLAIN } # 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 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 SELECT * FROM t1 -WHERE (t1_a*t1_b > 6) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c d 4 2 24 4 EXPLAIN SELECT * FROM t1 -WHERE (t1_a*t1_b > 6) AND -(t1_a,t1_b,t1_c) IN +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE (t1.a*t1.b > 6) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -2304,9 +2310,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.a * t1.b > 6 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2314,8 +2320,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -2328,7 +2334,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e * t2.t2_f > 6" + "attached_condition": "t2.e < 5 and t2.e * t2.f > 6" } } } @@ -2338,53 +2344,53 @@ EXPLAIN } # 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 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.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 +WHERE (t1.b/t1.a > 2) AND +(t1.a,t1.b,t1.c) IN ( -SELECT t2_e,t2_f,MAX(t2_g) +SELECT t2.e,t2.f,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e,t2_f +WHERE t2.e<5 +GROUP BY t2.e,t2.f ) ; EXPLAIN @@ -2394,9 +2400,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.b / t1.a > 2 and t1.a is not null and t1.b is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2404,8 +2410,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -2418,7 +2424,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_f / t2.t2_e > 2" + "attached_condition": "t2.e < 5 and t2.f / t2.e > 2" } } } @@ -2428,57 +2434,57 @@ EXPLAIN } # 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 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; -t1_a t1_b t1_c t1_d +a b c 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 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +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 +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.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 +WHERE (t1.a BETWEEN 1 AND 3) AND +(t1.a,t1.c) IN ( -SELECT t2_e,MAX(t2_g) +SELECT t2.e,MAX(t2.g) FROM t2 -WHERE t2_e<5 -GROUP BY t2_e +WHERE t2.e<5 +GROUP BY t2.e ) ; EXPLAIN @@ -2488,9 +2494,9 @@ EXPLAIN "table": { "table_name": "t1", "access_type": "ALL", - "rows": 12, + "rows": 16, "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" + "attached_condition": "t1.a between 1 and 3 and t1.a is not null and t1.c is not null" }, "table": { "table_name": "<subquery2>", @@ -2498,8 +2504,8 @@ EXPLAIN "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"], + "used_key_parts": ["e", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.c"], "rows": 1, "filtered": 100, "materialized": { @@ -2512,7 +2518,1278 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.t2_e < 5 and t2.t2_e between 1 and 3" + "attached_condition": "t2.e < 5 and t2.e between 1 and 3" + } + } + } + } + } + } +} +# conjunctive subformula : pushing into HAVING of the IN subquery +# conjunctive subformula : pushing into WHERE of the view from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +1 2 40 2 +2 3 70 3 +SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +1 2 40 2 +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.c>3 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.c > 3 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g > 3", + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the IN subquery +# conjunctive subformula : pushing into WHERE of the view +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE and HAVING +# of the IN subquery +# conjunctive subformula : pushing into WHERE of the view +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +a b c d +2 3 70 3 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND +(t1.a,t1.b,t1.c) IN +( +SELECT v2.e,MAX(v2.f),v2.max_g +FROM v2 +WHERE v2.e<5 +GROUP BY v2.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.c < 100 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(v2.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "v2.max_g < 100", + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v2.e < 5 and v2.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the IN subquery +# extracted AND formula : pushing into HAVING of the derived table +# from the IN subquery +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into HAVING of the derived table +# conjunctive subformula : pushing into WHERE of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>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 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.b>2; +x y a b max_c +2 15 2 3 70 +2 15 2 3 70 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the derived table +# extracted AND formula : pushing into WHERE of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.a<5; +x y a b max_c +2 15 2 3 70 +4 24 4 2 24 +2 15 2 3 70 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +a b c d +2 3 40 4 +EXPLAIN SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 12 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g +FROM +( +SELECT t2.e, t2.f, MAX(t2.g) AS max_g +FROM t2 +GROUP BY t2.f +HAVING max_g>25 +) as d_tab +WHERE d_tab.e<5 +GROUP BY d_tab.e +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and 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": ["e", "MAX(d_tab.f)", "max_g"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "d_tab.e < 5 and d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_g > 25", + "filesort": { + "sort_key": "t2.f", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE and HAVING +# of the derived table +# extracted AND formula : pushing into WHERE of the IN subquery +# from the derived table +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +EXPLAIN SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE of the derived table +# conjunctive subformula : pushing into HAVING of the IN subquery from +# the derived table +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE (t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +WHERE t2.f<4 +GROUP BY t2.f +) +GROUP BY t1.a +HAVING t1.b<5 +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.a<5; +x y a b max_c +1 25 1 2 70 +1 18 1 2 70 +2 15 2 3 40 +1 35 1 2 70 +2 15 2 3 40 +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +x y a b max_c +4 24 4 2 24 +EXPLAIN SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.x 2 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 16 Using where; Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 12 test.t1.a,test.t1.b,test.t1.c 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * +FROM t3, +( +SELECT t1.a,t1.b,max(t1.c) as max_c +FROM t1 +WHERE t1.a>1 AND +(t1.a,t1.b,t1.c) IN +( +SELECT t2.e,t2.f,MAX(t2.g) +FROM t2 +GROUP BY t2.e +HAVING t2.f<5 +) +GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 8, + "filtered": 100, + "attached_condition": "t3.x < 5 and t3.x is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t3.x"], + "rows": 2, + "filtered": 100, + "attached_condition": "d_tab.max_c < 70", + "materialized": { + "query_block": { + "select_id": 2, + "having_condition": "max_c < 70", + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.a > 1 and t1.a < 5 and t1.a is not null and t1.b is not null and t1.c is not null" + }, + "table": { + "table_name": "<subquery3>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["e", "f", "MAX(t2.g)"], + "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "having_condition": "t2.f < 5", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e > 1 and t2.e < 5" + } + } + } + } + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +# using WINDOW FUNCTIONS : using MAX function +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +1 3 40 1 +2 3 40 4 +1 4 35 3 +1 2 70 5 +SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +1 3 40 1 +2 3 40 4 +1 4 35 3 +1 2 70 5 +EXPLAIN SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.b,test.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>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) +FROM t2 +WHERE t2.e<5 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and 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": ["f", "MAX(t2.g) OVER (PARTITION BY t2.f)"], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.f" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } + } + } + } + } + } + } +} +# conjunctive subformula : pushing into WHERE +# using WINDOW FUNCTIONS : using SUM function +SET STATEMENT optimizer_switch='condition_pushdown_for_subquery=off' FOR SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +5 3 72 4 +SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +a b c d +5 3 72 4 +EXPLAIN SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.b,test.t1.c 1 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.b>1) AND +(t1.b, t1.c) IN +( +SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) +FROM t2 +WHERE t2.e<5 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 16, + "filtered": 100, + "attached_condition": "t1.b > 1 and t1.b is not null and 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": ["f", "CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)"], + "ref": ["test.t1.b", "test.t1.c"], + "rows": 1, + "filtered": 100, + "attached_condition": "t1.c = `<subquery2>`.`CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "window_functions_computation": { + "sorts": { + "filesort": { + "sort_key": "t2.f" + } + }, + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "t2.e < 5 and t2.f > 1" + } } } } diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test index 1aae56b..6c63b16 100644 --- a/mysql-test/t/in_subq_cond_pushdown.test +++ b/mysql-test/t/in_subq_cond_pushdown.test @@ -1,14 +1,15 @@ 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 (a INT, b INT, c INT, d INT); +CREATE TABLE t2 (e INT, f INT, g INT); +CREATE TABLE t3 (x INT, y INT); INSERT INTO t1 VALUES -(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), +(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,3,40,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); +(5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3), +(2,2,40,4), (1,4,55,1), (5,3,72,4), (1,2,70,5); INSERT INTO t2 VALUES (1,2,38), (2,3,15), (1,3,40), (1,4,35), @@ -21,14 +22,22 @@ INSERT INTO t3 VALUES 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 +); + +CREATE VIEW v2 AS +( + SELECT t2.e, t2.f, MAX(t2.g) AS max_g + FROM t2 + GROUP BY t2.e + HAVING max_g>25 ); --echo # conjunctive subformula : pushing into HAVING -let $query= +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); +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; @@ -36,15 +45,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # extracted AND formula : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_c>55 AND t1_b<4 AND - (t1_a,t1_b,t1_c) IN +WHERE t1.c>55 AND t1.b<4 AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -54,15 +63,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # extracted OR formula : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_c>60 OR t1_c<25) AND - (t1_a,t1_b,t1_c) IN +WHERE (t1.c>60 OR t1.c<25) AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -71,16 +80,16 @@ EVAL $query; EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted AND-or formula : pushing into HAVING -let $query= +--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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -90,15 +99,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into HAVING -let $query= +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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -108,10 +117,10 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # using view IN subquery defINition : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_c>20 AND - (t1_a,t1_c) IN +WHERE t1.c>20 AND + (t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -126,15 +135,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1,v1 -WHERE t1_c>20 AND t1_c=v1_y AND - (t1_a,t1_c) IN +WHERE t1.c>20 AND t1.c=v1_y AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -144,15 +153,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_a<2 AND - (t1_a,t1_c) IN +WHERE t1.a<2 AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -162,15 +171,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # extracted AND formula : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_a>2 AND t1_a<5 AND - (t1_a,t1_c) IN +WHERE t1.a>2 AND t1.a<5 AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -180,15 +189,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # extracted OR formula : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_a<2 OR t1_a>=4) AND - (t1_a,t1_c) IN +WHERE (t1.a<2 OR t1.a>=4) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -197,16 +206,16 @@ EVAL $query; EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted AND-or formula : pushing into WHERE -let $query= +--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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -215,16 +224,16 @@ EVAL $query; EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; ---echo # extracted AND-or formula : pushing into WHERE -let $query= +--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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -234,15 +243,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE -let $query= +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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -252,15 +261,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # using equalities : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_d=1 AND t1_a=t1_d AND - (t1_a,t1_c) IN +WHERE t1.d=1 AND t1.a=t1.d AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -270,15 +279,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE t1_d>1 AND t1_a=t1_d AND - (t1_a,t1_c) IN +WHERE t1.d>1 AND t1.a=t1.d AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -287,11 +296,11 @@ EVAL $query; EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; ---echo # using view IN subquery defINition : pushing into WHERE -let $query= +--echo # using view IN subquery definition : pushing into WHERE +LET $query= SELECT * FROM t1 -WHERE t1_a<3 AND - (t1_a,t1_c) IN +WHERE t1.a<3 AND + (t1.a,t1.c) IN ( SELECT v1_x,MAX(v1_y) FROM v1 @@ -306,15 +315,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # using equality : pushing into WHERE -let $query= +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 +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) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -325,15 +334,15 @@ EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula : pushing into WHERE --echo # extracted OR formula : pushing into HAVING -let $query= +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 +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) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -343,15 +352,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using addition : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_a+t1_c>41) AND - (t1_a,t1_c) IN +WHERE (t1.a+t1.c>41) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -361,15 +370,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using substitution : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_c-t1_a<35) AND - (t1_a,t1_c) IN +WHERE (t1.c-t1.a<35) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -379,15 +388,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using multiplication : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_c*t1_a>100) AND - (t1_a,t1_c) IN +WHERE (t1.c*t1.a>100) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -397,15 +406,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using division : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_c/t1_a>30) AND - (t1_a,t1_c) IN +WHERE (t1.c/t1.a>30) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -415,15 +424,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using BETWEEN : pushing into HAVING -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_c BETWEEN 50 AND 100) AND - (t1_a,t1_c) IN +WHERE (t1.c BETWEEN 50 AND 100) AND + (t1.a,t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.e,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 + GROUP BY t2.e ) ; @@ -433,15 +442,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using addition : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_a+t1_b > 5) AND - (t1_a,t1_b,t1_c) IN +WHERE (t1.a+t1.b > 5) AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -451,15 +460,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using substitution : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_a-t1_b > 0) AND - (t1_a,t1_b,t1_c) IN +WHERE (t1.a-t1.b > 0) AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -469,15 +478,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using multiplication : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_a*t1_b > 6) AND - (t1_a,t1_b,t1_c) IN +WHERE (t1.a*t1.b > 6) AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -487,15 +496,15 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using division : pushing into WHERE -let $query= +LET $query= SELECT * FROM t1 -WHERE (t1_b/t1_a > 2) AND - (t1_a,t1_b,t1_c) IN +WHERE (t1.b/t1.a > 2) AND + (t1.a,t1.b,t1.c) IN ( - SELECT t2_e,t2_f,MAX(t2_g) + SELECT t2.e,t2.f,MAX(t2.g) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e,t2_f + WHERE t2.e<5 + GROUP BY t2.e,t2.f ) ; @@ -505,15 +514,239 @@ EVAL EXPLAIN $query; EVAL EXPLAIN FORMAT=JSON $query; --echo # conjunctive subformula using BETWEEN : pushing into WHERE -let $query= +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; + +--echo # conjunctive subformula : pushing into HAVING of the IN subquery +--echo # conjunctive subformula : pushing into WHERE of the view from the IN subquery +LET $query= +SELECT * FROM t1 +WHERE t1.c>3 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT v2.e,MAX(v2.f),v2.max_g + FROM v2 + WHERE v2.e<5 + GROUP BY v2.e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE of the IN subquery +--echo # conjunctive subformula : pushing into WHERE of the view +--echo # from the IN subquery +LET $query= +SELECT * FROM t1 +WHERE t1.a>1 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT v2.e,MAX(v2.f),v2.max_g + FROM v2 + WHERE v2.e<5 + GROUP BY v2.e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE and HAVING +--echo # of the IN subquery +--echo # conjunctive subformula : pushing into WHERE of the view +--echo # from the IN subquery +LET $query= +SELECT * FROM t1 +WHERE t1.a>1 AND t1.c<100 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT v2.e,MAX(v2.f),v2.max_g + FROM v2 + WHERE v2.e<5 + GROUP BY v2.e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE of the IN subquery +--echo # extracted AND formula : pushing into HAVING of the derived table +--echo # from the IN subquery +LET $query= +SELECT * FROM t1 +WHERE t1.a>1 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g + FROM + ( + SELECT t2.e, t2.f, MAX(t2.g) AS max_g + FROM t2 + GROUP BY t2.f + HAVING max_g>25 + ) as d_tab + WHERE d_tab.e<5 + GROUP BY d_tab.e + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into HAVING of the derived table +--echo # conjunctive subformula : pushing into WHERE of the IN subquery from +--echo # the derived table +SELECT * +FROM t3, +( + SELECT t1.a,t1.b,max(t1.c) as max_c + FROM t1 + WHERE t1.a>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 + ) + GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.b>2; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE of the derived table +--echo # extracted AND formula : pushing into WHERE of the IN subquery from +--echo # the derived table +SELECT * +FROM t3, +( + SELECT t1.a,t1.b,max(t1.c) as max_c + FROM t1 + WHERE t1.a>1 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT t2.e,t2.f,MAX(t2.g) + FROM t2 + GROUP BY t2.e + HAVING t2.f<5 + ) + GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.a<5; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE and HAVING +--echo # of the derived table +--echo # extracted AND formula : pushing into WHERE of the IN subquery +--echo # from the derived table +LET $query= +SELECT * +FROM t3, +( + SELECT t1.a,t1.b,max(t1.c) as max_c + FROM t1 + WHERE t1.a>1 AND + (t1.a,t1.b,t1.c) IN + ( + SELECT t2.e,t2.f,MAX(t2.g) + FROM t2 + GROUP BY t2.e + HAVING t2.f<5 + ) + GROUP BY t1.a +) AS d_tab +WHERE d_tab.a=t3.x AND d_tab.a<5 AND d_tab.max_c<70; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE of the derived table +--echo # conjunctive subformula : pushing into HAVING of the IN subquery from +--echo # the derived table +SELECT * +FROM t3, +( + SELECT t1.a,t1.b,max(t1.c) as max_c + FROM t1 + WHERE (t1.a,t1.b,t1.c) IN + ( + SELECT t2.e,t2.f,MAX(t2.g) + FROM t2 + WHERE t2.f<4 + GROUP BY t2.f + ) + GROUP BY t1.a + HAVING t1.b<5 +) AS d_tab +WHERE d_tab.a=t3.x and d_tab.a<5; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE +--echo # using WINDOW FUNCTIONS : using MAX function +LET $query= +SELECT * FROM t1 +WHERE (t1.b>1) AND + (t1.b, t1.c) IN + ( + SELECT t2.f, MAX(t2.g) OVER (PARTITION BY t2.f) + FROM t2 + WHERE t2.e<5 + ) +; + +EVAL $no_pushdown $query; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +--echo # conjunctive subformula : pushing into WHERE +--echo # using WINDOW FUNCTIONS : using SUM function +LET $query= SELECT * FROM t1 -WHERE (t1_a BETWEEN 1 AND 3) AND - (t1_a,t1_c) IN +WHERE (t1.b>1) AND + (t1.b, t1.c) IN ( - SELECT t2_e,MAX(t2_g) + SELECT t2.f, CAST(SUM(t2.g) OVER (PARTITION BY t2.f) AS INT) FROM t2 - WHERE t2_e<5 - GROUP BY t2_e + WHERE t2.e<5 ) ; diff --git a/sql/item.cc b/sql/item.cc index 113a56e..f5878c5 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7865,18 +7865,18 @@ Item *Item_direct_view_ref::derived_field_transformer_for_where(THD *thd, } static -Grouping_tmp_field *find_matching_grouping_field(Item *item, - st_select_lex *sel) +Field_pair *find_matching_grouping_field(Item *item, + st_select_lex *sel) { DBUG_ASSERT(item->type() == Item::FIELD_ITEM || (item->type() == Item::REF_ITEM && ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF)); - List_iterator<Grouping_tmp_field> li(sel->grouping_tmp_fields); - Grouping_tmp_field *gr_field; + List_iterator<Field_pair> li(sel->grouping_tmp_fields); + Field_pair *gr_field; Item_field *field_item= (Item_field *) (item->real_item()); while ((gr_field= li++)) { - if (field_item->field == gr_field->tmp_field) + if (field_item->field == gr_field->field) return gr_field; } Item_equal *item_equal= item->get_item_equal(); @@ -7890,7 +7890,7 @@ Grouping_tmp_field *find_matching_grouping_field(Item *item, li.rewind(); while ((gr_field= li++)) { - if (field_item->field == gr_field->tmp_field) + if (field_item->field == gr_field->field) return gr_field; } } @@ -7902,7 +7902,7 @@ Grouping_tmp_field *find_matching_grouping_field(Item *item, Item *Item_field::grouping_field_transformer_for_where(THD *thd, uchar *arg) { st_select_lex *sel= (st_select_lex *)arg; - Grouping_tmp_field *gr_field= find_matching_grouping_field(this, sel); + Field_pair *gr_field= find_matching_grouping_field(this, sel); if (gr_field) return gr_field->corresponding_item->build_clone(thd); return this; @@ -7916,7 +7916,7 @@ Item_direct_view_ref::grouping_field_transformer_for_where(THD *thd, if (!item_equal) return this; st_select_lex *sel= (st_select_lex *)arg; - Grouping_tmp_field *gr_field= find_matching_grouping_field(this, sel); + Field_pair *gr_field= find_matching_grouping_field(this, sel); return gr_field->corresponding_item->build_clone(thd); } diff --git a/sql/item.h b/sql/item.h index 056e592..20efe65 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1788,6 +1788,7 @@ class Item: public Value_source, { return this; } virtual Item *grouping_field_transformer_for_where(THD *thd, uchar *arg) { return this; } + /* Now is not used. */ 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) @@ -1987,12 +1988,15 @@ class Item: public Value_source, Pushdown_checker checker, uchar *arg); /* - Checks if this item depends only on the tables used in arg + Checks if this item depends only on the arg table */ bool pushable_equality_checker_for_derived(uchar *arg) { return (used_tables() == *((table_map *)arg)); } + /* + Checks if this item consists in the left part of arg IN subquery predicate + */ bool pushable_equality_checker_for_subquery(uchar *arg) { return get_corresponding_field_in_insubq((Item_in_subselect *)arg); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 301f11c..3ab1d00 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -33,6 +33,7 @@ class subselect_hash_sj_engine; class Item_bool_func2; class Comp_creator; class With_element; +class Field_pair; typedef class st_select_lex SELECT_LEX; @@ -447,23 +448,6 @@ TABLE_LIST * const NO_JOIN_NEST=(TABLE_LIST*)0x1; #define SUBS_MAXMIN_ENGINE 128 -/* - The structure that consists of the field from the fields list - of the left part of in subquery and the field from the - projection list of the select from the right part of in subquery. - This fields stays on the same places (have the same indexes) in - their lists. -*/ -class In_subq_field :public Sql_alloc -{ -public: - Item *left_it; - Item *right_it; - In_subq_field(Item *l, Item *r) - :left_it(l), right_it(r) {} -}; - - /** Representation of IN subquery predicates of the form "left_expr IN (SELECT ...)". @@ -585,7 +569,7 @@ class Item_in_subselect :public Item_exists_subselect */ bool is_registered_semijoin; - List<In_subq_field> corresponding_fields; + List<Field_pair> corresponding_fields; /* Used to determine how this subselect item is represented in the item tree, @@ -871,7 +855,6 @@ class subselect_engine: public Sql_alloc, void set_row(List<Item> &item_list, Item_cache **row); }; -class select_value_catcher; class subselect_single_select_engine: public subselect_engine { bool prepared; /* simple subselect is prepared */ diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index e56801b..da9159d 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5464,7 +5464,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, List_iterator_fast<Item_equal> it(new_cond_equal.current_level); /* - Creates multiple equalities 'new_cond_equal' from new_conds list + Creates multiple equalities new_cond_equal from new_conds list equalities. If multiple equality can't be created or the condition from new_conds list isn't an equality the method leaves it in new_conds list. @@ -5477,7 +5477,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, { if (item->type() == Item::FUNC_ITEM && ((Item_func *) item)->functype() == Item_func::EQ_FUNC && - check_simple_equality(thd, + check_simple_equality(thd, Item::Context(Item::ANY_SUBST, ((Item_func_equal *)item)->compare_type_handler(), ((Item_func_equal *)item)->compare_collation()), @@ -5494,15 +5494,15 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, /* cond is an AND-condition. The method conjugates the AND-condition cond, created multiple - equalities 'new_cond_equal' and remain conditions from new_conds. + equalities new_cond_equal and remain conditions from new_conds. First, the method disjoins multiple equalities of cond and - merges 'new_cond_equal' multiple equalities with these equalities. + merges new_cond_equal multiple equalities with these equalities. It checks if after the merge the multiple equalities are knowingly true or false equalities. - It attaches to cond conditions from new_conds list and the result + It attaches to cond the conditions from new_conds list and the result of the merge of multiple equalities. The multiple equalities are - attached only to the upper level of cond AND-condition. So they + attached only to the upper level of AND-condition cond. So they should be pushed down to the inner levels of cond AND-condition if needed. It is done by propagate_new_equalities(). */ @@ -5545,24 +5545,26 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, 1. cond is a multiple equality. In this case cond is merged with the multiple equalities of - 'new_cond_equal'. + new_cond_equal. The new condition is created with the conjunction of new_conds list conditions and the result of merge of multiple equalities. - 2. cond isn't a multiple equality and isn't NULL - In this case new condition is created from cond, remain conditions + 2. cond is NULL + The new condition is created from the conditions of new_conds + list and multiple equalities from new_cond_equal. + 3. Otherwise + In this case the new condition is created from cond, remain conditions from new_conds list and created multiple equalities from - 'new_cond_equal'. - 3. cond is NULL - The new condition is created from the conditions from new_conds - list and multiple equalities from 'new_cond_equal'. + new_cond_equal. */ - List<Item> new_conds_list; // List to store new condition elements + List<Item> new_conds_list; + /* Flag is set to true if cond is a multiple equality */ 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)) return NULL; + if (new_conds.elements > 0) { li.rewind(); @@ -5573,6 +5575,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, if (item->const_item() && !item->val_int()) is_simplified_cond= true; } + if (new_conds.elements > 1) new_conds_list.append(&new_conds); else @@ -5639,8 +5642,8 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, /* If it was found that some of the created condition parts are knowingly - true or false equalities method calls removes_eq_cond() to remove them - from the condition and set the cond_value to the appropriate value. + true or false equalities the method calls removes_eq_cond() to remove them + from cond and set the cond_value to the appropriate value. */ if (is_simplified_cond) cond= cond->remove_eq_conds(thd, cond_value, true); @@ -5773,10 +5776,9 @@ bool execute_degenerate_jtbm_semi_join(THD *thd, @retval FALSE otherwise */ -bool -setup_degenerate_jtbm_semi_joins(JOIN *join, - List<TABLE_LIST> *join_list, - List<Item> &eq_list) +bool setup_degenerate_jtbm_semi_joins(JOIN *join, + List<TABLE_LIST> *join_list, + List<Item> &eq_list) { TABLE_LIST *table; NESTED_JOIN *nested_join; @@ -5799,7 +5801,6 @@ setup_degenerate_jtbm_semi_joins(JOIN *join, subq_pred, eq_list)) DBUG_RETURN(TRUE); - join->is_orig_degenerated= true; } } @@ -5808,10 +5809,9 @@ setup_degenerate_jtbm_semi_joins(JOIN *join, if (setup_degenerate_jtbm_semi_joins(join, &nested_join->join_list, eq_list)) - DBUG_RETURN(TRUE); + DBUG_RETURN(TRUE); } } - DBUG_RETURN(FALSE); } @@ -5831,8 +5831,10 @@ setup_degenerate_jtbm_semi_joins(JOIN *join, The method traverses join_list trying to find any jtbm semi joins for subqueries from IN predicates and optimizes them. After the optimization some of jtbm semi joins may become degenerate. - For example the subquery SELECT MAX(b) FROM t2 from the query + For example the subquery 'SELECT MAX(b) FROM t2' from the query + SELECT * FROM t1 WHERE 4 IN (SELECT MAX(b) FROM t2); + will become degenerate if there is an index on t2.b. If a subquery becomes degenerate it is handled by the function execute_degenerate_jtbm_semi_join(). @@ -6323,14 +6325,13 @@ Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred) (type() == Item::REF_ITEM && ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF)); - List_iterator<In_subq_field> it(subq_pred->corresponding_fields); - In_subq_field *ret; + List_iterator<Field_pair> it(subq_pred->corresponding_fields); + Field_pair *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 ret->right_it; + if (field_item->field == ret->field) + return ret->corresponding_item; } return NULL; } @@ -6401,7 +6402,7 @@ bool Item_equal::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) @param thd the thread handle @param item the item from the left part of subq_pred for which - corresponding item should be find + corresponding item should be found @param subq_pred the IN subquery predicate @details @@ -6413,9 +6414,10 @@ bool Item_equal::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred) 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 <item*> reference to the corresponding item @retval NULL if item was not found */ + static Item *get_corresponding_item(THD *thd, Item *item, Item_in_subselect *subq_pred) @@ -6454,6 +6456,7 @@ Item *Item_field::in_subq_field_transformer_for_where(THD *thd, uchar *arg) return this; } + Item *Item_direct_view_ref::in_subq_field_transformer_for_where(THD *thd, uchar *arg) { @@ -6483,9 +6486,10 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_where(THD *thd, 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 <item*> reference to the created corresponding 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) @@ -6499,9 +6503,11 @@ get_corresponding_item_for_in_subq_having(THD *thd, Item *in_item, &subq_pred->unit->first_select()->context, NullS, NullS, &new_item->name); + if (!ref) + DBUG_ASSERT(0); return ref; } - DBUG_ASSERT(0); + return new_item; } @@ -6515,8 +6521,16 @@ Item *Item_field::in_subq_field_transformer_for_having(THD *thd, uchar *arg) Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, uchar *arg) { - return get_corresponding_item_for_in_subq_having(thd, this, - (Item_in_subselect *)arg); + if (!item_equal) + return this; + else + { + Item *new_item= get_corresponding_item_for_in_subq_having(thd, this, + (Item_in_subselect *)arg); + if (!new_item) + return this; + return new_item; + } } @@ -6524,9 +6538,10 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, @brief Find fields that are used in the GROUP BY of the select - @param thd the thread handle - @param sel the select of the IN subquery predicate - @param fields fields of the left part of the IN subquery predicate + @param thd the thread handle + @param sel the select of the IN subquery predicate + @param fields fields of the left part of the IN subquery predicate + @param grouping_list GROUP BY clause @details This method traverses fields which are used in the GROUP BY of @@ -6535,25 +6550,20 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, bool grouping_fields_in_the_in_subq_left_part(THD *thd, st_select_lex *sel, - List<In_subq_field> *fields, + List<Field_pair> *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; + List_iterator<Field_pair> it(*fields); + Field_pair *item; while ((item= it++)) { for (ORDER *ord= grouping_list; ord; ord= ord->next) { - if ((*ord->item)->eq(item->right_it, 0)) + if ((*ord->item)->eq(item->corresponding_item, 0)) { - Field *field= - ((Item_field *)(item->left_it->real_item()))->field; - Grouping_tmp_field *grouping_tmp_field= - new Grouping_tmp_field(field, item->right_it); - if (sel->grouping_tmp_fields.push_back(grouping_tmp_field, - thd->mem_root)) + if (sel->grouping_tmp_fields.push_back(item, thd->mem_root)) DBUG_RETURN(TRUE); } } @@ -6570,10 +6580,10 @@ bool grouping_fields_in_the_in_subq_left_part(THD *thd, @param cond current condition @details - 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. + This function builds the most restrictive condition depending only on + the list of fields of the left part of this IN subquery predicate + (directly or indirectly through equality) that can be extracted from the + given condition cond and pushes it into this IN subquery. Example of the transformation: @@ -6595,11 +6605,13 @@ bool grouping_fields_in_the_in_subq_left_part(THD *thd, In details: 1. Check what pushable formula can be extracted from cond 2. Build a clone PC of the formula that can be extracted + (the clone is built only if the extracted formula is a AND subformula + of cond or conjunction of such subformulas) 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 + 5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC getting PC_having 6. Prepare PC_where to be conjuncted with the WHERE clause of the IN subquery @@ -6630,7 +6642,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) DBUG_RETURN(FALSE); /* - Create a list of In_subq_field items for this IN subquery. + Create a list of Field_pair 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 @@ -6648,26 +6660,28 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) if (elem->real_item()->type() != Item::FIELD_ITEM) continue; - if (corresponding_fields.push_back(new In_subq_field(elem, item))) + if (corresponding_fields.push_back( + new Field_pair(((Item_field *)(elem->real_item()))->field, + item))) DBUG_RETURN(TRUE); } - /* 1. Check what pushable formula can be extracted from cond */ + /* 1. Check what pushable formula can be extracted from cond */ Item *extracted_cond; cond->check_pushable_cond(&Item::pushable_cond_checker_for_subquery, (uchar *)this); - /* 2. Build a clone PC of the formula that can be extracted */ + /* 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 */ + /* Nothing to push */ if (!extracted_cond) { DBUG_RETURN(FALSE); } - /* Collect fields that are used in the GROUP BY of sel */ + /* Collect fields that are used in the GROUP BY of sel */ st_select_lex *save_curr_select= thd->lex->current_select; if (sel->have_window_funcs()) { @@ -6687,7 +6701,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) sel->group_list.first)) DBUG_RETURN(TRUE); - /* Do 4-6. */ + /* Do 4-6 */ sel->pushdown_cond_into_where_clause(thd, extracted_cond, &remaining_cond, &Item::in_subq_field_transformer_for_where, diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index dd7f93e..80cb63b 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1215,9 +1215,10 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) @param derived the reference to the derived table/view @details - 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. + This function builds the most restrictive condition depending only on + the derived table/view (directly or indirectly through equality) that + can be extracted from the given condition cond and pushes it into the + derived table/view. Example of the transformation: @@ -1246,13 +1247,14 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived) In details: 1. Check what pushable formula can be extracted from cond 2. Build a clone PC of the formula that can be extracted - + (the clone is built only if the extracted formula is a AND subformula + of cond or conjunction of such subformulas) 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 + 5. Build PC_where and if PC_where is a conjunct(s) 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 @@ -1300,11 +1302,11 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!some_select_allows_cond_pushdown) DBUG_RETURN(false); - /* 1. Check what pushable formula can be extracted from cond */ + /* 1. Check what pushable formula can be extracted from cond */ Item *extracted_cond; 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 */ + /* 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, @@ -1332,7 +1334,7 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) /* Collect fields that are used in the GROUP BY of sl */ if (sl->have_window_funcs()) { - if (sl->join->group_list || sl->join->implicit_grouping) + if (sl->group_list.first || sl->join->implicit_grouping) continue; ORDER *common_partition_fields= sl->find_common_window_func_partition_fields(thd); @@ -1341,10 +1343,10 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) sl->collect_grouping_fields(thd, common_partition_fields); } else - sl->collect_grouping_fields(thd, sl->join->group_list); + sl->collect_grouping_fields(thd, sl->group_list.first); Item *remaining_cond= NULL; - /* Do 4-6 */ + /* Do 4-6 */ sl->pushdown_cond_into_where_clause(thd, extracted_cond_copy, &remaining_cond, &Item::derived_field_transformer_for_where, diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 4e01447..bea9fa0 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7130,9 +7130,9 @@ void st_select_lex::collect_grouping_fields(THD *thd, { if ((*ord->item)->eq((Item*)item, 0)) { - Grouping_tmp_field *grouping_tmp_field= - new Grouping_tmp_field(master_unit()->derived->table->field[i], item); - grouping_tmp_fields.push_back(grouping_tmp_field); + Field_pair *grouping_tmp_field= + new Field_pair(master_unit()->derived->table->field[i], item); + grouping_tmp_fields.push_back(grouping_tmp_field); } } } @@ -7530,7 +7530,7 @@ bool SELECT_LEX::vers_push_field(THD *thd, TABLE_LIST *table, const LEX_CSTRING @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 + Field_pair. This list must be prepared before the call of the function. @note diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 57f7972..55d75ff 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -817,26 +817,16 @@ typedef Bounds_checked_array<Item*> Ref_ptr_array; /* - Structure which consists of the field and the item which - correspons to this field. - - In pushdown condition for derived case the corresponding item is - the item that produces this field. - - In pushdown condition for in subquery case the field is the - field from the left part of the in subquery list of fields. - The corresponding item is the item from the projection list of the - select from the right part of in subquery that has the same index - in its list as the field in its list. + Structure which consists of the field and the item that + corresponds to this field. */ - -class Grouping_tmp_field :public Sql_alloc +class Field_pair :public Sql_alloc { public: - Field *tmp_field; + Field *field; Item *corresponding_item; - Grouping_tmp_field(Field *fld, Item *item) - :tmp_field(fld), corresponding_item(item) {} + Field_pair(Field *fld, Item *item) + :field(fld), corresponding_item(item) {} }; @@ -1046,7 +1036,8 @@ class st_select_lex: public st_select_lex_node nesting_map name_visibility_map; table_map with_dep; - List<Grouping_tmp_field> grouping_tmp_fields; + /* the structure to store fields that are used in the GROUP BY of this select */ + List<Field_pair> grouping_tmp_fields; /* it is for correct printing SELECT options */ thr_lock_type lock_type; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5b7940a..4c99c7e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1729,7 +1729,7 @@ JOIN::optimize_inner() if (arena) thd->restore_active_arena(arena, &backup); } - + List<Item> eq_list; if (setup_degenerate_jtbm_semi_joins(this, join_list, eq_list)) @@ -1745,9 +1745,9 @@ JOIN::optimize_inner() new_cond= new (thd->mem_root) Item_cond_and(thd, eq_list); if (new_cond && - (new_cond->fix_fields(thd, &new_cond) || + ((new_cond->fix_fields(thd, &new_cond) || !(conds= and_items(thd, conds, new_cond)) || - conds->fix_fields(thd, &conds))) + conds->fix_fields(thd, &conds)))) DBUG_RETURN(TRUE); } eq_list.empty(); @@ -1837,7 +1837,6 @@ JOIN::optimize_inner() if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) DBUG_RETURN(1); } - if (thd->is_error()) { error= 1; @@ -13533,8 +13532,8 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field, */ bool check_simple_equality(THD *thd, const Item::Context &ctx, - Item *left_item, Item *right_item, - COND_EQUAL *cond_equal) + Item *left_item, Item *right_item, + COND_EQUAL *cond_equal) { Item *orig_left_item= left_item; Item *orig_right_item= right_item; diff --git a/sql/sql_select.h b/sql/sql_select.h index 56b7874..75e0e2a 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1470,7 +1470,10 @@ class JOIN :public Sql_alloc Dynamic_array<KEYUSE_EXT> *ext_keyuses_for_splitting; JOIN_TAB *sort_and_group_aggr_tab; - /* Degenerated before optimize */ + /* + Flag is set to true if select_lex was found to be degenerated before + the optimize_cond() call in JOIN::optimize_inner() method. + */ bool is_orig_degenerated; JOIN(THD *thd_arg, List<Item> &fields_arg, ulonglong select_options_arg,