Bug #5433 records_in_range result maybe non-deterministic for BTREE index on HEAP table

• Submitted: 6 Sep 2004 16:10
• Modified: 8 Sep 2004 21:22
• Reporter: Sergey Petrunya
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Linux (linux)
• CPU Architecture: Any

[6 Sep 2004 16:10] Sergey Petrunya

Description:
Running the same sequence of queries over a heap table may produce different explain results depending on an unknown condition.

The effect was observed on 4.1 debug build, gcc version 3.2 20020903, latest changeset
ChangeSet 1.1993 2004/09/03 19:32:18 [e-mail]

How to repeat:
At the end of t/heap_hash.test add the following:

create table t1 (
  id int unsigned not null primary key auto_increment, 
  name varchar(20) not null,
  index heap_idx(name),
  index btree_idx using btree(name)
) engine=heap;

create table t2 (
  id int unsigned not null primary key auto_increment, 
  name varchar(20) not null,
  index btree_idx using btree(name),
  index heap_idx(name)
) engine=heap;

insert into t1 (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), ('Suzy'), ('Hoppy'), ('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'),  ('Emily'), ('Mike');
insert into t2 select * from t1;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;
insert into t1 (name) select name from t2;

explain select * from t1 where name='matt';

Run ./mysql-test-run --record t/heap_hash.test. The .result file will contain:

explain select * from t1 where name='matt';
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ref     heap_idx,btree_idx      btree_idx       20      const   5       Using where

Then run all tests. The heap_hash test will fail and .reject file will contain:
1     SIMPLE  t1      ref     heap_idx,btree_idx      btree_idx       20      const   6       Using where

The observed effect is stable (always 5 rows in single test run vs. 6 rows in full test run.)

Suggested fix:
Find the cause.