revision-id: 48d155b5126807c31223cb25f7df473227c1f601 (mariadb-10.3.4-61-g48d155b) parent(s): ccae16dfc128bfb95421c98a7ce331c38fc747ce author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-04-25 16:57:15 +0200 message: MDEV-12387 Push conditions into materialized subqueries Comments changed, tests changed --- mysql-test/r/in_subq_cond_pushdown.result | 980 +++++++++++++++--------------- mysql-test/t/in_subq_cond_pushdown.test | 182 +++--- sql/opt_subselect.cc | 24 +- 3 files changed, 595 insertions(+), 591 deletions(-) diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result index 97abd21..6676d3b 100644 --- a/mysql-test/r/in_subq_cond_pushdown.result +++ b/mysql-test/r/in_subq_cond_pushdown.result @@ -1,6 +1,6 @@ -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); +create table t1 (t1_a int, t1_b int, t1_c int, t1_d int); +create table t2 (t2_e int, t2_f int, t2_g int); +create table t3 (t3_x int, t3_y int); insert into t1 values (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2), @@ -14,31 +14,31 @@ insert into t3 values (1,35), (3,23), (3,17), (2,15); create view v1 as ( -select * from t3 where x<=3 +select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3 ); # conjunctive subformula : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where c<25 and -(a,c) in (select e,max(g) from t2 where e<5 group by e); -a b c 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); +t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 select * from t1 -where c<25 and -(a,c) in (select e,max(g) from t2 where e<5 group by e); -a b c 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); +t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 explain select * from t1 -where c<25 and -(a,c) in (select e,max(g) from t2 where e<5 group by 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.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where c<25 and -(a,c) in (select e,max(g) from t2 where e<5 group by 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": { @@ -48,7 +48,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null" + "attached_condition": "t1.t1_c < 25 and t1.t1_a is not null and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -56,22 +56,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(g)` < 25", + "having_condition": "`max(t2_g)` < 25", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -81,51 +81,51 @@ EXPLAIN } # extracted and formula : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where c>55 and b<4 and -(a,b,c) in +where t1_c>55 and t1_b<4 and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 2 3 70 3 select * from t1 -where c>55 and b<4 and -(a,b,c) in +where t1_c>55 and t1_b<4 and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 2 3 70 3 explain select * from t1 -where c>55 and b<4 and -(a,b,c) in +where t1_c>55 and t1_b<4 and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where c>55 and b<4 and -(a,b,c) in +where t1_c>55 and t1_b<4 and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -137,7 +137,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -145,22 +145,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(g)` > 55 and t2.f < 4", + "having_condition": "`max(t2_g)` > 55 and t2.t2_f < 4", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -170,53 +170,53 @@ EXPLAIN } # extracted or formula : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where (c>60 or c<25) and -(a,b,c) in +where (t1_c>60 or t1_c<25) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 2 3 70 3 select * from t1 -where (c>60 or c<25) and -(a,b,c) in +where (t1_c>60 or t1_c<25) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 2 3 70 3 explain select * from t1 -where (c>60 or c<25) and -(a,b,c) in +where (t1_c>60 or t1_c<25) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where (c>60 or c<25) and -(a,b,c) in +where (t1_c>60 or t1_c<25) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -228,7 +228,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -236,22 +236,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(g)` > 60 or `max(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.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -261,51 +261,51 @@ EXPLAIN } # extracted and-or formula : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((c>60 or c<25) and b>2) and -(a,b,c) in +where ((t1_c>60 or t1_c<25) and t1_b>2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 2 3 70 3 select * from t1 -where ((c>60 or c<25) and b>2) and -(a,b,c) in +where ((t1_c>60 or t1_c<25) and t1_b>2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 2 3 70 3 explain select * from t1 -where ((c>60 or c<25) and b>2) and -(a,b,c) in +where ((t1_c>60 or t1_c<25) and t1_b>2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((c>60 or c<25) and b>2) and -(a,b,c) in +where ((t1_c>60 or t1_c<25) and t1_b>2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -317,7 +317,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -325,22 +325,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "(`max(g)` > 60 or `max(g)` < 25) and t2.f > 2", + "having_condition": "(`max(t2_g)` > 60 or `max(t2_g)` < 25) and t2.t2_f > 2", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -350,49 +350,53 @@ EXPLAIN } # conjunctive subformula : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((a<2 or d>2) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_d>3) and t1_b>1) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d +4 2 24 4 +1 2 40 2 select * from t1 -where ((a<2 or d>2) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_d>3) and t1_b>1) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d +4 2 24 4 +1 2 40 2 explain select * from t1 -where ((a<2 or d>2) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_d>3) and t1_b>1) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((a<2 or d>2) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_d>3) and t1_b>1) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -404,7 +408,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.d > 2) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null" + "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" }, "table": { "table_name": "<subquery2>", @@ -412,22 +416,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "t2.f > 3", + "having_condition": "t2.t2_f > 1", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -437,51 +441,51 @@ EXPLAIN } # using view in subquery definition : pushing into HAVING set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where c>20 and -(a,c) in +where t1_c>20 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; -a b c d +t1_a t1_b t1_c t1_d 3 2 23 1 select * from t1 -where c>20 and -(a,c) in +where t1_c>20 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; -a b c d +t1_a t1_b t1_c t1_d 3 2 23 1 explain select * from t1 -where c>20 and -(a,c) in +where t1_c>20 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary explain format=json select * from t1 -where c>20 and -(a,c) in +where t1_c>20 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; EXPLAIN @@ -493,7 +497,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null" + "attached_condition": "t1.t1_c > 20 and t1.t1_a is not null and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -501,22 +505,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["x", "max(y)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["v1_x", "max(v1_y)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(y)` > 20", + "having_condition": "`max(v1_y)` > 20", "temporary_table": { "table": { "table_name": "t3", "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.x > 1 and t3.x <= 3" + "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3" } } } @@ -526,52 +530,52 @@ EXPLAIN } # using equality : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1 -where c>20 and c=y and -(a,c) in +where t1_c>20 and t1_c=v1_y and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d x y +t1_a t1_b t1_c t1_d v1_x v1_y 3 2 23 1 3 23 select * from t1,v1 -where c>20 and c=y and -(a,c) in +where t1_c>20 and t1_c=v1_y and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d x y +t1_a t1_b t1_c t1_d v1_x v1_y 3 2 23 1 3 23 explain select * from t1,v1 -where c>20 and c=y and -(a,c) in +where t1_c>20 and t1_c=v1_y and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.a,test.t3.y 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t3.t3_y 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1,v1 -where c>20 and c=y and -(a,c) in +where t1_c>20 and t1_c=v1_y and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -583,7 +587,7 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null" + "attached_condition": "t3.t3_y > 20 and t3.t3_x <= 3 and t3.t3_y is not null" }, "block-nl-join": { "table": { @@ -595,7 +599,7 @@ EXPLAIN "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t1.c = t3.y and t1.a is not null" + "attached_condition": "t1.t1_c = t3.t3_y and t1.t1_a is not null" }, "table": { "table_name": "<subquery2>", @@ -603,22 +607,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t3.y"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t3.t3_y"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "`max(g)` > 20", + "having_condition": "`max(t2_g)` > 20", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5" + "attached_condition": "t2.t2_e < 5" } } } @@ -628,53 +632,53 @@ EXPLAIN } # conjunctive subformula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where a<2 and -(a,c) in +where t1_a<2 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 1 2 40 2 select * from t1 -where a<2 and -(a,c) in +where t1_a<2 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 1 2 40 2 explain select * from t1 -where a<2 and -(a,c) in +where t1_a<2 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where a<2 and -(a,c) in +where t1_a<2 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -686,7 +690,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null" + "attached_condition": "t1.t1_a < 2 and t1.t1_a is not null and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -694,8 +698,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -708,7 +712,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 2" + "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2" } } } @@ -718,53 +722,53 @@ EXPLAIN } # extracted and formula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where a>2 and a<5 and -(a,c) in +where t1_a>2 and t1_a<5 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 select * from t1 -where a>2 and a<5 and -(a,c) in +where t1_a>2 and t1_a<5 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 3 2 23 1 explain select * from t1 -where a>2 and a<5 and -(a,c) in +where t1_a>2 and t1_a<5 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where a>2 and a<5 and -(a,c) in +where t1_a>2 and t1_a<5 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -776,7 +780,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null" + "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" }, "table": { "table_name": "<subquery2>", @@ -784,8 +788,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -798,7 +802,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5" + "attached_condition": "t2.t2_e < 5 and t2.t2_e > 2 and t2.t2_e < 5" } } } @@ -808,55 +812,55 @@ EXPLAIN } # extracted or formula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where (a<2 or a>=4) and -(a,c) in +where (t1_a<2 or t1_a>=4) and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 4 2 24 4 1 2 40 2 select * from t1 -where (a<2 or a>=4) and -(a,c) in +where (t1_a<2 or t1_a>=4) and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 4 2 24 4 1 2 40 2 explain select * from t1 -where (a<2 or a>=4) and -(a,c) in +where (t1_a<2 or t1_a>=4) and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where (a<2 or a>=4) and -(a,c) in +where (t1_a<2 or t1_a>=4) and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -868,7 +872,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null" + "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" }, "table": { "table_name": "<subquery2>", @@ -876,8 +880,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -890,7 +894,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)" + "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e >= 4)" } } } @@ -900,51 +904,51 @@ EXPLAIN } # extracted and-or formula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; -a b c d +t1_a t1_b t1_c t1_d 1 4 35 3 select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; -a b c d +t1_a t1_b t1_c t1_d 1 4 35 3 explain select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; EXPLAIN @@ -956,7 +960,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -964,8 +968,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -978,7 +982,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3" } } } @@ -988,51 +992,51 @@ EXPLAIN } # extracted and-or formula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; -a b c d +t1_a t1_b t1_c t1_d 1 4 35 3 select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; -a b c d +t1_a t1_b t1_c t1_d 1 4 35 3 explain select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((a<2 or a=5) and b>3) and -(a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e,f +where t2_e<5 +group by t2_e,t2_f ) ; EXPLAIN @@ -1044,7 +1048,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -1052,8 +1056,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1066,7 +1070,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3" + "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3" } } } @@ -1076,51 +1080,51 @@ EXPLAIN } # conjunctive subformula : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where ((b<3 or d>2) and a<2) and -(a,b,c) in +where ((t1_b<3 or t1_d>2) and t1_a<2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 2 40 2 select * from t1 -where ((b<3 or d>2) and a<2) and -(a,b,c) in +where ((t1_b<3 or t1_d>2) and t1_a<2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 2 40 2 explain select * from t1 -where ((b<3 or d>2) and a<2) and -(a,b,c) in +where ((t1_b<3 or t1_d>2) and t1_a<2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((b<3 or d>2) and a<2) and -(a,b,c) in +where ((t1_b<3 or t1_d>2) and t1_a<2) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -1132,7 +1136,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -1140,8 +1144,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1154,7 +1158,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 2" + "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2" } } } @@ -1164,51 +1168,51 @@ EXPLAIN } # using equalities : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where d=1 and a=d and -(a,c) in +where t1_d=1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 select * from t1 -where d=1 and a=d and -(a,c) in +where t1_d=1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 3 40 1 explain select * from t1 -where d=1 and a=d and -(a,c) in +where t1_d=1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 const,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where explain format=json select * from t1 -where d=1 and a=d and -(a,c) in +where t1_d=1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -1220,7 +1224,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null" + "attached_condition": "t1.t1_a = 1 and t1.t1_d = 1 and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -1228,8 +1232,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["const", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["const", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1241,7 +1245,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e = 1" + "attached_condition": "t2.t2_e = 1" } } } @@ -1250,51 +1254,51 @@ EXPLAIN } # using equality : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where d>1 and a=d and -(a,c) in +where t1_d>1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 select * from t1 -where d>1 and a=d and -(a,c) in +where t1_d>1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 4 2 24 4 explain select * from t1 -where d>1 and a=d and -(a,c) in +where t1_d>1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where d>1 and a=d and -(a,c) in +where t1_d>1 and t1_a=t1_d and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -1306,7 +1310,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null" + "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" }, "table": { "table_name": "<subquery2>", @@ -1314,8 +1318,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1328,7 +1332,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e > 1" + "attached_condition": "t2.t2_e < 5 and t2.t2_e > 1" } } } @@ -1338,51 +1342,51 @@ EXPLAIN } # using view in subquery definition : pushing into WHERE set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1 -where a<3 and -(a,c) in +where t1_a<3 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; -a b c d +t1_a t1_b t1_c t1_d 2 1 15 4 select * from t1 -where a<3 and -(a,c) in +where t1_a<3 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; -a b c d +t1_a t1_b t1_c t1_d 2 1 15 4 explain select * from t1 -where a<3 and -(a,c) in +where t1_a<3 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 12 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.t1_a,test.t1.t1_c 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 8 Using where; Using temporary explain format=json select * from t1 -where a<3 and -(a,c) in +where t1_a<3 and +(t1_a,t1_c) in ( -select x,max(y) +select v1_x,max(v1_y) from v1 -where x>1 -group by x +where v1_x>1 +group by v1_x ) ; EXPLAIN @@ -1394,7 +1398,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null" + "attached_condition": "t1.t1_a < 3 and t1.t1_a is not null and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -1402,8 +1406,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["x", "max(y)"], - "ref": ["test.t1.a", "test.t1.c"], + "used_key_parts": ["v1_x", "max(v1_y)"], + "ref": ["test.t1.t1_a", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1416,7 +1420,7 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3" + "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3 and t3.t3_x < 3" } } } @@ -1426,54 +1430,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 e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d x y +t1_a t1_b t1_c t1_d v1_x v1_y 1 3 40 1 1 35 1 2 40 2 1 35 select * from t1,v1 -where t1.a=v1.x and v1.x<2 and v1.y>30 and -(t1.a,t1.c) in +where t1_a=v1_x and v1_x<2 and v1_y>30 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d x y +t1_a t1_b t1_c t1_d v1_x v1_y 1 3 40 1 1 35 1 2 40 2 1 35 explain select * from t1,v1 -where t1.a=v1.x and v1.x<2 and v1.y>30 and -(t1.a,t1.c) in +where t1_a=v1_x and v1_x<2 and v1_y>30 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by 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.x,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t3.t3_x,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1,v1 -where t1.a=v1.x and v1.x<2 and v1.y>30 and -(t1.a,t1.c) in +where t1_a=v1_x and v1_x<2 and v1_y>30 and +(t1_a,t1_c) in ( -select e,max(g) +select t2_e,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -1485,7 +1489,7 @@ EXPLAIN "access_type": "ALL", "rows": 8, "filtered": 100, - "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null" + "attached_condition": "t3.t3_x < 2 and t3.t3_y > 30 and t3.t3_x <= 3 and t3.t3_x is not null" }, "block-nl-join": { "table": { @@ -1497,7 +1501,7 @@ EXPLAIN "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", - "attached_condition": "t1.a = t3.x and t1.c is not null" + "attached_condition": "t1.t1_a = t3.t3_x and t1.t1_c is not null" }, "table": { "table_name": "<subquery2>", @@ -1505,8 +1509,8 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "8", - "used_key_parts": ["e", "max(g)"], - "ref": ["test.t3.x", "test.t1.c"], + "used_key_parts": ["t2_e", "max(t2_g)"], + "ref": ["test.t3.t3_x", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { @@ -1519,7 +1523,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e <= 3" + "attached_condition": "t2.t2_e < 5 and t2.t2_e <= 3" } } } @@ -1530,51 +1534,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 ((b<3 or b=4) and a<3) and -(a,b,c) in +where ((t1_b<3 or t1_b=4) and t1_a<3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 2 40 2 select * from t1 -where ((b<3 or b=4) and a<3) and -(a,b,c) in +where ((t1_b<3 or t1_b=4) and t1_a<3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; -a b c d +t1_a t1_b t1_c t1_d 1 2 40 2 explain select * from t1 -where ((b<3 or b=4) and a<3) and -(a,b,c) in +where ((t1_b<3 or t1_b=4) and t1_a<3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by 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.a,test.t1.b,test.t1.c 1 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 12 test.t1.t1_a,test.t1.t1_b,test.t1.t1_c 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 12 Using where; Using temporary explain format=json select * from t1 -where ((b<3 or b=4) and a<3) and -(a,b,c) in +where ((t1_b<3 or t1_b=4) and t1_a<3) and +(t1_a,t1_b,t1_c) in ( -select e,f,max(g) +select t2_e,t2_f,max(t2_g) from t2 -where e<5 -group by e +where t2_e<5 +group by t2_e ) ; EXPLAIN @@ -1586,7 +1590,7 @@ EXPLAIN "access_type": "ALL", "rows": 12, "filtered": 100, - "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" + "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" }, "table": { "table_name": "<subquery2>", @@ -1594,22 +1598,22 @@ EXPLAIN "possible_keys": ["distinct_key"], "key": "distinct_key", "key_length": "12", - "used_key_parts": ["e", "f", "max(g)"], - "ref": ["test.t1.a", "test.t1.b", "test.t1.c"], + "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"], + "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"], "rows": 1, "filtered": 100, "materialized": { "unique": 1, "query_block": { "select_id": 2, - "having_condition": "t2.f < 3 or t2.f = 4", + "having_condition": "t2.t2_f < 3 or t2.t2_f = 4", "temporary_table": { "table": { "table_name": "t2", "access_type": "ALL", "rows": 12, "filtered": 100, - "attached_condition": "t2.e < 5 and t2.e < 3" + "attached_condition": "t2.t2_e < 5 and t2.t2_e < 3" } } } diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test index a022491..a40ba1a 100644 --- a/mysql-test/t/in_subq_cond_pushdown.test +++ b/mysql-test/t/in_subq_cond_pushdown.test @@ -1,8 +1,8 @@ let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_subquery=off' for; -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); +create table t1 (t1_a int, t1_b int, t1_c int, t1_d int); +create table t2 (t2_e int, t2_f int, t2_g int); +create table t3 (t3_x int, t3_y int); insert into t1 values (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4), @@ -20,14 +20,14 @@ insert into t3 values create view v1 as ( - select * from t3 where x<=3 + select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3 ); --echo # conjunctive subformula : pushing into HAVING let $query= select * from t1 -where c<25 and - (a,c) in (select e,max(g) from t2 where e<5 group by 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; @@ -37,13 +37,13 @@ eval explain format=json $query; --echo # extracted and formula : pushing into HAVING let $query= select * from t1 -where c>55 and b<4 and - (a,b,c) in +where t1_c>55 and t1_b<4 and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -55,13 +55,13 @@ eval explain format=json $query; --echo # extracted or formula : pushing into HAVING let $query= select * from t1 -where (c>60 or c<25) and - (a,b,c) in +where (t1_c>60 or t1_c<25) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -73,13 +73,13 @@ eval explain format=json $query; --echo # extracted and-or formula : pushing into HAVING let $query= select * from t1 -where ((c>60 or c<25) and b>2) and - (a,b,c) in +where ((t1_c>60 or t1_c<25) and t1_b>2) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -91,13 +91,13 @@ eval explain format=json $query; --echo # conjunctive subformula : pushing into HAVING let $query= select * from t1 -where ((a<2 or d>2) and b>3) and - (a,b,c) in +where ((t1_a<2 or t1_d>3) and t1_b>1) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -109,13 +109,13 @@ eval explain format=json $query; --echo # using view in subquery definition : pushing into HAVING let $query= select * from t1 -where c>20 and - (a,c) in +where t1_c>20 and + (t1_a,t1_c) in ( - select x,max(y) + select v1_x,max(v1_y) from v1 - where x>1 - group by x + where v1_x>1 + group by v1_x ) ; @@ -127,13 +127,13 @@ eval explain format=json $query; --echo # using equality : pushing into WHERE let $query= select * from t1,v1 -where c>20 and c=y and - (a,c) in +where t1_c>20 and t1_c=v1_y and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -145,13 +145,13 @@ eval explain format=json $query; --echo # conjunctive subformula : pushing into WHERE let $query= select * from t1 -where a<2 and - (a,c) in +where t1_a<2 and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -163,13 +163,13 @@ eval explain format=json $query; --echo # extracted and formula : pushing into WHERE let $query= select * from t1 -where a>2 and a<5 and - (a,c) in +where t1_a>2 and t1_a<5 and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -181,13 +181,13 @@ eval explain format=json $query; --echo # extracted or formula : pushing into WHERE let $query= select * from t1 -where (a<2 or a>=4) and - (a,c) in +where (t1_a<2 or t1_a>=4) and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -199,13 +199,13 @@ eval explain format=json $query; --echo # extracted and-or formula : pushing into WHERE let $query= select * from t1 -where ((a<2 or a=5) and b>3) and - (a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e,f + where t2_e<5 + group by t2_e,t2_f ) ; @@ -217,13 +217,13 @@ eval explain format=json $query; --echo # extracted and-or formula : pushing into WHERE let $query= select * from t1 -where ((a<2 or a=5) and b>3) and - (a,b,c) in +where ((t1_a<2 or t1_a=5) and t1_b>3) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e,f + where t2_e<5 + group by t2_e,t2_f ) ; @@ -235,13 +235,13 @@ eval explain format=json $query; --echo # conjunctive subformula : pushing into WHERE let $query= select * from t1 -where ((b<3 or d>2) and a<2) and - (a,b,c) in +where ((t1_b<3 or t1_d>2) and t1_a<2) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -253,13 +253,13 @@ eval explain format=json $query; --echo # using equalities : pushing into WHERE let $query= select * from t1 -where d=1 and a=d and - (a,c) in +where t1_d=1 and t1_a=t1_d and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -271,13 +271,13 @@ eval explain format=json $query; --echo # using equality : pushing into WHERE let $query= select * from t1 -where d>1 and a=d and - (a,c) in +where t1_d>1 and t1_a=t1_d and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -289,13 +289,13 @@ eval explain format=json $query; --echo # using view in subquery definition : pushing into WHERE let $query= select * from t1 -where a<3 and - (a,c) in +where t1_a<3 and + (t1_a,t1_c) in ( - select x,max(y) + select v1_x,max(v1_y) from v1 - where x>1 - group by x + where v1_x>1 + group by v1_x ) ; @@ -307,13 +307,13 @@ eval explain format=json $query; --echo # using equality : pushing into WHERE let $query= select * from t1,v1 -where t1.a=v1.x and v1.x<2 and v1.y>30 and - (t1.a,t1.c) in +where t1_a=v1_x and v1_x<2 and v1_y>30 and + (t1_a,t1_c) in ( - select e,max(g) + select t2_e,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; @@ -326,13 +326,13 @@ eval explain format=json $query; --echo # extracted or formula : pushing into HAVING let $query= select * from t1 -where ((b<3 or b=4) and a<3) and - (a,b,c) in +where ((t1_b<3 or t1_b=4) and t1_a<3) and + (t1_a,t1_b,t1_c) in ( - select e,f,max(g) + select t2_e,t2_f,max(t2_g) from t2 - where e<5 - group by e + where t2_e<5 + group by t2_e ) ; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index c3a392c..49747e4 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -6495,7 +6495,7 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd, 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 select + @param sel the select of the IN subquery predicate @param fields fields of the left part of the IN subquery predicate @details @@ -6568,7 +6568,7 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd, right_part and delete cond_where from the extr. 5. Transforms extr so it can be pushed into the HAVING clause of the right_part @note - This method is similar with pushdown_cond_for_derived + This method is similar with pushdown_cond_for_derived() @retval TRUE if an error occurs @retval FALSE otherwise @@ -6591,10 +6591,9 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) /* Create the list of In_subq_field items for this IN subquery: it consists of the pairs of fields from the left part of the IN subquery - 'left_part' and the respective fields from the right part of the - IN subquery (fields from the projections list of the select of the right - part of the IN subquery 'right_part' that stay on the same places in - the list of projections as the fields from the left_part). + 'left_part' and the respective fields from the select of the right part of + the IN subquery 'right_part' (fields that stay on the same places as fields + of left_part in the projection list of right_part). */ comparable_fields.empty(); List_iterator_fast<Item> it(sel->join->fields_list); @@ -6611,7 +6610,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) } /* - Build the new condition from the cond that can be pushed into sel + Build the new condition from cond that can be pushed into sel */ Item *extracted_cond; cond->check_pushable_cond(0, this); @@ -6658,7 +6657,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) } /* - Checks what can be pushed into the WHERE clause of the sel from the + Checks what can be pushed into the WHERE clause of sel from the extracted condition */ Item *cond_over_grouping_fields; @@ -6670,7 +6669,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) /* Transforms the references to the left_part fields so they can be pushed - into the sel of the WHERE clause. + into sel of the WHERE clause. */ if (cond_over_grouping_fields) cond_over_grouping_fields= @@ -6681,7 +6680,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) if (cond_over_grouping_fields) { /* - Removes from extracted_cond all parts that can be pushed into the WHERE clause + Removes from extracted_cond all parts that can be pushed into the + WHERE clause */ extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond); @@ -6693,8 +6693,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond) goto exit; } /* - Transforms the references to the left_part fields so they can be pushed into - the sel of the HAVING clause + Transforms the references to the left_part fields so the transformed + condition can be pushed into sel of the HAVING clause */ extracted_cond= extracted_cond->transform(thd, &Item::in_subq_field_transformer_for_having,