| 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: | |
| 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
[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.
