|
1
by brian
clean slate |
1 |
#
|
2 |
# DuplicateElimination strategy test |
|
3 |
#
|
|
4 |
--source include/have_innodb.inc |
|
5 |
--disable_warnings |
|
6 |
drop table if exists t0, t1, t2, t3; |
|
7 |
--enable_warnings |
|
8 |
||
9 |
||
10 |
create table t0 (a int); |
|
11 |
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
12 |
||
13 |
# First test simple cases: I20 order, no join buffering. |
|
14 |
||
15 |
create table t1 ( |
|
16 |
a int, |
|
17 |
b int |
|
18 |
);
|
|
19 |
insert into t1 values (1,1),(1,1),(2,2); |
|
20 |
||
21 |
create table t2 ( |
|
22 |
a int, |
|
23 |
b int, |
|
24 |
key(b) |
|
25 |
);
|
|
26 |
insert into t2 select a, a/2 from t0; |
|
27 |
||
28 |
select * from t1; |
|
29 |
select * from t2; |
|
30 |
explain select * from t2 where b in (select a from t1); |
|
31 |
select * from t2 where b in (select a from t1); |
|
32 |
||
33 |
# Try an InnoDB table with very long rowid |
|
34 |
create table t3 ( |
|
35 |
a int, |
|
36 |
b int, |
|
37 |
key(b), |
|
38 |
pk1 char(200), pk2 char(200), pk3 char(200), |
|
39 |
primary key(pk1, pk2, pk3) |
|
40 |
) engine=innodb; |
|
41 |
insert into t3 select a,a, a,a,a from t0; |
|
42 |
||
43 |
explain select * from t3 where b in (select a from t1); |
|
44 |
select * from t3 where b in (select a from t1); |
|
45 |
||
46 |
# Test overflow to MyISAM: |
|
47 |
set @save_max_heap_table_size= @@max_heap_table_size; |
|
48 |
set max_heap_table_size=16384; |
|
49 |
set @save_join_buffer_size = @@join_buffer_size; |
|
50 |
set join_buffer_size= 8000; |
|
51 |
||
52 |
drop table t3; |
|
53 |
create table t3 ( |
|
54 |
a int, |
|
55 |
b int, |
|
56 |
key(b), |
|
57 |
pk1 char(200), pk2 char(200), |
|
58 |
primary key(pk1, pk2) |
|
59 |
) engine=innodb; |
|
60 |
insert into t3 select |
|
61 |
A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a |
|
62 |
from t0 A, t0 B where B.a <5; |
|
63 |
||
64 |
explain select * from t3 where b in (select a from t0); |
|
65 |
select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); |
|
66 |
||
67 |
set join_buffer_size= @save_join_buffer_size; |
|
68 |
set max_heap_table_size= @save_max_heap_table_size; |
|
69 |
||
70 |
# O2I join orders, with shortcutting: |
|
71 |
explain select * from t1 where a in (select b from t2); |
|
72 |
select * from t1; |
|
73 |
select * from t1 where a in (select b from t2); |
|
74 |
||
75 |
drop table t1, t2, t3; |
|
76 |
# (no need for anything in range/index_merge/DS-MRR) |
|
77 |
||
78 |
#
|
|
79 |
# Test join buffering |
|
80 |
#
|
|
81 |
set @save_join_buffer_size = @@join_buffer_size; |
|
82 |
set join_buffer_size= 8000; |
|
83 |
||
84 |
create table t1 (a int, filler1 binary(200), filler2 binary(200)); |
|
85 |
insert into t1 select a, 'filler123456', 'filler123456' from t0; |
|
86 |
insert into t1 select a+10, 'filler123456', 'filler123456' from t0; |
|
87 |
||
88 |
create table t2 as select * from t1; |
|
89 |
insert into t1 select a+20, 'filler123456', 'filler123456' from t0; |
|
90 |
||
91 |
insert into t1 values (2, 'duplicate ok', 'duplicate ok'); |
|
92 |
insert into t1 values (18, 'duplicate ok', 'duplicate ok'); |
|
93 |
||
94 |
insert into t2 values (3, 'duplicate ok', 'duplicate ok'); |
|
95 |
insert into t2 values (19, 'duplicate ok', 'duplicate ok'); |
|
96 |
||
97 |
explain select |
|
98 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z |
|
99 |
from t1 ot where a in (select a from t2 it); |
|
100 |
select |
|
101 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z |
|
102 |
from t1 ot where a in (select a from t2 it); |
|
103 |
||
104 |
explain select |
|
105 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) |
|
106 |
from t2 ot where a in (select a from t1 it); |
|
107 |
select |
|
108 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) |
|
109 |
from t2 ot where a in (select a from t1 it); |
|
110 |
||
111 |
# Now let the buffer overfill: |
|
112 |
insert into t1 select a+20, 'filler123456', 'filler123456' from t0; |
|
113 |
insert into t1 select a+20, 'filler123456', 'filler123456' from t0; |
|
114 |
||
115 |
explain select |
|
116 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z |
|
117 |
from t1 ot where a in (select a from t2 it); |
|
118 |
select |
|
119 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z |
|
120 |
from t1 ot where a in (select a from t2 it); |
|
121 |
||
122 |
explain select |
|
123 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) |
|
124 |
from t2 ot where a in (select a from t1 it); |
|
125 |
select |
|
126 |
a, mid(filler1, 1,10), length(filler1)=length(filler2) |
|
127 |
from t2 ot where a in (select a from t1 it); |
|
128 |
||
129 |
drop table t1, t2; |
|
130 |
||
131 |
# Check ref access to tables inside the OJ nest inside the SJ nest |
|
132 |
create table t1 (a int, b int, key(a)); |
|
133 |
create table t2 (a int, b int, key(a)); |
|
134 |
create table t3 (a int, b int, key(a)); |
|
135 |
||
136 |
insert into t1 select a,a from t0; |
|
137 |
insert into t2 select a,a from t0; |
|
138 |
insert into t3 select a,a from t0; |
|
139 |
||
140 |
--echo t2 and t3 must be use 'ref', not 'ALL': |
|
141 |
explain select * |
|
142 |
from t0 where a in |
|
143 |
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); |
|
144 |
||
145 |
drop table t0, t1,t2,t3; |
|
146 |
||
147 |
#
|
|
148 |
# Bug #27348: Assertion abort for a query with two subqueries to be flattened |
|
149 |
# |
|
150 |
CREATE TABLE t1 ( |
|
151 |
ID int(11) NOT NULL auto_increment, |
|
152 |
Name char(35) NOT NULL default '', |
|
153 |
Country char(3) NOT NULL default '', |
|
154 |
Population int(11) NOT NULL default '0', |
|
155 |
PRIMARY KEY (ID), |
|
156 |
INDEX (Population), |
|
157 |
INDEX (Country) |
|
158 |
);
|
|
159 |
CREATE TABLE t2 ( |
|
160 |
Code char(3) NOT NULL default '', |
|
161 |
Name char(52) NOT NULL default '', |
|
162 |
SurfaceArea float(10,2) NOT NULL default '0.00', |
|
163 |
Population int(11) NOT NULL default '0', |
|
164 |
Capital int(11) default NULL, |
|
165 |
PRIMARY KEY (Code), |
|
166 |
UNIQUE INDEX (Name), |
|
167 |
INDEX (Population) |
|
168 |
);
|
|
169 |
CREATE TABLE t3 ( |
|
170 |
Country char(3) NOT NULL default '', |
|
171 |
Language char(30) NOT NULL default '', |
|
172 |
Percentage float(3,1) NOT NULL default '0.0', |
|
173 |
PRIMARY KEY (Country, Language), |
|
174 |
INDEX (Percentage) |
|
175 |
);
|
|
176 |
||
177 |
--disable_query_log |
|
178 |
INSERT INTO t1 VALUES |
|
179 |
(1,'Kabul','AFG',1780000),(2,'Qandahar','AFG',237500), |
|
180 |
(3,'Herat','AFG',186800),(4,'Mazar-e-Sharif','AFG',127800), |
|
181 |
(5,'Amsterdam','NLD',731200),(6,'Rotterdam','NLD',593321), |
|
182 |
(7,'Haag','NLD',440900),(8,'Utrecht','NLD',234323), |
|
183 |
(9,'Eindhoven','NLD',201843),(10,'Tilburg','NLD',193238), |
|
184 |
(11,'Groningen','NLD',172701),(12,'Breda','NLD',160398), |
|
185 |
(13,'Apeldoorn','NLD',153491),(14,'Nijmegen','NLD',152463), |
|
186 |
(15,'Enschede','NLD',149544),(16,'Haarlem','NLD',148772), |
|
187 |
(17,'Almere','NLD',142465),(18,'Arnhem','NLD',138020), |
|
188 |
(19,'Zaanstad','NLD',135621),(20,'ÂŽs-Hertogenbosch','NLD',129170), |
|
189 |
(21,'Amersfoort','NLD',126270),(22,'Maastricht','NLD',122087), |
|
190 |
(23,'Dordrecht','NLD',119811),(24,'Leiden','NLD',117196), |
|
191 |
(25,'Haarlemmermeer','NLD',110722),(26,'Zoetermeer','NLD',110214), |
|
192 |
(27,'Emmen','NLD',105853),(28,'Zwolle','NLD',105819), |
|
193 |
(29,'Ede','NLD',101574),(30,'Delft','NLD',95268); |
|
194 |
||
195 |
INSERT INTO t2 VALUES |
|
196 |
('AFG','Afghanistan',652090.00,22720000,1), |
|
197 |
('NLD','Netherlands',41526.00,15864000,5), |
|
198 |
('ANT','Netherlands Antilles',800.00,217000,33), |
|
199 |
('ALB','Albania',28748.00,3401200,34), |
|
200 |
('DZA','Algeria',2381741.00,31471000,35), |
|
201 |
('ASM','American Samoa',199.00,68000,54), |
|
202 |
('AND','Andorra',468.00,78000,55), |
|
203 |
('AGO','Angola',1246700.00,12878000,56), |
|
204 |
('AIA','Anguilla',96.00,8000,62), |
|
205 |
('ATG','Antigua and Barbuda',442.00,68000,63), |
|
206 |
('ARE','United Arab Emirates',83600.00,2441000,65), |
|
207 |
('ARG','Argentina',2780400.00,37032000,69), |
|
208 |
('ARM','Armenia',29800.00,3520000,126), |
|
209 |
('ABW','Aruba',193.00,103000,129), |
|
210 |
('AUS','Australia',7741220.00,18886000,135), |
|
211 |
('AZE','Azerbaijan',86600.00,7734000,144); |
|
212 |
||
213 |
INSERT INTO t3 VALUES |
|
214 |
('AFG','Pashto',52.4),('NLD','Dutch',95.6), |
|
215 |
('ANT','Papiamento',86.2),('ALB','Albaniana',97.9), |
|
216 |
('DZA','Arabic',86.0),('ASM','Samoan',90.6), |
|
217 |
('AND','Spanish',44.6),('AGO','Ovimbundu',37.2), |
|
218 |
('AIA','English',0.0),('ATG','Creole English',95.7), |
|
219 |
('ARE','Arabic',42.0),('ARG','Spanish',96.8), |
|
220 |
('ARM','Armenian',93.4),('ABW','Papiamento',76.7), |
|
221 |
('AUS','English',81.2),('AZE','Azerbaijani',89.0), |
|
222 |
('BHS','Creole English',89.7),('BHR','Arabic',67.7), |
|
223 |
('BGD','Bengali',97.7),('BRB','Bajan',95.1), |
|
224 |
('BEL','Dutch',59.2),('BLZ','English',50.8); |
|
225 |
--enable_query_log |
|
226 |
||
227 |
EXPLAIN
|
|
228 |
SELECT Name FROM t2 |
|
229 |
WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000) |
|
230 |
AND |
|
231 |
t2.Code IN (SELECT Country FROM t3 |
|
232 |
WHERE Language='English' AND Percentage > 10 AND |
|
233 |
t2.Population > 100000); |
|
234 |
||
235 |
DROP TABLE t1,t2,t3; |
|
236 |
|
|
237 |
# BUG#30993: |
|
238 |
CREATE TABLE t1 ( |
|
239 |
Code char(3) NOT NULL DEFAULT '', |
|
240 |
Name char(52) NOT NULL DEFAULT '', |
|
241 |
Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', |
|
242 |
Region char(26) NOT NULL DEFAULT '', |
|
243 |
SurfaceArea float(10,2) NOT NULL DEFAULT '0.00', |
|
244 |
IndepYear smallint(6) DEFAULT NULL, |
|
245 |
Population int(11) NOT NULL DEFAULT '0', |
|
246 |
LifeExpectancy float(3,1) DEFAULT NULL, |
|
247 |
GNP float(10,2) DEFAULT NULL, |
|
248 |
GNPOld float(10,2) DEFAULT NULL, |
|
249 |
LocalName char(45) NOT NULL DEFAULT '', |
|
250 |
GovernmentForm char(45) NOT NULL DEFAULT '', |
|
251 |
HeadOfState char(60) DEFAULT NULL, |
|
252 |
Capital int(11) DEFAULT NULL, |
|
253 |
Code2 char(2) NOT NULL DEFAULT '', |
|
254 |
PRIMARY KEY (Code) |
|
255 |
);
|
|
256 |
||
257 |
CREATE TABLE t2 ( |
|
258 |
ID int(11) NOT NULL AUTO_INCREMENT, |
|
259 |
Name char(35) NOT NULL DEFAULT '', |
|
260 |
CountryCode char(3) NOT NULL DEFAULT '', |
|
261 |
District char(20) NOT NULL DEFAULT '', |
|
262 |
Population int(11) NOT NULL DEFAULT '0', |
|
263 |
PRIMARY KEY (ID), |
|
264 |
KEY CountryCode (CountryCode) |
|
265 |
);
|
|
266 |
||
267 |
--echo Fill the table with test data |
|
268 |
--disable_query_log |
|
269 |
insert into t2 (ID, Name, CountryCode, Population) values |
|
270 |
(1,'Kabul','AFG',1780000), (2,'Qandahar','AFG',237500), (3,'Herat','AFG',186800), |
|
271 |
(4,'Mazar-e-Sharif','AFG',127800), (33,'Willemstad','ANT',2345), (34,'Tirana','ALB',270000), |
|
272 |
(55,'Andorra la Vella','AND',21189), (61,'South Hill','AIA',961), (62,'The Valley','AIA',595), |
|
273 |
(63,'Saint JohnŽs','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu Dhabi','ARE',398695), |
|
274 |
(66,'Sharja','ARE',320095), (67,'al-Ayn','ARE',225970), (68,'Ajman','ARE',114395), |
|
275 |
(126,'Yerevan','ARM',1248700), (127,'Gjumri','ARM',211700), (128,'Vanadzor','ARM',172700), |
|
276 |
(129,'Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'Gäncä','AZE',299300), |
|
277 |
(146,'Sumqayit','AZE',283000), (147,'Mingäçevir','AZE',93900), (148,'Nassau','BHS',172000), |
|
278 |
(149,'al-Manama','BHR',148000), (150,'Dhaka','BGD',3612850), (151,'Chittagong','BGD',1392860), |
|
279 |
(152,'Khulna','BGD',663340), (153,'Rajshahi','BGD',294056), (154,'Narayanganj','BGD',202134), |
|
280 |
(155,'Rangpur','BGD',191398), (156,'Mymensingh','BGD',188713), (157,'Barisal','BGD',170232), |
|
281 |
(158,'Tungi','BGD',168702), (159,'Jessore','BGD',139710), (160,'Comilla','BGD',135313), |
|
282 |
(161,'Nawabganj','BGD',130577), (162,'Dinajpur','BGD',127815), (163,'Bogra','BGD',120170), |
|
283 |
(164,'Sylhet','BGD',117396), (165,'Brahmanbaria','BGD',109032), (166,'Tangail','BGD',106004), |
|
284 |
(167,'Jamalpur','BGD',103556), (168,'Pabna','BGD',103277), (169,'Naogaon','BGD',101266), |
|
285 |
(170,'Sirajganj','BGD',99669), (171,'Narsinghdi','BGD',98342), (172,'Saidpur','BGD',96777), |
|
286 |
(173,'Gazipur','BGD',96717), (174,'Bridgetown','BRB',6070), (175,'Antwerpen','BEL',446525), |
|
287 |
(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Liège','BEL',185639), |
|
288 |
(179,'Bruxelles [Brussel]','BEL',133859), (180,'Brugge','BEL',116246), (181,'Schaerbeek','BEL',105692), |
|
289 |
(182,'Namur','BEL',105419), (183,'Mons','BEL',90935), (184,'Belize City','BLZ',55810), |
|
290 |
(185,'Belmopan','BLZ',7105), (190,'Saint George','BMU',1800), (191,'Hamilton','BMU',1200), |
|
291 |
(192,'Thimphu','BTN',22000), (201,'Sarajevo','BIH',360000), (202,'Banja Luka','BIH',143079), |
|
292 |
(203,'Zenica','BIH',96027), (538,'Bandar Seri Begawan','BRN',21484), (539,'Sofija','BGR',1122302), |
|
293 |
(540,'Plovdiv','BGR',342584), (541,'Varna','BGR',299801), (542,'Burgas','BGR',195255), |
|
294 |
(543,'Ruse','BGR',166467), (544,'Stara Zagora','BGR',147939), (545,'Pleven','BGR',121952), |
|
295 |
(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'umen','BGR',94686), |
|
296 |
(553,'George Town','CYM',19600), (584,'San José','CRI',339131), (1523,'Wien','AUT',1608144), |
|
297 |
(1524,'Graz','AUT',240967), (1525,'Linz','AUT',188022), (1526,'Salzburg','AUT',144247), |
|
298 |
(1527,'Innsbruck','AUT',111752), (1528,'Klagenfurt','AUT',91141), (1810,'Montréal','CAN',1016376), |
|
299 |
(1811,'Calgary','CAN',768082), (1812,'Toronto','CAN',688275), (1813,'North York','CAN',622632), |
|
300 |
(1814,'Winnipeg','CAN',618477), (1815,'Edmonton','CAN',616306), (1816,'Mississauga','CAN',608072), |
|
301 |
(1817,'Scarborough','CAN',594501), (1818,'Vancouver','CAN',514008), (1819,'Etobicoke','CAN',348845), |
|
302 |
(1820,'London','CAN',339917), (1821,'Hamilton','CAN',335614), (1822,'Ottawa','CAN',335277), |
|
303 |
(1823,'Laval','CAN',330393), (1824,'Surrey','CAN',304477), (1825,'Brampton','CAN',296711), |
|
304 |
(1826,'Windsor','CAN',207588), (1827,'Saskatoon','CAN',193647), (1828,'Kitchener','CAN',189959), |
|
305 |
(1829,'Markham','CAN',189098), (1830,'Regina','CAN',180400), (1831,'Burnaby','CAN',179209), |
|
306 |
(1832,'Québec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','CAN',148867), |
|
307 |
(1835,'Vaughan','CAN',147889), (1836,'Burlington','CAN',145150), (1837,'Oshawa','CAN',140173), |
|
308 |
(1838,'Oakville','CAN',139192), (1839,'Saint Catharines','CAN',136216), (1840,'Longueuil','CAN',127977), |
|
309 |
(1841,'Richmond Hill','CAN',116428), (1842,'Thunder Bay','CAN',115913), (1843,'Nepean','CAN',115100), |
|
310 |
(1844,'Cape Breton','CAN',114733), (1845,'East York','CAN',114034), (1846,'Halifax','CAN',113910), |
|
311 |
(1847,'Cambridge','CAN',109186), (1848,'Gloucester','CAN',107314), (1849,'Abbotsford','CAN',105403), |
|
312 |
(1850,'Guelph','CAN',103593), (1851,'Saint JohnŽs','CAN',101936), (1852,'Coquitlam','CAN',101820), |
|
313 |
(1853,'Saanich','CAN',101388), (1854,'Gatineau','CAN',100702), (1855,'Delta','CAN',95411), |
|
314 |
(1856,'Sudbury','CAN',92686), (1857,'Kelowna','CAN',89442), (1858,'Barrie','CAN',89269), |
|
315 |
(1890,'Shanghai','CHN',9696300), (1891,'Peking','CHN',7472000), (1892,'Chongqing','CHN',6351600), |
|
316 |
(1893,'Tianjin','CHN',5286800), (1894,'Wuhan','CHN',4344600), (1895,'Harbin','CHN',4289800), |
|
317 |
(1896,'Shenyang','CHN',4265200), (1897,'Kanton [Guangzhou]','CHN',4256300), (1898,'Chengdu','CHN',3361500), |
|
318 |
(1899,'Nanking [Nanjing]','CHN',2870300), (1900,'Changchun','CHN',2812000), (1901,'XiŽan','CHN',2761400), |
|
319 |
(1902,'Dalian','CHN',2697000), (1903,'Qingdao','CHN',2596000), (1904,'Jinan','CHN',2278100), |
|
320 |
(1905,'Hangzhou','CHN',2190500), (1906,'Zhengzhou','CHN',2107200), (1907,'Shijiazhuang','CHN',2041500), |
|
321 |
(1908,'Taiyuan','CHN',1968400), (1909,'Kunming','CHN',1829500), (1910,'Changsha','CHN',1809800), |
|
322 |
(1911,'Nanchang','CHN',1691600), (1912,'Fuzhou','CHN',1593800), (1913,'Lanzhou','CHN',1565800), |
|
323 |
(1914,'Guiyang','CHN',1465200), (1915,'Ningbo','CHN',1371200), (1916,'Hefei','CHN',1369100), |
|
324 |
(1917,'Urumti [Ürümqi]','CHN',1310100), (1918,'Anshan','CHN',1200000), (1919,'Fushun','CHN',1200000), |
|
325 |
(1920,'Nanning','CHN',1161800), (1921,'Zibo','CHN',1140000), (1922,'Qiqihar','CHN',1070000), |
|
326 |
(1923,'Jilin','CHN',1040000), (1924,'Tangshan','CHN',1040000), (1925,'Baotou','CHN',980000), |
|
327 |
(1926,'Shenzhen','CHN',950500), (1927,'Hohhot','CHN',916700), (1928,'Handan','CHN',840000), |
|
328 |
(1929,'Wuxi','CHN',830000), (1930,'Xuzhou','CHN',810000), (1931,'Datong','CHN',800000), |
|
329 |
(1932,'Yichun','CHN',800000), (1933,'Benxi','CHN',770000), (1934,'Luoyang','CHN',760000), |
|
330 |
(1935,'Suzhou','CHN',710000), (1936,'Xining','CHN',700200), (1937,'Huainan','CHN',700000), |
|
331 |
(1938,'Jixi','CHN',683885), (1939,'Daqing','CHN',660000), (1940,'Fuxin','CHN',640000), |
|
332 |
(1941,'Amoy [Xiamen]','CHN',627500), (1942,'Liuzhou','CHN',610000), (1943,'Shantou','CHN',580000), |
|
333 |
(1944,'Jinzhou','CHN',570000), (1945,'Mudanjiang','CHN',570000), (1946,'Yinchuan','CHN',544500), |
|
334 |
(1947,'Changzhou','CHN',530000), (1948,'Zhangjiakou','CHN',530000), (1949,'Dandong','CHN',520000), |
|
335 |
(1950,'Hegang','CHN',520000), (1951,'Kaifeng','CHN',510000), (1952,'Jiamusi','CHN',493409), |
|
336 |
(1953,'Liaoyang','CHN',492559), (1954,'Hengyang','CHN',487148), (1955,'Baoding','CHN',483155), |
|
337 |
(1956,'Hunjiang','CHN',482043), (1957,'Xinxiang','CHN',473762), (1958,'Huangshi','CHN',457601), |
|
338 |
(1959,'Haikou','CHN',454300), (1960,'Yantai','CHN',452127), (1961,'Bengbu','CHN',449245), |
|
339 |
(1962,'Xiangtan','CHN',441968), (1963,'Weifang','CHN',428522), (1964,'Wuhu','CHN',425740), |
|
340 |
(1965,'Pingxiang','CHN',425579), (1966,'Yingkou','CHN',421589), (1967,'Anyang','CHN',420332), |
|
341 |
(1968,'Panzhihua','CHN',415466), (1969,'Pingdingshan','CHN',410775), (1970,'Xiangfan','CHN',410407), |
|
342 |
(1971,'Zhuzhou','CHN',409924), (1972,'Jiaozuo','CHN',409100), (1973,'Wenzhou','CHN',401871), |
|
343 |
(1974,'Zhangjiang','CHN',400997), (1975,'Zigong','CHN',393184), (1976,'Shuangyashan','CHN',386081), |
|
344 |
(1977,'Zaozhuang','CHN',380846), (1978,'Yakeshi','CHN',377869), (1979,'Yichang','CHN',371601), |
|
345 |
(1980,'Zhenjiang','CHN',368316), (1981,'Huaibei','CHN',366549), (1982,'Qinhuangdao','CHN',364972), |
|
346 |
(1983,'Guilin','CHN',364130), (1984,'Liupanshui','CHN',363954), (1985,'Panjin','CHN',362773), |
|
347 |
(1986,'Yangquan','CHN',362268), (1987,'Jinxi','CHN',357052), (1988,'Liaoyuan','CHN',354141), |
|
348 |
(1989,'Lianyungang','CHN',354139), (1990,'Xianyang','CHN',352125), (1991,'TaiŽan','CHN',350696), |
|
349 |
(1992,'Chifeng','CHN',350077), (1993,'Shaoguan','CHN',350043), (1994,'Nantong','CHN',343341), |
|
350 |
(1995,'Leshan','CHN',341128), (1996,'Baoji','CHN',337765), (1997,'Linyi','CHN',324720), |
|
351 |
(1998,'Tonghua','CHN',324600), (1999,'Siping','CHN',317223), (2000,'Changzhi','CHN',317144), |
|
352 |
(2001,'Tengzhou','CHN',315083), (2002,'Chaozhou','CHN',313469), (2003,'Yangzhou','CHN',312892), |
|
353 |
(2004,'Dongwan','CHN',308669), (2005,'MaŽanshan','CHN',305421), (2006,'Foshan','CHN',303160), |
|
354 |
(2007,'Yueyang','CHN',302800), (2008,'Xingtai','CHN',302789), (2009,'Changde','CHN',301276), |
|
355 |
(2010,'Shihezi','CHN',299676), (2011,'Yancheng','CHN',296831), (2012,'Jiujiang','CHN',291187), |
|
356 |
(2013,'Dongying','CHN',281728), (2014,'Shashi','CHN',281352), (2015,'Xintai','CHN',281248), |
|
357 |
(2016,'Jingdezhen','CHN',281183), (2017,'Tongchuan','CHN',280657), (2018,'Zhongshan','CHN',278829), |
|
358 |
(2019,'Shiyan','CHN',273786), (2020,'Tieli','CHN',265683), (2021,'Jining','CHN',265248), |
|
359 |
(2022,'Wuhai','CHN',264081), (2023,'Mianyang','CHN',262947), (2024,'Luzhou','CHN',262892), |
|
360 |
(2025,'Zunyi','CHN',261862), (2026,'Shizuishan','CHN',257862), (2027,'Neijiang','CHN',256012), |
|
361 |
(2028,'Tongliao','CHN',255129), (2029,'Tieling','CHN',254842), (2030,'Wafangdian','CHN',251733), |
|
362 |
(2031,'Anqing','CHN',250718), (2032,'Shaoyang','CHN',247227), (2033,'Laiwu','CHN',246833), |
|
363 |
(2034,'Chengde','CHN',246799), (2035,'Tianshui','CHN',244974), (2036,'Nanyang','CHN',243303), |
|
364 |
(2037,'Cangzhou','CHN',242708), (2038,'Yibin','CHN',241019), (2039,'Huaiyin','CHN',239675), |
|
365 |
(2040,'Dunhua','CHN',235100), (2041,'Yanji','CHN',230892), (2042,'Jiangmen','CHN',230587), |
|
366 |
(2043,'Tongling','CHN',228017), (2044,'Suihua','CHN',227881), (2045,'Gongziling','CHN',226569), |
|
367 |
(2046,'Xiantao','CHN',222884), (2047,'Chaoyang','CHN',222394), (2048,'Ganzhou','CHN',220129), |
|
368 |
(2049,'Huzhou','CHN',218071), (2050,'Baicheng','CHN',217987), (2051,'Shangzi','CHN',215373), |
|
369 |
(2052,'Yangjiang','CHN',215196), (2053,'Qitaihe','CHN',214957), (2054,'Gejiu','CHN',214294), |
|
370 |
(2055,'Jiangyin','CHN',213659), (2056,'Hebi','CHN',212976), (2057,'Jiaxing','CHN',211526), |
|
371 |
(2058,'Wuzhou','CHN',210452), (2059,'Meihekou','CHN',209038), (2060,'Xuchang','CHN',208815), |
|
372 |
(2061,'Liaocheng','CHN',207844), (2062,'Haicheng','CHN',205560), (2063,'Qianjiang','CHN',205504), |
|
373 |
(2064,'Baiyin','CHN',204970), (2065,'BeiŽan','CHN',204899), (2066,'Yixing','CHN',200824), |
|
374 |
(2067,'Laizhou','CHN',198664), (2068,'Qaramay','CHN',197602), (2069,'Acheng','CHN',197595), |
|
375 |
(2070,'Dezhou','CHN',195485), (2071,'Nanping','CHN',195064), (2072,'Zhaoqing','CHN',194784), |
|
376 |
(2073,'Beipiao','CHN',194301), (2074,'Fengcheng','CHN',193784), (2075,'Fuyu','CHN',192981), |
|
377 |
(2076,'Xinyang','CHN',192509), (2077,'Dongtai','CHN',192247), (2078,'Yuci','CHN',191356), |
|
378 |
(2079,'Honghu','CHN',190772), (2080,'Ezhou','CHN',190123), (2081,'Heze','CHN',189293), |
|
379 |
(2082,'Daxian','CHN',188101), (2083,'Linfen','CHN',187309), (2084,'Tianmen','CHN',186332), |
|
380 |
(2085,'Yiyang','CHN',185818), (2086,'Quanzhou','CHN',185154), (2087,'Rizhao','CHN',185048), |
|
381 |
(2088,'Deyang','CHN',182488), (2089,'Guangyuan','CHN',182241), (2090,'Changshu','CHN',181805), |
|
382 |
(2091,'Zhangzhou','CHN',181424), (2092,'Hailar','CHN',180650), (2093,'Nanchong','CHN',180273), |
|
383 |
(2094,'Jiutai','CHN',180130), (2095,'Zhaodong','CHN',179976), (2096,'Shaoxing','CHN',179818), |
|
384 |
(2097,'Fuyang','CHN',179572), (2098,'Maoming','CHN',178683), (2099,'Qujing','CHN',178669), |
|
385 |
(2100,'Ghulja','CHN',177193), (2101,'Jiaohe','CHN',176367), (2102,'Puyang','CHN',175988), |
|
386 |
(2103,'Huadian','CHN',175873), (2104,'Jiangyou','CHN',175753), (2105,'Qashqar','CHN',174570), |
|
387 |
(2106,'Anshun','CHN',174142), (2107,'Fuling','CHN',173878), (2108,'Xinyu','CHN',173524), |
|
388 |
(2109,'Hanzhong','CHN',169930), (2110,'Danyang','CHN',169603), (2111,'Chenzhou','CHN',169400), |
|
389 |
(2112,'Xiaogan','CHN',166280), (2113,'Shangqiu','CHN',164880), (2114,'Zhuhai','CHN',164747), |
|
390 |
(2115,'Qingyuan','CHN',164641), (2116,'Aqsu','CHN',164092), (2117,'Jining','CHN',163552), |
|
391 |
(2118,'Xiaoshan','CHN',162930), (2119,'Zaoyang','CHN',162198), (2120,'Xinghua','CHN',161910), |
|
392 |
(2121,'Hami','CHN',161315), (2122,'Huizhou','CHN',161023), (2123,'Jinmen','CHN',160794), |
|
393 |
(2124,'Sanming','CHN',160691), (2125,'Ulanhot','CHN',159538), (2126,'Korla','CHN',159344), |
|
394 |
(2127,'Wanxian','CHN',156823), (2128,'RuiŽan','CHN',156468), (2129,'Zhoushan','CHN',156317), |
|
395 |
(2130,'Liangcheng','CHN',156307), (2131,'Jiaozhou','CHN',153364), (2132,'Taizhou','CHN',152442), |
|
396 |
(2133,'Suzhou','CHN',151862), (2134,'Yichun','CHN',151585), (2135,'Taonan','CHN',150168), |
|
397 |
(2136,'Pingdu','CHN',150123), (2137,'JiŽan','CHN',148583), (2138,'Longkou','CHN',148362), |
|
398 |
(2139,'Langfang','CHN',148105), (2140,'Zhoukou','CHN',146288), (2141,'Suining','CHN',146086), |
|
399 |
(2142,'Yulin','CHN',144467), (2143,'Jinhua','CHN',144280), (2144,'LiuŽan','CHN',144248), |
|
400 |
(2145,'Shuangcheng','CHN',142659), (2146,'Suizhou','CHN',142302), (2147,'Ankang','CHN',142170), |
|
401 |
(2148,'Weinan','CHN',140169), (2149,'Longjing','CHN',139417), (2150,'DaŽan','CHN',138963), |
|
402 |
(2151,'Lengshuijiang','CHN',137994), (2152,'Laiyang','CHN',137080), (2153,'Xianning','CHN',136811), |
|
403 |
(2154,'Dali','CHN',136554), (2155,'Anda','CHN',136446), (2156,'Jincheng','CHN',136396), |
|
404 |
(2157,'Longyan','CHN',134481), (2158,'Xichang','CHN',134419), (2159,'Wendeng','CHN',133910), |
|
405 |
(2160,'Hailun','CHN',133565), (2161,'Binzhou','CHN',133555), (2162,'Linhe','CHN',133183), |
|
406 |
(2163,'Wuwei','CHN',133101), (2164,'Duyun','CHN',132971), (2165,'Mishan','CHN',132744), |
|
407 |
(2166,'Shangrao','CHN',132455), (2167,'Changji','CHN',132260), (2168,'Meixian','CHN',132156), |
|
408 |
(2169,'Yushu','CHN',131861), (2170,'Tiefa','CHN',131807), (2171,'HuaiŽan','CHN',131149), |
|
409 |
(2172,'Leiyang','CHN',130115), (2173,'Zalantun','CHN',130031), (2174,'Weihai','CHN',128888), |
|
410 |
(2175,'Loudi','CHN',128418), (2176,'Qingzhou','CHN',128258), (2177,'Qidong','CHN',126872), |
|
411 |
(2178,'Huaihua','CHN',126785), (2179,'Luohe','CHN',126438), (2180,'Chuzhou','CHN',125341), |
|
412 |
(2181,'Kaiyuan','CHN',124219), (2182,'Linqing','CHN',123958), (2183,'Chaohu','CHN',123676), |
|
413 |
(2184,'Laohekou','CHN',123366), (2185,'Dujiangyan','CHN',123357), (2186,'Zhumadian','CHN',123232), |
|
414 |
(2187,'Linchuan','CHN',121949), (2188,'Jiaonan','CHN',121397), (2189,'Sanmenxia','CHN',120523), |
|
415 |
(2190,'Heyuan','CHN',120101), (2191,'Manzhouli','CHN',120023), (2192,'Lhasa','CHN',120000), |
|
416 |
(2193,'Lianyuan','CHN',118858), (2194,'Kuytun','CHN',118553), (2195,'Puqi','CHN',117264), |
|
417 |
(2196,'Hongjiang','CHN',116188), (2197,'Qinzhou','CHN',114586), (2198,'Renqiu','CHN',114256), |
|
418 |
(2199,'Yuyao','CHN',114065), (2200,'Guigang','CHN',114025), (2201,'Kaili','CHN',113958), |
|
419 |
(2202,'YanŽan','CHN',113277), (2203,'Beihai','CHN',112673), (2204,'Xuangzhou','CHN',112673), |
|
420 |
(2205,'Quzhou','CHN',112373), (2206,'YongŽan','CHN',111762), (2207,'Zixing','CHN',110048), |
|
421 |
(2208,'Liyang','CHN',109520), (2209,'Yizheng','CHN',109268), (2210,'Yumen','CHN',109234), |
|
422 |
(2211,'Liling','CHN',108504), (2212,'Yuncheng','CHN',108359), (2213,'Shanwei','CHN',107847), |
|
423 |
(2214,'Cixi','CHN',107329), (2215,'Yuanjiang','CHN',107004), (2216,'Bozhou','CHN',106346), |
|
424 |
(2217,'Jinchang','CHN',105287), (2218,'FuŽan','CHN',105265), (2219,'Suqian','CHN',105021), |
|
425 |
(2220,'Shishou','CHN',104571), (2221,'Hengshui','CHN',104269), (2222,'Danjiangkou','CHN',103211), |
|
426 |
(2223,'Fujin','CHN',103104), (2224,'Sanya','CHN',102820), (2225,'Guangshui','CHN',102770), |
|
427 |
(2226,'Huangshan','CHN',102628), (2227,'Xingcheng','CHN',102384), (2228,'Zhucheng','CHN',102134), |
|
428 |
(2229,'Kunshan','CHN',102052), (2230,'Haining','CHN',100478), (2231,'Pingliang','CHN',99265), |
|
429 |
(2232,'Fuqing','CHN',99193), (2233,'Xinzhou','CHN',98667), (2234,'Jieyang','CHN',98531), |
|
430 |
(2235,'Zhangjiagang','CHN',97994), (2236,'Tong Xian','CHN',97168), (2237,'YaŽan','CHN',95900), |
|
431 |
(2238,'Jinzhou','CHN',95761), (2239,'Emeishan','CHN',94000), (2240,'Enshi','CHN',93056), |
|
432 |
(2241,'Bose','CHN',93009), (2242,'Yuzhou','CHN',92889), (2243,'Kaiyuan','CHN',91999), |
|
433 |
(2244,'Tumen','CHN',91471), (2245,'Putian','CHN',91030), (2246,'Linhai','CHN',90870), |
|
434 |
(2247,'Xilin Hot','CHN',90646), (2248,'Shaowu','CHN',90286), (2249,'Junan','CHN',90222), |
|
435 |
(2250,'Huaying','CHN',89400), (2251,'Pingyi','CHN',89373), (2252,'Huangyan','CHN',89288), |
|
436 |
(2413,'La Habana','CUB',2256000), (2414,'Santiago de Cuba','CUB',433180), (2415,'Camagüey','CUB',298726), |
|
437 |
(2416,'Holguín','CUB',249492), (2417,'Santa Clara','CUB',207350), (2418,'Guantánamo','CUB',205078), |
|
438 |
(2419,'Pinar del Río','CUB',142100), (2420,'Bayamo','CUB',141000), (2421,'Cienfuegos','CUB',132770), |
|
439 |
(2422,'Victoria de las Tunas','CUB',132350), (2423,'Matanzas','CUB',123273), (2424,'Manzanillo','CUB',109350), |
|
440 |
(2425,'Sancti-Spíritus','CUB',100751), (2426,'Ciego de Ávila','CUB',98505), (2430,'Nicosia','CYP',195000), |
|
441 |
(2431,'Limassol','CYP',154400), (3245,'Zürich','CHE',336800), (3246,'Geneve','CHE',173500), |
|
442 |
(3247,'Basel','CHE',166700), (3248,'Bern','CHE',122700), (3249,'Lausanne','CHE',114500), |
|
443 |
(3339,'Praha','CZE',1181126), (3340,'Brno','CZE',381862), (3341,'Ostrava','CZE',320041), |
|
444 |
(3342,'Plzen','CZE',166759), (3343,'Olomouc','CZE',102702), (3344,'Liberec','CZE',99155), |
|
445 |
(3345,'Ceské Budejovice','CZE',98186), (3346,'Hradec Králové','CZE',98080), (3347,'Ústí nad Labem','CZE',95491), |
|
446 |
(3348,'Pardubice','CZE',91309), (3520,'Minsk','BLR',1674000), (3521,'Gomel','BLR',475000), |
|
447 |
(3522,'Mogiljov','BLR',356000), (3523,'Vitebsk','BLR',340000), (3524,'Grodno','BLR',302000), |
|
448 |
(3525,'Brest','BLR',286000), (3526,'Bobruisk','BLR',221000), (3527,'Baranoviti','BLR',167000), |
|
449 |
(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Ora','BLR',124000), |
|
450 |
(3531,'Mozyr','BLR',110000), (3532,'Novopolotsk','BLR',106000), (3533,'Lida','BLR',101000), |
|
451 |
(3534,'Soligorsk','BLR',101000), (3535,'Molodetno','BLR',97000); |
|
452 |
||
453 |
insert into t1 (Code, Name, Continent) values |
|
454 |
('AFG','Afghanistan','Asia'), ('ANT','Netherlands Antilles','North America'), |
|
455 |
('ALB','Albania','Europe'), ('AND','Andorra','Europe'), |
|
456 |
('AIA','Anguilla','North America'), ('ATG','Antigua and Barbuda','North America'), |
|
457 |
('ARE','United Arab Emirates','Asia'), ('ARM','Armenia','Asia'), |
|
458 |
('ABW','Aruba','North America'), ('AZE','Azerbaijan','Asia'), |
|
459 |
('BHS','Bahamas','North America'), ('BHR','Bahrain','Asia'), |
|
460 |
('BGD','Bangladesh','Asia'), ('BRB','Barbados','North America'), |
|
461 |
('BEL','Belgium','Europe'), ('BLZ','Belize','North America'), |
|
462 |
('BMU','Bermuda','North America'), ('BTN','Bhutan','Asia'), |
|
463 |
('BIH','Bosnia and Herzegovina','Europe'), ('BRN','Brunei','Asia'), |
|
464 |
('BGR','Bulgaria','Europe'), ('CYM','Cayman Islands','North America'), |
|
465 |
('CRI','Costa Rica','North America'), ('AUT','Austria','Europe'), |
|
466 |
('CAN','Canada','North America'), ('CHN','China','Asia'), |
|
467 |
('CUB','Cuba','North America'), ('CYP','Cyprus','Asia'), |
|
468 |
('CHE','Switzerland','Europe'), ('CZE','Czech Republic','Europe'), |
|
469 |
('BLR','Belarus','Europe'); |
|
470 |
update t2 set population=6000000 where Name in ('Wien', 'Vancouver', 'Praha'); |
|
471 |
--enable_query_log |
|
472 |
||
473 |
--echo This must not use LooseScan: |
|
474 |
EXPLAIN SELECT Name FROM t1 |
|
475 |
WHERE t1.Code IN ( |
|
476 |
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); |
|
477 |
||
478 |
SELECT Name FROM t1 |
|
479 |
WHERE t1.Code IN ( |
|
480 |
SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); |
|
481 |
||
482 |
drop table t1, t2; |
|
483 |
||
484 |
#
|
|
485 |
# Bug#33062: subquery in stored routine cause crash |
|
486 |
#
|
|
487 |
||
488 |
CREATE TABLE t1(a INT); |
|
489 |
CREATE TABLE t2(c INT); |
|
490 |
||
491 |
DELIMITER //; |
|
492 |
||
493 |
CREATE PROCEDURE p1(v1 int) |
|
494 |
BEGIN
|
|
495 |
SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2); |
|
496 |
END
|
|
497 |
//
|
|
498 |
||
499 |
CREATE PROCEDURE p2(v1 int) |
|
500 |
BEGIN
|
|
501 |
SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2); |
|
502 |
END
|
|
503 |
//
|
|
504 |
||
505 |
CREATE PROCEDURE p3(v1 int) |
|
506 |
BEGIN
|
|
507 |
SELECT 1 |
|
508 |
FROM |
|
509 |
t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, |
|
510 |
t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, |
|
511 |
t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, |
|
512 |
t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, |
|
513 |
t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, |
|
514 |
t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, |
|
515 |
t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, |
|
516 |
t1 t57,t1 t58,t1 t59,t1 t60 |
|
517 |
WHERE t01.a IN (SELECT c FROM t2); |
|
518 |
END
|
|
519 |
//
|
|
520 |
||
521 |
CREATE PROCEDURE p4(v1 int) |
|
522 |
BEGIN
|
|
523 |
SELECT 1 |
|
524 |
FROM |
|
525 |
t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, |
|
526 |
t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, |
|
527 |
t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, |
|
528 |
t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, |
|
529 |
t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, |
|
530 |
t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, |
|
531 |
t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, |
|
532 |
t1 t57,t1 t58,t1 t59,t1 t60 |
|
533 |
WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2); |
|
534 |
END
|
|
535 |
//
|
|
536 |
||
537 |
DELIMITER ;// |
|
538 |
||
539 |
CALL p1(1); |
|
540 |
CALL p2(1); |
|
541 |
CALL p3(1); |
|
542 |
CALL p4(1); |
|
543 |
||
544 |
DROP TABLE t1, t2; |
|
545 |
DROP PROCEDURE p1; |
|
546 |
DROP PROCEDURE p2; |
|
547 |
DROP PROCEDURE p3; |
|
548 |
DROP PROCEDURE p4; |