Bug #8578 Test case 'index_merge_ror' fails on SGI irix
Submitted: 17 Feb 2005 17:06 Modified: 22 Feb 2005 21:26
Reporter: Kent Boortz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.3-pre OS:Other (Irix)
Assigned to: Sergey Petrunya CPU Architecture:Any

[17 Feb 2005 17:06] Kent Boortz
Description:
Test case 'index_merge_ror' fails on SGI irix

How to repeat:
Run the test case on the specified targets.
[22 Feb 2005 13:13] Joerg Bruehe
The differences (here: 32 bit - 64 bit looks the same):

-------------------------------------------------------
*** r/index_merge_ror.result    Tue Feb 22 00:59:21 2005
--- r/index_merge_ror.reject    Tue Feb 22 03:03:53 2005
***************
*** 130,146 ****
  1     SIMPLE  t1      ref     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b   st_b    4       cons
t   14720   Using where
  explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      ref     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a  sta_swt12a      12      const,const,const
    958
  explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t1      ref     stb_swt1a_2b,stb_swt1b,st_b     stb_swt1b       8       const,const     3757    Using where
  explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b      sta_
swt12a,stb_swt1a_2b 12,12   NULL    42      Using intersect(sta_swt12a,stb_swt1a_2b); Using where
  explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b      sta_swt12a,stb_swt1b    12,8
    NULL    42      Using intersect(sta_swt12a,stb_swt1b); Using where
  explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
--- 130,146 ----
  1     SIMPLE  t1      ref     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b   st_b    4       cons
t   14720   Using where
  explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a  sta_swt12a,sta_swt21a   12,12   NULL
    1       Using intersect(sta_swt12a,sta_swt21a); Using where
  explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
  1     SIMPLE  t1      ref     stb_swt1a_2b,stb_swt1b,st_b     stb_swt1b       8       const,const     3757    Using where
  explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b      sta_
swt12a,sta_swt21a   12,12   NULL    1       Using intersect(sta_swt12a,sta_swt21a); Using where
  explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b      sta_swt12a,sta_swt1a    12,8
    NULL    168     Using intersect(sta_swt12a,sta_swt1a); Using where
  explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
***************
*** 152,158 ****
  explain select * from t1
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b      sta_
swt12a,stb_swt1a_2b 12,12   NULL    42      Using intersect(sta_swt12a,stb_swt1a_2b); Using where
  explain select * from t1
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
--- 152,158 ----
  explain select * from t1
  where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
! 1     SIMPLE  t1      index_merge     sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b      sta_
swt12a,sta_swt21a   12,12   NULL    1       Using intersect(sta_swt12a,sta_swt21a); Using where
  explain select * from t1
  where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
  id    select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-------------------------------------------------------
[22 Feb 2005 14:46] Sergey Petrunya
I have the fix already, testing it now
[22 Feb 2005 14:59] Sergey Petrunya
ChangeSet@1.1870, 2005-02-22 18:00:01+03:00, sergefp@mysql.com
[22 Feb 2005 15:36] Sergey Petrunya
Fix reviewed by Ingo
[22 Feb 2005 15:50] Sergey Petrunya
Note to docs team: The bug caused inoptimal "index_merge" query execution plans to be chosen on IRIX
[22 Feb 2005 15:52] Sergey Petrunya
Fixed in 5.0.3
[22 Feb 2005 21:26] Paul Dubois
Noted in 5.0.3 changelog.