maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #01296
bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (igor:2745)
#At lp:maria based on revid:igor@xxxxxxxxxxxx-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%'));
