← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/subselect_sj2.test

  • Committer: Monty Taylor
  • Date: 2009-09-30 07:01:32 UTC
  • mto: This revision was merged to the branch mainline in revision 1184.
  • Revision ID: mordred@inaugust.com-20090930070132-b1ol1xu1rpajdddy
Small namespace cleanup.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/subselect_sj2.test
29
29
select * from t2;
30
30
explain select * from t2 where b in (select a from t1);
31
31
select * from t2 where b in (select a from t1);
32
 
 
 
32
 
 
33
#Bug 310344: This dies because pk* char(200) is too big. Changed pk* to char(100)
 
34
#due to UTF8 column sizes.
33
35
# Try an InnoDB table with very long rowid
34
36
create table t3 (
35
37
   a int, 
36
38
   b int,
37
39
   key(b),
38
 
   pk1 char(200), pk2 char(200), pk3 char(200),
 
40
   pk1 char(100), pk2 char(100), pk3 char(100),
39
41
   primary key(pk1, pk2, pk3)
40
42
) engine=innodb;
41
43
insert into t3 select a,a, a,a,a from t0;
47
49
set @save_max_heap_table_size= @@max_heap_table_size;
48
50
set max_heap_table_size=16384;
49
51
set @save_join_buffer_size = @@join_buffer_size;
50
 
set join_buffer_size= 8000;
51
52
 
 
53
#Bug 310344: This dies because pk* char(200) is too big. Based on UTF8 colummns.
 
54
#Changed pk* to char(100)
52
55
drop table t3;
53
56
create table t3 (
54
57
   a int, 
55
58
   b int,
56
59
   key(b),
57
 
   pk1 char(200), pk2 char(200),
 
60
   pk1 char(100), pk2 char(100),
58
61
   primary key(pk1, pk2)
59
62
) engine=innodb;
60
63
insert into t3 select 
79
82
# Test join buffering
80
83
#
81
84
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
set join_buffer_size=8000;
 
86
#Bug 310447: This query dies because binary datatype is not supported with join buffering and create table command.
 
87
#Change binary(200) to varbinary(20)
 
88
create table t1 (a int, filler1 varbinary(20), filler2 varbinary(20));
85
89
insert into t1 select a, 'filler123456', 'filler123456' from t0;
86
90
insert into t1 select a+10, 'filler123456', 'filler123456' from t0;
87
91
 
94
98
insert into t2 values (3, 'duplicate ok', 'duplicate ok');
95
99
insert into t2 values (19, 'duplicate ok', 'duplicate ok');
96
100
 
 
101
#This dies because: changed ot to t0
97
102
explain select 
98
103
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
99
 
from t1 ot where a in (select a from t2 it);
 
104
from t1 t0 where a in (select a from t2 it);
100
105
select 
101
106
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
102
 
from t1 ot where a in (select a from t2 it);
 
107
from t1 t0 where a in (select a from t2 it);
103
108
 
104
109
explain select 
105
110
  a, mid(filler1, 1,10), length(filler1)=length(filler2) 
106
 
from t2 ot where a in (select a from t1 it);
 
111
from t2 t0 where a in (select a from t1 it);
107
112
select 
108
113
  a, mid(filler1, 1,10), length(filler1)=length(filler2) 
109
 
from t2 ot where a in (select a from t1 it);
 
114
from t2 t0 where a in (select a from t1 it);
110
115
 
111
116
# Now let the buffer overfill:
112
117
insert into t1 select a+20, 'filler123456', 'filler123456' from t0;
114
119
 
115
120
explain select 
116
121
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
117
 
from t1 ot where a in (select a from t2 it);
 
122
from t1 t0 where a in (select a from t2 it);
118
123
select 
119
124
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
120
 
from t1 ot where a in (select a from t2 it);
 
125
from t1 t0 where a in (select a from t2 it);
121
126
 
122
127
explain select 
123
128
  a, mid(filler1, 1,10), length(filler1)=length(filler2) 
124
 
from t2 ot where a in (select a from t1 it);
 
129
from t2 t0 where a in (select a from t1 it);
125
130
select 
126
131
  a, mid(filler1, 1,10), length(filler1)=length(filler2) 
127
 
from t2 ot where a in (select a from t1 it);
 
132
from t2 t0 where a in (select a from t1 it);
128
133
 
129
134
drop table t1, t2;
130
135
 
146
151
 
147
152
#
148
153
# Bug #27348: Assertion abort for a query with two subqueries to be flattened  
149
 
#  
 
154
# Changed int(11) to int. 
150
155
CREATE TABLE t1 (
151
 
  ID int(11) NOT NULL auto_increment,
 
156
  ID int NOT NULL auto_increment,
152
157
  Name char(35) NOT NULL default '',
153
158
  Country char(3) NOT NULL default '',
154
 
  Population int(11) NOT NULL default '0',
 
159
  Population int NOT NULL default '0',
155
160
  PRIMARY KEY  (ID),
156
161
  INDEX (Population),
157
162
  INDEX (Country) 
160
165
  Code char(3) NOT NULL default '',
161
166
  Name char(52) NOT NULL default '',
162
167
  SurfaceArea float(10,2) NOT NULL default '0.00',
163
 
  Population int(11) NOT NULL default '0',
164
 
  Capital int(11) default NULL,
 
168
  Population int NOT NULL default '0',
 
169
  Capital int default NULL,
165
170
  PRIMARY KEY  (Code),
166
171
  UNIQUE INDEX (Name),
167
172
  INDEX (Population)
235
240
DROP TABLE t1,t2,t3;
236
241
  
237
242
# BUG#30993:
 
243
# changed syntax of int(11) to int
238
244
CREATE TABLE t1 (
239
245
  Code char(3) NOT NULL DEFAULT '',
240
246
  Name char(52) NOT NULL DEFAULT '',
241
247
  Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
242
248
  Region char(26) NOT NULL DEFAULT '',
243
249
  SurfaceArea float(10,2) NOT NULL DEFAULT '0.00',
244
 
  IndepYear int(6) DEFAULT NULL,
245
 
  Population int(11) NOT NULL DEFAULT '0',
 
250
  IndepYear int DEFAULT NULL,
 
251
  Population int NOT NULL DEFAULT '0',
246
252
  LifeExpectancy float(3,1) DEFAULT NULL,
247
253
  GNP float(10,2) DEFAULT NULL,
248
254
  GNPOld float(10,2) DEFAULT NULL,
249
255
  LocalName char(45) NOT NULL DEFAULT '',
250
256
  GovernmentForm char(45) NOT NULL DEFAULT '',
251
257
  HeadOfState char(60) DEFAULT NULL,
252
 
  Capital int(11) DEFAULT NULL,
 
258
  Capital int DEFAULT NULL,
253
259
  Code2 char(2) NOT NULL DEFAULT '',
254
260
  PRIMARY KEY (Code)
255
261
);
256
262
 
257
263
CREATE TABLE t2 (
258
 
  ID int(11) NOT NULL AUTO_INCREMENT,
 
264
  ID int NOT NULL, 
259
265
  Name char(35) NOT NULL DEFAULT '',
260
266
  CountryCode char(3) NOT NULL DEFAULT '',
261
 
  District char(20) NOT NULL DEFAULT '',
262
 
  Population int(11) NOT NULL DEFAULT '0',
 
267
  Population int NOT NULL DEFAULT '0',
263
268
  PRIMARY KEY (ID),
264
269
  KEY CountryCode (CountryCode)
265
270
);
266
271
 
 
272
# This died because the test data contained over 100 records with 
 
273
# characters such as umlauts, french accent codes. 
 
274
 
267
275
--echo Fill the table with test data
268
276
--disable_query_log
269
277
insert into t2 (ID, Name, CountryCode, Population) values 
270
278
(1,'Kabul','AFG',1780000), (2,'Qandahar','AFG',237500), (3,'Herat','AFG',186800),
271
279
(4,'Mazar-e-Sharif','AFG',127800), (33,'Willemstad','ANT',2345), (34,'Tirana','ALB',270000),
272
280
(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),
 
281
(63,'Saint Johns','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu Dhabi','ARE',398695),
274
282
(66,'Sharja','ARE',320095), (67,'al-Ayn','ARE',225970), (68,'Ajman','ARE',114395),
275
283
(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),
 
284
(129,'Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'Gnc','AZE',299300),
 
285
(146,'Sumqayit','AZE',283000), (147,'evir','AZE',93900), (148,'Nassau','BHS',172000),
278
286
(149,'al-Manama','BHR',148000), (150,'Dhaka','BGD',3612850), (151,'Chittagong','BGD',1392860),
279
287
(152,'Khulna','BGD',663340), (153,'Rajshahi','BGD',294056), (154,'Narayanganj','BGD',202134),
280
288
(155,'Rangpur','BGD',191398), (156,'Mymensingh','BGD',188713), (157,'Barisal','BGD',170232),
284
292
(167,'Jamalpur','BGD',103556), (168,'Pabna','BGD',103277), (169,'Naogaon','BGD',101266),
285
293
(170,'Sirajganj','BGD',99669), (171,'Narsinghdi','BGD',98342), (172,'Saidpur','BGD',96777),
286
294
(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),
 
295
(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Lige','BEL',185639),
 
296
(179,'Bruxelles','BEL',133859), (180,'Brugge','BEL',116246), (181,'Schaerbeek','BEL',105692),
289
297
(182,'Namur','BEL',105419), (183,'Mons','BEL',90935), (184,'Belize City','BLZ',55810),
290
298
(185,'Belmopan','BLZ',7105), (190,'Saint George','BMU',1800), (191,'Hamilton','BMU',1200),
291
299
(192,'Thimphu','BTN',22000), (201,'Sarajevo','BIH',360000), (202,'Banja Luka','BIH',143079),
292
300
(203,'Zenica','BIH',96027), (538,'Bandar Seri Begawan','BRN',21484), (539,'Sofija','BGR',1122302),
293
301
(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),
 
302
(543,'Ruse','BGR',166467), (544,'Stara Zagora','BGR',147939),
 
303
(545,'Pleven','BGR',121952),
 
304
(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'umen','BGR',94686),
 
305
(553,'George Town','CYM',19600), (584,'San Jose','CRI',339131),
 
306
(1523,'Wien','AUT',1608144),
297
307
(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,'Urumt�i [�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,'Baranovit�i','BLR',167000),
449
 
(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Or�a','BLR',124000),
450
 
(3531,'Mozyr','BLR',110000), (3532,'Novopolotsk','BLR',106000), (3533,'Lida','BLR',101000),
451
 
(3534,'Soligorsk','BLR',101000), (3535,'Molodet�no','BLR',97000);
 
308
 
 
309
(1814,'Winnipeg','CAN',618477), (1815,'Edmonton','CAN',616306),
 
310
(1816,'Mississauga','CAN',608072),
 
311
(1817,'Scarborough','CAN',594501), (1818,'Vancouver','CAN',514008),
 
312
(1819,'Etobicoke','CAN',348845),
 
313
(1820,'London','CAN',339917), (1821,'Hamilton','CAN',335614),
 
314
(1822,'Ottawa','CAN',335277),
 
315
(1823,'Laval','CAN',330393), (1824,'Surrey','CAN',304477),
 
316
(1825,'Brampton','CAN',296711),
 
317
(1826,'Windsor','CAN',207588), (1827,'Saskatoon','CAN',193647),
 
318
(1828,'Kitchener','CAN',189959),
 
319
 (1829,'Markham','CAN',189098), (1830,'Regina','CAN',180400),
 
320
(1831,'Burnaby','CAN',179209),
 
321
(1832,'Quebec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','CAN',148867),
 
322
(1835,'Vaughan','CAN',147889), (1836,'Burlington','CAN',145150),
 
323
(1837,'Oshawa','CAN',140173),
 
324
(1838,'Oakville','CAN',139192), (1839,'Saint Catharines','CAN',136216),
 
325
(1840,'Longueuil','CAN',127977),
 
326
(1841,'Richmond Hill','CAN',116428), (1842,'Thunder Bay','CAN',115913),
 
327
(1843,'Nepean','CAN',115100),
 
328
(1844,'Cape Breton','CAN',114733), (1845,'East York','CAN',114034),
 
329
(1846,'Halifax','CAN',113910),
 
330
(1847,'Cambridge','CAN',109186), (1848,'Gloucester','CAN',107314),
 
331
(1849,'Abbotsford','CAN',105403),
 
332
(1850,'Guelph','CAN',103593), (1851,'Saint John4s','CAN',101936),
 
333
(1852,'Coquitlam','CAN',101820),
 
334
(3534,'Soligorsk','BLR',101000), (3535,'Molodetno','BLR',97000);
452
335
 
453
336
insert into t1 (Code, Name, Continent) values 
454
337
('AFG','Afghanistan','Asia'), ('ANT','Netherlands Antilles','North America'),
467
350
('CUB','Cuba','North America'), ('CYP','Cyprus','Asia'),
468
351
('CHE','Switzerland','Europe'), ('CZE','Czech Republic','Europe'),
469
352
('BLR','Belarus','Europe');
470
 
update t2 set population=6000000 where Name in ('Wien', 'Vancouver', 'Praha');
 
353
#update t2 set population=6000000 where Name in ('Wien', 'Vancouver', 'Praha');
471
354
--enable_query_log
472
355
 
473
356
--echo This must not use LooseScan:
485
368
# Bug#33062: subquery in stored routine cause crash
486
369
#
487
370
 
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;
 
371
# This dies: stored procedures are not supported by drizzle
 
372
 
 
373
#CREATE TABLE t1(a INT);
 
374
#CREATE TABLE t2(c INT);
 
375
 
 
376
#DELIMITER //;
 
377
 
 
378
#CREATE PROCEDURE p1(v1 int)
 
379
#BEGIN
 
380
#  SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2);
 
381
#END
 
382
#//
 
383
 
 
384
#CREATE PROCEDURE p2(v1 int)
 
385
#BEGIN
 
386
#  SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2);
 
387
#END
 
388
#//
 
389
 
 
390
#CREATE PROCEDURE p3(v1 int)
 
391
#BEGIN
 
392
#  SELECT 1 
 
393
#  FROM 
 
394
#    t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
 
395
#    t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
 
396
#    t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
 
397
#    t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
 
398
#    t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
 
399
#    t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
 
400
#    t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
 
401
#    t1 t57,t1 t58,t1 t59,t1 t60
 
402
#  WHERE t01.a IN (SELECT c FROM t2);
 
403
#END
 
404
#//
 
405
 
 
406
#CREATE PROCEDURE p4(v1 int)
 
407
#BEGIN
 
408
#  SELECT 1 
 
409
#  FROM 
 
410
#    t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08,
 
411
#    t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16,
 
412
#    t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24,
 
413
#    t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32,
 
414
#    t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40,
 
415
#    t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48,
 
416
#    t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56,
 
417
#    t1 t57,t1 t58,t1 t59,t1 t60
 
418
#  WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2);
 
419
#END
 
420
#//
 
421
 
 
422
#DELIMITER ;//
 
423
 
 
424
#CALL p1(1);
 
425
#CALL p2(1);
 
426
#CALL p3(1);
 
427
#CALL p4(1);
 
428
 
 
429
#DROP TABLE t1, t2;
 
430
#DROP PROCEDURE p1;
 
431
#DROP PROCEDURE p2;
 
432
#DROP PROCEDURE p3;
 
433
#DROP PROCEDURE p4;