- commits - lists.mariadb.org

[Commits] f264498: MDEV-17017 Explain for query using derived table specified with a table
by IgorBabaev 19 Aug '18

19 Aug '18

revision-id: f26449872153d4b80ad0d99ee2f79a4eca701fd7 (mariadb-10.3.7-138-gf264498) parent(s): 34c7222c088ded8f1192142db935c000b2ba6b8d author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-18 22:40:58 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows If the specification of a derived table contained a table value constructor then the cardinality of the constructor was not passed to the derived table and as a result the optimizer incorrectly estimated the number of rows in the derived table. This could lead to choosing inefficient execution plans. --- mysql-test/main/opt_tvc.result | 30 ++++++++-------- mysql-test/main/opt_tvc.test | 2 +- mysql-test/main/range.result | 6 ++-- mysql-test/main/range.test | 4 +++ mysql-test/main/range_mrr_icp.result | 6 ++-- mysql-test/main/table_value_constr.result | 57 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 29 ++++++++++++++++ sql/sql_tvc.h | 2 ++ sql/sql_union.cc | 2 ++ 9 files changed, 115 insertions(+), 23 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..fdbd932 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -486,34 +486,32 @@ a b deallocate prepare stmt; # use inside out access from tvc rows set @@in_predicate_conversion_threshold= default; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4) set @@in_predicate_conversion_threshold= 2; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -540,9 +538,9 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) @@ -570,7 +568,7 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -578,7 +576,7 @@ explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -592,7 +590,7 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -608,7 +606,7 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) @@ -632,7 +630,7 @@ i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index d5c9a5c..2d06a0a 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -255,7 +255,7 @@ deallocate prepare stmt; --echo # use inside out access from tvc rows -let $query= select * from t3 where a in (1,4,10); +let $query= select * from t3 where a in (1,4); set @@in_predicate_conversion_threshold= default; eval $query; eval explain extended $query; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b9..cbf9d5b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1052,6 +1052,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1062,15 +1063,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 36e0e32..43b5b18 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -870,6 +870,8 @@ insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; + set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; set @a=concat(@a, ')'); @@ -884,6 +886,8 @@ execute stmt1; prepare stmt1 from @a; execute stmt1; +set in_predicate_conversion_threshold= default; + drop table t1, t2; # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 629d183..483957f 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1054,6 +1054,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1064,15 +1065,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..b0b0fa8 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2097,3 +2097,60 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..eb30f00 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,32 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-17017: TVC in derived table +--echo # + +create table t1 (a int); +insert into t1 values (9), (3), (2); + +let $q1= +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +eval $q1; +eval explain $q1; + +let $q2= +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +eval $q2; +eval explain $q2; + +let $q3= +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +eval $q3; +eval explain $q3; + +let $q4= +select * from (values (7), (5), (8), (1) union select * from t1) t; +eval $q4; +eval explain $q4; + +drop table t1; diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 420311c..128cc88 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -50,6 +50,8 @@ class table_value_constr : public Sql_alloc have_query_plan(QEP_NOT_PRESENT_YET), explain(0), select_options(select_options_arg) { }; + + ha_rows get_records() { return lists_of_values.elements; } bool prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tmp_result, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 1203532..c8bf9bd 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1321,6 +1321,8 @@ bool st_select_lex_unit::optimize() thd->lex->current_select= lex_select_save; DBUG_RETURN(TRUE); } + if (derived) + sl->increase_derived_records(sl->tvc->get_records()); continue; } thd->lex->current_select= sl;

1 0

862a977: MDEV-17011 “condition_pushdown_for_derived” optimization not used when
by IgorBabaev 18 Aug '18

18 Aug '18

revision-id: 862a97749d5a36218ba4d55c26eef30cd7b2e3cb (mariadb-10.2.16-93-g862a977) parent(s): 4eac5df3fcebb1adf52e33d9d88dc05bc1e339ce author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-17 19:29:01 -0700 message: MDEV-17011 “condition_pushdown_for_derived” optimization not used when using INSERT INTO This patch allows condition pushdown into a materialized derived / view when this table is used in INSERT SELECT, multi-table UPDATE and multi-table DELETE. --- mysql-test/r/derived_cond_pushdown.result | 143 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 44 +++++++++ sql/sql_select.cc | 3 +- 3 files changed, 188 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 22b81ef..839ae58 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9874,3 +9874,146 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-17011: condition pushdown into materialized derived used +# in INSERT SELECT, multi-table UPDATE and DELETE +# +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES +(3), (7), (1), (4), (1); +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; +EXPLAIN FORMAT=JSON INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a <= 2", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a <= 2" + } + } + } + } + } + } + } +} +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +SELECT * FROM t3; +a b +1 2 +2 2 +EXPLAIN FORMAT=JSON UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "8", + "used_key_parts": ["c"], + "ref": ["test.t2.a"], + "rows": 2, + "filtered": 100, + "attached_condition": "t2.a = t.c and t.a >= 3", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a >= 3" + } + } + } + } + } + } + } +} +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +SELECT * FROM t2; +a +3 +7 +11 +4 +11 +EXPLAIN FORMAT=JSON DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a = 2 and t2.a = t.c + 9", + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + } + } + } +} +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +SELECT * FROM t2; +a +3 +7 +4 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d9d767f..f85b301 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1920,3 +1920,47 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-17011: condition pushdown into materialized derived used +--echo # in INSERT SELECT, multi-table UPDATE and DELETE +--echo # + +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); + +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES + (3), (7), (1), (4), (1); + +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; + +let $q1= +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; + +eval EXPLAIN FORMAT=JSON $q1; +eval $q1; + +SELECT * FROM t3; + +let $q2= +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 + WHERE t2.a= t.c and t.a>=3; + +eval EXPLAIN FORMAT=JSON $q2; +eval $q2; + +SELECT * FROM t2; + +let $q3= +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t + WHERE t2.a= t.c+9 and t.a=2; + +eval EXPLAIN FORMAT=JSON $q3; +eval $q3; + +SELECT * FROM t2; + +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 643ddfe..b94ca49 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1375,8 +1375,7 @@ JOIN::optimize_inner() DBUG_RETURN(1); } - if (thd->lex->sql_command == SQLCOM_SELECT && - optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) + if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { TABLE_LIST *tbl; List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables);

1 0

[Commits] ed0b27a: MDEV-16934 Query with very large IN clause lists runs slowly
by IgorBabaev 17 Aug '18

17 Aug '18

revision-id: ed0b27a1577b61fee7031a6c3e33b8d658c0f45b (mariadb-10.2.16-50-ged0b27a) parent(s): 3c141e319ab29afdfe843553da385fe5d981906e author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-17 14:27:42 -0700 message: MDEV-16934 Query with very large IN clause lists runs slowly This patch introduces support for the system variable eq_range_index_dive_limit that existed in MySQL starting from 5.6. The variable sets a limit for index dives into equality ranges. Index dives are performed by optimizer to estimate the number of rows in range scans. Index dives usually provide good estimate but they are pretty expensive. To estimate the number of rows in equality ranges statistical data on indexes can be employed. Its usage gives not so good estimates but it's cheap. So if the number of equality dives required by an index scan exceeds the set limit no dives for equality ranges are performed by the optimizer for this index. As the new system variable is introduced in a stable version the default value for it is set to a special value meaning there is no limit for the number of index dives performed by the optimizer. The patch partially uses the MySQL code for WL 5957 'Statistics-based Range optimization for many ranges'. --- mysql-test/r/mysqld--help.result | 6 +++ mysql-test/r/range.result | 41 ++++++++++++++++ mysql-test/r/range_mrr_icp.result | 41 ++++++++++++++++ .../sys_vars/r/sysvars_server_embedded.result | 14 ++++++ .../sys_vars/r/sysvars_server_notembedded.result | 14 ++++++ mysql-test/t/range.test | 33 +++++++++++++ sql/multi_range_read.cc | 11 +++++ sql/opt_range.cc | 27 +++++++++++ sql/opt_range.h | 4 +- sql/opt_range_mrr.cc | 54 +++++++++++++++------- sql/sql_class.h | 1 + sql/sql_statistics.h | 2 +- sql/sys_vars.cc | 10 ++++ 13 files changed, 239 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 3b7d5e2..51ece33 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -197,6 +197,11 @@ The following specify which files/extra groups are read (specified before remain cache, etc) --enforce-storage-engine=name Force the use of a storage engine for new tables + --eq-range-index-dive-limit=# + The optimizer will use existing index statistics instead + of doing index dives for equality ranges if the number of + equality ranges for the index is larger than or equal to + this number. If set to 0, index dives are always used. --event-scheduler[=name] Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely @@ -1259,6 +1264,7 @@ encrypt-binlog FALSE encrypt-tmp-disk-tables FALSE encrypt-tmp-files FALSE enforce-storage-engine (No default value) +eq-range-index-dive-limit 0 event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 3a71d08..0c6be5e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -3002,5 +3002,46 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 799a299..93f414f 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3014,6 +3014,47 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index f7a5cf5..9d7fa10 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -765,6 +765,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 034195e..cada139 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -779,6 +779,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index ab95180..ed68cfb 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -2046,6 +2046,39 @@ set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; --echo # +--echo # MDEV-16934: using system variable eq_range_index_dive_limit +--echo # to reduce the number of index dives +--echo # + +create table t1 (a int, b varchar(31), index idx(a)); + +insert into t1 values + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; + +analyze table t1; + +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; + +let $q= +select * from t1 where a in (8, 15, 31, 1, 9); + +set eq_range_index_dive_limit=0; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=2; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=default; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 50918d8..94a96c2 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -17,6 +17,7 @@ #include <my_bit.h> #include "sql_select.h" #include "key.h" +#include "sql_statistics.h" /**************************************************************************** * Default MRR implementation (MRR to non-MRR converter) @@ -67,6 +68,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, /* Default MRR implementation doesn't need buffer */ *bufsz= 0; + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, + seq_init_param); + seq_it= seq->init(seq_init_param, n_ranges, *flags); while (!seq->next(seq_it, &range)) { @@ -87,8 +91,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, min_endp= range.start_key.length? &range.start_key : NULL; max_endp= range.end_key.length? &range.end_key : NULL; } + int keyparts_used= my_count_bits(range.start_key.keypart_map); if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) rows= 1; /* there can be at most one row */ + else if (use_statistics_for_eq_range && + !(range.range_flag & NULL_RANGE) && + (range.range_flag & EQ_RANGE) && + table->key_info[keyno].actual_rec_per_key(keyparts_used - 1) > 0.5) + rows= + (ha_rows) table->key_info[keyno].actual_rec_per_key(keyparts_used - 1); else { if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b011c01..c9c37a2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14616,6 +14616,33 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, } +/* Check whether the number for equality ranges exceeds the set threshold */ + +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) +{ + KEY_MULTI_RANGE range; + range_seq_t seq_it; + uint count = 0; + PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; + uint limit= param->thd->variables.eq_range_index_dive_limit; + + if (limit == 0) + { + /* 'Statistics instead of index dives' feature is turned off */ + return false; + } + seq_it= seq->init(seq_init_param, 0, 0); + while (!seq->next(seq_it, &range)) + { + if ((range.range_flag & EQ_RANGE) && !(range.range_flag & NULL_RANGE)) + { + if (++count >= limit) + return true; + } + } + return false; +} + #ifndef DBUG_OFF static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map, diff --git a/sql/opt_range.h b/sql/opt_range.h index c1f7079..6698c98 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -242,7 +242,7 @@ class SEL_ARG :public Sql_alloc Number of children of this element in the RB-tree, plus 1 for this element itself. */ - uint16 elements; + uint32 elements; /* Valid only for elements which are RB-tree roots: Number of times this RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by @@ -1664,6 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); + #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); #endif diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index ace6208..c7e4496 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -72,6 +72,7 @@ typedef struct st_sel_arg_range_seq range_seq_t sel_arg_range_seq_init(void *init_param, uint n_ranges, uint flags) { SEL_ARG_RANGE_SEQ *seq= (SEL_ARG_RANGE_SEQ*)init_param; + seq->param->range_count=0; seq->at_start= TRUE; seq->stack[0].key_tree= NULL; seq->stack[0].min_key= seq->param->min_key; @@ -272,25 +273,44 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) key_info= NULL; else key_info= &seq->param->table->key_info[seq->real_keyno]; - + /* - Conditions below: - (1) - range analysis is used for estimating condition selectivity - (2) - This is a unique key, and we have conditions for all its - user-defined key parts. - (3) - The table uses extended keys, this key covers all components, - and we have conditions for all key parts. + This is an equality range (keypart_0=X and ... and keypart_n=Z) if + (1) - There are no flags indicating open range (e.g., + "keypart_x > y") or GIS. + (2) - The lower bound and the upper bound of the range has the + same value (min_key == max_key). */ - if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && - (!key_info || // (1) - ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) - key_info->flags & HA_NOSAME) || // (2) - ((key_info->flags & HA_EXT_NOSAME) && // (3) - (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) - ) && - range->start_key.length == range->end_key.length && - !memcmp(seq->param->min_key,seq->param->max_key,range->start_key.length)) - range->range_flag= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + const uint is_open_range = + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | NEAR_MAX | GEOM_FLAG); + const bool is_eq_range_pred = + !(cur->min_key_flag & is_open_range) && // (1) + !(cur->max_key_flag & is_open_range) && // (1) + range->start_key.length == range->end_key.length && // (2) + !memcmp(seq->param->min_key, seq->param->max_key, // (2) + range->start_key.length); + + if (is_eq_range_pred) + { + range->range_flag = EQ_RANGE; + + /* + Conditions below: + (1) - Range analysis is used for estimating condition selectivity + (2) - This is a unique key, and we have conditions for all its + user-defined key parts. + (3) - The table uses extended keys, this key covers all components, + and we have conditions for all key parts. + */ + if ( + !key_info || // (1) + ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) + key_info->flags & HA_NOSAME) || // (2) + ((key_info->flags & HA_EXT_NOSAME) && // (3) + (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) + ) + range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + } if (seq->param->is_ror_scan) { diff --git a/sql/sql_class.h b/sql/sql_class.h index 6663d9e..d2f00b2d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -569,6 +569,7 @@ typedef struct system_variables ha_rows max_join_size; ha_rows expensive_subquery_limit; ulong auto_increment_increment, auto_increment_offset; + uint eq_range_index_dive_limit; ulong lock_wait_timeout; ulong join_cache_level; ulong max_allowed_packet; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..4ff85cd 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -21,7 +21,7 @@ enum enum_use_stat_tables_mode { NEVER, COMPLEMENTARY, - PEFERABLY, + PREFERABLY, } Use_stat_tables_mode; typedef diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index e6aec47..650b17a 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2578,6 +2578,16 @@ static Sys_var_ulong Sys_div_precincrement( SESSION_VAR(div_precincrement), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, DECIMAL_MAX_SCALE), DEFAULT(4), BLOCK_SIZE(1)); +static Sys_var_uint Sys_eq_range_index_dive_limit( + "eq_range_index_dive_limit", + "The optimizer will use existing index statistics instead of " + "doing index dives for equality ranges if the number of equality " + "ranges for the index is larger than or equal to this number. " + "If set to 0, index dives are always used.", + SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, UINT_MAX32), DEFAULT(0), + BLOCK_SIZE(1)); + static Sys_var_ulong Sys_range_alloc_block_size( "range_alloc_block_size", "Allocation block size for storing ranges during optimization",

1 0

[Commits] 6cc133df12b: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with
by Varun 17 Aug '18

17 Aug '18

revision-id: 6cc133df12b0805e53dfeba09a277ca4dc3fbd77 (mariadb-10.1.34-45-g6cc133df12b) parent(s): ef25cbea8ec8cbd23f281890ad60136707db9608 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-18 01:02:20 +0530 message: MDEV-16695: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with optimizer_use_condition_selectivity>=3 Selectivity analysis should be disabled for Geometrical columns for the case like geometric_field= string_constant. --- mysql-test/r/gis.result | 16 ++++++++++++++++ mysql-test/t/gis.test | 16 ++++++++++++++++ sql/opt_range.cc | 6 ++++++ 3 files changed, 38 insertions(+) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 76f4f6accdb..e12b4a0d0fb 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1861,3 +1861,19 @@ t2 CREATE TABLE `t2` ( `w2` int(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1,t2; +# +# MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +# optimizer_use_condition_selectivity>=3 +# +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +COUNT(*) +0 +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index ca43e8d4e2f..80edad21c81 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1553,3 +1553,19 @@ CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL); CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1; SHOW CREATE TABLE t2; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +--echo # optimizer_use_condition_selectivity>=3 +--echo # + +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 1e29efda0d1..2255c9a2fe6 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7607,6 +7607,12 @@ Item_bool_func::get_mm_parts(RANGE_OPT_PARAM *param, Field *field, if (field->table != param->table) DBUG_RETURN(0); + #ifdef HAVE_SPATIAL + if (type == Item_func::EQ_FUNC && field->type() == MYSQL_TYPE_GEOMETRY + && value->const_item() && value->type() == Item::STRING_ITEM) + DBUG_RETURN(NULL); + #endif + KEY_PART *key_part = param->key_parts; KEY_PART *end = param->key_parts_end; SEL_TREE *tree=0;

1 0

[Commits] f522fb9: MDEV-16934 Query with very large IN clause lists runs slowly
by IgorBabaev 17 Aug '18

17 Aug '18

revision-id: f522fb9f63f88195bccbbc0326a4a318cc2d3db0 (mariadb-10.2.16-50-gf522fb9) parent(s): 3c141e319ab29afdfe843553da385fe5d981906e author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-15 19:48:30 -0700 message: MDEV-16934 Query with very large IN clause lists runs slowly This patch introduces support for the system variable eq_range_index_dive_limit that existed in MySQL starting from 5.6. The variable sets a limit for index dives into equality ranges. Index dives are performed by optimizer to estimate the number of rows in range scans. Index dives usually provide good estimate but they are pretty expensive. To estimate the number of rows in equality ranges statistical data on indexes can be employed. Its usage gives not so good estimates but it's cheap. So if the number of equality dives required by an index scan exceeds the set limit no dives for equality ranges are performed by the optimizer for this index. As the new system variable is introduced in a stable version the default value for it is set to a special value meaning there is no limit for the number of index dives performed by the optimizer. The patch partially uses the MySQL code for WL 5957 'Statistics-based Range optimization for many ranges'. --- mysql-test/r/mysqld--help.result | 6 +++ mysql-test/r/range.result | 41 +++++++++++++++++ mysql-test/r/range_mrr_icp.result | 41 +++++++++++++++++ .../sys_vars/r/sysvars_server_embedded.result | 14 ++++++ .../sys_vars/r/sysvars_server_notembedded.result | 14 ++++++ mysql-test/t/range.test | 33 ++++++++++++++ sql/multi_range_read.cc | 11 +++++ sql/opt_range.cc | 27 +++++++++++ sql/opt_range.h | 4 +- sql/opt_range_mrr.cc | 53 +++++++++++++++------- sql/sql_class.h | 1 + sql/sql_statistics.h | 2 +- sql/sys_vars.cc | 10 ++++ 13 files changed, 238 insertions(+), 19 deletions(-) diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 3b7d5e2..51ece33 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -197,6 +197,11 @@ The following specify which files/extra groups are read (specified before remain cache, etc) --enforce-storage-engine=name Force the use of a storage engine for new tables + --eq-range-index-dive-limit=# + The optimizer will use existing index statistics instead + of doing index dives for equality ranges if the number of + equality ranges for the index is larger than or equal to + this number. If set to 0, index dives are always used. --event-scheduler[=name] Enable the event scheduler. Possible values are ON, OFF, and DISABLED (keep the event scheduler completely @@ -1259,6 +1264,7 @@ encrypt-binlog FALSE encrypt-tmp-disk-tables FALSE encrypt-tmp-files FALSE enforce-storage-engine (No default value) +eq-range-index-dive-limit 0 event-scheduler OFF expensive-subquery-limit 100 expire-logs-days 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 3a71d08..0c6be5e 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -3002,5 +3002,46 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +1 cc +9 zzzzz +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 799a299..93f414f 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3014,6 +3014,47 @@ deallocate prepare stmt; set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; # +# MDEV-16934: using system variable eq_range_index_dive_limit +# to reduce the number of index dives +# +create table t1 (a int, b varchar(31), index idx(a)); +insert into t1 values +(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), +(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; +rec_per_key +2 +set eq_range_index_dive_limit=0; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 7 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=2; +explain select * from t1 where a in (8, 15, 31, 1, 9); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition; Rowid-ordered scan +select * from t1 where a in (8, 15, 31, 1, 9); +a b +1 yy +1 bbb +9 zzzzz +1 cc +15 ffffzz +set eq_range_index_dive_limit=default; +drop table t1; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index f7a5cf5..9d7fa10 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -765,6 +765,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 034195e..cada139 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -779,6 +779,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME EQ_RANGE_INDEX_DIVE_LIMIT +SESSION_VALUE 0 +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to this number. If set to 0, index dives are always used. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME ERROR_COUNT SESSION_VALUE 0 GLOBAL_VALUE NULL diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index ab95180..ed68cfb 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -2046,6 +2046,39 @@ set optimizer_switch=@save_optimizer_switch; drop table t1,t2,t3; --echo # +--echo # MDEV-16934: using system variable eq_range_index_dive_limit +--echo # to reduce the number of index dives +--echo # + +create table t1 (a int, b varchar(31), index idx(a)); + +insert into t1 values + (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'), + (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff'); +insert into t1 select a+10, concat(b,'zz') from t1; +insert into t1 select a+15, concat(b,'yy') from t1; +insert into t1 select a+100, concat(b,'xx') from t1; + +analyze table t1; + +select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1; + +let $q= +select * from t1 where a in (8, 15, 31, 1, 9); + +set eq_range_index_dive_limit=0; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=2; +eval explain $q; +eval $q; + +set eq_range_index_dive_limit=default; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 50918d8..94a96c2 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -17,6 +17,7 @@ #include <my_bit.h> #include "sql_select.h" #include "key.h" +#include "sql_statistics.h" /**************************************************************************** * Default MRR implementation (MRR to non-MRR converter) @@ -67,6 +68,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, /* Default MRR implementation doesn't need buffer */ *bufsz= 0; + bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, + seq_init_param); + seq_it= seq->init(seq_init_param, n_ranges, *flags); while (!seq->next(seq_it, &range)) { @@ -87,8 +91,15 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, min_endp= range.start_key.length? &range.start_key : NULL; max_endp= range.end_key.length? &range.end_key : NULL; } + int keyparts_used= my_count_bits(range.start_key.keypart_map); if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) rows= 1; /* there can be at most one row */ + else if (use_statistics_for_eq_range && + !(range.range_flag & NULL_RANGE) && + (range.range_flag & EQ_RANGE) && + table->key_info[keyno].actual_rec_per_key(keyparts_used - 1) > 0.5) + rows= + (ha_rows) table->key_info[keyno].actual_rec_per_key(keyparts_used - 1); else { if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b011c01..c9c37a2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -14616,6 +14616,33 @@ void QUICK_GROUP_MIN_MAX_SELECT::add_keys_and_lengths(String *key_names, } +/* Check whether the number for equality ranges exceeds the set threshold */ + +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param) +{ + KEY_MULTI_RANGE range; + range_seq_t seq_it; + uint count = 0; + PARAM *param= ((SEL_ARG_RANGE_SEQ*) seq_init_param)->param; + uint limit= param->thd->variables.eq_range_index_dive_limit; + + if (limit == 0) + { + /* 'Statistics instead of index dives' feature is turned off */ + return false; + } + seq_it= seq->init(seq_init_param, 0, 0); + while (!seq->next(seq_it, &range)) + { + if ((range.range_flag & EQ_RANGE) && !(range.range_flag & NULL_RANGE)) + { + if (++count >= limit) + return true; + } + } + return false; +} + #ifndef DBUG_OFF static void print_sel_tree(PARAM *param, SEL_TREE *tree, key_map *tree_map, diff --git a/sql/opt_range.h b/sql/opt_range.h index c1f7079..6698c98 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -242,7 +242,7 @@ class SEL_ARG :public Sql_alloc Number of children of this element in the RB-tree, plus 1 for this element itself. */ - uint16 elements; + uint32 elements; /* Valid only for elements which are RB-tree roots: Number of times this RB-tree is referred to (it is referred by SEL_ARG::next_key_part or by @@ -1664,6 +1664,8 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item **cond); +bool eq_ranges_exceeds_limit(RANGE_SEQ_IF *seq, void *seq_init_param); + #ifdef WITH_PARTITION_STORAGE_ENGINE bool prune_partitions(THD *thd, TABLE *table, Item *pprune_cond); #endif diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index ace6208..7f80a3e 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -272,25 +272,44 @@ bool sel_arg_range_seq_next(range_seq_t rseq, KEY_MULTI_RANGE *range) key_info= NULL; else key_info= &seq->param->table->key_info[seq->real_keyno]; - + /* - Conditions below: - (1) - range analysis is used for estimating condition selectivity - (2) - This is a unique key, and we have conditions for all its - user-defined key parts. - (3) - The table uses extended keys, this key covers all components, - and we have conditions for all key parts. + This is an equality range (keypart_0=X and ... and keypart_n=Z) if + (1) - There are no flags indicating open range (e.g., + "keypart_x > y") or GIS. + (2) - The lower bound and the upper bound of the range has the + same value (min_key == max_key). */ - if (!(cur->min_key_flag & ~NULL_RANGE) && !cur->max_key_flag && - (!key_info || // (1) - ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) - key_info->flags & HA_NOSAME) || // (2) - ((key_info->flags & HA_EXT_NOSAME) && // (3) - (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) - ) && - range->start_key.length == range->end_key.length && - !memcmp(seq->param->min_key,seq->param->max_key,range->start_key.length)) - range->range_flag= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + const uint is_open_range = + (NO_MIN_RANGE | NO_MAX_RANGE | NEAR_MIN | NEAR_MAX | GEOM_FLAG); + const bool is_eq_range_pred = + !(cur->min_key_flag & is_open_range) && // (1) + !(cur->max_key_flag & is_open_range) && // (1) + range->start_key.length == range->end_key.length && // (2) + !memcmp(seq->param->min_key, seq->param->max_key, // (2) + range->start_key.length); + + if (is_eq_range_pred) + { + range->range_flag = EQ_RANGE; + + /* + Conditions below: + (1) - Range analysis is used for estimating condition selectivity + (2) - This is a unique key, and we have conditions for all its + user-defined key parts. + (3) - The table uses extended keys, this key covers all components, + and we have conditions for all key parts. + */ + if ( + !key_info || // (1) + ((uint)key_tree->part+1 == key_info->user_defined_key_parts && // (2) + key_info->flags & HA_NOSAME) || // (2) + ((key_info->flags & HA_EXT_NOSAME) && // (3) + (uint)key_tree->part+1 == key_info->ext_key_parts) // (3) + ) + range->range_flag |= UNIQUE_RANGE | (cur->min_key_flag & NULL_RANGE); + } if (seq->param->is_ror_scan) { diff --git a/sql/sql_class.h b/sql/sql_class.h index 6663d9e..d2f00b2d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -569,6 +569,7 @@ typedef struct system_variables ha_rows max_join_size; ha_rows expensive_subquery_limit; ulong auto_increment_increment, auto_increment_offset; + uint eq_range_index_dive_limit; ulong lock_wait_timeout; ulong join_cache_level; ulong max_allowed_packet; diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index f465838..4ff85cd 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -21,7 +21,7 @@ enum enum_use_stat_tables_mode { NEVER, COMPLEMENTARY, - PEFERABLY, + PREFERABLY, } Use_stat_tables_mode; typedef diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index e6aec47..650b17a 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2578,6 +2578,16 @@ static Sys_var_ulong Sys_div_precincrement( SESSION_VAR(div_precincrement), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, DECIMAL_MAX_SCALE), DEFAULT(4), BLOCK_SIZE(1)); +static Sys_var_uint Sys_eq_range_index_dive_limit( + "eq_range_index_dive_limit", + "The optimizer will use existing index statistics instead of " + "doing index dives for equality ranges if the number of equality " + "ranges for the index is larger than or equal to this number. " + "If set to 0, index dives are always used.", + SESSION_VAR(eq_range_index_dive_limit), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, UINT_MAX32), DEFAULT(0), + BLOCK_SIZE(1)); + static Sys_var_ulong Sys_range_alloc_block_size( "range_alloc_block_size", "Allocation block size for storing ranges during optimization",

2 1

[Commits] fe3739639c5: Fixed ASAN failure for the test main.func_misc.
by Varun 16 Aug '18

16 Aug '18

revision-id: fe3739639c587e4df425672924ed2b0aa5bd5480 (mariadb-10.3.6-106-gfe3739639c5) parent(s): ead9a34a3e934f607c2ea7a6c68f7f6d9d29b5bd author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-17 00:50:30 +0530 message: Fixed ASAN failure for the test main.func_misc. For Item name_const , we should never do typecast it to Item_field because we always expect it to be a constant value. So instead of checking the type() its better to introduce a function in the Item class get_item_field, which would return the item_field object for the item which have type of FIELD_ITEM --- sql/item.h | 2 ++ sql/sql_yacc.yy | 2 +- 2 files changed, 3 insertions(+), 1 deletion(-) diff --git a/sql/item.h b/sql/item.h index c013781f30f..6f1f70c3cc7 100644 --- a/sql/item.h +++ b/sql/item.h @@ -881,6 +881,7 @@ class Item: public Value_source, */ String *val_str() { return val_str(&str_value); } virtual Item_func *get_item_func() { return NULL; } + virtual Item_field *get_item_field() {return NULL;} const MY_LOCALE *locale_from_val_str(); @@ -3261,6 +3262,7 @@ class Item_field :public Item_ident, longlong val_int_endpoint(bool left_endp, bool *incl_endp); bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate); bool get_date_result(MYSQL_TIME *ltime,ulonglong fuzzydate); + Item_field* get_item_field() {return this;} bool is_null() { return field->is_null(); } void update_null_value(); void update_table_bitmaps() diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index cf894325ba5..1fb3041d523 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -297,7 +297,7 @@ LEX::set_system_variable(enum enum_var_type var_type, if (spcont && sysvar == Sys_autocommit_ptr) sphead->m_flags|= sp_head::HAS_SET_AUTOCOMMIT_STMT; - if (val && val->type() == Item::FIELD_ITEM && + if (val && val->get_item_field() && ((Item_field*)val)->table_name) { my_error(ER_WRONG_TYPE_FOR_VAR, MYF(0), sysvar->name.str);

1 0

[Commits] adaafcf5184: MDEV-16930: Crash when VALUES in derived table contains expressions
by Oleksandr Byelkin 16 Aug '18

16 Aug '18

revision-id: adaafcf5184375369c0c4b3ccb1e82e846024ef8 (mariadb-10.3.7-137-gadaafcf5184) parent(s): 8716bb3b72b7f5fed69b6dde413c2138f6d175b2 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-16 13:55:36 +0200 message: MDEV-16930: Crash when VALUES in derived table contains expressions Give names to the value constructor columns as in SELECT-list. --- mysql-test/main/table_value_constr.result | 22 ++++++++++++++++++++-- mysql-test/main/table_value_constr.test | 13 +++++++++++++ sql/sql_yacc.yy | 13 +++++++++---- 3 files changed, 42 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a0968932..5b7f167cbde 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -11,7 +11,7 @@ values (1,2), (3,4), (5.6,0); 3.0 4 5.6 0 values ("abc", "def"); -abc def +"abc" "def" abc def # UNION that uses VALUES structure(s) select 1,2 @@ -92,7 +92,7 @@ we q values ("ab", "cdf") union select "ab","cdf"; -ab cdf +"ab" "cdf" ab cdf values (1,2) union @@ -2097,3 +2097,21 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# +VALUES(1+1,2); +1+1 2 +2 2 +SELECT * FROM (VALUES(1+1,2)) t; +1+1 2 +2 2 +PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t"; +EXECUTE stmt; +1+1 2 +2 2 +EXECUTE stmt; +1+1 2 +2 2 +DEALLOCATE PREPARE stmt; +# End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59f829..9908f8e61f1 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,16 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # + +VALUES(1+1,2); +SELECT * FROM (VALUES(1+1,2)) t; +PREPARE stmt FROM "SELECT * FROM (VALUES(1+1,2)) t"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # End of 10.3 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f915895a260..20cea4959eb 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13306,15 +13306,20 @@ opt_values: ; values: - values ',' expr_or_default + values ',' remember_name expr_or_default remember_end { - if (unlikely(Lex->insert_list->push_back($3, thd->mem_root))) + if (unlikely(Lex->insert_list->push_back($4, thd->mem_root))) MYSQL_YYABORT; + // give some name in case of using in table value constuctor (TVC) + $4->set_name(thd, $3, (uint) ($5 - $3), thd->charset()); + } - | expr_or_default + | remember_name expr_or_default remember_end { - if (unlikely(Lex->insert_list->push_back($1, thd->mem_root))) + if (unlikely(Lex->insert_list->push_back($2, thd->mem_root))) MYSQL_YYABORT; + // give some name in case of using in table value constuctor (TVC) + $2->set_name(thd, $1, (uint) ($3 - $1), thd->charset()); } ;

1 0

[Commits] bcc677bb726: Merge branch '5.5' into 10.0
by Oleksandr Byelkin 15 Aug '18

15 Aug '18

revision-id: bcc677bb7264db08b22284998706b44c377ed8ec (mariadb-10.0.36-7-gbcc677bb726) parent(s): b62ac161856570e9a0e92d17de1e3dd31d54410f 1b797e9e6308913c2472f3e04ad253e95a35d59f author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-15 16:48:13 +0200 message: Merge branch '5.5' into 10.0 mysql-test/r/derived_view.result | 6 +++--- mysql-test/r/func_isnull.result | 20 ++++++++++++++++++++ mysql-test/r/func_time.result | 3 +++ mysql-test/r/subselect_mat.result | 6 +++--- mysql-test/r/subselect_sj_mat.result | 6 +++--- mysql-test/suite/rpl/r/rpl_row_spatial.result | 14 ++++++++++++++ mysql-test/suite/rpl/t/rpl_row_spatial.test | 17 +++++++++++++++++ mysql-test/t/func_isnull.test | 16 ++++++++++++++++ mysql-test/t/func_time.test | 4 ++++ sql/item.cc | 2 +- sql/item_cmpfunc.cc | 13 +++++++++++++ sql/item_cmpfunc.h | 1 + sql/key.cc | 3 ++- sql/mysqld.cc | 22 +++++++++++----------- sql/protocol.cc | 8 ++++---- sql/sql_lex.cc | 2 +- sql/sql_list.h | 5 ++++- sql/sql_show.cc | 2 +- sql/sql_time.cc | 2 +- 19 files changed, 122 insertions(+), 30 deletions(-) diff --cc mysql-test/t/func_time.test index 1baded9fef7,361eff170fb..949e5cada2a --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@@ -1695,8 -1626,13 +1695,12 @@@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUI --enable_warnings DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); -SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); + # + # MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str + # + SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); --echo # --echo # End of 5.5 tests diff --cc sql/key.cc index 414c3392cff,7e5a3309b10..f900a1b4527 --- a/sql/key.cc +++ b/sql/key.cc @@@ -147,8 -144,9 +147,9 @@@ void key_copy(uchar *to_key, uchar *fro key_part->key_part_flag & HA_VAR_LENGTH_PART) { key_length-= HA_KEY_BLOB_LENGTH; - length= min(key_length, key_part->length); + length= min<uint>(key_length, key_part->length); - uint bytes= key_part->field->get_key_image(to_key, length, Field::itRAW); + uint bytes= key_part->field->get_key_image(to_key, length, + key_info->flags & HA_SPATIAL ? Field::itMBR : Field::itRAW); if (with_zerofill && bytes < length) bzero((char*) to_key + bytes, length - bytes); to_key+= HA_KEY_BLOB_LENGTH; diff --cc sql/mysqld.cc index 75d6cca7fda,aa749e5aaef..9643f0699d1 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@@ -6395,8 -5857,8 +6394,9 @@@ pthread_handler_t handle_connections_na /* Host is unknown */ thd->security_ctx->host= my_strdup(my_localhost, MYF(0)); create_new_thread(thd); + set_current_thd(0); } + LocalFree(saPipeSecurity.lpSecurityDescriptor); CloseHandle(connectOverlapped.hEvent); DBUG_LEAVE; decrement_handler_count();

1 0

[Commits] f48c07f: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
by psergey＠askmonty.org 15 Aug '18

15 Aug '18

revision-id: f48c07f0ecdb68a1347333648e5ab79728f38fd9 parent(s): a2c0376e08d80d7b7dad8713d1df334b2b81eff9 committer: Sergei Petrunia branch nick: 10.0 timestamp: 2018-08-15 15:21:37 +0300 message: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever it saw that one of SELECTs in the Subquery's UNION is degenerate. It ignored the fact that other parts of the UNION might not be inexpensive, including the case where pther parts of the UNION have no query plan yet. For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar') this would cause the query to be considered inexpensive when there is no query plan for the second part of the UNION, which in turn would cause the SELECT 'foo' to compute and free itself while still inside JOIN::optimize for that SELECT (See MDEV comment for full description). --- mysql-test/r/subselect_extra_no_semijoin.result | 19 +++++++++++++++ mysql-test/t/subselect_extra_no_semijoin.test | 31 ++++++++++++++++++++++++- sql/item_subselect.cc | 2 +- 3 files changed, 50 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index 79bca38..200682b 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -482,3 +482,22 @@ DROP TABLE t1,t2; set optimizer_switch= @tmp_subselect_extra_derived; set optimizer_switch= @subselect_extra_no_sj_tmp; set @optimizer_switch_for_subselect_extra_test=null; +# +# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +# +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +a +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +create table t2 (b int); +insert into t2 values (1),(2),(3); +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test index 8aba3dd..d8809c7 100644 --- a/mysql-test/t/subselect_extra_no_semijoin.test +++ b/mysql-test/t/subselect_extra_no_semijoin.test @@ -6,4 +6,33 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo set optimizer_switch= @subselect_extra_no_sj_tmp; -set @optimizer_switch_for_subselect_extra_test=null; \ No newline at end of file +set @optimizer_switch_for_subselect_extra_test=null; + +--echo # +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +--echo # + +## Using a separate client connection is easier than restoring state +connect(con1,localhost,root,,); + +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); + +create table t2 (b int); +insert into t2 values (1),(2),(3); + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); + +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; +disconnect con1; +connection default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index a8dfdff..14b2ccd 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -575,7 +575,7 @@ bool Item_subselect::is_expensive() */ if (cur_join->optimized && (cur_join->zero_result_cause || !cur_join->tables_list)) - return false; + continue; /* If a subquery is not optimized we cannot estimate its cost. A subquery is

1 0

[Commits] 7fc57734575: MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
by Sergey Petrunia 15 Aug '18

15 Aug '18

MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever it saw that one of SELECTs in the Subquery's UNION is degenerate. It ignored the fact that other parts of the UNION might not be inexpensive, including the case where pther parts of the UNION have no query plan yet. For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar') this would cause the query to be considered inexpensive when there is no query plan for the second part of the UNION, which in turn would cause the SELECT 'foo' to compute and free itself while still inside JOIN::optimize for that SELECT (See MDEV comment for full description). diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index 79bca38..200682b 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -482,3 +482,22 @@ DROP TABLE t1,t2; set optimizer_switch= @tmp_subselect_extra_derived; set optimizer_switch= @subselect_extra_no_sj_tmp; set @optimizer_switch_for_subselect_extra_test=null; +# +# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +# +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +a +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +create table t2 (b int); +insert into t2 values (1),(2),(3); +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); +ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<=' +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test index 8aba3dd..41593fb 100644 --- a/mysql-test/t/subselect_extra_no_semijoin.test +++ b/mysql-test/t/subselect_extra_no_semijoin.test @@ -6,4 +6,34 @@ set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo set optimizer_switch= @subselect_extra_no_sj_tmp; -set @optimizer_switch_for_subselect_extra_test=null; \ No newline at end of file +set @optimizer_switch_for_subselect_extra_test=null; + +--echo # +--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset +--echo # + +## Using a separate client connection is easier than restoring state +connect(con1,localhost,root,,); + +SET NAMES tis620; +set @tmp= @@global.slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000001; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM; +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo'); +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' ); + +create table t2 (b int); +insert into t2 values (1),(2),(3); + +--error ER_CANT_AGGREGATE_2COLLATIONS +SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2); + +drop table t1,t2; +SET GLOBAL slow_query_log=@tmp; +disconnect con1; +connection default; + diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index a8dfdff..14b2ccd 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -575,7 +575,7 @@ bool Item_subselect::is_expensive() */ if (cur_join->optimized && (cur_join->zero_result_cause || !cur_join->tables_list)) - return false; + continue; /* If a subquery is not optimized we cannot estimate its cost. A subquery is On Wed, Aug 15, 2018 at 02:16:51PM +0300, jan wrote: > revision-id: 148df3abde105671b5b33e87269298dec74a2dda (mariadb-10.1.35-12-g148df3abde1) > parent(s): 678bc584a123746e7db4355132d3cf7b6c56fc70 > author: Jan Lindström > committer: Jan Lindström > timestamp: 2018-08-15 14:16:21 +0300 > message: > > InnoDB adjustments > - Apply patch to xtradb > - Fix coding standard > - Fix test case result to match 10.1 > > --- > .../galera/r/galera_binlog_stmt_autoinc.result | 14 ----- > storage/innobase/handler/ha_innodb.cc | 67 ++++++++++------------ > storage/xtradb/handler/ha_innodb.cc | 57 ++++++++++++++---- > 3 files changed, 76 insertions(+), 62 deletions(-) > > diff --git a/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result > index 78b40228eb0..542bd156816 100644 > --- a/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result > +++ b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result > @@ -1,8 +1,4 @@ > -connection node_1; > -connection node_2; > -connection node_2; > SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; > -connection node_1; > SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; > CREATE TABLE t1 ( > i int(11) NOT NULL AUTO_INCREMENT, > @@ -20,7 +16,6 @@ i c > 3 dummy_text > 5 dummy_text > 7 dummy_text > -connection node_2; > select * from t1; > i c > 1 dummy_text > @@ -28,7 +23,6 @@ i c > 5 dummy_text > 7 dummy_text > SET GLOBAL wsrep_forced_binlog_format='none'; > -connection node_1; > SET GLOBAL wsrep_forced_binlog_format='none'; > drop table t1; > SET SESSION binlog_format='STATEMENT'; > @@ -54,14 +48,12 @@ i c > 4 dummy_text > 7 dummy_text > 10 dummy_text > -connection node_2; > select * from t1; > i c > 1 dummy_text > 4 dummy_text > 7 dummy_text > 10 dummy_text > -connection node_1; > SET GLOBAL wsrep_auto_increment_control='ON'; > SET SESSION binlog_format='ROW'; > show variables like 'binlog_format'; > @@ -80,9 +72,7 @@ auto_increment_offset 1 > wsrep_auto_increment_control OFF > SET GLOBAL wsrep_auto_increment_control='ON'; > drop table t1; > -connection node_2; > SET GLOBAL wsrep_forced_binlog_format='ROW'; > -connection node_1; > SET GLOBAL wsrep_forced_binlog_format='ROW'; > CREATE TABLE t1 ( > i int(11) NOT NULL AUTO_INCREMENT, > @@ -100,7 +90,6 @@ i c > 3 dummy_text > 5 dummy_text > 7 dummy_text > -connection node_2; > select * from t1; > i c > 1 dummy_text > @@ -108,7 +97,6 @@ i c > 5 dummy_text > 7 dummy_text > SET GLOBAL wsrep_forced_binlog_format='none'; > -connection node_1; > SET GLOBAL wsrep_forced_binlog_format='none'; > drop table t1; > SET SESSION binlog_format='ROW'; > @@ -134,14 +122,12 @@ i c > 4 dummy_text > 7 dummy_text > 10 dummy_text > -connection node_2; > select * from t1; > i c > 1 dummy_text > 4 dummy_text > 7 dummy_text > 10 dummy_text > -connection node_1; > SET GLOBAL wsrep_auto_increment_control='ON'; > show variables like 'binlog_format'; > Variable_name Value > diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc > index bee3c2ee9c0..3d569a2d1d7 100644 > --- a/storage/innobase/handler/ha_innodb.cc > +++ b/storage/innobase/handler/ha_innodb.cc > @@ -8401,26 +8401,25 @@ ha_innobase::write_row( > dberr_t err; > > #ifdef WITH_WSREP > - /* Applier threads which are processing > - ROW events and don't go through server > - level autoinc processing, therefore > - m_prebuilt autoinc values don't get > - properly assigned. Fetch values from > - server side. */ > - if (wsrep_on(current_thd) && > - wsrep_thd_exec_mode(current_thd) == REPL_RECV) > - { > - wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); > - } > - else > - { > + /* Applier threads which are > + processing ROW events and don't go > + through server level autoinc > + processing, therefore m_prebuilt > + autoinc values don't get > + properly assigned. Fetch values from > + server side. */ > + if (wsrep_on(current_thd) && > + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { > + wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); > + } else { > #endif /* WITH_WSREP */ > - ut_a(prebuilt->autoinc_increment > 0); > - offset = prebuilt->autoinc_offset; > - increment = prebuilt->autoinc_increment; > + ut_a(prebuilt->autoinc_increment > 0); > + offset = prebuilt->autoinc_offset; > + increment = prebuilt->autoinc_increment; > #ifdef WITH_WSREP > - } > + } > #endif /* WITH_WSREP */ > + > auto_inc = innobase_next_autoinc( > auto_inc, > 1, increment, offset, > @@ -8943,25 +8942,22 @@ ha_innobase::update_row( > ulonglong increment; > > #ifdef WITH_WSREP > - /* Applier threads which are processing > - ROW events and don't go through server > - level autoinc processing, therefore > - m_prebuilt autoinc values don't get > - properly assigned. Fetch values from > - server side. */ > - if (wsrep_on(current_thd) && > - wsrep_thd_exec_mode(current_thd) == REPL_RECV) > - { > - wsrep_thd_auto_increment_variables( > - current_thd, &offset, &increment); > - } > - else > - { > + /* Applier threads which are processing > + ROW events and don't go through server > + level autoinc processing, therefore > + m_prebuilt autoinc values don't get > + properly assigned. Fetch values from > + server side. */ > + if (wsrep_on(current_thd) && > + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { > + wsrep_thd_auto_increment_variables( > + current_thd, &offset, &increment); > + } else { > #endif /* WITH_WSREP */ > - offset = prebuilt->autoinc_offset; > - increment = prebuilt->autoinc_increment; > + offset = prebuilt->autoinc_offset; > + increment = prebuilt->autoinc_increment; > #ifdef WITH_WSREP > - } > + } > #endif /* WITH_WSREP */ > > auto_inc = innobase_next_autoinc( > @@ -16074,8 +16070,7 @@ ha_innobase::get_auto_increment( > thd_get_thread_id(ha_thd()), > current, autoinc); > > - if (!wsrep_on(ha_thd())) > - { > + if (!wsrep_on(ha_thd())) { > current = autoinc - prebuilt->autoinc_increment; > > current = innobase_next_autoinc( > diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc > index 3cd7cb6977b..857fc7e66f1 100644 > --- a/storage/xtradb/handler/ha_innodb.cc > +++ b/storage/xtradb/handler/ha_innodb.cc > @@ -8988,8 +8988,25 @@ ha_innobase::write_row( > ulonglong increment; > dberr_t err; > > - offset = prebuilt->autoinc_offset; > - increment = prebuilt->autoinc_increment; > +#ifdef WITH_WSREP > + /* Applier threads which are > + processing ROW events and don't go > + through server level autoinc > + processing, therefore m_prebuilt > + autoinc values don't get > + properly assigned. Fetch values from > + server side. */ > + if (wsrep_on(current_thd) && > + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { > + wsrep_thd_auto_increment_variables(current_thd, &offset, &increment); > + } else { > +#endif /* WITH_WSREP */ > + ut_a(prebuilt->autoinc_increment > 0); > + offset = prebuilt->autoinc_offset; > + increment = prebuilt->autoinc_increment; > +#ifdef WITH_WSREP > + } > +#endif /* WITH_WSREP */ > > auto_inc = innobase_next_autoinc( > auto_inc, > @@ -9502,16 +9519,32 @@ ha_innobase::update_row( > > /* We need the upper limit of the col type to check for > whether we update the table autoinc counter or not. */ > - col_max_value = innobase_get_int_col_max_value( > - table->next_number_field); > + col_max_value = > + table->next_number_field->get_max_int_value(); > > if (auto_inc <= col_max_value && auto_inc != 0) { > > ulonglong offset; > ulonglong increment; > > - offset = prebuilt->autoinc_offset; > - increment = prebuilt->autoinc_increment; > +#ifdef WITH_WSREP > + /* Applier threads which are processing > + ROW events and don't go through server > + level autoinc processing, therefore > + m_prebuilt autoinc values don't get > + properly assigned. Fetch values from > + server side. */ > + if (wsrep_on(current_thd) && > + wsrep_thd_exec_mode(current_thd) == REPL_RECV) { > + wsrep_thd_auto_increment_variables( > + current_thd, &offset, &increment); > + } else { > +#endif /* WITH_WSREP */ > + offset = prebuilt->autoinc_offset; > + increment = prebuilt->autoinc_increment; > +#ifdef WITH_WSREP > + } > +#endif /* WITH_WSREP */ > > auto_inc = innobase_next_autoinc( > auto_inc, 1, increment, offset, col_max_value); > @@ -16742,13 +16775,13 @@ ha_innobase::get_auto_increment( > increment, > thd_get_thread_id(ha_thd()), > current, autoinc); > - if (!wsrep_on(ha_thd())) > - { > - current = autoinc - prebuilt->autoinc_increment; > - } > > - current = innobase_next_autoinc( > - current, 1, increment, offset, col_max_value); > + if (!wsrep_on(ha_thd())) { > + current = autoinc - prebuilt->autoinc_increment; > + > + current = innobase_next_autoinc( > + current, 1, increment, offset, col_max_value); > + } > > dict_table_autoinc_initialize(prebuilt->table, current); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1