- commits - lists.mariadb.org

[Commits] 48d155b: MDEV-12387 Push conditions into materialized subqueries
by galina.shalygina＠mariadb.com 25 Apr '18

25 Apr '18

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,

1 0

[Commits] 92d2a8a3d7b: MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
by varunraiko1803＠gmail.com 25 Apr '18

25 Apr '18

revision-id: 92d2a8a3d7b2b1530b53fe2a2d1d5ac3e6506d34 (mariadb-10.2.5-613-g92d2a8a3d7b) parent(s): f033fbd9f2366619c52186a1a902066495539141 author: Varun Gupta committer: Varun Gupta timestamp: 2018-04-25 19:43:25 +0530 message: MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' failed in compare_order_elements function The issue here is the function compare_order_lists() is called for the order by list of the window functions so that those window function that can be computed together are adjacent. So in the function compare_order_list we iterate over all the elements in the order list of the two functions and compare the items in their order by clause. The function compare_order_elements() is called for each item in the order by clause. This function assumes that all the items that are in the order by list would be of the type Item::FIELD_ITEM. The case we have is that we have constants in the order by clause. We should ignore the constant and only compare items of the type Item::FIELD_ITEM in compare_order_elements() --- mysql-test/r/win.result | 12 ++++++++++++ mysql-test/t/win.test | 9 +++++++++ sql/sql_window.cc | 16 ++++++++++++++++ 3 files changed, 37 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index e3cb40e8343..8c6e3d79e80 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3299,3 +3299,15 @@ ROW_NUMBER() OVER() i SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0; ROW_NUMBER() OVER() i DROP TABLE t1; +# +# MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' +# failed in compare_order_elements function +# +CREATE TABLE t1 (a1 int); +insert into t1 values (1),(2),(3); +SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1; +rank() OVER (ORDER BY 1) ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) +1 2 +1 1 +1 3 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 95ffb6d9909..6422ebc5d4b 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2067,3 +2067,12 @@ SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0; SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0; DROP TABLE t1; +--echo # +--echo # MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM' +--echo # failed in compare_order_elements function +--echo # + +CREATE TABLE t1 (a1 int); +insert into t1 values (1),(2),(3); +SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1; +drop table t1; diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 4e1e64365ae..e556d75442f 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -342,6 +342,22 @@ int compare_order_lists(SQL_I_List<ORDER> *part_list1, for ( ; elem1 && elem2; elem1= elem1->next, elem2= elem2->next) { int cmp; + // remove all constants as we don't need them for comparision + while(elem1 && ((*elem1->item)->real_item())->const_item()) + { + elem1= elem1->next; + continue; + } + + while(elem2 && ((*elem2->item)->real_item())->const_item()) + { + elem2= elem2->next; + continue; + } + + if (!elem1 || !elem2) + break; + if ((cmp= compare_order_elements(elem1, elem2))) return cmp; }

1 0

[Commits] 99c40e0628d: MDEV-13727 rpl.rpl_concurrency_error failed
by andrei.elkin＠pp.inet.fi 25 Apr '18

25 Apr '18

revision-id: 99c40e0628d1157a835d0225d2810637774c9174 (mariadb-10.3.6-26-g99c40e0628d) parent(s): 38c799c9a5e5aadd3f4df157a4151dd1f71d5bcb author: Andrei Elkin committer: Andrei Elkin timestamp: 2018-04-25 14:16:38 +0300 message: MDEV-13727 rpl.rpl_concurrency_error failed The test actually revealed a flaw in MDEV-8305 which inadvertently enrolled the trigger and stored function into slow query reporting which was aimed exclusively to the stored procedure. Specifically to the test, a query on the master was logged with a timestamp of the query's top-level statement but its (post update) trigger computed one more (later) timestamp which got inserted into another table. Master-vs-slave whole seconds timestamp discrepancy became evident thanks to different execution time of the trigger combined with the fact of the logged with micro-second fractional part master timestamp was truncated on the slave. On master when the fractional part was close to 1 the trigger execution added up its own latency to overflow to next second value. That's how the master timestamp surprisingly turned out to bigger than the slave's one. Fixed with slight refactoring of MDEV-8305 to reuse always existing timestamp resetting mechanism engaged prior to a stored procedure's next statement execution. Now the resetter is augmented to also deal with THD::start_utime et al. --- mysql-test/main/func_time.result | 35 +++++++++++++++++++ mysql-test/main/func_time.test | 75 ++++++++++++++++++++++++++++++++++++++++ sql/sp_head.cc | 22 +----------- 3 files changed, 111 insertions(+), 21 deletions(-) diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index 2772f850ce9..4a999280159 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -3484,3 +3484,38 @@ t1 CREATE TABLE `t1` ( `c5` varchar(100) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +CREATE TABLE t_ts (a timestamp(6)); +CREATE TABLE t_trig (a timestamp(6)); +CREATE TABLE t1 (a timestamp(6)); +CREATE TABLE t2 (a timestamp(6)); +CREATE FUNCTION fn_sleep_before_now() returns int +BEGIN +INSERT INTO t_ts SET a= current_timestamp(6); +RETURN 0; +END// +CREATE TRIGGER trg_insert_t_ts AFTER INSERT ON t_ts FOR EACH ROW +BEGIN +INSERT into t_trig set a= current_timestamp(6); +END// +CREATE PROCEDURE sp() +BEGIN +INSERT INTO t1 SET a=current_timestamp(6); +INSERT INTO t2 SET a=current_timestamp(6); +END// +SET @sav_slow_query_log= @@session.slow_query_log; +SET @@session.slow_query_log= ON; +SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func; +SELECT a FROM t_ts LIMIT 1 into @ts_func; +SELECT a FROM t_trig LIMIT 1 into @ts_trig; +DELETE FROM t_ts; +DELETE FROM t_trig; +SET @@session.slow_query_log= OFF; +SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts; +SELECT a FROM t_ts LIMIT 1 into @ts_func; +SELECT a FROM t_trig LIMIT 1 into @ts_trig; +CALL sp(); +SET @@session.slow_query_log= @sav_slow_query_log; +DROP PROCEDURE sp; +DROP FUNCTION fn_sleep_before_now; +DROP TRIGGER trg_insert_t_ts; +DROP TABLE t_ts, t_trig, t1, t2; diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index 5417cb20a92..9f44761d938 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -2057,3 +2057,78 @@ EXECUTE IMMEDIATE USING NULL, '10', 10, 10.0, 10e0, TIME'10:20:30'; SHOW CREATE TABLE t1; DROP TABLE t1; + + +############ +# MDEV-13727 +# Current timestamp functions inside stored functions must return the +# value of the top-level statement's timestamp (its start time). +# This must hold regardless of @@slow_query_log option. +# In contrast the current timestamp of stored procedure +# monotonically grows from statement to statement. + +CREATE TABLE t_ts (a timestamp(6)); +CREATE TABLE t_trig (a timestamp(6)); +CREATE TABLE t1 (a timestamp(6)); +CREATE TABLE t2 (a timestamp(6)); +delimiter //; +CREATE FUNCTION fn_sleep_before_now() returns int +BEGIN + INSERT INTO t_ts SET a= current_timestamp(6); + RETURN 0; +END// +CREATE TRIGGER trg_insert_t_ts AFTER INSERT ON t_ts FOR EACH ROW +BEGIN + INSERT into t_trig set a= current_timestamp(6); +END// +CREATE PROCEDURE sp() +BEGIN + INSERT INTO t1 SET a=current_timestamp(6); + INSERT INTO t2 SET a=current_timestamp(6); +END// +delimiter ;// + +SET @sav_slow_query_log= @@session.slow_query_log; + +# @@slow_query_log ON check +SET @@session.slow_query_log= ON; +SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @ts_func; + +SELECT a FROM t_ts LIMIT 1 into @ts_func; +SELECT a FROM t_trig LIMIT 1 into @ts_trig; +if (!`SELECT @ts_cur = @ts_func AND @ts_func = @ts_trig`) +{ + SELECT @ts_cur, @ts_func, @ts_trig; + --die Error: timestamps must be equal but they diverge +} +DELETE FROM t_ts; +DELETE FROM t_trig; + +# @@slow_query_log OFF check +SET @@session.slow_query_log= OFF; +SELECT current_timestamp(6),fn_sleep_before_now() INTO @ts_cur, @func_ts; +SELECT a FROM t_ts LIMIT 1 into @ts_func; +SELECT a FROM t_trig LIMIT 1 into @ts_trig; +if (!`SELECT @ts_cur = @ts_func AND @ts_func = @ts_trig`) +{ + SELECT @ts_cur, @ts_func, @ts_trig; + --die Error: timestamps must be equal but they diverge +} + +CALL sp(); +if (!`SELECT t2.a > t1.a FROM t1,t2`) +{ + SELECT t1.a, t2.a FROM t1,t2; + --die Error: timestamps must be growing monotonically +} + +# Cleanup +SET @@session.slow_query_log= @sav_slow_query_log; +DROP PROCEDURE sp; +DROP FUNCTION fn_sleep_before_now; +DROP TRIGGER trg_insert_t_ts; +DROP TABLE t_ts, t_trig, t1, t2; + +# +# End of MDEV-13727 +################### diff --git a/sql/sp_head.cc b/sql/sp_head.cc index f7847bae89d..ac3e490e598 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -66,7 +66,7 @@ extern "C" uchar *sp_table_key(const uchar *ptr, size_t *plen, my_bool first); static void reset_start_time_for_sp(THD *thd) { if (!thd->in_sub_stmt) - thd->set_start_time(); + thd->set_time(); } @@ -3459,9 +3459,7 @@ int sp_instr_stmt::execute(THD *thd, uint *nextp) { int res; - bool save_enable_slow_log; const CSET_STRING query_backup= thd->query_string; - QUERY_START_TIME_INFO time_info; Sub_statement_state backup_state; DBUG_ENTER("sp_instr_stmt::execute"); DBUG_PRINT("info", ("command: %d", m_lex_keeper.sql_command())); @@ -3471,15 +3469,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp) thd->profiling.set_query_source(m_query.str, m_query.length); #endif - if ((save_enable_slow_log= thd->enable_slow_log)) - { - /* - Save start time info for the CALL statement and overwrite it with the - current time for log_slow_statement() to log the individual query timing. - */ - thd->backup_query_start_time(&time_info); - thd->set_time(); - } thd->store_slow_query_state(&backup_state); if (!(res= alloc_query(thd, m_query.str, m_query.length)) && @@ -3515,12 +3504,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp) if (log_slow) log_slow_statement(thd); - /* - Restore enable_slow_log, that can be changed by a admin or call - command - */ - thd->enable_slow_log= save_enable_slow_log; - /* Add the number of rows to thd for the 'call' statistics */ thd->add_slow_query_state(&backup_state); } @@ -3543,9 +3526,6 @@ sp_instr_stmt::execute(THD *thd, uint *nextp) thd->get_stmt_da()->reset_diagnostics_area(); } } - /* Restore the original query start time */ - if (thd->enable_slow_log) - thd->restore_query_start_time(&time_info); DBUG_RETURN(res || thd->is_error()); }

1 0

[Commits] cb07364: MDEV-15035 Wrong results when calling a stored procedure
by IgorBabaev 25 Apr '18

25 Apr '18

revision-id: cb07364f1d1ec47febbcaf2397e3ed2b4c3dc06b (mariadb-5.5.59-59-gcb07364) parent(s): 5e61e1716e763315009318081fba5994b8910242 author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-24 15:51:49 -0700 message: MDEV-15035 Wrong results when calling a stored procedure multiple times with different arguments. If the ON expression of an outer join is an OR formula with one of the disjunct being a constant formula then the expression cannot be null-rejected if the constant formula is true. Otherwise it can be null-rejected and if so the outer join can be converted into inner join. This optimization was added in the patch for mdev-4817. Yet the code had a defect: if the query was used in a stored procedure with parameters and the constant item contained some of them then the value of this constant item depended on the values of the parameters. With some parameters it may be true, for others not. The validity of conversion to inner join is checked only once and it happens only for the first call of procedure. So if the parameters in the first call allowed the conversion it was done and next calls used the transformed query though there could be calls whose parameters made the conversion invalid. Fixed by cheking whether the constant disjunct in the ON expression originally contained an SP parameter. If so the expression is not considered as null-rejected. For this check a new item's attribute was intruduced: Item::with_param. It is calculated for each item by fix fields() functions. Also moved the call of optimize_constant_subqueries() in JOIN::optimize after the call of simplify_joins(). The reason for this is that after the optimization introduced by the patch for mdev-4817 simplify_joins() can use the results of execution of non-expensive constant subqueries and this is not valid. --- mysql-test/r/sp-innodb.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/sp-innodb.test | 42 ++++++++++++++++++++++++++++++++++++++++++ sql/item.cc | 7 +++++++ sql/item.h | 1 + sql/item_cmpfunc.cc | 43 +++++++++++++++++++++++++++++++------------ sql/item_func.cc | 1 + sql/item_func.h | 7 +++++++ sql/item_row.cc | 1 + sql/item_sum.cc | 3 +++ sql/sql_select.cc | 6 +++--- 10 files changed, 130 insertions(+), 15 deletions(-) diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result index b5fe920..9daf2c4 100644 --- a/mysql-test/r/sp-innodb.result +++ b/mysql-test/r/sp-innodb.result @@ -138,3 +138,37 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; # # BUG 16041903: End of test case # +# +# MDEV-15035: SP using query with outer join and a parameter +# in ON expression +# +CREATE TABLE t1 ( +id int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 ( +id int NOT NULL, +id_foo int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1, 1); +DROP PROCEDURE IF EXISTS test_proc; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN +SELECT DISTINCT f.id +FROM t1 f +LEFT OUTER JOIN t2 b ON b.id_foo = f.id +WHERE (param <> 0 OR b.id IS NOT NULL); +END| +CALL test_proc(0); +id +1 +CALL test_proc(1); +id +1 +2 +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test index 2371516..e44a853 100644 --- a/mysql-test/t/sp-innodb.test +++ b/mysql-test/t/sp-innodb.test @@ -158,5 +158,47 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; --echo # BUG 16041903: End of test case --echo # +--echo # +--echo # MDEV-15035: SP using query with outer join and a parameter +--echo # in ON expression +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 ( + id int NOT NULL, + id_foo int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1, 1); + +--disable_warnings +DROP PROCEDURE IF EXISTS test_proc; +--enable_warnings + +DELIMITER |; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN + SELECT DISTINCT f.id + FROM t1 f + LEFT OUTER JOIN t2 b ON b.id_foo = f.id + WHERE (param <> 0 OR b.id IS NOT NULL); +END| +DELIMITER ;| + +CALL test_proc(0); +CALL test_proc(1); + +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/sql/item.cc b/sql/item.cc index 08a0061..c5c6df0 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -504,6 +504,7 @@ Item::Item(): in_rollup= 0; decimals= 0; max_length= 0; with_subselect= 0; + with_param= 0; cmp_context= IMPOSSIBLE_RESULT; /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES; @@ -550,6 +551,7 @@ Item::Item(THD *thd, Item *item): null_value(item->null_value), unsigned_flag(item->unsigned_flag), with_sum_func(item->with_sum_func), + with_param(item->with_param), with_field(item->with_field), fixed(item->fixed), is_autogenerated_name(item->is_autogenerated_name), @@ -1486,6 +1488,9 @@ bool Item_sp_variable::fix_fields(THD *thd, Item **) max_length= it->max_length; decimals= it->decimals; unsigned_flag= it->unsigned_flag; + with_param= 1; + if (thd->lex->current_select->master_unit()->item) + thd->lex->current_select->master_unit()->item->with_param= 1; fixed= 1; collation.set(it->collation.collation, it->collation.derivation); @@ -7234,6 +7239,7 @@ void Item_ref::set_properties() split_sum_func() doesn't try to change the reference. */ with_sum_func= (*ref)->with_sum_func; + with_param= (*ref)->with_param; with_field= (*ref)->with_field; unsigned_flag= (*ref)->unsigned_flag; fixed= 1; @@ -7681,6 +7687,7 @@ Item_cache_wrapper::Item_cache_wrapper(Item *item_arg) decimals= orig_item->decimals; collation.set(orig_item->collation); with_sum_func= orig_item->with_sum_func; + with_param= orig_item->with_param; with_field= orig_item->with_field; unsigned_flag= orig_item->unsigned_flag; name= item_arg->name; diff --git a/sql/item.h b/sql/item.h index 830f8bf..d756cf8 100644 --- a/sql/item.h +++ b/sql/item.h @@ -644,6 +644,7 @@ class Item { bool null_value; /* if item is null */ bool unsigned_flag; bool with_sum_func; /* True if item contains a sum func */ + bool with_param; /* True if contains an SP parameter */ /** True if any item except Item_sum_func contains a field. Set during parsing. */ diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 39f497e..6fb650b 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1546,6 +1546,7 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) } eval_not_null_tables(NULL); with_sum_func= args[0]->with_sum_func; + with_param= args[0]->with_param || args[1]->with_param; with_field= args[0]->with_field; if ((const_item_cache= args[0]->const_item())) { @@ -1587,6 +1588,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; with_field= with_field || args[1]->with_field; + with_param= args[0]->with_param || args[1]->with_param; used_tables_cache|= args[1]->used_tables(); const_item_cache&= args[1]->const_item(); fixed= 1; @@ -2108,6 +2110,7 @@ void Item_func_interval::fix_length_and_dec() used_tables_cache|= row->used_tables(); not_null_tables_cache= row->not_null_tables(); with_sum_func= with_sum_func || row->with_sum_func; + with_param= with_param || row->with_param; with_field= with_field || row->with_field; const_item_cache&= row->const_item(); } @@ -4335,6 +4338,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) List_iterator<Item> li(list); Item *item; uchar buff[sizeof(char*)]; // Max local vars in function + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; not_null_tables_cache= used_tables_cache= 0; const_item_cache= 1; @@ -4396,26 +4400,33 @@ Item_cond::fix_fields(THD *thd, Item **ref) (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition const_cond might evaluate to true (regardless of whether some tables were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } + if (thd->is_error()) + return TRUE; } else { @@ -4427,6 +4438,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) } with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; with_subselect|= item->has_subquery(); if (item->maybe_null) @@ -4443,30 +4455,36 @@ bool Item_cond::eval_not_null_tables(uchar *opt_arg) { Item *item; + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; List_iterator<Item> li(list); not_null_tables_cache= (table_map) 0; and_tables_cache= ~(table_map) 0; while ((item=li++)) { table_map tmp_table_map; - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition - some_cond_or might be true regardless of what tables are - NULL-complemented. + const_cond might evaluate to true (regardless of whether some tables + were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } } @@ -5118,6 +5136,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref) args[1]->fix_fields(thd, args + 1)) || args[1]->check_cols(1)) return TRUE; /* purecov: inspected */ with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func; + with_param=args[0]->with_param || args[1]->with_param; with_field= args[0]->with_field || args[1]->with_field; with_subselect= args[0]->has_subquery() || args[1]->has_subquery(); max_length= 1; diff --git a/sql/item_func.cc b/sql/item_func.cc index 9e4edfc..8b3c72dd3 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -222,6 +222,7 @@ Item_func::fix_fields(THD *thd, Item **ref) maybe_null=1; with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; used_tables_cache|= item->used_tables(); const_item_cache&= item->const_item(); diff --git a/sql/item_func.h b/sql/item_func.h index 5781822..3a609fc 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -83,6 +83,7 @@ class Item_func :public Item_result_field args= tmp_arg; args[0]= a; with_sum_func= a->with_sum_func; + with_param= a->with_param; with_field= a->with_field; } Item_func(Item *a,Item *b): @@ -91,6 +92,7 @@ class Item_func :public Item_result_field args= tmp_arg; args[0]= a; args[1]= b; with_sum_func= a->with_sum_func || b->with_sum_func; + with_param= a->with_param || b->with_param; with_field= a->with_field || b->with_field; } Item_func(Item *a,Item *b,Item *c): @@ -102,6 +104,7 @@ class Item_func :public Item_result_field arg_count= 3; args[0]= a; args[1]= b; args[2]= c; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func; + with_param= a->with_param || b->with_param || c->with_param; with_field= a->with_field || b->with_field || c->with_field; } } @@ -115,6 +118,8 @@ class Item_func :public Item_result_field args[0]= a; args[1]= b; args[2]= c; args[3]= d; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field; } @@ -128,6 +133,8 @@ class Item_func :public Item_result_field args[0]= a; args[1]= b; args[2]= c; args[3]= d; args[4]= e; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func || e->with_sum_func ; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param || e->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field || e->with_field; } diff --git a/sql/item_row.cc b/sql/item_row.cc index 9e81c05..9fe34dd 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -125,6 +125,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref) with_sum_func= with_sum_func || item->with_sum_func; with_field= with_field || item->with_field; with_subselect|= item->with_subselect; + with_param|= item->with_param; } fixed= 1; return FALSE; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 709c2b6..16334cd 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1164,6 +1164,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) return TRUE; set_if_bigger(decimals, args[i]->decimals); with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } result_field=0; max_length=float_length(decimals); @@ -1195,6 +1196,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) return TRUE; decimals=item->decimals; with_subselect= args[0]->with_subselect; + with_param= args[0]->with_param; switch (hybrid_type= item->result_type()) { case INT_RESULT: @@ -3430,6 +3432,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref) args[i]->check_cols(1)) return TRUE; with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } /* skip charset aggregation for order columns */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 90bb536..1e9f1c0 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1034,9 +1034,6 @@ JOIN::optimize() eval_select_list_used_tables(); - if (optimize_constant_subqueries()) - DBUG_RETURN(1); - table_count= select_lex->leaf_tables.elements; if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ @@ -1098,6 +1095,9 @@ JOIN::optimize() thd->restore_active_arena(arena, &backup); } + if (optimize_constant_subqueries()) + DBUG_RETURN(1); + if (setup_jtbm_semi_joins(this, join_list, &conds)) DBUG_RETURN(1);

2 1

[Commits] aff4dbc: Changed the test case for MDEV-15571
by IgorBabaev 24 Apr '18

24 Apr '18

revision-id: aff4dbce5d1e68376798b28e74b48133bb948ea3 (mariadb-10.2.14-61-gaff4dbc) parent(s): 619dc2b24f26aea29345dc3f3289bed406738025 author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-24 12:33:56 -0700 message: Changed the test case for MDEV-15571 It has been done to demonstrate that the fix of this bug is good for 10.3 as well. The previous test case is not good for this purpose because 10.2 and 10.3 use different rules for determining the types of recursive CTEs. --- mysql-test/r/cte_recursive.result | 5 ++++- mysql-test/t/cte_recursive.test | 7 ++++++- sql/sql_select.cc | 2 ++ 3 files changed, 12 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 6b2db35..70752c7 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3083,16 +3083,19 @@ set big_tables=default; # # MDEV-15571: using recursive cte with big_tables enabled # +create table t1 (a bigint); +insert into t1 values(1); set big_tables=1; with recursive qn as ( -select 1 as a from dual +select a from t1 union all select a*2000 from qn where a<10000000000000000000 ) select * from qn; ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' set big_tables=default; +drop table t1; # # MDEV-15556: using recursive cte with big_tables enabled # when recursive tables are accessed by key diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 50cb39a..332a64b 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2114,12 +2114,15 @@ set big_tables=default; --echo # MDEV-15571: using recursive cte with big_tables enabled --echo # +create table t1 (a bigint); +insert into t1 values(1); + set big_tables=1; --error ER_DATA_OUT_OF_RANGE with recursive qn as ( - select 1 as a from dual + select a from t1 union all select a*2000 from qn where a<10000000000000000000 ) @@ -2127,6 +2130,8 @@ select * from qn; set big_tables=default; +drop table t1; + --echo # --echo # MDEV-15556: using recursive cte with big_tables enabled --echo # when recursive tables are accessed by key diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6450eb0..db97596 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1304,6 +1304,8 @@ JOIN::optimize_inner() /* Convert all outer joins to inner joins if possible */ conds= simplify_joins(this, join_list, conds, TRUE, FALSE); + if (thd->is_error()) + DBUG_RETURN(1); if (select_lex->save_leaf_tables(thd)) DBUG_RETURN(1); build_bitmap_for_nested_joins(join_list, 0);

1 0

[Commits] db3a1ae: MDEV-15035 Wrong results when calling a stored procedure
by IgorBabaev 24 Apr '18

24 Apr '18

revision-id: db3a1ae5d6e2796f61e6c8eec3446a81780aeee3 (mariadb-5.5.59-59-gdb3a1ae) parent(s): 5e61e1716e763315009318081fba5994b8910242 author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-24 11:34:44 -0700 message: MDEV-15035 Wrong results when calling a stored procedure multiple times with different arguments. If the ON expression of an outer join is an OR formula with one of the disjunct being a constant formula then the expression cannot be null-rejected if the constant formula is true. Otherwise it can be null-rejected and if so the outer join can be converted into inner join. This optimization was added in the patch for mdev-4817. Yet the code had a defect: if the query was used in a stored procedure with parameters and the constant item contained some of them then the value of this constant item depended on the values of the parameters. With some parameters it may be true, for others not. The validity of conversion to inner join is checked only once and it happens only for the first call of procedure. So if the parameters in the first call allowed the conversion it was done and next calls used the transformed query though there could be calls whose parameters made the conversion invalid. Fixed by cheking whether the constant disjunct in the ON expression originally contained an SP parameter. If so the expression is not considered as null-rejected. For this check a new item's attribute was intruduced: Item::with_param. It is calculated for each item by fix fields() functions. --- mysql-test/r/sp-innodb.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/sp-innodb.test | 42 ++++++++++++++++++++++++++++++++++++++++++ sql/item.cc | 7 +++++++ sql/item.h | 1 + sql/item_cmpfunc.cc | 43 +++++++++++++++++++++++++++++++------------ sql/item_func.cc | 1 + sql/item_func.h | 7 +++++++ sql/item_row.cc | 1 + sql/item_sum.cc | 3 +++ 9 files changed, 127 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/sp-innodb.result b/mysql-test/r/sp-innodb.result index b5fe920..9daf2c4 100644 --- a/mysql-test/r/sp-innodb.result +++ b/mysql-test/r/sp-innodb.result @@ -138,3 +138,37 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; # # BUG 16041903: End of test case # +# +# MDEV-15035: SP using query with outer join and a parameter +# in ON expression +# +CREATE TABLE t1 ( +id int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 ( +id int NOT NULL, +id_foo int NOT NULL, +PRIMARY KEY (id) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1, 1); +DROP PROCEDURE IF EXISTS test_proc; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN +SELECT DISTINCT f.id +FROM t1 f +LEFT OUTER JOIN t2 b ON b.id_foo = f.id +WHERE (param <> 0 OR b.id IS NOT NULL); +END| +CALL test_proc(0); +id +1 +CALL test_proc(1); +id +1 +2 +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test index 2371516..e44a853 100644 --- a/mysql-test/t/sp-innodb.test +++ b/mysql-test/t/sp-innodb.test @@ -158,5 +158,47 @@ SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; --echo # BUG 16041903: End of test case --echo # +--echo # +--echo # MDEV-15035: SP using query with outer join and a parameter +--echo # in ON expression +--echo # + +CREATE TABLE t1 ( + id int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (2); + +CREATE TABLE t2 ( + id int NOT NULL, + id_foo int NOT NULL, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t2 VALUES (1, 1); + +--disable_warnings +DROP PROCEDURE IF EXISTS test_proc; +--enable_warnings + +DELIMITER |; +CREATE PROCEDURE test_proc(IN param int) +LANGUAGE SQL +READS SQL DATA +BEGIN + SELECT DISTINCT f.id + FROM t1 f + LEFT OUTER JOIN t2 b ON b.id_foo = f.id + WHERE (param <> 0 OR b.id IS NOT NULL); +END| +DELIMITER ;| + +CALL test_proc(0); +CALL test_proc(1); + +DROP PROCEDURE IF EXISTS test_proc; +DROP TABLE t1, t2; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/sql/item.cc b/sql/item.cc index 08a0061..c5c6df0 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -504,6 +504,7 @@ Item::Item(): in_rollup= 0; decimals= 0; max_length= 0; with_subselect= 0; + with_param= 0; cmp_context= IMPOSSIBLE_RESULT; /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES; @@ -550,6 +551,7 @@ Item::Item(THD *thd, Item *item): null_value(item->null_value), unsigned_flag(item->unsigned_flag), with_sum_func(item->with_sum_func), + with_param(item->with_param), with_field(item->with_field), fixed(item->fixed), is_autogenerated_name(item->is_autogenerated_name), @@ -1486,6 +1488,9 @@ bool Item_sp_variable::fix_fields(THD *thd, Item **) max_length= it->max_length; decimals= it->decimals; unsigned_flag= it->unsigned_flag; + with_param= 1; + if (thd->lex->current_select->master_unit()->item) + thd->lex->current_select->master_unit()->item->with_param= 1; fixed= 1; collation.set(it->collation.collation, it->collation.derivation); @@ -7234,6 +7239,7 @@ void Item_ref::set_properties() split_sum_func() doesn't try to change the reference. */ with_sum_func= (*ref)->with_sum_func; + with_param= (*ref)->with_param; with_field= (*ref)->with_field; unsigned_flag= (*ref)->unsigned_flag; fixed= 1; @@ -7681,6 +7687,7 @@ Item_cache_wrapper::Item_cache_wrapper(Item *item_arg) decimals= orig_item->decimals; collation.set(orig_item->collation); with_sum_func= orig_item->with_sum_func; + with_param= orig_item->with_param; with_field= orig_item->with_field; unsigned_flag= orig_item->unsigned_flag; name= item_arg->name; diff --git a/sql/item.h b/sql/item.h index 830f8bf..d756cf8 100644 --- a/sql/item.h +++ b/sql/item.h @@ -644,6 +644,7 @@ class Item { bool null_value; /* if item is null */ bool unsigned_flag; bool with_sum_func; /* True if item contains a sum func */ + bool with_param; /* True if contains an SP parameter */ /** True if any item except Item_sum_func contains a field. Set during parsing. */ diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 39f497e..6fb650b 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1546,6 +1546,7 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) } eval_not_null_tables(NULL); with_sum_func= args[0]->with_sum_func; + with_param= args[0]->with_param || args[1]->with_param; with_field= args[0]->with_field; if ((const_item_cache= args[0]->const_item())) { @@ -1587,6 +1588,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) with_subselect= 1; with_sum_func= with_sum_func || args[1]->with_sum_func; with_field= with_field || args[1]->with_field; + with_param= args[0]->with_param || args[1]->with_param; used_tables_cache|= args[1]->used_tables(); const_item_cache&= args[1]->const_item(); fixed= 1; @@ -2108,6 +2110,7 @@ void Item_func_interval::fix_length_and_dec() used_tables_cache|= row->used_tables(); not_null_tables_cache= row->not_null_tables(); with_sum_func= with_sum_func || row->with_sum_func; + with_param= with_param || row->with_param; with_field= with_field || row->with_field; const_item_cache&= row->const_item(); } @@ -4335,6 +4338,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) List_iterator<Item> li(list); Item *item; uchar buff[sizeof(char*)]; // Max local vars in function + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; not_null_tables_cache= used_tables_cache= 0; const_item_cache= 1; @@ -4396,26 +4400,33 @@ Item_cond::fix_fields(THD *thd, Item **ref) (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition const_cond might evaluate to true (regardless of whether some tables were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } + if (thd->is_error()) + return TRUE; } else { @@ -4427,6 +4438,7 @@ Item_cond::fix_fields(THD *thd, Item **ref) } with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; with_subselect|= item->has_subquery(); if (item->maybe_null) @@ -4443,30 +4455,36 @@ bool Item_cond::eval_not_null_tables(uchar *opt_arg) { Item *item; + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; List_iterator<Item> li(list); not_null_tables_cache= (table_map) 0; and_tables_cache= ~(table_map) 0; while ((item=li++)) { table_map tmp_table_map; - if (item->const_item()) + if (item->const_item() && !item->with_param && + !item->is_expensive() && !cond_has_datetime_is_null(item)) { - if (!item->is_expensive() && !cond_has_datetime_is_null(item) && - item->val_int() == 0) + if (item->val_int() == is_and_cond && top_level()) { /* - This is "... OR false_cond OR ..." + a. This is "... AND true_cond AND ..." + In this case, true_cond has no effect on cond_and->not_null_tables() + b. This is "... OR false_cond/null cond OR ..." In this case, false_cond has no effect on cond_or->not_null_tables() */ } else { /* - This is "... OR const_cond OR ..." + a. This is "... AND false_cond/null_cond AND ..." + The whole condition is FALSE/UNKNOWN. + b. This is "... OR const_cond OR ..." In this case, cond_or->not_null_tables()=0, because the condition - some_cond_or might be true regardless of what tables are - NULL-complemented. + const_cond might evaluate to true (regardless of whether some tables + were NULL-complemented). */ + not_null_tables_cache= (table_map) 0; and_tables_cache= (table_map) 0; } } @@ -5118,6 +5136,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref) args[1]->fix_fields(thd, args + 1)) || args[1]->check_cols(1)) return TRUE; /* purecov: inspected */ with_sum_func=args[0]->with_sum_func || args[1]->with_sum_func; + with_param=args[0]->with_param || args[1]->with_param; with_field= args[0]->with_field || args[1]->with_field; with_subselect= args[0]->has_subquery() || args[1]->has_subquery(); max_length= 1; diff --git a/sql/item_func.cc b/sql/item_func.cc index 9e4edfc..8b3c72dd3 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -222,6 +222,7 @@ Item_func::fix_fields(THD *thd, Item **ref) maybe_null=1; with_sum_func= with_sum_func || item->with_sum_func; + with_param= with_param || item->with_param; with_field= with_field || item->with_field; used_tables_cache|= item->used_tables(); const_item_cache&= item->const_item(); diff --git a/sql/item_func.h b/sql/item_func.h index 5781822..3a609fc 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -83,6 +83,7 @@ class Item_func :public Item_result_field args= tmp_arg; args[0]= a; with_sum_func= a->with_sum_func; + with_param= a->with_param; with_field= a->with_field; } Item_func(Item *a,Item *b): @@ -91,6 +92,7 @@ class Item_func :public Item_result_field args= tmp_arg; args[0]= a; args[1]= b; with_sum_func= a->with_sum_func || b->with_sum_func; + with_param= a->with_param || b->with_param; with_field= a->with_field || b->with_field; } Item_func(Item *a,Item *b,Item *c): @@ -102,6 +104,7 @@ class Item_func :public Item_result_field arg_count= 3; args[0]= a; args[1]= b; args[2]= c; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func; + with_param= a->with_param || b->with_param || c->with_param; with_field= a->with_field || b->with_field || c->with_field; } } @@ -115,6 +118,8 @@ class Item_func :public Item_result_field args[0]= a; args[1]= b; args[2]= c; args[3]= d; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field; } @@ -128,6 +133,8 @@ class Item_func :public Item_result_field args[0]= a; args[1]= b; args[2]= c; args[3]= d; args[4]= e; with_sum_func= a->with_sum_func || b->with_sum_func || c->with_sum_func || d->with_sum_func || e->with_sum_func ; + with_param= a->with_param || b->with_param || + c->with_param || d->with_param || e->with_param; with_field= a->with_field || b->with_field || c->with_field || d->with_field || e->with_field; } diff --git a/sql/item_row.cc b/sql/item_row.cc index 9e81c05..9fe34dd 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -125,6 +125,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref) with_sum_func= with_sum_func || item->with_sum_func; with_field= with_field || item->with_field; with_subselect|= item->with_subselect; + with_param|= item->with_param; } fixed= 1; return FALSE; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 709c2b6..16334cd 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -1164,6 +1164,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) return TRUE; set_if_bigger(decimals, args[i]->decimals); with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } result_field=0; max_length=float_length(decimals); @@ -1195,6 +1196,7 @@ Item_sum_hybrid::fix_fields(THD *thd, Item **ref) return TRUE; decimals=item->decimals; with_subselect= args[0]->with_subselect; + with_param= args[0]->with_param; switch (hybrid_type= item->result_type()) { case INT_RESULT: @@ -3430,6 +3432,7 @@ Item_func_group_concat::fix_fields(THD *thd, Item **ref) args[i]->check_cols(1)) return TRUE; with_subselect|= args[i]->with_subselect; + with_param|= args[i]->with_param; } /* skip charset aggregation for order columns */

1 0

[Commits] 64ca3812839: buildbot finding
by Oleksandr Byelkin 24 Apr '18

24 Apr '18

revision-id: 64ca3812839824baf0e300d5d11aa2a03c6a2537 (mariadb-10.3.6-44-g64ca3812839) parent(s): e304f088837682f6186f56ee34bd74e9f33a59c3 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-04-24 15:17:58 +0200 message: buildbot finding --- tests/mysql_client_test.c | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index e574d6b6e78..97ffa5bc8ac 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -20236,8 +20236,6 @@ static void test_proxy_header() test_proxy_header_ignore(); } -#endif - static void test_bulk_autoinc() { @@ -20291,6 +20289,8 @@ static void test_bulk_autoinc() myquery(rc); } +#endif + static struct my_tests_st my_tests[]= { { "disable_query_logs", disable_query_logs }, { "test_view_sp_list_fields", test_view_sp_list_fields }, @@ -20576,8 +20576,8 @@ static struct my_tests_st my_tests[]= { { "test_mdev14454", test_mdev14454 }, #ifndef EMBEDDED_LIBRARY { "test_proxy_header", test_proxy_header}, -#endif { "test_bulk_autoinc", test_bulk_autoinc}, +#endif { 0, 0 } };

1 0

[Commits] 48e3b4ca5dd: MDEV-15607: mysqld crashed few after node is being joined with sst
by jan 24 Apr '18

24 Apr '18

revision-id: 48e3b4ca5dd6a6cffbee64381dc301d43c66e036 (mariadb-10.1.32-67-g48e3b4ca5dd) parent(s): 9c34a4124d67d9e3f70837eaeb11290f35e8f8d0 author: Jan Lindström committer: Jan Lindström timestamp: 2018-04-24 14:43:41 +0300 message: MDEV-15607: mysqld crashed few after node is being joined with sst This is a typical systemd response where it tries to shutdown the joiner (due to "timeout") before the joiner manages to complete SST. wsrep_sst_wait wsrep_SE_init_wait While waiting the operation to finish use mysql_cond_timedwait instead of mysql_cond_wait and if operation is not finished extend systemd timeout (if needed). --- sql/wsrep_sst.cc | 21 +++++++++++++++++++-- 1 file changed, 19 insertions(+), 2 deletions(-) diff --git a/sql/wsrep_sst.cc b/sql/wsrep_sst.cc index 260755d08a8..c1f980bd595 100644 --- a/sql/wsrep_sst.cc +++ b/sql/wsrep_sst.cc @@ -30,6 +30,10 @@ #include <cstdio> #include <cstdlib> +#if MYSQL_VERSION_ID < 100200 +# include <my_service_manager.h> +#endif + static char wsrep_defaults_file[FN_REFLEN * 2 + 10 + 30 + sizeof(WSREP_SST_OPT_CONF) + sizeof(WSREP_SST_OPT_CONF_SUFFIX) + @@ -186,6 +190,9 @@ bool wsrep_before_SE() static bool sst_complete = false; static bool sst_needed = false; +#define WSREP_EXTEND_TIMEOUT_INTERVAL 30 +#define WSREP_TIMEDWAIT_SECONDS 10 + void wsrep_sst_grab () { WSREP_INFO("wsrep_sst_grab()"); @@ -197,11 +204,16 @@ void wsrep_sst_grab () // Wait for end of SST bool wsrep_sst_wait () { + struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0}; if (mysql_mutex_lock (&LOCK_wsrep_sst)) abort(); while (!sst_complete) { WSREP_INFO("Waiting for SST to complete."); - mysql_cond_wait (&COND_wsrep_sst, &LOCK_wsrep_sst); + mysql_cond_timedwait (&COND_wsrep_sst, &LOCK_wsrep_sst, &wtime); + if (!sst_complete) + service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL, + "WSREP state transfer ongoing, current seqno: %ld", local_seqno); + } if (local_seqno >= 0) @@ -1298,9 +1310,14 @@ void wsrep_SE_init_grab() void wsrep_SE_init_wait() { + struct timespec wtime = {WSREP_TIMEDWAIT_SECONDS, 0}; while (SE_initialized == false) { - mysql_cond_wait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init); + mysql_cond_timedwait (&COND_wsrep_sst_init, &LOCK_wsrep_sst_init, &wtime); + + if (!SE_initialized) + service_manager_extend_timeout(WSREP_EXTEND_TIMEOUT_INTERVAL, + "WSREP SE initialization ongoing."); } mysql_mutex_unlock (&LOCK_wsrep_sst_init); }

1 0

[Commits] cb6aade130b: MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle
by Oleksandr Byelkin 24 Apr '18

24 Apr '18

revision-id: cb6aade130b75f3dc4b7c3a1dd4d33864adffd90 (mariadb-10.3.6-43-gcb6aade130b) parent(s): f79c5a658cc33a10d7744a748a4328254e2cbaf7 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-04-24 12:04:59 +0200 message: MDEV-13695: INTERSECT precedence is not in line with Oracle even in SQL_MODE=Oracle Switch off automatic INTERSECT priority for ORACLE MODE --- mysql-test/main/intersect.result | 60 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/intersect.test | 38 +++++++++++++++++++++++++ sql/sql_yacc.yy | 3 +- 3 files changed, 100 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index b589e8bd17e..66c7addfd36 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -607,6 +607,22 @@ NULL INTERSECT RESULT <intersect2,4> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,3,5> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`) +set SQL_MODE=ORACLE; +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +a b +3 3 +4 4 +explain extended +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 UNION t2 ALL NULL NULL NULL NULL 2 100.00 +3 INTERSECT t3 ALL NULL NULL NULL NULL 2 100.00 +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") intersect (/* select#3 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") union (/* select#4 */ select 4 AS "4",4 AS "4") +set SQL_MODE=default; (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); e f 3 3 @@ -623,6 +639,24 @@ id select_type table type possible_keys key key_len ref rows filtered Extra NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 (/* select#1 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`) intersect (/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) union (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union (/* select#4 */ select 4 AS `4`,4 AS `4`) +set SQL_MODE=ORACLE; +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +e f +3 3 +4 4 +5 5 +6 6 +explain extended +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 +2 INTERSECT t2 ALL NULL NULL NULL NULL 2 100.00 +3 UNION t1 ALL NULL NULL NULL NULL 2 100.00 +4 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1,2,3,4> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select "test"."t3"."e" AS "e","test"."t3"."f" AS "f" from "test"."t3") intersect (/* select#2 */ select "test"."t2"."c" AS "c","test"."t2"."d" AS "d" from "test"."t2") union (/* select#3 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1") union (/* select#4 */ select 4 AS "4",4 AS "4") +set SQL_MODE=default; (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`); a b 3 3 @@ -772,4 +806,30 @@ SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 ON b < c ON a > b count(*) 14848 drop table t1,t2,t3; +# +# MDEV-13695: INTERSECT precedence is not in line with Oracle even +# in SQL_MODE=Oracle +# +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); +set SQL_MODE=oracle; +select * from t13 union select * from t234 intersect select * from t12; +c1 +1 +2 +set SQL_MODE=default; +select * from t13 union select * from t234 intersect select * from t12; +c1 +1 +2 +3 +drop table t12,t13,t234; # End of 10.3 tests diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test index d9d420c786b..fb5e991a24c 100644 --- a/mysql-test/main/intersect.test +++ b/mysql-test/main/intersect.test @@ -147,12 +147,25 @@ insert into t3 values (1,1),(3,3); (select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); explain extended (select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +set SQL_MODE=ORACLE; +--sorted_result +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +explain extended +(select a,b from t1) union (select c,d from t2) intersect (select e,f from t3) union (select 4,4); +set SQL_MODE=default; + # test result of linear mix operation --sorted_result (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); explain extended (select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +set SQL_MODE=ORACLE; +--sorted_result +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +explain extended +(select e,f from t3) intersect (select c,d from t2) union (select a,b from t1) union (select 4,4); +set SQL_MODE=default; --sorted_result (/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`) union /* select#3 */ select `__3`.`c` AS `c`,`__3`.`d` AS `d` from ((/* select#2 */ select `test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2`) intersect (/* select#4 */ select `test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t3`)) `__3` union (/* select#5 */ select 4 AS `4`,4 AS `4`); @@ -282,4 +295,29 @@ select count(*) from ( drop table t1,t2,t3; +--echo # +--echo # MDEV-13695: INTERSECT precedence is not in line with Oracle even +--echo # in SQL_MODE=Oracle +--echo # + +create table t12(c1 int); +insert into t12 values(1); +insert into t12 values(2); +create table t13(c1 int); +insert into t13 values(1); +insert into t13 values(3); +create table t234(c1 int); +insert into t234 values(2); +insert into t234 values(3); +insert into t234 values(4); + +set SQL_MODE=oracle; +--sorted_result +select * from t13 union select * from t234 intersect select * from t12; +set SQL_MODE=default; +--sorted_result +select * from t13 union select * from t234 intersect select * from t12; + +drop table t12,t13,t234; + --echo # End of 10.3 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 93704cda06d..e1f3d15e514 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -546,7 +546,8 @@ bool LEX::add_select_to_union_list(bool is_union_distinct, as possible */ if (type == INTERSECT_TYPE && (current_select->linkage != INTERSECT_TYPE && - current_select != current_select->master_unit()->first_select())) + current_select != current_select->master_unit()->first_select()) + && !(thd->variables.sql_mode & MODE_ORACLE)) { /* This and previous SELECTs should go one level down because of

1 0

[Commits] 863e0712906: fix
by Oleksandr Byelkin 24 Apr '18

24 Apr '18

revision-id: 863e0712906d4e0938fe5afba71b94f1ecaab10a (mariadb-10.3.6-43-g863e0712906) parent(s): 0d0cc6b0e903192600e8efdefa51129a485afec9 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-04-24 10:25:07 +0200 message: fix --- tests/mysql_client_test.c | 1 + 1 file changed, 1 insertion(+) diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index f24bb919906..e574d6b6e78 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -20226,6 +20226,7 @@ static void test_proxy_header_ignore() myquery(rc); } + static void test_proxy_header() { test_proxy_header_tcp("192.0.2.1",3333);

1 0