Bug #8924 'Explain' shows different strategy
Submitted: 3 Mar 2005 15:38 Modified: 12 Apr 2005 15:16
Reporter: Joerg Bruehe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Other (QNX)
Assigned to: Magnus Blåudd CPU Architecture:Any

[3 Mar 2005 15:38] Joerg Bruehe 
Description:
Build based on ChangeSet
  1.1777 05/03/02 08:04:26 patg@krsna.patg.net +1 -0
  Merge pgalbraith@bk-internal.mysql.com:/home/bk/mysql-5.0
  into krsna.patg.net:/home/patg/mysql-5.0

(but failures of this test on QNX are older, just could not be reported)

Test failure with different 'explain' output in both 'standard' and 'max' build on machine 'buildqnx':

-------------------------------------------------------
*** r/heap.result       Wed Mar  2 22:40:40 2005
--- r/heap.reject       Thu Mar  3 13:04:52 2005
***************
*** 66,72 ****
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x (x), unique y (y))
  engine=heap;
--- 66,72 ----
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      range   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x (x), unique y (y))
  engine=heap;
-------------------------------------------------------

-------------------------------------------------------
*** r/heap_btree.result Wed Mar  2 22:40:40 2005
--- r/heap_btree.reject Thu Mar  3 13:05:12 2005
***************
*** 66,72 ****
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x  using BTREE (x,y), unique y  using BTREE (y))
  engine=heap;
--- 66,72 ----
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      range   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x  using BTREE (x,y), unique y  using BTREE (y))
  engine=heap;
-------------------------------------------------------

-------------------------------------------------------
*** r/heap_hash.result  Wed Mar  2 22:40:40 2005
--- r/heap_hash.reject  Thu Mar  3 13:05:30 2005
***************
*** 66,72 ****
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
  engine=heap;
--- 66,72 ----
  alter table t1 engine=myisam;
  explain select * from t1 where a in (869751,736494,226312,802616);
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      range   uniq_id uniq_id 4       NULL    4       Using where; Using index
  drop table t1;
  create table t1 (x int not null, y int not null, key x  using HASH (x), unique y  using HASH (y))
  engine=heap;
-------------------------------------------------------

-------------------------------------------------------
*** r/range.result      Wed Mar  2 22:40:40 2005
--- r/range.reject      Thu Mar  3 13:33:18 2005
***************
*** 256,267 ****
  explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t2      ref     j1      j1      4       const   1       Using index
! 1     SIMPLE  t1      index   i1      i1      4       NULL    4       Using where; Using index
  explain select * from t1 force index(i1), t2 force index(j1) where
  (t1.key1 <t2.keya + 1) and t2.keya=3;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t2      ref     j1      j1      4       const   1       Using index
! 1     SIMPLE  t1      index   i1      i1      4       NULL    4       Using where; Using index
  DROP TABLE t1,t2;
  CREATE TABLE t1 (
  a int(11) default NULL,
--- 256,267 ----
  explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t2      ref     j1      j1      4       const   1       Using index
! 1     SIMPLE  t1      range   i1      i1      4       NULL    4       Using where; Using index
  explain select * from t1 force index(i1), t2 force index(j1) where
  (t1.key1 <t2.keya + 1) and t2.keya=3;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t2      ref     j1      j1      4       const   1       Using index
! 1     SIMPLE  t1      range   i1      i1      4       NULL    4       Using where; Using index
  DROP TABLE t1,t2;
  CREATE TABLE t1 (
  a int(11) default NULL,
-------------------------------------------------------

How to repeat:
Build + test on QNX.
[4 Apr 2005 8:50] Magnus Blåudd 
Test file for mysql-test to reproduce the problem

Attachment: bug8924.test (application/octet-stream, text), 354 bytes.
[5 Apr 2005 11:32] Magnus Blåudd 
This is definitely a rounding problem which makes the optimizer select the "range" plan instead of index. 

For example in  get_key_scan_params(opt_range.cc), the cpu_cost is calculated as 
double cpu_cost= (double) found_records / TIME_FOR_COMPARE;
In the test case found_records are 4 and TIME_FOR_COMPARE is 5. This results in the division
4/5 = 0,7999999999999993 // on qnx
and on linux
4/5 = 0,800000000000000004 

So this explains the problem, will investigate how it can be solved.

I have a trace file if anyone is interested.
[12 Apr 2005 15:16] Magnus Blåudd 
Changed the test results to get results that are more predictable.  Pushed to 5.0.5