Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
Reproduce
create or replace table t1 ( id int not null, name varchar(20) not null, dept varchar(20) not null, age tinyint(3) unsigned not null, primary key (id), index (name,dept) ) engine=innodb; |
explain select distinct t1.name, t1.dept from t1 where t1.name='rs5'; |
insert into t1(id, dept, age, name) values (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); |
delete from t1; |
explain select distinct t1.name, t1.dept from t1 where t1.name='rs5'; |
Result
Results from first and second explain do not match:
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | t1 | ref | name | name | 22 | const | 1 | Using where; Using index |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
| 1 | SIMPLE | t1 | range | name | name | 44 | NULL | 1 | Using where; Using index for group-by |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
Expected
Results from first and second explain should be equal.
Side-effect
Test innodb.innodb_mysql is unstable, with --repeat=100 it fails:
--- /home/midenok/src/mariadb/10.4/src/mysql-test/suite/innodb/r/innodb_mysql.result 2019-05-31 09:23:14.003022993 +0300
|
+++ /home/midenok/src/mariadb/10.4/src/mysql-test/suite/innodb/r/innodb_mysql.reject 2019-06-26 15:33:23.132090751 +0300
|
@@ -394,7 +394,7 @@
|
# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
|
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
|
id select_type table type possible_keys key key_len ref rows Extra
|
-1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by
|
+1 SIMPLE t1 ref name name 22 const # Using where; Using index
|
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
|
name dept
|
DROP TABLE t1; |
Info
The original fix was https://lists.mysql.com/commits/19658 but now result is overridden by 0700cde7f1071fb676d21794aaf97bf0a74acc61.
Attachments
Issue Links
- relates to
-
MDEV-21895 Refactor handler::records_in_range()
- Stalled