diff options
| -rw-r--r-- | mysql-test/main/group_min_max.result | 31 | ||||
| -rw-r--r-- | mysql-test/main/group_min_max.test | 23 | ||||
| -rw-r--r-- | sql/opt_range.cc | 38 |
3 files changed, 74 insertions, 18 deletions
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index e6a5bc0ab50..7b1482f7c95 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4065,3 +4065,34 @@ SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUN owner_id 1 DROP TABLE t1; +# +# MDEV-24819 Wrong result or ASAN use-after-poison errors upon SELECT with GROUP BY +# +# Firstly test MyISAM engine: +CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w'); +# Make sure "Using index for group-by" is chosen: +EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b b 9 NULL 4 Using where; Using index for group-by +SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +b a +h 1 +n 4 +DROP TABLE t1; +# Test the same for InnoDB engine: +CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w'); +EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b b 9 NULL 4 Using where; Using index for group-by +SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +b a +h 1 +n 4 +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,3),(2,6),(3,9),(4,NULL),(5,NULL); +SELECT a, pk FROM t1 WHERE pk != 0 AND (a < 0 OR pk <= 0) GROUP BY a, pk; +a pk +DROP TABLE t1; diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 3f7ef4ec9b8..62177e46848 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1722,3 +1722,26 @@ EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); DROP TABLE t1; + + +--echo # +--echo # MDEV-24819 Wrong result or ASAN use-after-poison errors upon SELECT with GROUP BY +--echo # +--echo # Firstly test MyISAM engine: +CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w'); +--echo # Make sure "Using index for group-by" is chosen: +EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +DROP TABLE t1; +--echo # Test the same for InnoDB engine: +CREATE TABLE t1 (a INT, b VARCHAR(1), KEY (a), KEY(b,a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4,'n'),(1,'h'),(NULL,'w'); +EXPLAIN SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +SELECT b, a FROM t1 WHERE b <> 'p' OR a = 4 GROUP BY b, a HAVING a <= 7; +DROP TABLE t1; + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,3),(2,6),(3,9),(4,NULL),(5,NULL); +SELECT a, pk FROM t1 WHERE pk != 0 AND (a < 0 OR pk <= 0) GROUP BY a, pk; +DROP TABLE t1;
\ No newline at end of file diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e3287e1bbea..e9f49599756 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -13754,13 +13754,9 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) double best_read_cost= DBL_MAX; ha_rows best_records= 0; SEL_ARG *best_index_tree= NULL; - ha_rows best_quick_prefix_records= 0; + ha_rows best_quick_prefix_records= HA_POS_ERROR; uint best_param_idx= 0; - const uint pk= param->table->s->primary_key; - uint max_key_part; - SEL_ARG *cur_index_tree= NULL; - ha_rows cur_quick_prefix_records= 0; // We go through allowed indexes Json_writer_array trace_indexes(thd, "potential_group_range_indexes"); @@ -13789,6 +13785,9 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) uint cur_key_infix_len= 0; uchar cur_key_infix[MAX_KEY_LENGTH]; uint cur_used_key_parts; + ha_rows cur_quick_prefix_records= HA_POS_ERROR; + SEL_ARG *cur_index_tree= NULL; + uint max_key_part= 0; /* Check (B1) - if current index is covering. @@ -13846,7 +13845,6 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) trace_idx.add("covering", true); - max_key_part= 0; used_key_parts_map.clear_all(); /* @@ -14089,22 +14087,26 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) (first_non_infix_part - first_non_group_part) : 0; cur_used_key_parts= cur_group_key_parts + key_infix_parts; - /* Compute the cost of using this index. */ - if (tree) + /* + Consider using this index for constructing QUICK_RANGE_SELECT object + in TRP_GROUP_MIN_MAX. + Initially the index is considered not applicable: + */ + cur_quick_prefix_records= HA_POS_ERROR; + if (tree && ((cur_index_tree= tree->keys[cur_param_idx]) != nullptr)) { - if ((cur_index_tree= tree->keys[cur_param_idx])) + if (param->table->opt_range_keys.is_set(cur_index)) + /* Current index is applicable for QUICK_RANGE_SELECT object */ + cur_quick_prefix_records= param->table->opt_range[cur_index].rows; + if (unlikely(thd->trace_started())) { - cur_quick_prefix_records= param->quick_rows[cur_index]; - if (unlikely(cur_index_tree && thd->trace_started())) - { - Json_writer_array trace_range(thd, "ranges"); - trace_ranges(&trace_range, param, cur_param_idx, - cur_index_tree, cur_index_info->key_part); - } + Json_writer_array trace_range(thd, "ranges"); + trace_ranges(&trace_range, param, cur_param_idx, cur_index_tree, + cur_index_info->key_part); } - else - cur_quick_prefix_records= HA_POS_ERROR; } + + /* Compute the cost of using this index. */ cost_group_min_max(table, cur_index_info, cur_used_key_parts, cur_group_key_parts, tree, cur_index_tree, cur_quick_prefix_records, have_min, have_max, |