[MDEV-19873] Different EXPLAIN SELECT DISTINCT on just created table and after deleted records Created: 2019-06-26  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB, Tests
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Aleksey Midenkov Assignee: Vladislav Lesin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-21895 Refactor handler::records_in_range() Stalled

 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.

 Comments   
Comment by Marko Mäkelä [ 2019-06-27 ]

My guess is that this happens because ha_innobase::records_in_range() is not only nondeterministic, but sometimes intentionally returning bogus values when it detects that the index tree was modified while the code was executing without holding appropriate page latches.

Also, the function is probably not taking MVCC into account and could thus include purgeable (non-existing) records in the count.

Both these problems might be infeasible to fix, because records_in_range() is expected to be fast. If it locked the appropriate upper level pages of the index tree, it might reduce concurrency too much. Likewise, taking MVCC into account especially in secondary indexes would be very costly (unless we implement MDEV-17598).

Last but not least, I suspect that the optimizer can be unnecessarily invoking ha_innobase::records_in_range() multiple times. I remember one innodb_zip test essentially giving multiple different row counts for the same table, in different lines of the same EXPLAIN output.

Comment by Aleksey Midenkov [ 2019-06-27 ]

A some measure should be done quickly that will at least fix unstable `innodb.innodb_mysql`.

Comment by Sergei Petrunia [ 2019-06-28 ]

The only feasible option that I see is to just remove the EXPLAIN from the test file. Just run the SELECT itself. (It might or might not use LooseScan. If it doesn't, we 've lost test coverage for this very old bugfix. Maintaining testcase coverage for bugfixes from >10 years ago doesn't seem like something worth spending time on )

(EITS statistics won't help here, because records_in_range estimates are only available from the storage engine. They cannot be replaced by range.)

Comment by Sergei Petrunia [ 2019-06-28 ]

(just trying something, give me a bit of time)

Comment by Sergei Petrunia [ 2019-06-28 ]

we can't use EITS to override records_in_range return value, but at least we can use it to override #rows_in_table and rec_per_key values.
So, I've made this patch (without any debugging of why plan was changing):

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
 
index 6a47b39e5bc..b147f4e1eab 100644
 
--- a/mysql-test/include/mix1.inc
 
+++ b/mysql-test/include/mix1.inc
 
@@ -454,6 +454,7 @@ INSERT INTO t1(id, dept, age, name) VALUES
 
   (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');
 
+analyze table t1 persistent for all;
 
 
 EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 
 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
 
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
 
index 7a164da41b5..285eab403b3 100644
 
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
 
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
 
@@ -383,6 +383,10 @@ INSERT INTO t1(id, dept, age, name) VALUES
 
 (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');
 
+analyze table t1 persistent for all;
 
+Table	Op	Msg_type	Msg_text
 
+test.t1	analyze	status	Engine-independent statistics collected
 
+test.t1	analyze	status	OK
 
 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	ref	name	name	22	const	2	Using where; Using index

The testcase doesn't fail for me anymore. midenok, can you try it?

Generated at Thu Feb 08 08:55:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.