diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2016-07-05 16:53:03 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2016-07-05 16:55:11 +0300 |
commit | 95c286cedf4b9330240a0a91a9fc3e58a17782b9 (patch) | |
tree | 7ec297064b2266b58e8daf500a0ea9506d067298 | |
parent | d1b25890745a140446e4bdd5fd4f489ce1f76fae (diff) | |
download | mariadb-git-95c286cedf4b9330240a0a91a9fc3e58a17782b9.tar.gz |
MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion
The crash was caused by this problem:
get_best_group_min_max() tries to construct query plans for keys that
are not processed by the range optimizer. This wasn't a problem as long
as SEL_TREE::keys was an array of MAX_KEY elements.
However, now it is a Mem_root_array and only has elements for the used
keys, and get_best_group_min_max attempts to address beyond the end of
the array.
The obvious way to fix the crash was to port (and improve) a part of
96fcfcbd7b5120e8f64fd45985001eca8d36fbfb from mysql-5.7. This makes
get_best_group_min_max not to consider indexes that Mem_root_arrays
have no element for.
After that, I got non-sensical query plans (see MDEV-10325 for details).
Fixed that by making get_best_group_min_max to check if the index is in
table->keys_in_use_for_group_by bitmap.
-rw-r--r-- | mysql-test/r/group_by.result | 34 | ||||
-rw-r--r-- | mysql-test/r/group_by_innodb.result | 35 | ||||
-rw-r--r-- | mysql-test/r/order_by.result | 2 | ||||
-rw-r--r-- | mysql-test/t/group_by.test | 36 | ||||
-rw-r--r-- | mysql-test/t/group_by_innodb.test | 30 | ||||
-rw-r--r-- | sql/opt_range.cc | 64 |
6 files changed, 145 insertions, 56 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 3ef73bb1943..adf1a3c9ab3 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2692,3 +2692,37 @@ select distinct a from t1 group by 'a'; a 2001-02-02 drop table t1; +# +# MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion `n < size()' failed in Mem_root_array +# +CREATE TABLE t1 ( +job_id int(10) unsigned NOT NULL AUTO_INCREMENT, +job_cmd varbinary(60) NOT NULL DEFAULT '', +job_namespace int(11) NOT NULL, +job_title varbinary(255) NOT NULL, +job_params blob NOT NULL, +job_timestamp varbinary(14) DEFAULT NULL, +job_random int(10) unsigned NOT NULL DEFAULT '0', +job_token varbinary(32) NOT NULL DEFAULT '', +job_token_timestamp varbinary(14) DEFAULT NULL, +job_sha1 varbinary(32) NOT NULL DEFAULT '', +job_attempts int(10) unsigned NOT NULL DEFAULT '0', +PRIMARY KEY (job_id), +KEY job_cmd (job_cmd,job_namespace,job_title,job_params(128)), +KEY job_timestamp (job_timestamp), +KEY job_sha1 (job_sha1), +KEY job_cmd_token (job_cmd,job_token,job_random), +KEY job_cmd_token_id (job_cmd,job_token,job_id) +); +INSERT INTO t1 VALUES +(NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), +(NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); +SELECT DISTINCT job_cmd FROM t1 WHERE job_cmd IN ('foobar','null'); +job_cmd +drop table t1; +CREATE TABLE t1 (f1 INT NOT NULL, f2 VARCHAR(3) NOT NULL, KEY(f1), KEY(f2, f1)); +INSERT INTO t1 VALUES (0,'foo'),(1,'bar'); +SELECT 1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ); +1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ) +0 +drop table t1; diff --git a/mysql-test/r/group_by_innodb.result b/mysql-test/r/group_by_innodb.result index 381e0d7493c..bf6b25f31fa 100644 --- a/mysql-test/r/group_by_innodb.result +++ b/mysql-test/r/group_by_innodb.result @@ -123,4 +123,39 @@ id xtext optionen 2 number 22,25 1 select Kabel mit Stecker 5-polig,Kabel ohne Stecker DROP TABLE t1, t2; +# Port of testcase: +# +# Bug#20819199 ASSERTION FAILED IN TEST_IF_SKIP_SORT_ORDER +# +CREATE TABLE t0 ( a INT ); +INSERT INTO t0 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +a INT, +b INT, +PRIMARY KEY (pk), +KEY idx1 (a), +KEY idx2 (b, a), +KEY idx3 (a, b) +) ENGINE = InnoDB; +INSERT INTO t1 (a, b) SELECT t01.a, t02.a FROM t0 t01, t0 t02; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx3 idx3 5 NULL 100 Using where; Using index +SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a; +a MAX(b) +1 10 +2 10 +3 10 +4 10 +5 10 +6 10 +7 10 +8 10 +9 10 +10 10 +DROP TABLE t0, t1; # End of tests diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 03e7c48951a..d108bce5eb1 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1163,7 +1163,7 @@ INSERT INTO t1 SELECT a +32, b +32 FROM t1; INSERT INTO t1 SELECT a +64, b +64 FROM t1; EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by +1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; a 1 diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 5d8b79b9fca..4aa5c10ece8 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1803,3 +1803,39 @@ select distinct a from t1 group by 'a'; insert into t1 values("2001-02-02"),("2001-02-03"); select distinct a from t1 group by 'a'; drop table t1; + +--echo # +--echo # MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion `n < size()' failed in Mem_root_array +--echo # +CREATE TABLE t1 ( + job_id int(10) unsigned NOT NULL AUTO_INCREMENT, + job_cmd varbinary(60) NOT NULL DEFAULT '', + job_namespace int(11) NOT NULL, + job_title varbinary(255) NOT NULL, + job_params blob NOT NULL, + job_timestamp varbinary(14) DEFAULT NULL, + job_random int(10) unsigned NOT NULL DEFAULT '0', + job_token varbinary(32) NOT NULL DEFAULT '', + job_token_timestamp varbinary(14) DEFAULT NULL, + job_sha1 varbinary(32) NOT NULL DEFAULT '', + job_attempts int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (job_id), + KEY job_cmd (job_cmd,job_namespace,job_title,job_params(128)), + KEY job_timestamp (job_timestamp), + KEY job_sha1 (job_sha1), + KEY job_cmd_token (job_cmd,job_token,job_random), + KEY job_cmd_token_id (job_cmd,job_token,job_id) +); + +INSERT INTO t1 VALUES + (NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), + (NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); + +SELECT DISTINCT job_cmd FROM t1 WHERE job_cmd IN ('foobar','null'); +drop table t1; + +CREATE TABLE t1 (f1 INT NOT NULL, f2 VARCHAR(3) NOT NULL, KEY(f1), KEY(f2, f1)); +INSERT INTO t1 VALUES (0,'foo'),(1,'bar'); +SELECT 1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ); +drop table t1; + diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test index e072a94fada..ed65e0c3e57 100644 --- a/mysql-test/t/group_by_innodb.test +++ b/mysql-test/t/group_by_innodb.test @@ -125,4 +125,34 @@ ORDER BY id DESC; DROP TABLE t1, t2; +--echo # Port of testcase: +--echo # +--echo # Bug#20819199 ASSERTION FAILED IN TEST_IF_SKIP_SORT_ORDER +--echo # + +CREATE TABLE t0 ( a INT ); +INSERT INTO t0 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + a INT, + b INT, + PRIMARY KEY (pk), + KEY idx1 (a), + KEY idx2 (b, a), + KEY idx3 (a, b) +) ENGINE = InnoDB; + +INSERT INTO t1 (a, b) SELECT t01.a, t02.a FROM t0 t01, t0 t02; + +ANALYZE TABLE t1; + +let $query= +SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t0, t1; + --echo # End of tests diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 1dc8f73a99d..0d92d434762 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11902,8 +11902,6 @@ void QUICK_ROR_UNION_SELECT::add_used_key_part_to_set(MY_BITMAP *col_set) *******************************************************************************/ static inline uint get_field_keypart(KEY *index, Field *field); -static inline SEL_ARG * get_index_range_tree(uint index, SEL_TREE* range_tree, - PARAM *param, uint *param_idx); static bool get_sel_arg_for_keypart(Field *field, SEL_ARG *index_range_tree, SEL_ARG **cur_range); static bool get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, @@ -12180,8 +12178,6 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) (GA1,GA2) are all TRUE. If there is more than one such index, select the first one. Here we set the variables: group_prefix_len and index_info. */ - KEY *cur_index_info= table->key_info; - KEY *cur_index_info_end= cur_index_info + table->s->keys; /* Cost-related variables for the best index so far. */ double best_read_cost= DBL_MAX; ha_rows best_records= 0; @@ -12193,11 +12189,12 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) uint max_key_part; SEL_ARG *cur_index_tree= NULL; ha_rows cur_quick_prefix_records= 0; - uint cur_param_idx=MAX_KEY; - for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ; - cur_index_info++, cur_index++) + // We go through allowed indexes + for (uint cur_param_idx= 0; cur_param_idx < param->keys ; ++cur_param_idx) { + const uint cur_index= param->real_keynr[cur_param_idx]; + KEY *const cur_index_info= &table->key_info[cur_index]; KEY_PART_INFO *cur_part; KEY_PART_INFO *end_part; /* Last part for loops. */ /* Last index part. */ @@ -12220,7 +12217,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) (was also: "Exclude UNIQUE indexes ..." but this was removed because there are cases Loose Scan over a multi-part index is useful). */ - if (!table->covering_keys.is_set(cur_index)) + if (!table->covering_keys.is_set(cur_index) || + !table->keys_in_use_for_group_by.is_set(cur_index)) continue; /* @@ -12399,9 +12397,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) { if (tree) { - uint dummy; - SEL_ARG *index_range_tree= get_index_range_tree(cur_index, tree, param, - &dummy); + SEL_ARG *index_range_tree= tree->keys[cur_param_idx]; if (!get_constant_key_infix(cur_index_info, index_range_tree, first_non_group_part, min_max_arg_part, last_part, thd, cur_key_infix, @@ -12465,9 +12461,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) */ if (tree && min_max_arg_item) { - uint dummy; - SEL_ARG *index_range_tree= get_index_range_tree(cur_index, tree, param, - &dummy); + SEL_ARG *index_range_tree= tree->keys[cur_param_idx]; SEL_ARG *cur_range= NULL; if (get_sel_arg_for_keypart(min_max_arg_part->field, index_range_tree, &cur_range) || @@ -12485,9 +12479,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) /* Compute the cost of using this index. */ if (tree) { - /* Find the SEL_ARG sub-tree that corresponds to the chosen index. */ - cur_index_tree= get_index_range_tree(cur_index, tree, param, - &cur_param_idx); + cur_index_tree= tree->keys[cur_param_idx]; /* Check if this range tree can be used for prefix retrieval. */ Cost_estimate dummy_cost; uint mrr_flags= HA_MRR_USE_DEFAULT_IMPL; @@ -13021,44 +13013,6 @@ get_field_keypart(KEY *index, Field *field) /* - Find the SEL_ARG sub-tree that corresponds to the chosen index. - - SYNOPSIS - get_index_range_tree() - index [in] The ID of the index being looked for - range_tree[in] Tree of ranges being searched - param [in] PARAM from SQL_SELECT::test_quick_select - param_idx [out] Index in the array PARAM::key that corresponds to 'index' - - DESCRIPTION - - A SEL_TREE contains range trees for all usable indexes. This procedure - finds the SEL_ARG sub-tree for 'index'. The members of a SEL_TREE are - ordered in the same way as the members of PARAM::key, thus we first find - the corresponding index in the array PARAM::key. This index is returned - through the variable param_idx, to be used later as argument of - check_quick_select(). - - RETURN - Pointer to the SEL_ARG subtree that corresponds to index. -*/ - -SEL_ARG * get_index_range_tree(uint index, SEL_TREE* range_tree, PARAM *param, - uint *param_idx) -{ - uint idx= 0; /* Index nr in param->key_parts */ - while (idx < param->keys) - { - if (index == param->real_keynr[idx]) - break; - idx++; - } - *param_idx= idx; - return(range_tree->keys[idx]); -} - - -/* Compute the cost of a quick_group_min_max_select for a particular index. SYNOPSIS |