#At lp:maria based on revid:igor@askmonty.org-20091012045934-8rkx13xrp0vqy6p2 2745 Igor Babaev 2009-10-17 Changed test cases to make results for innodb platform independent. modified: mysql-test/r/range_vs_index_merge.result* mysql-test/r/range_vs_index_merge_innodb.result* mysql-test/t/range_vs_index_merge.test === modified file 'mysql-test/r/range_vs_index_merge.result' (properties changed: +x to -x) --- a/mysql-test/r/range_vs_index_merge.result 2009-10-12 04:59:34 +0000 +++ b/mysql-test/r/range_vs_index_merge.result 2009-10-17 18:40:46 +0000 @@ -176,9 +176,9 @@ SELECT * FROM City WHERE (Name < 'Ac'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 13 Using where EXPLAIN -SELECT * FROM City WHERE (Name < 'C'); +SELECT * FROM City WHERE (Name < 'Bb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 325 Using where +1 SIMPLE City range Name Name 35 NULL 208 Using where EXPLAIN SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); id select_type table type possible_keys key key_len ref rows Extra @@ -188,9 +188,9 @@ SELECT * FROM City WHERE (Name BETWEEN id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 39 Using where EXPLAIN -SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'T'); +SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 468 Using where +1 SIMPLE City range Name Name 35 NULL 221 Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); id select_type table type possible_keys key key_len ref rows Extra @@ -208,19 +208,19 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Name,Population 35,4 NULL 50 Using sort_union(Name,Population); Using where EXPLAIN SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Country,Name 3,35 NULL 143 Using sort_union(Country,Name); Using where EXPLAIN SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Country,Population 3,4 NULL 141 Using sort_union(Country,Population); Using where SELECT * FROM City USE INDEX () @@ -243,39 +243,33 @@ ID Name Country Population 189 Parakou BEN 103577 SELECT * FROM City USE INDEX () WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 65 Abu Dhabi ARE 398695 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 65 Abu Dhabi ARE 398695 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City USE INDEX () -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 @@ -283,20 +277,16 @@ ID Name Country Population 750 Paarl ZAF 105768 2865 Pak Pattan PAK 107800 SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 @@ -304,47 +294,37 @@ ID Name Country Population 750 Paarl ZAF 105768 2865 Pak Pattan PAK 107800 SELECT * FROM City USE INDEX () -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 EXPLAIN @@ -651,9 +631,9 @@ SELECT * FROM City WHERE Country < 'AGO' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Country Country 3 NULL 6 Using where EXPLAIN -SELECT * FROM City WHERE Name BETWEEN 'P' AND 'T'; +SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Name Name 35 NULL 468 Using where +1 SIMPLE City range Name Name 35 NULL 221 Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; id select_type table type possible_keys key key_len ref rows Extra @@ -673,7 +653,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); id select_type table type possible_keys key key_len ref rows Extra @@ -688,7 +668,7 @@ id select_type table type possible_keys 1 SIMPLE City index_merge PRIMARY,Population,Country,Name Country,Name,PRIMARY 3,35,4 NULL 56 Using sort_union(Country,Name,PRIMARY); Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); ID Name Country Population @@ -697,15 +677,11 @@ ID Name Country Population 417 Itaituba BRA 101320 418 Araras BRA 101046 751 Potchefstroom ZAF 101817 -1752 Sakata JPN 101651 -1851 Saint John´s CAN 101936 -1853 Saanich CAN 101388 2909 Puno PER 101578 3463 Pavlograd UKR 127000 -4030 Sandy USA 101853 SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); ID Name Country Population @@ -714,12 +690,8 @@ ID Name Country Population 417 Itaituba BRA 101320 418 Araras BRA 101046 751 Potchefstroom ZAF 101817 -1752 Sakata JPN 101651 -1851 Saint John´s CAN 101936 -1853 Saanich CAN 101388 2909 Puno PER 101578 3463 Pavlograd UKR 127000 -4030 Sandy USA 101853 SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 110000) AND (Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR === modified file 'mysql-test/r/range_vs_index_merge_innodb.result' (properties changed: +x to -x) --- a/mysql-test/r/range_vs_index_merge_innodb.result 2009-10-12 04:59:34 +0000 +++ b/mysql-test/r/range_vs_index_merge_innodb.result 2009-10-17 18:40:46 +0000 @@ -43,7 +43,7 @@ EXPLAIN SELECT * FROM City WHERE (Population >= 100000 OR Name LIKE 'P%' OR Population < 100000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ALL Population,Name NULL NULL NULL 4249 Using where +1 SIMPLE City ALL Population,Name NULL NULL NULL 4079 Using where EXPLAIN SELECT * FROM City WHERE (Population >= 100000 OR Name LIKE 'P%') AND Country='CAN' OR @@ -177,9 +177,9 @@ SELECT * FROM City WHERE (Name < 'Ac'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 23 Using where EXPLAIN -SELECT * FROM City WHERE (Name < 'C'); +SELECT * FROM City WHERE (Name < 'Bb'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ALL Name NULL NULL NULL 4249 Using where +1 SIMPLE City range Name Name 35 NULL 373 Using where EXPLAIN SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); id select_type table type possible_keys key key_len ref rows Extra @@ -189,9 +189,9 @@ SELECT * FROM City WHERE (Name BETWEEN id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Name Name 35 NULL 71 Using where EXPLAIN -SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'T'); +SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ALL Name NULL NULL NULL 4249 Using where +1 SIMPLE City range Name Name 35 NULL 384 Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); id select_type table type possible_keys key key_len ref rows Extra @@ -209,19 +209,19 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Name,Population 35,4 NULL 59 Using sort_union(Name,Population); Using where EXPLAIN SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Country,Name 3,35 NULL 177 Using sort_union(Country,Name); Using where EXPLAIN SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City index_merge Population,Country,Name Country,Population 3,4 NULL 142 Using sort_union(Country,Population); Using where SELECT * FROM City USE INDEX () @@ -244,39 +244,33 @@ ID Name Country Population 189 Parakou BEN 103577 SELECT * FROM City USE INDEX () WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 65 Abu Dhabi ARE 398695 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 65 Abu Dhabi ARE 398695 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City USE INDEX () -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 @@ -284,20 +278,16 @@ ID Name Country Population 750 Paarl ZAF 105768 2865 Pak Pattan PAK 107800 SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 @@ -305,47 +295,37 @@ ID Name Country Population 750 Paarl ZAF 105768 2865 Pak Pattan PAK 107800 SELECT * FROM City USE INDEX () -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 SELECT * FROM City -WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR -(Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); +WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR +(Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); ID Name Country Population 55 Andorra la Vella AND 21189 -59 Benguela AGO 128300 65 Abu Dhabi ARE 398695 67 al-Ayn ARE 225970 68 Ajman ARE 114395 -69 Buenos Aires ARG 2982146 75 Almirante Brown ARG 538918 85 Avellaneda ARG 353046 -93 Berazategui ARG 276916 96 Bahía Blanca ARG 239810 -132 Brisbane AUS 1291117 134 Adelaide AUS 978100 144 Baku AZE 1787800 168 Pabna BGD 103277 189 Parakou BEN 103577 -914 Sekondi-Takoradi GHA 103653 1003 Pemalang IDN 103500 2663 Río Bravo MEX 103901 EXPLAIN @@ -652,9 +632,9 @@ SELECT * FROM City WHERE Country < 'AGO' id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE City range Country Country 3 NULL 5 Using where EXPLAIN -SELECT * FROM City WHERE Name BETWEEN 'P' AND 'T'; +SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ALL Name NULL NULL NULL 4249 Using where +1 SIMPLE City range Name Name 35 NULL 384 Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; id select_type table type possible_keys key key_len ref rows Extra @@ -674,7 +654,7 @@ id select_type table type possible_keys EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); id select_type table type possible_keys key key_len ref rows Extra @@ -689,7 +669,7 @@ id select_type table type possible_keys 1 SIMPLE City index_merge PRIMARY,Population,Country,Name Country,Name,PRIMARY 3,35,4 NULL 87 Using sort_union(Country,Name,PRIMARY); Using where SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); ID Name Country Population @@ -698,15 +678,11 @@ ID Name Country Population 417 Itaituba BRA 101320 418 Araras BRA 101046 751 Potchefstroom ZAF 101817 -1752 Sakata JPN 101651 -1851 Saint John´s CAN 101936 -1853 Saanich CAN 101388 2909 Puno PER 101578 3463 Pavlograd UKR 127000 -4030 Sandy USA 101853 SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND -(Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR +(Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); ID Name Country Population @@ -715,12 +691,8 @@ ID Name Country Population 417 Itaituba BRA 101320 418 Araras BRA 101046 751 Potchefstroom ZAF 101817 -1752 Sakata JPN 101651 -1851 Saint John´s CAN 101936 -1853 Saanich CAN 101388 2909 Puno PER 101578 3463 Pavlograd UKR 127000 -4030 Sandy USA 101853 SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 110000) AND (Country < 'AGO' OR Name BETWEEN 'P' AND 'Pb')) OR === modified file 'mysql-test/t/range_vs_index_merge.test' --- a/mysql-test/t/range_vs_index_merge.test 2009-10-12 04:59:34 +0000 +++ b/mysql-test/t/range_vs_index_merge.test 2009-10-17 18:40:46 +0000 @@ -35,6 +35,8 @@ ANALYZE TABLE City; # The following 4 queries are added for code coverage +#the exptected # of rows differ on 32-bit and 64-bit platforms for innodb +--replace_column 9 4079 EXPLAIN SELECT * FROM City WHERE (Population >= 100000 OR Name LIKE 'P%' OR Population < 100000); @@ -113,13 +115,13 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE (Name < 'Ac'); EXPLAIN -SELECT * FROM City WHERE (Name < 'C'); +SELECT * FROM City WHERE (Name < 'Bb'); EXPLAIN SELECT * FROM City WHERE (Country > 'A' AND Country < 'B'); EXPLAIN SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'Pb'); EXPLAIN -SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'T'); +SELECT * FROM City WHERE (Name BETWEEN 'P' AND 'S'); EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 110000); EXPLAIN @@ -143,17 +145,17 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); EXPLAIN SELECT * FROM City - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); EXPLAIN SELECT * FROM City - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); # The following 8 queries check that the plans # for the previous 4 plans are valid @@ -168,27 +170,27 @@ SELECT * FROM City SELECT * FROM City USE INDEX () WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); SELECT * FROM City WHERE (Name < 'Ac' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); SELECT * FROM City USE INDEX () - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); SELECT * FROM City - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR (Name BETWEEN 'P' AND 'Pb' AND (Population > 101000 AND Population < 110000)); SELECT * FROM City USE INDEX () - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); SELECT * FROM City - WHERE (Name < 'C' AND (Country > 'A' AND Country < 'B')) OR - (Name BETWEEN 'P' AND 'T' AND (Population > 103000 AND Population < 104000)); + WHERE (Name < 'Bb' AND (Country > 'A' AND Country < 'B')) OR + (Name BETWEEN 'P' AND 'S' AND (Population > 103000 AND Population < 104000)); # The output of the next 6 commands tells us about selectivities @@ -280,7 +282,7 @@ SELECT * FROM City WHERE Country < 'C'; EXPLAIN SELECT * FROM City WHERE Country < 'AGO'; EXPLAIN -SELECT * FROM City WHERE Name BETWEEN 'P' AND 'T'; +SELECT * FROM City WHERE Name BETWEEN 'P' AND 'S'; EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'P' AND 'Pb'; EXPLAIN @@ -302,7 +304,7 @@ SELECT * FROM City WHERE Name LIKE 'P%'; EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND - (Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR + (Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); @@ -318,13 +320,13 @@ SELECT * FROM City SELECT * FROM City USE INDEX () WHERE ((Population > 101000 AND Population < 102000) AND - (Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR + (Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%')); SELECT * FROM City WHERE ((Population > 101000 AND Population < 102000) AND - (Country < 'C' OR Name BETWEEN 'P' AND 'T')) OR + (Country < 'C' OR Name BETWEEN 'P' AND 'S')) OR ((ID BETWEEN 3400 AND 3800) AND (Country < 'AGO' OR Name LIKE 'Pa%'));