- commits - lists.mariadb.org

[Commits] f7579518e2c: MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
by Sergei Petrunia 05 Jun '19

05 Jun '19

revision-id: f7579518e2c32936442a1e20e391f60660c94b3c (mariadb-10.3.12-226-gf7579518e2c) parent(s): 7060b0320d1479bb9476e0cbd4acc584e059e1ff author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-06-05 14:00:45 +0300 message: MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns Modify best_access_path() to produce rows=1 estimate for null-rejecting lookups on unique NULL keys. --- mysql-test/main/invisible_field.result | 4 +- mysql-test/main/join.result | 30 +++++++++ mysql-test/main/join.test | 30 +++++++++ mysql-test/main/order_by.result | 2 +- mysql-test/main/subselect_sj.result | 74 ++++++++++++++++++++--- mysql-test/main/subselect_sj.test | 52 +++++++++++++++- mysql-test/main/subselect_sj_jcl6.result | 58 +++++++++++++++++- mysql-test/main/subselect_sj_nonmerged.result | 12 ++-- mysql-test/main/table_elim.result | 2 +- mysql-test/suite/gcol/r/gcol_select_myisam.result | 2 +- sql/sql_select.cc | 47 +++++++++----- 11 files changed, 276 insertions(+), 37 deletions(-) diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result index 876a80814e5..43a8b9d726b 100644 --- a/mysql-test/main/invisible_field.result +++ b/mysql-test/main/invisible_field.result @@ -404,8 +404,8 @@ b int(11) YES NULL c int(11) YES NULL explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 10 -1 SIMPLE t1 ALL b,c NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ref b,c b 5 test.t2.c 1 Using where select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; a a b c 1 1 1 1 diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index cc8e174c8e6..8ca82002855 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1599,3 +1599,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); a b c b c DROP TABLE t1,t2; +# +# MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +pk int not null primary key auto_increment, +a int, +b int, +unique key(a) +); +insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C; +insert into t1 (a,b) select a,a from t0; +# Simulate InnoDB's persistent statistics (It always uses nulls_equal) +set @tmp1= @@myisam_stats_method; +set myisam_stats_method=nulls_equal; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set myisam_stats_method=@tmp1; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE +t1 0 a 1 a A 10 NULL NULL YES BTREE +# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45): +explain select * from t0,t1 where t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 1 +drop table t0,t1; diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index 3d2a02e2346..b90a9cc39eb 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1254,3 +1254,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); DROP TABLE t1,t2; + +--echo # +--echo # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int not null primary key auto_increment, + a int, + b int, + unique key(a) +); + +# 10K of null values +insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C; +insert into t1 (a,b) select a,a from t0; + +--echo # Simulate InnoDB's persistent statistics (It always uses nulls_equal) +set @tmp1= @@myisam_stats_method; +set myisam_stats_method=nulls_equal; +analyze table t1; +set myisam_stats_method=@tmp1; +show keys from t1; + +--echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45): +explain select * from t0,t1 where t0.a=t1.a; + +drop table t0,t1; diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 8d1e471f618..8692e727c60 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where -1 SIMPLE t1 ref a a 39 test.t2.a,const 10 Using where; Using index +1 SIMPLE t1 ref a a 39 test.t2.a,const 1 Using where; Using index SELECT d FROM t3 AS t1, t2 AS t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index 13d5224566b..ad43a09c7ff 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -2555,33 +2555,89 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); INSERT INTO t1 VALUES (1,2),(2,1),(3,3),(4,2),(5,5), (6,3),(7,1),(8,4),(9,3),(10,2); -CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +CREATE TABLE t2 ( c INT, d INT, KEY(c) ); INSERT INTO t2 VALUES (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +explain +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan +1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1) +1 PRIMARY t1 ref b b 5 test.t1.b 2 SELECT a, b, d FROM t1, t2 WHERE ( b, d ) IN ( SELECT b, d FROM t1, t2 WHERE b = c ); a b d 2 1 2 7 1 2 -2 1 2 -7 1 2 -1 2 1 -4 2 1 -10 2 1 +8 4 2 1 2 1 4 2 1 10 2 1 3 3 3 6 3 3 9 3 3 +2 1 2 +7 1 2 +8 4 2 +5 5 5 3 3 3 6 3 3 9 3 3 -8 4 2 -8 4 2 -5 5 5 +1 2 1 +4 2 1 +10 2 1 DROP TABLE t1, t2; +# Another testcase for the above that still uses LooseScan: +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int primary key); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int primary key auto_increment, +kp1 int, +kp2 int, +filler char(100), +key (kp1, kp2) +); +insert into t1 (kp1, kp2, filler) +select +A.a, B.a, 'filler-data' +from t0 A, t0 B; +create table t2 (a int, filler char(100), key(a)); +create table t3 (a int); +insert into t3 values (1),(2); +insert into t2 +select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +test.t2 analyze status Table is already up to date +test.t3 analyze status OK +delete from t1 where kp2 in (1,3); +# Ref + LooseScan on t1: +explain select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan +1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index +select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); +sum(t2.a) +1640 +drop table t0,t10; +drop table t1,t2,t3; # # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... # diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index 29211cadd9c..81cca95092d 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2285,16 +2285,66 @@ INSERT INTO t1 VALUES (1,2),(2,1),(3,3),(4,2),(5,5), (6,3),(7,1),(8,4),(9,3),(10,2); -CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +CREATE TABLE t2 ( c INT, d INT, KEY(c) ); INSERT INTO t2 VALUES (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +analyze table t1,t2; +explain +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN + ( SELECT b, d FROM t1, t2 WHERE b = c ); SELECT a, b, d FROM t1, t2 WHERE ( b, d ) IN ( SELECT b, d FROM t1, t2 WHERE b = c ); DROP TABLE t1, t2; +--echo # Another testcase for the above that still uses LooseScan: + +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t10(a int primary key); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; + +create table t1 ( + pk int primary key auto_increment, + kp1 int, + kp2 int, + filler char(100), + key (kp1, kp2) +); + +# 10 groups, each has 10 elements. +insert into t1 (kp1, kp2, filler) +select + A.a, B.a, 'filler-data' +from t0 A, t0 B; + +create table t2 (a int, filler char(100), key(a)); + +create table t3 (a int); +insert into t3 values (1),(2); + +insert into t2 +select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B; + +analyze table t1,t2,t3; +delete from t1 where kp2 in (1,3); + +--echo # Ref + LooseScan on t1: +explain select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); + +select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); + +drop table t0,t10; +drop table t1,t2,t3; + --echo # --echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... --echo # diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 014c44d1181..0026924b605 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -2569,9 +2569,22 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); INSERT INTO t1 VALUES (1,2),(2,1),(3,3),(4,2),(5,5), (6,3),(7,1),(8,4),(9,3),(10,2); -CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +CREATE TABLE t2 ( c INT, d INT, KEY(c) ); INSERT INTO t2 VALUES (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +explain +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 7 +1 PRIMARY t1 index b b 5 NULL 10 Using where; Using index; LooseScan +1 PRIMARY t2 ref c c 5 test.t1.b 1 Using where; FirstMatch(t1) +1 PRIMARY t1 ref b b 5 test.t1.b 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT a, b, d FROM t1, t2 WHERE ( b, d ) IN ( SELECT b, d FROM t1, t2 WHERE b = c ); @@ -2596,6 +2609,49 @@ a b d 10 2 1 10 2 1 DROP TABLE t1, t2; +# Another testcase for the above that still uses LooseScan: +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t10(a int primary key); +insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t1 ( +pk int primary key auto_increment, +kp1 int, +kp2 int, +filler char(100), +key (kp1, kp2) +); +insert into t1 (kp1, kp2, filler) +select +A.a, B.a, 'filler-data' +from t0 A, t0 B; +create table t2 (a int, filler char(100), key(a)); +create table t3 (a int); +insert into t3 values (1),(2); +insert into t2 +select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +test.t2 analyze status Table is already up to date +test.t3 analyze status OK +delete from t1 where kp2 in (1,3); +# Ref + LooseScan on t1: +explain select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t0 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 10 Using where; Using index; LooseScan +1 PRIMARY t2 ref a a 5 test.t1.kp2 20 Using index +select sum(t2.a) +from t2,t3 +where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2); +sum(t2.a) +1640 +drop table t0,t10; +drop table t1,t2,t3; # # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ... # diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result index 47970668ae5..4d9a70e6bba 100644 --- a/mysql-test/main/subselect_sj_nonmerged.result +++ b/mysql-test/main/subselect_sj_nonmerged.result @@ -67,9 +67,9 @@ insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B; explain select * from t0, t4 where t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t0 ALL NULL NULL NULL NULL 10 -1 PRIMARY t4 ALL a NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t4.a 1 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),test.t0.a 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B; @@ -77,9 +77,9 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 -1 PRIMARY t4 ref a a 5 <subquery2>.max(t2.a) 12 Using index condition -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 test.t4.b 1 +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result index cf9a4a38779..7780e165451 100644 --- a/mysql-test/main/table_elim.result +++ b/mysql-test/main/table_elim.result @@ -279,7 +279,7 @@ insert into t2 values explain select t1.* from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 ref a a 3 test.t1.a 2 Using where +1 SIMPLE t2 ref a a 3 test.t1.a 1 Using where drop table t1, t2; # # check UPDATE/DELETE that look like they could be eliminated diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index d0fe7fbd0d4..280618ed76e 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -1102,7 +1102,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where -1 SIMPLE t1 ref b b 5 test.t2.b 2 +1 SIMPLE t1 ref b b 5 test.t2.b 1 EXPLAIN SELECT b FROM t1 FORCE INDEX(b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL b 5 NULL 2 Using index diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0fbc4bac7d8..13a5cd88c93 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5507,18 +5507,16 @@ add_key_field(JOIN *join, (*key_fields)->level= and_level; (*key_fields)->optimize= optimize; /* - If the condition has form "tbl.keypart = othertbl.field" and - othertbl.field can be NULL, there will be no matches if othertbl.field - has NULL value. - We use null_rejecting in add_not_null_conds() to add - 'othertbl.field IS NOT NULL' to tab->select_cond. + If the condition we are analyzing is NULL-rejecting and at least + one side of the equalities is NULLable, mark the KEY_FIELD object as + null-rejecting. This property is used by: + - add_not_null_conds() to add "column IS NOT NULL" conditions + - best_access_path() to produce better estimates for NULL-able unique keys. */ { - Item *real= (*value)->real_item(); - if (((cond->functype() == Item_func::EQ_FUNC) || - (cond->functype() == Item_func::MULT_EQUAL_FUNC)) && - (real->type() == Item::FIELD_ITEM) && - ((Item_field*)real)->field->maybe_null()) + if ((cond->functype() == Item_func::EQ_FUNC || + cond->functype() == Item_func::MULT_EQUAL_FUNC) && + ((*value)->maybe_null || field->real_maybe_null())) (*key_fields)->null_rejecting= true; else (*key_fields)->null_rejecting= false; @@ -6834,6 +6832,7 @@ best_access_path(JOIN *join, ulong key_flags; uint key_parts; key_part_map found_part= 0; + key_part_map notnull_part=0; // key parts which won't have NULL in lookup tuple. table_map found_ref= 0; uint key= keyuse->key; bool ft_key= (keyuse->keypart == FT_KEYPART); @@ -6892,6 +6891,9 @@ best_access_path(JOIN *join, if (!(keyuse->used_tables & ~join->const_table_map)) const_part|= keyuse->keypart_map; + if (!keyuse->val->maybe_null || keyuse->null_rejecting) + notnull_part|=keyuse->keypart_map; + double tmp2= prev_record_reads(join->positions, idx, (found_ref | keyuse->used_tables)); if (tmp2 < best_prev_record_reads) @@ -6942,12 +6944,19 @@ best_access_path(JOIN *join, loose_scan_opt.check_ref_access_part1(s, key, start_key, found_part); /* Check if we found full key */ - if (found_part == PREV_BITS(uint, key_parts) && - !ref_or_null_part) + const key_part_map all_key_parts= PREV_BITS(uint, key_parts); + if (found_part == all_key_parts && !ref_or_null_part) { /* use eq key */ max_key_part= (uint) ~0; - if ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || - MY_TEST(key_flags & HA_EXT_NOSAME)) + /* + If the index is a unique index (1), and + - all its columns are not null (2), or + - equalities we are using reject NULLs (3) + then the estimate is rows=1. + */ + if ((key_flags & (HA_NOSAME | HA_EXT_NOSAME)) && // (1) + (!(key_flags & HA_NULL_PART_KEY) || // (2) + all_key_parts == notnull_part)) // (3) { tmp = prev_record_reads(join->positions, idx, found_ref); records=1.0; @@ -9991,8 +10000,16 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, uint maybe_null= MY_TEST(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal j->ref.cond_guards[i]= keyuse->cond_guard; - if (keyuse->null_rejecting) + + /* + Set ref.null_rejecting to true only if we are going to inject a + "keyuse->val IS NOT NULL" predicate. + */ + Item *real= (keyuse->val)->real_item(); + if (keyuse->null_rejecting && (real->type() == Item::FIELD_ITEM) && + ((Item_field*)real)->field->maybe_null()) j->ref.null_rejecting|= (key_part_map)1 << i; + keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; /* We don't want to compute heavy expressions in EXPLAIN, an example would

1 0

[Commits] 38564f9: Sc #27132 proposed fix.
by holyfoot＠askmonty.org 04 Jun '19

04 Jun '19

revision-id: 38564f99677726bf35013b5b94dcbf11a3809db0 (mariadb-10.1.39-43-g38564f9) parent(s): aaf53ea0b68efde4a90cabbbcaf9ca41c1fbf62f committer: Alexey Botchkov timestamp: 2019-05-27 09:48:40 +0400 message: Sc #27132 proposed fix. --- sql/sql_audit.cc | 16 ++++++++++++++++ sql/sql_audit.h | 1 + sql/sql_class.cc | 4 +++- sql/sql_class.h | 1 + sql/sql_connect.cc | 3 ++- sql/sql_plugin.cc | 3 +++ sql/sql_plugin.h | 1 + sql/threadpool_common.cc | 3 ++- 8 files changed, 29 insertions(+), 3 deletions(-) diff --git a/sql/sql_audit.cc b/sql/sql_audit.cc index dd98e3c..cee0ac2 100644 --- a/sql/sql_audit.cc +++ b/sql/sql_audit.cc @@ -212,6 +212,7 @@ void mysql_audit_acquire_plugins(THD *thd, ulong *event_class_mask) { plugin_foreach(thd, acquire_plugins, MYSQL_AUDIT_PLUGIN, event_class_mask); add_audit_mask(thd->audit_class_mask, event_class_mask); + thd->audit_plugin_version= global_plugin_version; } DBUG_VOID_RETURN; } @@ -242,6 +243,20 @@ void mysql_audit_notify(THD *thd, uint event_class, uint event_subtype, ...) /** + Check if there were changes in the state of plugins + so we need to do the mysql_audit_release asap. + + @param[in] thd + +*/ + +my_bool mysql_audit_release_required(THD *thd) +{ + return thd && (thd->audit_plugin_version != global_plugin_version); +} + + +/** Release any resources associated with the current thd. @param[in] thd @@ -276,6 +291,7 @@ void mysql_audit_release(THD *thd) /* Reset the state of thread values */ reset_dynamic(&thd->audit_class_plugins); bzero(thd->audit_class_mask, sizeof(thd->audit_class_mask)); + thd->audit_plugin_version= -1; } diff --git a/sql/sql_audit.h b/sql/sql_audit.h index 550b2a5..9a74675 100644 --- a/sql/sql_audit.h +++ b/sql/sql_audit.h @@ -60,6 +60,7 @@ static inline void mysql_audit_notify(THD *thd, uint event_class, #define mysql_audit_connection_enabled() 0 #define mysql_audit_table_enabled() 0 #endif +extern my_bool mysql_audit_release_required(THD *thd); extern void mysql_audit_release(THD *thd); #define MAX_USER_HOST_SIZE 512 diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 8fabcd5..6bcff6d 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -776,6 +776,9 @@ THD::THD(bool is_wsrep_applier) waiting_on_group_commit(FALSE), has_waiter(FALSE), spcont(NULL), m_parser_state(NULL), +#ifndef EMBEDDED_LIBRARY + audit_plugin_version(-1), +#endif #if defined(ENABLED_DEBUG_SYNC) debug_sync_control(0), #endif /* defined(ENABLED_DEBUG_SYNC) */ @@ -1562,7 +1565,6 @@ THD::~THD() mdl_context.destroy(); ha_close_connection(this); - mysql_audit_release(this); plugin_thdvar_cleanup(this); main_security_ctx.destroy(); diff --git a/sql/sql_class.h b/sql/sql_class.h index 1cb516c..6392394 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2978,6 +2978,7 @@ class THD :public Statement, added to the list of audit plugins which are currently in use. */ unsigned long audit_class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE]; + int audit_plugin_version; #endif #if defined(ENABLED_DEBUG_SYNC) diff --git a/sql/sql_connect.cc b/sql/sql_connect.cc index 4dbb53f..a6a01b1 100644 --- a/sql/sql_connect.cc +++ b/sql/sql_connect.cc @@ -1326,7 +1326,8 @@ void do_handle_one_connection(THD *thd_arg) while (thd_is_connection_alive(thd)) { - mysql_audit_release(thd); + if (mysql_audit_release_required(thd)) + mysql_audit_release(thd); if (do_command(thd)) break; } diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index 21093e3..48131b1 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -228,6 +228,7 @@ static DYNAMIC_ARRAY plugin_array; static HASH plugin_hash[MYSQL_MAX_PLUGIN_TYPE_NUM]; static MEM_ROOT plugin_mem_root; static bool reap_needed= false; +volatile int global_plugin_version= 1; static bool initialized= 0; ulong dlopen_count; @@ -2181,6 +2182,7 @@ bool mysql_install_plugin(THD *thd, const LEX_STRING *name, reap_plugins(); } err: + global_plugin_version++; mysql_mutex_unlock(&LOCK_plugin); if (argv) free_defaults(argv); @@ -2327,6 +2329,7 @@ bool mysql_uninstall_plugin(THD *thd, const LEX_STRING *name, } reap_plugins(); + global_plugin_version++; mysql_mutex_unlock(&LOCK_plugin); DBUG_RETURN(error); diff --git a/sql/sql_plugin.h b/sql/sql_plugin.h index 7f74114..3bde06a 100644 --- a/sql/sql_plugin.h +++ b/sql/sql_plugin.h @@ -37,6 +37,7 @@ enum enum_plugin_load_option { PLUGIN_OFF, PLUGIN_ON, PLUGIN_FORCE, PLUGIN_FORCE_PLUS_PERMANENT }; extern const char *global_plugin_typelib_names[]; +extern volatile int global_plugin_version; extern ulong dlopen_count; #include <my_sys.h> diff --git a/sql/threadpool_common.cc b/sql/threadpool_common.cc index b4066bd..b8be708 100644 --- a/sql/threadpool_common.cc +++ b/sql/threadpool_common.cc @@ -266,7 +266,8 @@ int threadpool_process_request(THD *thd) { Vio *vio; thd->net.reading_or_writing= 0; - mysql_audit_release(thd); + if (mysql_audit_release_required(thd)) + mysql_audit_release(thd); if ((retval= do_command(thd)) != 0) goto end;

3 4

[Commits] 15f97e3fd92: MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search
by Varun 03 Jun '19

03 Jun '19

revision-id: 15f97e3fd922933987e78ef3c2b6490f0edf8172 (mariadb-10.4.4-141-g15f97e3fd92) parent(s): 92df31dfbfcf6068f4f4a7e7794a15333158c569 author: Varun Gupta committer: Varun Gupta timestamp: 2019-06-03 11:22:39 +0530 message: MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, [Warning] InnoDB: Using a partial-field key prefix in search For a key with keyparts (k1,k2,k3) , if we are building a range over the keyparts we should make sure that if min_value/max_value for a keypart is not added to key buffer then the keyparts following should also not be allowed. --- mysql-test/main/range_innodb.result | 19 +++++++++++++++++++ mysql-test/main/range_innodb.test | 19 +++++++++++++++++++ sql/opt_range.h | 10 ++++++---- 3 files changed, 44 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result index 30161a2711d..7d8c6e5b475 100644 --- a/mysql-test/main/range_innodb.result +++ b/mysql-test/main/range_innodb.result @@ -80,3 +80,22 @@ ERROR HY000: Table definition has changed, please retry transaction DROP TABLE t0,t1; set @@global.debug_dbug="-d"; set @@optimizer_switch= @optimizer_switch_save; +# +# MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, +# [Warning] InnoDB: Using a partial-field key prefix in search +# +CREATE TABLE t1 ( +pk INT, +a VARCHAR(1), +b INT, +PRIMARY KEY (pk), +KEY (a,b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); +explain +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY,a a 9 NULL 2 Using where; Using index +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +a +drop table t1; diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test index a17ef3f1146..2c225df27fe 100644 --- a/mysql-test/main/range_innodb.test +++ b/mysql-test/main/range_innodb.test @@ -87,3 +87,22 @@ select * from t1 where a=10 and b=10; DROP TABLE t0,t1; set @@global.debug_dbug="-d"; set @@optimizer_switch= @optimizer_switch_save; + +--echo # +--echo # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, +--echo # [Warning] InnoDB: Using a partial-field key prefix in search +--echo # + +CREATE TABLE t1 ( + pk INT, + a VARCHAR(1), + b INT, + PRIMARY KEY (pk), + KEY (a,b) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1,'a',1),(2,'b',2); + +explain SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +SELECT a FROM t1 WHERE pk < 0 AND a <= 'w' and b > 0; +drop table t1; diff --git a/sql/opt_range.h b/sql/opt_range.h index ae0e3822272..73def7bde92 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -459,8 +459,9 @@ class SEL_ARG :public Sql_alloc uint res= key_tree->store_min(key[key_tree->part].store_length, range_key, *range_key_flag); // add flags only if a key_part is written to the buffer - if (res) - *range_key_flag|= key_tree->min_flag; + if (!res) + return 0; + *range_key_flag|= key_tree->min_flag; if (key_tree->next_key_part && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part && @@ -482,8 +483,9 @@ class SEL_ARG :public Sql_alloc SEL_ARG *key_tree= last(); uint res=key_tree->store_max(key[key_tree->part].store_length, range_key, *range_key_flag); - if (res) - (*range_key_flag)|= key_tree->max_flag; + if (!res) + return 0; + *range_key_flag|= key_tree->max_flag; if (key_tree->next_key_part && key_tree->next_key_part->type == SEL_ARG::KEY_RANGE && key_tree->part != last_part &&

1 0

[Commits] 32818f5e57d: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB
by sachin.setiya＠mariadb.com 02 Jun '19

02 Jun '19

revision-id: 32818f5e57ddb262dff8b2e72ed612748d29d3d0 (mariadb-10.4.5-25-g32818f5e57d) parent(s): 8b545d3d41416772203cb98f06243873a4f0d8f7 author: Sachin committer: Sachin timestamp: 2019-06-02 13:05:54 +0530 message: MDEV-18791 Wrong error upon creating Aria table with long index on BLOB Create a Create_table_error_handler to mask ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN error. --- mysql-test/main/long_unique_bugs.result | 2 ++ mysql-test/main/long_unique_bugs.test | 6 ++++++ sql/handler.cc | 9 +++++++++ sql/share/errmsg-utf8.txt | 2 ++ sql/sql_class.cc | 25 +++++++++++++++++++++++++ sql/sql_class.h | 24 ++++++++++++++++++++++++ 6 files changed, 68 insertions(+) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 33496c4e20d..4a65cc69ced 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -265,3 +265,5 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction disconnect con1; connection default; DROP TABLE t1, t2; +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; +ERROR HY000: Aria Storage engine does not support long unique keys diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index dc78f6c7067..ce76211184d 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -317,3 +317,9 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; --disconnect con1 --connection default DROP TABLE t1, t2; + +# +# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +# +--error ER_NO_LONG_UNIQUE_ENGINE_SUPPORT +CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; diff --git a/sql/handler.cc b/sql/handler.cc index 124f5c8e9ce..b5303fbf67e 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5135,6 +5135,7 @@ int ha_create_table(THD *thd, const char *path, TABLE_SHARE share; bool temp_table __attribute__((unused)) = create_info->options & (HA_LEX_CREATE_TMP_TABLE | HA_CREATE_TMP_ALTER); + Create_table_error_handler err_handler; DBUG_ENTER("ha_create_table"); init_tmp_table_share(thd, &share, db, 0, table_name, path); @@ -5170,12 +5171,20 @@ int ha_create_table(THD *thd, const char *path, name= get_canonical_filename(table.file, share.path.str, name_buff); + thd->push_internal_handler(&err_handler); error= table.file->ha_create(name, &table, create_info); + thd->pop_internal_handler(); if (unlikely(error)) { if (!thd->is_error()) + { + if (err_handler.safely_trapped_errors()) + { + my_error(ER_NO_LONG_UNIQUE_ENGINE_SUPPORT, MYF(0), hton_name(table.file->ht)->str); + } my_error(ER_CANT_CREATE_TABLE, MYF(0), db, table_name, error); + } table.file->print_error(error, MYF(ME_WARNING)); PSI_CALL_drop_table_share(temp_table, share.db.str, (uint)share.db.length, share.table_name.str, (uint)share.table_name.length); diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index c64f60f3562..3fb53ad55e6 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7931,3 +7931,5 @@ ER_PERIOD_CONSTRAINT_DROP eng "Can't DROP CONSTRAINT `%s`. Use DROP PERIOD `%s` for this" ER_TOO_LONG_KEYPART 42000 S1009 eng "Specified key part was too long; max key part length is %u bytes" +ER_NO_LONG_UNIQUE_ENGINE_SUPPORT + eng "%s Storage engine does not support long unique keys" diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 94e2b518fa4..4cd3c49cd41 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -563,6 +563,31 @@ bool Drop_table_error_handler::handle_condition(THD *thd, sql_errno == ER_TRG_NO_DEFINER); } +/** + Implementation of Create_table_error_handler::handle_condition(). + The reason in having this implementation is to silence technical low-level + error during CREATE TABLE operation. Currently we don't want to expose + the following warnings during DROP TABLE: + - When using long unique on ARIA storage engine show long unique not + avaliable in ARIA instead of ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN + @return TRUE if the condition is handled. +*/ +bool Create_table_error_handler::handle_condition(THD *thd, + uint sql_errno, + const char* sqlstate, + Sql_condition::enum_warning_level *level, + const char* msg, + Sql_condition ** cond_hdl) +{ + *cond_hdl= NULL; + if (sql_errno == ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN) + { + m_handled_error++; + return true; + } + return false; +} + /** Handle an error from MDL_context::upgrade_lock() and mysql_lock_tables(). diff --git a/sql/sql_class.h b/sql/sql_class.h index dd9cfbbd1c4..c5c3058cc06 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1808,6 +1808,30 @@ class Drop_table_error_handler : public Internal_error_handler private: }; +/** + This class is an internal error handler implementation for + CREATE TABLE statements. The thing is that there may be error during + execution of these statements, which should not be exposed to the user. + This class is intended to silence such error. +*/ + +class Create_table_error_handler : public Internal_error_handler +{ +public: + Create_table_error_handler(): m_handled_error(0) {} + +public: + bool handle_condition(THD *thd, + uint sql_errno, + const char* sqlstate, + Sql_condition::enum_warning_level *level, + const char* msg, + Sql_condition ** cond_hdl); + bool safely_trapped_errors(){return m_handled_error > 0;} +private: + int m_handled_error; +}; + /** Internal error handler to process an error from MDL_context::upgrade_lock()

1 0

[Commits] a602998: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19

01 Jun '19

revision-id: a6029989cc289f80eed5da707815838c6848d24b (mariadb-5.5.64-13-ga602998) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-01 13:07:09 -0700 message: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)' Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier. --- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++ sql/sql_select.cc | 86 ++++++++++++++++++++++++++----- 9 files changed, 279 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02..2999f4c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a..ae24485 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a729129..5c21e74 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1b..8ab1f1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c18135..f0afa7e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c6712..0d5fbc3 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fd9435e..9dde115 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c82c1e7..997e71a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37f8292..bc69817 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) psergey: have put the -1 below. It's bad, will need to fix it. */ COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2, - current_map, /*(tab - first_tab)*/ -1, - FALSE, FALSE); + current_map, + /*(tab - first_tab)*/ -1, + FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (tab->bush_children) { @@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field then it is pushed + to the corresponding table by the same rule as all other conjuncts. + Otherwise, if the conjunct is used in WHERE is is pushed to the last + joined table, if is it is used in ON condition of an outer join it + is pushed into the last inner table of the outer join. Such conjuncts + are pushed in a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to RAND_TABLE_BIT. + */ + if (is_top_and_level && used_table == rand_table_bit && + item->used_tables() != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix); } @@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new Item_cond_or; if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result

1 0

[Commits] eb89a6c: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'
by IgorBabaev 01 Jun '19

01 Jun '19

revision-id: eb89a6cd23df80cad091ef6f1d50e0969488de7c (mariadb-5.5.64-13-geb89a6c) parent(s): cbb90f77cdbf57c02145dc6cd86acf8ebb8a88f0 author: Igor Babaev committer: Igor Babaev timestamp: 2019-06-01 13:04:08 -0700 message: MDEV-19580 Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)' Handling of top level conjuncts in WHERE whose used_tables() contained RAND_TABLE_BIT in the function make_join_select() was incorrect. As a result if such a conjunct referred to fields non of which belonged to the last joined table it was pushed twice. (This could be seen for a test case from subselect.test whose output was changed after this patch had been applied. In 10.1 when running EXPLAIN FORMAT=JSON for the query from this test case we clearly see that one of the conjuncts is pushed twice.) This fact by itself was not good. Besides, if such a conjunct was pushed to a table that was the result of materialization of a semi-join the query could return a wrong result set. In particular we could watch it for queries with semi-join subqueries whose left parts used stored functions without "deterministic' specifier. --- mysql-test/r/subselect.result | 2 +- mysql-test/r/subselect_mat.result | 68 ++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 2 +- mysql-test/r/subselect_no_opts.result | 2 +- mysql-test/r/subselect_no_scache.result | 2 +- mysql-test/r/subselect_no_semijoin.result | 2 +- mysql-test/r/subselect_sj_mat.result | 68 ++++++++++++++++++++++++ mysql-test/t/subselect_sj_mat.test | 66 ++++++++++++++++++++++++ sql/sql_select.cc | 86 ++++++++++++++++++++++++++----- 9 files changed, 279 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index cdedc02..2999f4c 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6939,7 +6939,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index efc348a..ae24485 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2453,6 +2453,74 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index a729129..5c21e74 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6937,7 +6937,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index c41fa1b..8ab1f1b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 1c18135..f0afa7e 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6945,7 +6945,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 89c6712..0d5fbc3 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6934,7 +6934,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index fd9435e..9dde115 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2493,4 +2493,72 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c82c1e7..997e71a 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2230,4 +2230,70 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 37f8292..797b3a5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -204,7 +204,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -8922,12 +8923,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -8968,6 +8963,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -9283,8 +9292,24 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) psergey: have put the -1 below. It's bad, will need to fix it. */ COND *tmp_cond= make_cond_for_table(thd, on_expr, used_tables2, - current_map, /*(tab - first_tab)*/ -1, - FALSE, FALSE); + current_map, + /*(tab - first_tab)*/ -1, + FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (tab->bush_children) { @@ -18824,7 +18849,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -18834,9 +18859,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -18852,11 +18880,27 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field then it is pushed + to the corresponding table by the same rule as all other conjuncts. + Otherwise, if the conjunct is used in WHERE is is pushed to the last + joined table, if is it is used in ON condition of an outer join it + is pushed into the last inner table of the outer join. Such conjuncts + are pushed in a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to RAND_TABLE_BIT. + */ + if (is_top_and_level && used_table == rand_table_bit && + item->used_tables() != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix); } @@ -18880,6 +18924,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new Item_cond_or; if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -18891,7 +18942,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18908,6 +18959,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + cond->used_tables() != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result

1 0

[Commits] 5469d88e7b4: MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image
by sachin.setiya＠mariadb.com 31 May '19

31 May '19

revision-id: 5469d88e7b4e723e545cd7d511801a16a3642dc4 (mariadb-10.4.3-104-g5469d88e7b4) parent(s): 0bc42602266815b81fe86b08c2228912c1a95340 author: Sachin committer: Sachin timestamp: 2019-03-28 11:29:25 +0530 message: MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image Long Unique keys should always be last unique key. --- mysql-test/main/long_unique.result | 64 +++++++++++++++---------------- mysql-test/main/long_unique_bugs.result | 46 ++++++++++++++++++++++ mysql-test/main/long_unique_bugs.test | 22 ++++++++++- mysql-test/main/long_unique_innodb.result | 4 +- mysql-test/main/long_unique_update.result | 8 ++-- sql/sql_table.cc | 9 +++++ 6 files changed, 114 insertions(+), 39 deletions(-) diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 3843ff4aff0..8ea6d36c321 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -184,8 +184,8 @@ t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, `db_row_hash_1` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) USING HASH, - UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(45,1,55),(46,1,55); ERROR 23000: Duplicate entry '55' for key 'db_row_hash_1' @@ -507,13 +507,13 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_3` int(11) DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`), UNIQUE KEY `a` (`a`) USING HASH, UNIQUE KEY `c` (`c`) USING HASH, UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, - UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), - UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`) + UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ; alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4; @@ -543,17 +543,17 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), - UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`) + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `d` (`d`) USING HASH, + UNIQUE KEY `e` (`e`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH #add column with unique index on blob; alter table t1 add column a blob unique; show create table t1; @@ -567,18 +567,18 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `d` (`d`) USING HASH, + UNIQUE KEY `e` (`e`) USING HASH, UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 a 1 a A NULL NULL NULL YES HASH #try to change the blob unique column name; #this will change index to b tree; @@ -594,19 +594,19 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `a` (`a`) + UNIQUE KEY `e` (`e`), + UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH alter table t1 add column clm1 blob unique,add column clm2 blob unique; #try changing the name; alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob; @@ -623,21 +623,21 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), + UNIQUE KEY `e` (`e`), UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH, UNIQUE KEY `clm1` (`clm_changed1`) USING HASH, UNIQUE KEY `clm2` (`clm_changed2`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH t1 0 clm1 1 clm_changed1 A NULL NULL NULL YES HASH t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH #now drop the unique key; @@ -655,19 +655,19 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`) USING HASH, - UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), - UNIQUE KEY `a` (`a`) + UNIQUE KEY `e` (`e`), + UNIQUE KEY `a` (`a`), + UNIQUE KEY `d` (`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH -t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 0 d 1 d A NULL NULL NULL YES HASH drop table t1; #now the table with key on multiple columns; the ultimate test; create table t1(a blob, b int , c varchar(2000) , d text , e varchar(3000) , f longblob , g int , h text , @@ -1130,17 +1130,17 @@ t1 CREATE TABLE `t1` ( `c` blob DEFAULT NULL, `d` blob DEFAULT NULL, `e` int(11) DEFAULT NULL, + UNIQUE KEY `e` (`e`), UNIQUE KEY `a` (`a`,`c`) USING HASH, - UNIQUE KEY `b` (`b`,`d`) USING HASH, - UNIQUE KEY `e` (`e`) + UNIQUE KEY `b` (`b`,`d`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 a 1 a A NULL NULL NULL YES HASH t1 0 a 2 c A NULL NULL NULL YES HASH t1 0 b 1 b A NULL NULL NULL YES HASH t1 0 b 2 d A NULL NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES BTREE drop table t1; #visibility of db_row_hash create table t1 (a blob unique , b blob unique); diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 87a57fb4614..48e74bdd564 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -239,3 +239,49 @@ CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; +CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk)); +INSERT INTO t1 VALUES (1,'foo','bar'); +ALTER TABLE t1 ADD KEY (b(64)); +ALTER TABLE t1 ADD UNIQUE (b(165)); +ALTER TABLE t1 ADD KEY (b(1000)); +ALTER TABLE t1 ADD KEY (b(500)); +ALTER TABLE t1 ADD UNIQUE (a,b); +ALTER TABLE t1 ADD UNIQUE (b(95)); +ALTER TABLE t1 ADD KEY (b(30)); +ALTER TABLE t1 ADD UNIQUE (b(20)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `a` char(4) DEFAULT NULL, + `b` blob NOT NULL, + PRIMARY KEY (`pk`), + UNIQUE KEY `b_2` (`b`(165)), + UNIQUE KEY `b_5` (`b`(95)), + UNIQUE KEY `b_7` (`b`(20)), + UNIQUE KEY `a` (`a`,`b`) USING HASH, + KEY `b` (`b`(64)), + KEY `b_3` (`b`(1000)), + KEY `b_4` (`b`(500)), + KEY `b_6` (`b`(30)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD UNIQUE (b); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `pk` int(11) NOT NULL, + `a` char(4) DEFAULT NULL, + `b` blob NOT NULL, + PRIMARY KEY (`pk`), + UNIQUE KEY `b_2` (`b`(165)), + UNIQUE KEY `b_5` (`b`(95)), + UNIQUE KEY `b_7` (`b`(20)), + UNIQUE KEY `a` (`a`,`b`) USING HASH, + UNIQUE KEY `b_8` (`b`) USING HASH, + KEY `b` (`b`(64)), + KEY `b_3` (`b`(1000)), + KEY `b_4` (`b`(500)), + KEY `b_6` (`b`(30)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +ALTER TABLE t1 FORCE; +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index ed0daee426f..11b1c4f09b6 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/have_partition.inc # # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list @@ -269,8 +270,27 @@ drop table t1; # # MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map # ---source include/have_partition.inc CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; + +# +# MDEV-19049 Server crashes in check_duplicate_long_entry_key, ASAN stack-buffer-overflow in Field_blob::get_key_image +# +CREATE TABLE t1 (pk INT, a CHAR(4), b BLOB NOT NULL, PRIMARY KEY(pk)); +INSERT INTO t1 VALUES (1,'foo','bar'); + +ALTER TABLE t1 ADD KEY (b(64)); +ALTER TABLE t1 ADD UNIQUE (b(165)); +ALTER TABLE t1 ADD KEY (b(1000)); +ALTER TABLE t1 ADD KEY (b(500)); +ALTER TABLE t1 ADD UNIQUE (a,b); +ALTER TABLE t1 ADD UNIQUE (b(95)); +ALTER TABLE t1 ADD KEY (b(30)); +ALTER TABLE t1 ADD UNIQUE (b(20)); +show create table t1; +ALTER TABLE t1 ADD UNIQUE (b); +show create table t1; +ALTER TABLE t1 FORCE; +DROP TABLE t1; diff --git a/mysql-test/main/long_unique_innodb.result b/mysql-test/main/long_unique_innodb.result index cb8c3ea4858..135bb0808cc 100644 --- a/mysql-test/main/long_unique_innodb.result +++ b/mysql-test/main/long_unique_innodb.result @@ -9,8 +9,8 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) USING HASH, - UNIQUE KEY `c` (`c`) + UNIQUE KEY `c` (`c`), + UNIQUE KEY `a` (`a`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; #test for concurrent insert of long unique in innodb diff --git a/mysql-test/main/long_unique_update.result b/mysql-test/main/long_unique_update.result index 60a4fb46558..b508583f47c 100644 --- a/mysql-test/main/long_unique_update.result +++ b/mysql-test/main/long_unique_update.result @@ -71,8 +71,8 @@ create table t1 (a int primary key, b blob unique , c int unique ); show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES BTREE +t1 0 b 1 b A NULL NULL NULL YES HASH insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7); select * from t1 limit 3; a b c @@ -220,18 +220,18 @@ t1 CREATE TABLE `t1` ( `f` int(11) DEFAULT NULL, `g` text DEFAULT NULL, PRIMARY KEY (`a`), - UNIQUE KEY `b` (`b`,`c`) USING HASH, UNIQUE KEY `b_2` (`b`,`f`), + UNIQUE KEY `b` (`b`,`c`) USING HASH, UNIQUE KEY `e` (`e`,`g`) USING HASH, UNIQUE KEY `a` (`a`,`b`,`c`,`d`,`e`,`f`,`g`) USING HASH ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a A 0 NULL NULL BTREE -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 c A NULL NULL NULL YES HASH t1 0 b_2 1 b A NULL NULL NULL YES BTREE t1 0 b_2 2 f A NULL NULL NULL YES BTREE +t1 0 b 1 b A NULL NULL NULL YES HASH +t1 0 b 2 c A NULL NULL NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES HASH t1 0 e 2 g A NULL NULL NULL YES HASH t1 0 a 1 a A NULL NULL NULL HASH diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ad62ecc1103..c755a74e174 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2773,6 +2773,7 @@ bool quick_rm_table(THD *thd, handlerton *base, const LEX_CSTRING *db, - UNIQUE keys where all column are NOT NULL - UNIQUE keys that don't contain partial segments - Other UNIQUE keys + - LONG UNIQUE keys - Normal keys - Fulltext keys @@ -2796,6 +2797,14 @@ static int sort_keys(KEY *a, KEY *b) { if (!(b_flags & HA_NOSAME)) return -1; + /* + Long Unique keys should always be last unique key. + Before this patch they used to change order wrt to partial keys (MDEV-19049) + */ + if (a->algorithm == HA_KEY_ALG_LONG_HASH) + return 1; + if (b->algorithm == HA_KEY_ALG_LONG_HASH) + return -1; if ((a_flags ^ b_flags) & HA_NULL_PART_KEY) { /* Sort NOT NULL keys before other keys */

1 0

[Commits] 4dc2b6b7180: MDEV-18479, post fix after merge to 10.1
by Sergei Petrunia 31 May '19

31 May '19

revision-id: 4dc2b6b7180991e27c5a7d9cad1273791c14a6b5 (mariadb-10.1.39-53-g4dc2b6b7180) parent(s): a8abbeb66376bb990da685c9492abec8b4d623f3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-05-31 12:47:15 +0300 message: MDEV-18479, post fix after merge to 10.1 Make the EXPLAIN [FORMAT=JSON] print very large #rows values properly, as unsigned int64. --- mysql-test/r/derived_view.result | 14 +++++++------- sql/my_json_writer.cc | 7 +++++++ sql/my_json_writer.h | 1 + sql/sql_explain.cc | 8 ++++---- 4 files changed, 19 insertions(+), 11 deletions(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 4320aec430e..f3c7c66a240 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2953,13 +2953,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived7> ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived8> ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 72b000df639..22c66ba7a34 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -129,6 +129,13 @@ void Json_writer::add_ll(longlong val) add_unquoted_str(buf); } +void Json_writer::add_ull(ulonglong val) +{ + char buf[64]; + my_snprintf(buf, sizeof(buf), "%llu", val); + add_unquoted_str(buf); +} + /* Add a memory size, printing in Kb, Kb, Gb if necessary */ void Json_writer::add_size(longlong val) diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index 349a1f380da..ffee6db4c03 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -108,6 +108,7 @@ class Json_writer void add_str(const String &str); void add_ll(longlong val); + void add_ull(ulonglong val); void add_size(longlong val); void add_double(double val); void add_bool(bool val); diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index c8576136069..2fa4b2cb3ae 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1223,7 +1223,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai if (rows_set) { item_list.push_back(new (mem_root) - Item_int(thd, (longlong) (ulonglong) rows, + Item_int(thd, (ulonglong) rows, MY_INT64_NUM_DECIMAL_DIGITS), mem_root); } @@ -1601,7 +1601,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, /* `rows` */ if (rows_set) - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); /* `r_rows` */ if (is_analyze) @@ -2239,7 +2239,7 @@ void Explain_update::print_explain_json(Explain_query *query, } /* `rows` */ - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); if (mrr_type.length() != 0) @@ -2268,7 +2268,7 @@ void Explain_update::print_explain_json(Explain_query *query, r_rows= 0; r_filtered= buf_tracker.get_filtered_after_where() * 100.0; } - writer->add_member("r_rows").add_ll(r_rows); + writer->add_member("r_rows").add_ull(r_rows); writer->add_member("r_filtered").add_double(r_filtered); } else /* Not doing buffering */

1 0

Re: [Commits] MDEV-19653 Add class Sql_cmd_create_table
by Sergey Vojtovich 31 May '19

31 May '19

Hi Alexander, > > commit d070c68861670c0a556e3f29cb0b3d5fdd87a8c8 > Author: Alexander Barkov <bar(a)mariadb.com> > Date: Fri May 31 09:29:35 2019 +0400 > > MDEV-19653 Add class Sql_cmd_create_table > > diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc > index e37d547..dfd421f 100644 > --- a/sql/sql_alter.cc > +++ b/sql/sql_alter.cc > @@ -193,6 +193,20 @@ bool Sql_cmd_alter_table::execute(THD *thd) > SELECT_LEX *select_lex= &lex->select_lex; > /* first table of first SELECT_LEX */ > TABLE_LIST *first_table= (TABLE_LIST*) select_lex->table_list.first; > + > + bool used_engine= lex->create_info.used_fields & HA_CREATE_USED_ENGINE; > + DBUG_ASSERT(m_storage_engine_name.str || !used_engine); I think it is possible to specify ALTER TABLE t1 ENGINE='' We should not crash in this case. > + if (used_engine && > + thd->resolve_storage_engine(&lex->create_info.db_type, > + m_storage_engine_name, > + lex->create_info.tmp_table())) > + return true; > + > + if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) && > + !lex->create_info.db_type) > + lex->create_info.used_fields&= ~HA_CREATE_USED_ENGINE; > + > + Why not if (lex->create_info.used_fields & HA_CREATE_USED_ENGINE) { DBUG_ASSERT(m_storage_engine_name.str); if (thd->resolve_storage_engine(&lex->create_info.db_type, m_storage_engine_name, lex->create_info.tmp_table())) return true; if (!lex->create_info.db_type) lex->create_info.used_fields&= ~HA_CREATE_USED_ENGINE; } > /* > Code in mysql_alter_table() may modify its HA_CREATE_INFO argument, > so we have to use a copy of this structure to make execution > diff --git a/sql/sql_alter.h b/sql/sql_alter.h > index a503837..66c20fc 100644 > --- a/sql/sql_alter.h > +++ b/sql/sql_alter.h > @@ -385,7 +385,8 @@ class Sql_cmd_common_alter_table : public Sql_cmd > Sql_cmd_alter_table represents the generic ALTER TABLE statement. > @todo move Alter_info and other ALTER specific structures from Lex here. > */ > -class Sql_cmd_alter_table : public Sql_cmd_common_alter_table > +class Sql_cmd_alter_table : public Sql_cmd_common_alter_table, > + public Sql_cmd_options_table_dml s/dml/ddl? > { > public: > /** > @@ -397,6 +398,8 @@ class Sql_cmd_alter_table : public Sql_cmd_common_alter_table > ~Sql_cmd_alter_table() > {} > > + Sql_cmd_options_table_dml *options_table_dml() { return this; } > + > bool execute(THD *thd); > }; > > @@ -423,4 +426,27 @@ class Sql_cmd_discard_import_tablespace : public Sql_cmd_common_alter_table > const enum_tablespace_op_type m_tablespace_op; > }; > > + > +class Sql_cmd_create_table: public Sql_cmd, > + public Sql_cmd_options_table_dml > +{ > +public: > + Sql_cmd_create_table() > + {} > + > + ~Sql_cmd_create_table() > + { } > + > + enum_sql_command sql_command_code() const > + { > + return SQLCOM_ALTER_TABLE; > + } Did you mean SQLCOM_CREATE_TABLE? > + > + Sql_cmd_options_table_dml *options_table_dml() { return this; } > + > + bool execute(THD *thd); > + > +}; > + > + > #endif It doesn't feels like a proper place for Sql_cmd_create_table. May be move either to sql_table.h or sql_cmd.h? Why do you need empty constructor/destructor? > diff --git a/sql/sql_class.cc b/sql/sql_class.cc > index 8fabcd5..e107f57 100644 > --- a/sql/sql_class.cc > +++ b/sql/sql_class.cc > @@ -6151,6 +6151,32 @@ int THD::decide_logging_format(TABLE_LIST *tables) > > #ifndef MYSQL_CLIENT > > +bool THD::resolve_storage_engine(handlerton **ha, const LEX_CSTRING &name, > + bool tmp_table) I don't feel like THD is the right home for this. Can we have it as a regular function in handler.cc. Like ha_resolve_by_name_with_error(). > +{ > + LEX_STRING tmp_name= {(char*) name.str, name.length}; const_cast<char*>(name.str) precisely. > + plugin_ref plugin= ha_resolve_by_name(this, &tmp_name, tmp_table); > + > + if (plugin) May be if (plugin_ref plugin= ha_resolve_by_name(this, &tmp_name, tmp_table)) > + { > + *ha= plugin_hton(plugin); > + return false; > + } > + > + *ha= NULL; > + if (variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) > + { > + my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(0), name.str); > + return true; > + } > + push_warning_printf(this, Sql_condition::WARN_LEVEL_WARN, > + ER_UNKNOWN_STORAGE_ENGINE, > + ER_THD(this, ER_UNKNOWN_STORAGE_ENGINE), > + name.str); > + return false; > +} > + > + > /* > Template member function for ensuring that there is an rows log > event of the apropriate type before proceeding. > diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h > index 9583e01..3f6aa4d 100644 > --- a/sql/sql_cmd.h > +++ b/sql/sql_cmd.h > @@ -102,6 +102,23 @@ enum enum_sql_command { > SQLCOM_END > }; > > + > +// We should eventually move LEX::create_info here > +class Sql_cmd_options_table_dml > +{ > +protected: > + LEX_CSTRING m_storage_engine_name; > +public: > + Sql_cmd_options_table_dml() > + :m_storage_engine_name({0,0}) > + { } Not sure if we need initialize this. In fact it'd be nice to let ASAN catch bad accesses. > + void set_storage_engine_name(const LEX_CSTRING &name) > + { > + m_storage_engine_name= name; > + } > +}; > + > + > /** > @class Sql_cmd - Representation of an SQL command. > > @@ -145,6 +162,8 @@ class Sql_cmd : public Sql_alloc > */ > virtual bool execute(THD *thd) = 0; > > + virtual Sql_cmd_options_table_dml *options_table_dml() { return NULL; } > + > protected: > Sql_cmd() > {} > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index 9cb65e8..c8b9219 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -5699,6 +5431,7 @@ mysql_execute_command(THD *thd) > case SQLCOM_OPTIMIZE: > case SQLCOM_REPAIR: > case SQLCOM_TRUNCATE: > + case SQLCOM_CREATE_TABLE: > case SQLCOM_ALTER_TABLE: > DBUG_ASSERT(first_table == all_tables && first_table != 0); > /* fall through */ This assertion is already duplicated by Sql_cmd_create_table::execute(). I wonder if it'd make sense to move SQLCOM_CREATE_TABLE forward? > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index ca78c01..c4c3ef8 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -10116,3 +10116,304 @@ bool check_engine(THD *thd, const char *db_name, > > DBUG_RETURN(false); > } > + > + > +bool Sql_cmd_create_table::execute(THD *thd) > +{ > + DBUG_ENTER("Sql_cmd_create_table::execute"); > + LEX *lex= thd->lex; > + TABLE_LIST *all_tables= lex->query_tables; > + SELECT_LEX *select_lex= &lex->select_lex; > + TABLE_LIST *first_table= select_lex->table_list.first; > + DBUG_ASSERT(first_table == all_tables && first_table != 0); > + bool link_to_local; > + TABLE_LIST *create_table= first_table; > + TABLE_LIST *select_tables= lex->create_last_non_select_table->next_global; > + /* most outer SELECT_LEX_UNIT of query */ > + SELECT_LEX_UNIT *unit= &lex->unit; > + int res= 0; > + > + bool used_engine= lex->create_info.used_fields & HA_CREATE_USED_ENGINE; > + DBUG_ASSERT(m_storage_engine_name.length || !used_engine); > + if (used_engine) > + { > + if (thd->resolve_storage_engine(&lex->create_info.db_type, > + m_storage_engine_name, > + lex->create_info.tmp_table())) > + DBUG_RETURN(true); // Engine not found, substitution is not allowed > + > + if (!lex->create_info.db_type) > + { > + lex->create_info.use_default_db_type(thd); > + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > + ER_WARN_USING_OTHER_HANDLER, > + ER_THD(thd, ER_WARN_USING_OTHER_HANDLER), > + hton_name(lex->create_info.db_type)->str, > + create_table->table_name); > + } > + } It should probably be joined with "If no engine type was given..." condition. Then you won't need used_engine variable. > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index 59cc73d..7de5bef 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -2454,6 +2454,8 @@ create: > create_or_replace opt_temporary TABLE_SYM opt_if_not_exists table_ident > { > LEX *lex= thd->lex; > + if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_create_table())) > + MYSQL_YYABORT; C++ allows to omit parenthesis if constructor has no parameters. > @@ -5515,10 +5507,19 @@ create_table_options: > ; > > create_table_option: > - ENGINE_SYM opt_equal storage_engines > + ENGINE_SYM opt_equal ident_or_text > { > - Lex->create_info.db_type= $3; > - Lex->create_info.used_fields|= HA_CREATE_USED_ENGINE; > + LEX *lex= Lex; > + if (!lex->m_sql_cmd) > + { > + DBUG_ASSERT(lex->sql_command == SQLCOM_ALTER_TABLE); > + if (!(lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_table())) > + MYSQL_YYABORT; > + } This looks ugly, but I guess we cannot fix it with low effort. > + Sql_cmd_options_table_dml *opt= lex->m_sql_cmd->options_table_dml(); > + DBUG_ASSERT(opt); // Expect a proper Sql_cmd Are there any other options to get Sql_cmd_options_table_dml here? > + opt->set_storage_engine_name({$3.str, $3.length}); > + lex->create_info.used_fields|= HA_CREATE_USED_ENGINE; > } > | MAX_ROWS opt_equal ulonglong_num > { > @@ -5783,21 +5784,10 @@ default_collation: > storage_engines: > ident_or_text > { > - plugin_ref plugin= ha_resolve_by_name(thd, &$1, > - thd->lex->create_info.tmp_table()); > - > - if (plugin) > - $$= plugin_hton(plugin); > - else > - { > - if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) > - my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); > - $$= 0; > - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, > - ER_UNKNOWN_STORAGE_ENGINE, > - ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE), > - $1.str); > - } > + LEX_CSTRING tmp= {$1.str, $1.length}; > + if (thd->resolve_storage_engine(&$$, tmp, > + thd->lex->create_info.tmp_table())) > + MYSQL_YYABORT; I guess you could avoid temporary variable here. Regards, Sergey

1 0

Re: [Commits] b97452a6204: MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
by Sergey Vojtovich 30 May '19

30 May '19

Hi Sergei, On Fri, May 24, 2019 at 06:06:43PM +0200, Sergei Golubchik wrote: > revision-id: b97452a6204 (mariadb-5.5.64-11-gb97452a6204) > parent(s): 7fceef405ae > author: Sergei Golubchik <serg(a)mariadb.com> > committer: Sergei Golubchik <serg(a)mariadb.com> > timestamp: 2019-05-24 18:00:34 +0200 > message: > > MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 ...skip... > diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test > index 14c5574f61c..a3b64ffde8c 100644 > --- a/mysql-test/t/multi_update.test > +++ b/mysql-test/t/multi_update.test > @@ -1081,6 +1081,18 @@ select * from t2; > drop table t1,t2, t3; > drop user foo; > > +# > +# MDEV-19521 Update Table Fails with Trigger and Stored Function > +# > +create table t1 (a int, b varchar(50), c varchar(50)); > +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); > +create function f1() returns varchar(50) return 'result'; > +create trigger tr before update on t1 for each row set new.c = (select f1()); > +create table t2 select a, b from t1; > +update t1 join t2 using (a) set t1.b = t2.b; > +drop table t1, t2; > +drop function f1; I don't completely understand why this test was failing. It doesn't request backoff action, right? Or is this patch fixing more than just backoff retry? > diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test > new file mode 100644 > index 00000000000..ccd7791f029 > --- /dev/null > +++ b/mysql-test/t/multi_update_debug.test > @@ -0,0 +1,30 @@ > +# > +# test MDL backoff-and-retry during multi-update > +# > +source include/have_debug_sync.inc; > +create table t1 (a int, b int); > +create table t2 (c int, d int); > +insert t1 values (1,2),(3,4); > +insert t2 values (5,6),(7,8); > +create table t0 (x int); > +insert t0 values (11), (22); > +create trigger tr1 before update on t1 for each row insert t0 values (new.b); > + > +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; > +send update t1 join t2 on (a=c+4) set b=d; > + > +connect con1, localhost, root; You should wait here until update reaches open_tables_after_open_and_process_table. Also you should be able to do something like set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; So that you don't need con2 at all. ...skip... > diff --git a/sql/sql_base.h b/sql/sql_base.h > index ea92e880db7..439052a28f5 100644 > --- a/sql/sql_base.h > +++ b/sql/sql_base.h > @@ -426,6 +426,7 @@ class Prelocking_strategy > public: > virtual ~Prelocking_strategy() { } > > + virtual void reset(THD *thd) { }; > virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx, > Sroutine_hash_entry *rt, sp_head *sp, > bool *need_prelocking) = 0; > @@ -433,6 +434,7 @@ class Prelocking_strategy > TABLE_LIST *table_list, bool *need_prelocking) = 0; > virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx, > TABLE_LIST *table_list, bool *need_prelocking)= 0; > + virtual bool handle_end(THD *thd) { return 0; }; > }; May be just end()? It looks inconsistent with reset(). Also end() sounds multi-update specific. Isn't it "next nesting level dive"? May be level_end()? Or next_level()? I wonder if handle_table() alone can handle this? Or do we need to open all tables before we can tell which ones are go ing to be updated? ...skip... > +int mysql_multi_update_prepare(THD *thd) > +{ > + LEX *lex= thd->lex; > + TABLE_LIST *table_list= lex->query_tables; > + TABLE_LIST *tl; > + Multiupdate_prelocking_strategy prelocking_strategy; > + uint table_count= lex->table_count; Bad spacing. > + bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI); Do you really need this variable? > + DBUG_ENTER("mysql_multi_update_prepare"); > + > + /* > + Open tables and create derived ones, but do not lock and fill them yet. > + > + During prepare phase acquire only S metadata locks instead of SW locks to > + keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE > + and global read lock. > + */ > + if (original_multiupdate) > + { > + if (open_tables(thd, &table_list, &table_count, > + thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, > + &prelocking_strategy)) > + DBUG_RETURN(TRUE); Wrong indentation. Do we ever enter this branch with is_stmt_prepare()? > + } > + else > + { > + /* following need for prepared statements, to run next time multi-update */ > + thd->lex->sql_command= SQLCOM_UPDATE_MULTI; > + prelocking_strategy.reset(thd); > + if (prelocking_strategy.handle_end(thd)) > DBUG_RETURN(TRUE); > } > In 10.2 we got MDL_SHARED_READ_ONLY, which is acquired by LOCK TABLES ... READ for InnoDB. I guess it is going to be incompatible with multi update tables opened for reading. I don't see this approach making fix for this issue impossible, but still worth to double check. Looks good otherwise. Regards, Sergey

2 3