Bug #8578 Test case 'index_merge_ror' fails on SGI irix

Description:
Test case 'index_merge_ror' fails on SGI irix

How to repeat:
Run the test case on the specified targets.

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
-------------------------------------------------------

I have the fix already, testing it now

ChangeSet@1.1870, 2005-02-22 18:00:01+03:00, sergefp@mysql.com

Fix reviewed by Ingo

Note to docs team: The bug caused inoptimal "index_merge" query execution plans to be chosen on IRIX

Fixed in 5.0.3

Noted in 5.0.3 changelog.