1# 2# DuplicateElimination strategy test 3# 4 5set @innodb_stats_persistent_save= @@innodb_stats_persistent; 6set @innodb_stats_persistent_sample_pages_save= 7 @@innodb_stats_persistent_sample_pages; 8 9set global innodb_stats_persistent= 1; 10set global innodb_stats_persistent_sample_pages=100; 11--source include/have_innodb.inc 12 13set @subselect_sj2_tmp= @@optimizer_switch; 14set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; 15set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 16SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'outer_join_with_cache=off'); 17SET optimizer_switch=ifnull(@optimizer_switch_for_subselect_sj2_test,'semijoin_with_cache=off'); 18if (`select @join_cache_level_for_subselect_sj2_test is null`) 19{ 20 set join_cache_level=1; 21} 22if (`select @join_cache_level_for_subselect_sj2_test is not null`) 23{ 24 set join_cache_level=@join_cache_level_for_subselect_sj2_test; 25} 26 27--disable_warnings 28drop table if exists t0, t1, t2, t3, t4, t5; 29drop view if exists v1; 30--enable_warnings 31 32create table t0 (a int); 33insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 34 35# First test simple cases: I20 order, no join buffering. 36 37create table t1 ( 38 a int, 39 b int 40); 41insert into t1 values (1,1),(1,1),(2,2); 42 43create table t2 ( 44 a int, 45 b int, 46 key(b) 47); 48insert into t2 select a, a/2 from t0; 49insert into t2 select a+10, a+10/2 from t0; 50 51select * from t1; 52select * from t2; 53explain select * from t2 where b in (select a from t1); 54select * from t2 where b in (select a from t1); 55 56# Try an InnoDB table with very long rowid 57create table t3 ( 58 a int, 59 b int, 60 key(b), 61 pk1 char(200), pk2 char(200), pk3 char(200), 62 primary key(pk1, pk2, pk3) 63) engine=innodb; 64insert into t3 select a,a, a,a,a from t0; 65insert into t3 select a,a, a+100,a+100,a+100 from t0; 66 67analyze table t1,t2,t3; 68 69explain select * from t3 where b in (select a from t1); 70select * from t3 where b in (select a from t1); 71 72# Test overflow to MyISAM: 73set @save_max_heap_table_size= @@max_heap_table_size; 74set max_heap_table_size=16384; 75set @save_join_buffer_size = @@join_buffer_size; 76set join_buffer_size= 8192; 77 78drop table t3; 79create table t3 ( 80 a int, 81 b int, 82 key(b), 83 pk1 char(200), pk2 char(200), 84 primary key(pk1, pk2) 85) engine=innodb; 86insert into t3 select 87 A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a 88from t0 A, t0 B where B.a <5; 89 90--replace_column 9 # 91explain select * from t3 where b in (select a from t0); 92select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); 93 94set join_buffer_size= @save_join_buffer_size; 95set max_heap_table_size= @save_max_heap_table_size; 96 97# O2I join orders, with shortcutting: 98explain select * from t1 where a in (select b from t2); 99select * from t1; 100select * from t1 where a in (select b from t2); 101 102drop table t1, t2, t3; 103# (no need for anything in range/index_merge/DS-MRR) 104 105# 106# Test join buffering 107# 108set @save_join_buffer_size = @@join_buffer_size; 109set join_buffer_size= 8192; 110 111create table t1 (a int, filler1 binary(200), filler2 binary(200)); 112insert into t1 select a, 'filler123456', 'filler123456' from t0; 113insert into t1 select a+10, 'filler123456', 'filler123456' from t0; 114 115create table t2 as select * from t1; 116insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 117 118insert into t1 values (2, 'duplicate ok', 'duplicate ok'); 119insert into t1 values (18, 'duplicate ok', 'duplicate ok'); 120 121insert into t2 values (3, 'duplicate ok', 'duplicate ok'); 122insert into t2 values (19, 'duplicate ok', 'duplicate ok'); 123 124explain select 125 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 126from t1 ot where a in (select a from t2 it); 127select 128 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 129from t1 ot where a in (select a from t2 it); 130 131explain select 132 a, mid(filler1, 1,10), length(filler1)=length(filler2) 133from t2 ot where a in (select a from t1 it); 134select 135 a, mid(filler1, 1,10), length(filler1)=length(filler2) 136from t2 ot where a in (select a from t1 it); 137 138# Now let the buffer overfill: 139insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 140insert into t1 select a+20, 'filler123456', 'filler123456' from t0; 141 142explain select 143 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 144from t1 ot where a in (select a from t2 it); 145select 146 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 147from t1 ot where a in (select a from t2 it); 148 149explain select 150 a, mid(filler1, 1,10), length(filler1)=length(filler2) 151from t2 ot where a in (select a from t1 it); 152select 153 a, mid(filler1, 1,10), length(filler1)=length(filler2) 154from t2 ot where a in (select a from t1 it); 155 156drop table t1, t2; 157 158# Check ref access to tables inside the OJ nest inside the SJ nest 159create table t1 (a int, b int, key(a)); 160create table t2 (a int, b int, key(a)); 161create table t3 (a int, b int, key(a)); 162 163insert into t1 select a,a from t0; 164insert into t2 select a,a from t0; 165insert into t3 select a,a from t0; 166 167--echo t2 and t3 must be use 'ref', not 'ALL': 168explain select * 169from t0 where a in 170 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a); 171 172drop table t0, t1,t2,t3; 173 174# 175# Bug #27348: Assertion abort for a query with two subqueries to be flattened 176# Bug #35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries 177# 178CREATE TABLE t1 ( 179 ID int(11) NOT NULL auto_increment, 180 Name char(35) NOT NULL default '', 181 Country char(3) NOT NULL default '', 182 Population int(11) NOT NULL default '0', 183 PRIMARY KEY (ID), 184 INDEX (Population), 185 INDEX (Country) 186); 187CREATE TABLE t2 ( 188 Code char(3) NOT NULL default '', 189 Name char(52) NOT NULL default '', 190 SurfaceArea float(10,2) NOT NULL default '0.00', 191 Population int(11) NOT NULL default '0', 192 Capital int(11) default NULL, 193 PRIMARY KEY (Code), 194 UNIQUE INDEX (Name), 195 INDEX (Population) 196); 197CREATE TABLE t3 ( 198 Country char(3) NOT NULL default '', 199 Language char(30) NOT NULL default '', 200 Percentage float(3,1) NOT NULL default '0.0', 201 PRIMARY KEY (Country, Language), 202 INDEX (Percentage) 203); 204 205--disable_query_log 206INSERT INTO t1 VALUES 207(1,'Kabul','AFG',1780000),(2,'Qandahar','AFG',237500), 208(3,'Herat','AFG',186800),(4,'Mazar-e-Sharif','AFG',127800), 209(5,'Amsterdam','NLD',731200),(6,'Rotterdam','NLD',593321), 210(7,'Haag','NLD',440900),(8,'Utrecht','NLD',234323), 211(9,'Eindhoven','NLD',201843),(10,'Tilburg','NLD',193238), 212(11,'Groningen','NLD',172701),(12,'Breda','NLD',160398), 213(13,'Apeldoorn','NLD',153491),(14,'Nijmegen','NLD',152463), 214(15,'Enschede','NLD',149544),(16,'Haarlem','NLD',148772), 215(17,'Almere','NLD',142465),(18,'Arnhem','NLD',138020), 216(19,'Zaanstad','NLD',135621),(20,'´s-Hertogenbosch','NLD',129170), 217(21,'Amersfoort','NLD',126270),(22,'Maastricht','NLD',122087), 218(23,'Dordrecht','NLD',119811),(24,'Leiden','NLD',117196), 219(25,'Haarlemmermeer','NLD',110722),(26,'Zoetermeer','NLD',110214), 220(27,'Emmen','NLD',105853),(28,'Zwolle','NLD',105819), 221(29,'Ede','NLD',101574),(30,'Delft','NLD',95268); 222 223INSERT INTO t2 VALUES 224('AFG','Afghanistan',652090.00,22720000,1), 225('NLD','Netherlands',41526.00,15864000,5), 226('ANT','Netherlands Antilles',800.00,217000,33), 227('ALB','Albania',28748.00,3401200,34), 228('DZA','Algeria',2381741.00,31471000,35), 229('ASM','American Samoa',199.00,68000,54), 230('AND','Andorra',468.00,78000,55), 231('AGO','Angola',1246700.00,12878000,56), 232('AIA','Anguilla',96.00,8000,62), 233('ATG','Antigua and Barbuda',442.00,68000,63), 234('ARE','United Arab Emirates',83600.00,2441000,65), 235('ARG','Argentina',2780400.00,37032000,69), 236('ARM','Armenia',29800.00,3520000,126), 237('ABW','Aruba',193.00,103000,129), 238('AUS','Australia',7741220.00,18886000,135), 239('AZE','Azerbaijan',86600.00,7734000,144); 240 241INSERT INTO t3 VALUES 242('AFG','Pashto',52.4),('NLD','Dutch',95.6), 243('ANT','Papiamento',86.2),('ALB','Albaniana',97.9), 244('DZA','Arabic',86.0),('ASM','Samoan',90.6), 245('AND','Spanish',44.6),('AGO','Ovimbundu',37.2), 246('AIA','English',0.0),('ATG','Creole English',95.7), 247('ARE','Arabic',42.0),('ARG','Spanish',96.8), 248('ARM','Armenian',93.4),('ABW','Papiamento',76.7), 249('AUS','English',81.2),('AZE','Azerbaijani',89.0), 250('BHS','Creole English',89.7),('BHR','Arabic',67.7), 251('BGD','Bengali',97.7),('BRB','Bajan',95.1), 252('BEL','Dutch',59.2),('BLZ','English',50.8); 253--enable_query_log 254 255# Disable materialization to avoid races between query plans 256set @bug35674_save_optimizer_switch=@@optimizer_switch; 257set optimizer_switch='materialization=off'; 258EXPLAIN 259SELECT Name FROM t2 260 WHERE t2.Code IN (SELECT Country FROM t1 WHERE Population > 5000000) 261 AND 262 t2.Code IN (SELECT Country FROM t3 263 WHERE Language='English' AND Percentage > 10 AND 264 t2.Population > 100000); 265set optimizer_switch=@bug35674_save_optimizer_switch; 266 267DROP TABLE t1,t2,t3; 268 269# BUG#30993: 270CREATE TABLE t1 ( 271 Code char(3) NOT NULL DEFAULT '', 272 Name char(52) NOT NULL DEFAULT '', 273 Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', 274 Region char(26) NOT NULL DEFAULT '', 275 SurfaceArea float(10,2) NOT NULL DEFAULT '0.00', 276 IndepYear smallint(6) DEFAULT NULL, 277 Population int(11) NOT NULL DEFAULT '0', 278 LifeExpectancy float(3,1) DEFAULT NULL, 279 GNP float(10,2) DEFAULT NULL, 280 GNPOld float(10,2) DEFAULT NULL, 281 LocalName char(45) NOT NULL DEFAULT '', 282 GovernmentForm char(45) NOT NULL DEFAULT '', 283 HeadOfState char(60) DEFAULT NULL, 284 Capital int(11) DEFAULT NULL, 285 Code2 char(2) NOT NULL DEFAULT '', 286 PRIMARY KEY (Code) 287); 288 289CREATE TABLE t2 ( 290 ID int(11) NOT NULL AUTO_INCREMENT, 291 Name char(35) NOT NULL DEFAULT '', 292 CountryCode char(3) NOT NULL DEFAULT '', 293 District char(20) NOT NULL DEFAULT '', 294 Population int(11) NOT NULL DEFAULT '0', 295 PRIMARY KEY (ID), 296 KEY CountryCode (CountryCode) 297); 298 299--echo Fill the table with test data 300--disable_query_log 301insert into t2 (ID, Name, CountryCode, Population) values 302(1,'Kabul','AFG',1780000), (2,'Qandahar','AFG',237500), (3,'Herat','AFG',186800), 303(4,'Mazar-e-Sharif','AFG',127800), (33,'Willemstad','ANT',2345), (34,'Tirana','ALB',270000), 304(55,'Andorra la Vella','AND',21189), (61,'South Hill','AIA',961), (62,'The Valley','AIA',595), 305(63,'Saint John�s','ATG',24000), (64,'Dubai','ARE',669181), (65,'Abu Dhabi','ARE',398695), 306(66,'Sharja','ARE',320095), (67,'al-Ayn','ARE',225970), (68,'Ajman','ARE',114395), 307(126,'Yerevan','ARM',1248700), (127,'Gjumri','ARM',211700), (128,'Vanadzor','ARM',172700), 308(129,'Oranjestad','ABW',29034), (144,'Baku','AZE',1787800), (145,'G�nc�','AZE',299300), 309(146,'Sumqayit','AZE',283000), (147,'Ming��evir','AZE',93900), (148,'Nassau','BHS',172000), 310(149,'al-Manama','BHR',148000), (150,'Dhaka','BGD',3612850), (151,'Chittagong','BGD',1392860), 311(152,'Khulna','BGD',663340), (153,'Rajshahi','BGD',294056), (154,'Narayanganj','BGD',202134), 312(155,'Rangpur','BGD',191398), (156,'Mymensingh','BGD',188713), (157,'Barisal','BGD',170232), 313(158,'Tungi','BGD',168702), (159,'Jessore','BGD',139710), (160,'Comilla','BGD',135313), 314(161,'Nawabganj','BGD',130577), (162,'Dinajpur','BGD',127815), (163,'Bogra','BGD',120170), 315(164,'Sylhet','BGD',117396), (165,'Brahmanbaria','BGD',109032), (166,'Tangail','BGD',106004), 316(167,'Jamalpur','BGD',103556), (168,'Pabna','BGD',103277), (169,'Naogaon','BGD',101266), 317(170,'Sirajganj','BGD',99669), (171,'Narsinghdi','BGD',98342), (172,'Saidpur','BGD',96777), 318(173,'Gazipur','BGD',96717), (174,'Bridgetown','BRB',6070), (175,'Antwerpen','BEL',446525), 319(176,'Gent','BEL',224180), (177,'Charleroi','BEL',200827), (178,'Li�ge','BEL',185639), 320(179,'Bruxelles [Brussel]','BEL',133859), (180,'Brugge','BEL',116246), (181,'Schaerbeek','BEL',105692), 321(182,'Namur','BEL',105419), (183,'Mons','BEL',90935), (184,'Belize City','BLZ',55810), 322(185,'Belmopan','BLZ',7105), (190,'Saint George','BMU',1800), (191,'Hamilton','BMU',1200), 323(192,'Thimphu','BTN',22000), (201,'Sarajevo','BIH',360000), (202,'Banja Luka','BIH',143079), 324(203,'Zenica','BIH',96027), (538,'Bandar Seri Begawan','BRN',21484), (539,'Sofija','BGR',1122302), 325(540,'Plovdiv','BGR',342584), (541,'Varna','BGR',299801), (542,'Burgas','BGR',195255), 326(543,'Ruse','BGR',166467), (544,'Stara Zagora','BGR',147939), (545,'Pleven','BGR',121952), 327(546,'Sliven','BGR',105530), (547,'Dobric','BGR',100399), (548,'�umen','BGR',94686), 328(553,'George Town','CYM',19600), (584,'San Jos�','CRI',339131), (1523,'Wien','AUT',1608144), 329(1524,'Graz','AUT',240967), (1525,'Linz','AUT',188022), (1526,'Salzburg','AUT',144247), 330(1527,'Innsbruck','AUT',111752), (1528,'Klagenfurt','AUT',91141), (1810,'Montr�al','CAN',1016376), 331(1811,'Calgary','CAN',768082), (1812,'Toronto','CAN',688275), (1813,'North York','CAN',622632), 332(1814,'Winnipeg','CAN',618477), (1815,'Edmonton','CAN',616306), (1816,'Mississauga','CAN',608072), 333(1817,'Scarborough','CAN',594501), (1818,'Vancouver','CAN',514008), (1819,'Etobicoke','CAN',348845), 334(1820,'London','CAN',339917), (1821,'Hamilton','CAN',335614), (1822,'Ottawa','CAN',335277), 335(1823,'Laval','CAN',330393), (1824,'Surrey','CAN',304477), (1825,'Brampton','CAN',296711), 336(1826,'Windsor','CAN',207588), (1827,'Saskatoon','CAN',193647), (1828,'Kitchener','CAN',189959), 337(1829,'Markham','CAN',189098), (1830,'Regina','CAN',180400), (1831,'Burnaby','CAN',179209), 338(1832,'Qu�bec','CAN',167264), (1833,'York','CAN',154980), (1834,'Richmond','CAN',148867), 339(1835,'Vaughan','CAN',147889), (1836,'Burlington','CAN',145150), (1837,'Oshawa','CAN',140173), 340(1838,'Oakville','CAN',139192), (1839,'Saint Catharines','CAN',136216), (1840,'Longueuil','CAN',127977), 341(1841,'Richmond Hill','CAN',116428), (1842,'Thunder Bay','CAN',115913), (1843,'Nepean','CAN',115100), 342(1844,'Cape Breton','CAN',114733), (1845,'East York','CAN',114034), (1846,'Halifax','CAN',113910), 343(1847,'Cambridge','CAN',109186), (1848,'Gloucester','CAN',107314), (1849,'Abbotsford','CAN',105403), 344(1850,'Guelph','CAN',103593), (1851,'Saint John�s','CAN',101936), (1852,'Coquitlam','CAN',101820), 345(1853,'Saanich','CAN',101388), (1854,'Gatineau','CAN',100702), (1855,'Delta','CAN',95411), 346(1856,'Sudbury','CAN',92686), (1857,'Kelowna','CAN',89442), (1858,'Barrie','CAN',89269), 347(1890,'Shanghai','CHN',9696300), (1891,'Peking','CHN',7472000), (1892,'Chongqing','CHN',6351600), 348(1893,'Tianjin','CHN',5286800), (1894,'Wuhan','CHN',4344600), (1895,'Harbin','CHN',4289800), 349(1896,'Shenyang','CHN',4265200), (1897,'Kanton [Guangzhou]','CHN',4256300), (1898,'Chengdu','CHN',3361500), 350(1899,'Nanking [Nanjing]','CHN',2870300), (1900,'Changchun','CHN',2812000), (1901,'Xi�an','CHN',2761400), 351(1902,'Dalian','CHN',2697000), (1903,'Qingdao','CHN',2596000), (1904,'Jinan','CHN',2278100), 352(1905,'Hangzhou','CHN',2190500), (1906,'Zhengzhou','CHN',2107200), (1907,'Shijiazhuang','CHN',2041500), 353(1908,'Taiyuan','CHN',1968400), (1909,'Kunming','CHN',1829500), (1910,'Changsha','CHN',1809800), 354(1911,'Nanchang','CHN',1691600), (1912,'Fuzhou','CHN',1593800), (1913,'Lanzhou','CHN',1565800), 355(1914,'Guiyang','CHN',1465200), (1915,'Ningbo','CHN',1371200), (1916,'Hefei','CHN',1369100), 356(1917,'Urumt�i [�r�mqi]','CHN',1310100), (1918,'Anshan','CHN',1200000), (1919,'Fushun','CHN',1200000), 357(1920,'Nanning','CHN',1161800), (1921,'Zibo','CHN',1140000), (1922,'Qiqihar','CHN',1070000), 358(1923,'Jilin','CHN',1040000), (1924,'Tangshan','CHN',1040000), (1925,'Baotou','CHN',980000), 359(1926,'Shenzhen','CHN',950500), (1927,'Hohhot','CHN',916700), (1928,'Handan','CHN',840000), 360(1929,'Wuxi','CHN',830000), (1930,'Xuzhou','CHN',810000), (1931,'Datong','CHN',800000), 361(1932,'Yichun','CHN',800000), (1933,'Benxi','CHN',770000), (1934,'Luoyang','CHN',760000), 362(1935,'Suzhou','CHN',710000), (1936,'Xining','CHN',700200), (1937,'Huainan','CHN',700000), 363(1938,'Jixi','CHN',683885), (1939,'Daqing','CHN',660000), (1940,'Fuxin','CHN',640000), 364(1941,'Amoy [Xiamen]','CHN',627500), (1942,'Liuzhou','CHN',610000), (1943,'Shantou','CHN',580000), 365(1944,'Jinzhou','CHN',570000), (1945,'Mudanjiang','CHN',570000), (1946,'Yinchuan','CHN',544500), 366(1947,'Changzhou','CHN',530000), (1948,'Zhangjiakou','CHN',530000), (1949,'Dandong','CHN',520000), 367(1950,'Hegang','CHN',520000), (1951,'Kaifeng','CHN',510000), (1952,'Jiamusi','CHN',493409), 368(1953,'Liaoyang','CHN',492559), (1954,'Hengyang','CHN',487148), (1955,'Baoding','CHN',483155), 369(1956,'Hunjiang','CHN',482043), (1957,'Xinxiang','CHN',473762), (1958,'Huangshi','CHN',457601), 370(1959,'Haikou','CHN',454300), (1960,'Yantai','CHN',452127), (1961,'Bengbu','CHN',449245), 371(1962,'Xiangtan','CHN',441968), (1963,'Weifang','CHN',428522), (1964,'Wuhu','CHN',425740), 372(1965,'Pingxiang','CHN',425579), (1966,'Yingkou','CHN',421589), (1967,'Anyang','CHN',420332), 373(1968,'Panzhihua','CHN',415466), (1969,'Pingdingshan','CHN',410775), (1970,'Xiangfan','CHN',410407), 374(1971,'Zhuzhou','CHN',409924), (1972,'Jiaozuo','CHN',409100), (1973,'Wenzhou','CHN',401871), 375(1974,'Zhangjiang','CHN',400997), (1975,'Zigong','CHN',393184), (1976,'Shuangyashan','CHN',386081), 376(1977,'Zaozhuang','CHN',380846), (1978,'Yakeshi','CHN',377869), (1979,'Yichang','CHN',371601), 377(1980,'Zhenjiang','CHN',368316), (1981,'Huaibei','CHN',366549), (1982,'Qinhuangdao','CHN',364972), 378(1983,'Guilin','CHN',364130), (1984,'Liupanshui','CHN',363954), (1985,'Panjin','CHN',362773), 379(1986,'Yangquan','CHN',362268), (1987,'Jinxi','CHN',357052), (1988,'Liaoyuan','CHN',354141), 380(1989,'Lianyungang','CHN',354139), (1990,'Xianyang','CHN',352125), (1991,'Tai�an','CHN',350696), 381(1992,'Chifeng','CHN',350077), (1993,'Shaoguan','CHN',350043), (1994,'Nantong','CHN',343341), 382(1995,'Leshan','CHN',341128), (1996,'Baoji','CHN',337765), (1997,'Linyi','CHN',324720), 383(1998,'Tonghua','CHN',324600), (1999,'Siping','CHN',317223), (2000,'Changzhi','CHN',317144), 384(2001,'Tengzhou','CHN',315083), (2002,'Chaozhou','CHN',313469), (2003,'Yangzhou','CHN',312892), 385(2004,'Dongwan','CHN',308669), (2005,'Ma�anshan','CHN',305421), (2006,'Foshan','CHN',303160), 386(2007,'Yueyang','CHN',302800), (2008,'Xingtai','CHN',302789), (2009,'Changde','CHN',301276), 387(2010,'Shihezi','CHN',299676), (2011,'Yancheng','CHN',296831), (2012,'Jiujiang','CHN',291187), 388(2013,'Dongying','CHN',281728), (2014,'Shashi','CHN',281352), (2015,'Xintai','CHN',281248), 389(2016,'Jingdezhen','CHN',281183), (2017,'Tongchuan','CHN',280657), (2018,'Zhongshan','CHN',278829), 390(2019,'Shiyan','CHN',273786), (2020,'Tieli','CHN',265683), (2021,'Jining','CHN',265248), 391(2022,'Wuhai','CHN',264081), (2023,'Mianyang','CHN',262947), (2024,'Luzhou','CHN',262892), 392(2025,'Zunyi','CHN',261862), (2026,'Shizuishan','CHN',257862), (2027,'Neijiang','CHN',256012), 393(2028,'Tongliao','CHN',255129), (2029,'Tieling','CHN',254842), (2030,'Wafangdian','CHN',251733), 394(2031,'Anqing','CHN',250718), (2032,'Shaoyang','CHN',247227), (2033,'Laiwu','CHN',246833), 395(2034,'Chengde','CHN',246799), (2035,'Tianshui','CHN',244974), (2036,'Nanyang','CHN',243303), 396(2037,'Cangzhou','CHN',242708), (2038,'Yibin','CHN',241019), (2039,'Huaiyin','CHN',239675), 397(2040,'Dunhua','CHN',235100), (2041,'Yanji','CHN',230892), (2042,'Jiangmen','CHN',230587), 398(2043,'Tongling','CHN',228017), (2044,'Suihua','CHN',227881), (2045,'Gongziling','CHN',226569), 399(2046,'Xiantao','CHN',222884), (2047,'Chaoyang','CHN',222394), (2048,'Ganzhou','CHN',220129), 400(2049,'Huzhou','CHN',218071), (2050,'Baicheng','CHN',217987), (2051,'Shangzi','CHN',215373), 401(2052,'Yangjiang','CHN',215196), (2053,'Qitaihe','CHN',214957), (2054,'Gejiu','CHN',214294), 402(2055,'Jiangyin','CHN',213659), (2056,'Hebi','CHN',212976), (2057,'Jiaxing','CHN',211526), 403(2058,'Wuzhou','CHN',210452), (2059,'Meihekou','CHN',209038), (2060,'Xuchang','CHN',208815), 404(2061,'Liaocheng','CHN',207844), (2062,'Haicheng','CHN',205560), (2063,'Qianjiang','CHN',205504), 405(2064,'Baiyin','CHN',204970), (2065,'Bei�an','CHN',204899), (2066,'Yixing','CHN',200824), 406(2067,'Laizhou','CHN',198664), (2068,'Qaramay','CHN',197602), (2069,'Acheng','CHN',197595), 407(2070,'Dezhou','CHN',195485), (2071,'Nanping','CHN',195064), (2072,'Zhaoqing','CHN',194784), 408(2073,'Beipiao','CHN',194301), (2074,'Fengcheng','CHN',193784), (2075,'Fuyu','CHN',192981), 409(2076,'Xinyang','CHN',192509), (2077,'Dongtai','CHN',192247), (2078,'Yuci','CHN',191356), 410(2079,'Honghu','CHN',190772), (2080,'Ezhou','CHN',190123), (2081,'Heze','CHN',189293), 411(2082,'Daxian','CHN',188101), (2083,'Linfen','CHN',187309), (2084,'Tianmen','CHN',186332), 412(2085,'Yiyang','CHN',185818), (2086,'Quanzhou','CHN',185154), (2087,'Rizhao','CHN',185048), 413(2088,'Deyang','CHN',182488), (2089,'Guangyuan','CHN',182241), (2090,'Changshu','CHN',181805), 414(2091,'Zhangzhou','CHN',181424), (2092,'Hailar','CHN',180650), (2093,'Nanchong','CHN',180273), 415(2094,'Jiutai','CHN',180130), (2095,'Zhaodong','CHN',179976), (2096,'Shaoxing','CHN',179818), 416(2097,'Fuyang','CHN',179572), (2098,'Maoming','CHN',178683), (2099,'Qujing','CHN',178669), 417(2100,'Ghulja','CHN',177193), (2101,'Jiaohe','CHN',176367), (2102,'Puyang','CHN',175988), 418(2103,'Huadian','CHN',175873), (2104,'Jiangyou','CHN',175753), (2105,'Qashqar','CHN',174570), 419(2106,'Anshun','CHN',174142), (2107,'Fuling','CHN',173878), (2108,'Xinyu','CHN',173524), 420(2109,'Hanzhong','CHN',169930), (2110,'Danyang','CHN',169603), (2111,'Chenzhou','CHN',169400), 421(2112,'Xiaogan','CHN',166280), (2113,'Shangqiu','CHN',164880), (2114,'Zhuhai','CHN',164747), 422(2115,'Qingyuan','CHN',164641), (2116,'Aqsu','CHN',164092), (2117,'Jining','CHN',163552), 423(2118,'Xiaoshan','CHN',162930), (2119,'Zaoyang','CHN',162198), (2120,'Xinghua','CHN',161910), 424(2121,'Hami','CHN',161315), (2122,'Huizhou','CHN',161023), (2123,'Jinmen','CHN',160794), 425(2124,'Sanming','CHN',160691), (2125,'Ulanhot','CHN',159538), (2126,'Korla','CHN',159344), 426(2127,'Wanxian','CHN',156823), (2128,'Rui�an','CHN',156468), (2129,'Zhoushan','CHN',156317), 427(2130,'Liangcheng','CHN',156307), (2131,'Jiaozhou','CHN',153364), (2132,'Taizhou','CHN',152442), 428(2133,'Suzhou','CHN',151862), (2134,'Yichun','CHN',151585), (2135,'Taonan','CHN',150168), 429(2136,'Pingdu','CHN',150123), (2137,'Ji�an','CHN',148583), (2138,'Longkou','CHN',148362), 430(2139,'Langfang','CHN',148105), (2140,'Zhoukou','CHN',146288), (2141,'Suining','CHN',146086), 431(2142,'Yulin','CHN',144467), (2143,'Jinhua','CHN',144280), (2144,'Liu�an','CHN',144248), 432(2145,'Shuangcheng','CHN',142659), (2146,'Suizhou','CHN',142302), (2147,'Ankang','CHN',142170), 433(2148,'Weinan','CHN',140169), (2149,'Longjing','CHN',139417), (2150,'Da�an','CHN',138963), 434(2151,'Lengshuijiang','CHN',137994), (2152,'Laiyang','CHN',137080), (2153,'Xianning','CHN',136811), 435(2154,'Dali','CHN',136554), (2155,'Anda','CHN',136446), (2156,'Jincheng','CHN',136396), 436(2157,'Longyan','CHN',134481), (2158,'Xichang','CHN',134419), (2159,'Wendeng','CHN',133910), 437(2160,'Hailun','CHN',133565), (2161,'Binzhou','CHN',133555), (2162,'Linhe','CHN',133183), 438(2163,'Wuwei','CHN',133101), (2164,'Duyun','CHN',132971), (2165,'Mishan','CHN',132744), 439(2166,'Shangrao','CHN',132455), (2167,'Changji','CHN',132260), (2168,'Meixian','CHN',132156), 440(2169,'Yushu','CHN',131861), (2170,'Tiefa','CHN',131807), (2171,'Huai�an','CHN',131149), 441(2172,'Leiyang','CHN',130115), (2173,'Zalantun','CHN',130031), (2174,'Weihai','CHN',128888), 442(2175,'Loudi','CHN',128418), (2176,'Qingzhou','CHN',128258), (2177,'Qidong','CHN',126872), 443(2178,'Huaihua','CHN',126785), (2179,'Luohe','CHN',126438), (2180,'Chuzhou','CHN',125341), 444(2181,'Kaiyuan','CHN',124219), (2182,'Linqing','CHN',123958), (2183,'Chaohu','CHN',123676), 445(2184,'Laohekou','CHN',123366), (2185,'Dujiangyan','CHN',123357), (2186,'Zhumadian','CHN',123232), 446(2187,'Linchuan','CHN',121949), (2188,'Jiaonan','CHN',121397), (2189,'Sanmenxia','CHN',120523), 447(2190,'Heyuan','CHN',120101), (2191,'Manzhouli','CHN',120023), (2192,'Lhasa','CHN',120000), 448(2193,'Lianyuan','CHN',118858), (2194,'Kuytun','CHN',118553), (2195,'Puqi','CHN',117264), 449(2196,'Hongjiang','CHN',116188), (2197,'Qinzhou','CHN',114586), (2198,'Renqiu','CHN',114256), 450(2199,'Yuyao','CHN',114065), (2200,'Guigang','CHN',114025), (2201,'Kaili','CHN',113958), 451(2202,'Yan�an','CHN',113277), (2203,'Beihai','CHN',112673), (2204,'Xuangzhou','CHN',112673), 452(2205,'Quzhou','CHN',112373), (2206,'Yong�an','CHN',111762), (2207,'Zixing','CHN',110048), 453(2208,'Liyang','CHN',109520), (2209,'Yizheng','CHN',109268), (2210,'Yumen','CHN',109234), 454(2211,'Liling','CHN',108504), (2212,'Yuncheng','CHN',108359), (2213,'Shanwei','CHN',107847), 455(2214,'Cixi','CHN',107329), (2215,'Yuanjiang','CHN',107004), (2216,'Bozhou','CHN',106346), 456(2217,'Jinchang','CHN',105287), (2218,'Fu�an','CHN',105265), (2219,'Suqian','CHN',105021), 457(2220,'Shishou','CHN',104571), (2221,'Hengshui','CHN',104269), (2222,'Danjiangkou','CHN',103211), 458(2223,'Fujin','CHN',103104), (2224,'Sanya','CHN',102820), (2225,'Guangshui','CHN',102770), 459(2226,'Huangshan','CHN',102628), (2227,'Xingcheng','CHN',102384), (2228,'Zhucheng','CHN',102134), 460(2229,'Kunshan','CHN',102052), (2230,'Haining','CHN',100478), (2231,'Pingliang','CHN',99265), 461(2232,'Fuqing','CHN',99193), (2233,'Xinzhou','CHN',98667), (2234,'Jieyang','CHN',98531), 462(2235,'Zhangjiagang','CHN',97994), (2236,'Tong Xian','CHN',97168), (2237,'Ya�an','CHN',95900), 463(2238,'Jinzhou','CHN',95761), (2239,'Emeishan','CHN',94000), (2240,'Enshi','CHN',93056), 464(2241,'Bose','CHN',93009), (2242,'Yuzhou','CHN',92889), (2243,'Kaiyuan','CHN',91999), 465(2244,'Tumen','CHN',91471), (2245,'Putian','CHN',91030), (2246,'Linhai','CHN',90870), 466(2247,'Xilin Hot','CHN',90646), (2248,'Shaowu','CHN',90286), (2249,'Junan','CHN',90222), 467(2250,'Huaying','CHN',89400), (2251,'Pingyi','CHN',89373), (2252,'Huangyan','CHN',89288), 468(2413,'La Habana','CUB',2256000), (2414,'Santiago de Cuba','CUB',433180), (2415,'Camag�ey','CUB',298726), 469(2416,'Holgu�n','CUB',249492), (2417,'Santa Clara','CUB',207350), (2418,'Guant�namo','CUB',205078), 470(2419,'Pinar del R�o','CUB',142100), (2420,'Bayamo','CUB',141000), (2421,'Cienfuegos','CUB',132770), 471(2422,'Victoria de las Tunas','CUB',132350), (2423,'Matanzas','CUB',123273), (2424,'Manzanillo','CUB',109350), 472(2425,'Sancti-Sp�ritus','CUB',100751), (2426,'Ciego de �vila','CUB',98505), (2430,'Nicosia','CYP',195000), 473(2431,'Limassol','CYP',154400), (3245,'Z�rich','CHE',336800), (3246,'Geneve','CHE',173500), 474(3247,'Basel','CHE',166700), (3248,'Bern','CHE',122700), (3249,'Lausanne','CHE',114500), 475(3339,'Praha','CZE',1181126), (3340,'Brno','CZE',381862), (3341,'Ostrava','CZE',320041), 476(3342,'Plzen','CZE',166759), (3343,'Olomouc','CZE',102702), (3344,'Liberec','CZE',99155), 477(3345,'Cesk� Budejovice','CZE',98186), (3346,'Hradec Kr�lov�','CZE',98080), (3347,'�st� nad Labem','CZE',95491), 478(3348,'Pardubice','CZE',91309), (3520,'Minsk','BLR',1674000), (3521,'Gomel','BLR',475000), 479(3522,'Mogiljov','BLR',356000), (3523,'Vitebsk','BLR',340000), (3524,'Grodno','BLR',302000), 480(3525,'Brest','BLR',286000), (3526,'Bobruisk','BLR',221000), (3527,'Baranovit�i','BLR',167000), 481(3528,'Borisov','BLR',151000), (3529,'Pinsk','BLR',130000), (3530,'Or�a','BLR',124000), 482(3531,'Mozyr','BLR',110000), (3532,'Novopolotsk','BLR',106000), (3533,'Lida','BLR',101000), 483(3534,'Soligorsk','BLR',101000), (3535,'Molodet�no','BLR',97000); 484 485insert into t1 (Code, Name, Continent) values 486('AFG','Afghanistan','Asia'), ('ANT','Netherlands Antilles','North America'), 487('ALB','Albania','Europe'), ('AND','Andorra','Europe'), 488('AIA','Anguilla','North America'), ('ATG','Antigua and Barbuda','North America'), 489('ARE','United Arab Emirates','Asia'), ('ARM','Armenia','Asia'), 490('ABW','Aruba','North America'), ('AZE','Azerbaijan','Asia'), 491('BHS','Bahamas','North America'), ('BHR','Bahrain','Asia'), 492('BGD','Bangladesh','Asia'), ('BRB','Barbados','North America'), 493('BEL','Belgium','Europe'), ('BLZ','Belize','North America'), 494('BMU','Bermuda','North America'), ('BTN','Bhutan','Asia'), 495('BIH','Bosnia and Herzegovina','Europe'), ('BRN','Brunei','Asia'), 496('BGR','Bulgaria','Europe'), ('CYM','Cayman Islands','North America'), 497('CRI','Costa Rica','North America'), ('AUT','Austria','Europe'), 498('CAN','Canada','North America'), ('CHN','China','Asia'), 499('CUB','Cuba','North America'), ('CYP','Cyprus','Asia'), 500('CHE','Switzerland','Europe'), ('CZE','Czech Republic','Europe'), 501('BLR','Belarus','Europe'); 502update t2 set population=6000000 where Name in ('Wien', 'Vancouver', 'Praha'); 503--enable_query_log 504 505--echo This must not use LooseScan: 506EXPLAIN SELECT Name FROM t1 507 WHERE t1.Code IN ( 508 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); 509 510SELECT Name FROM t1 511 WHERE t1.Code IN ( 512 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000); 513 514drop table t1, t2; 515 516# 517# Bug#33062: subquery in stored routine cause crash 518# 519 520--disable_warnings 521drop procedure if exists p1; 522drop procedure if exists p2; 523drop procedure if exists p3; 524drop procedure if exists p4; 525--enable_warnings 526CREATE TABLE t1(a INT); 527CREATE TABLE t2(c INT); 528 529DELIMITER //; 530 531CREATE PROCEDURE p1(v1 int) 532BEGIN 533 SELECT 1 FROM t1 WHERE a = v1 AND a IN (SELECT c FROM t2); 534END 535// 536 537CREATE PROCEDURE p2(v1 int) 538BEGIN 539 SELECT 1 FROM t1 WHERE a IN (SELECT c FROM t2); 540END 541// 542 543CREATE PROCEDURE p3(v1 int) 544BEGIN 545 SELECT 1 546 FROM 547 t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, 548 t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, 549 t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, 550 t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, 551 t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, 552 t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, 553 t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, 554 t1 t57,t1 t58,t1 t59,t1 t60 555 WHERE t01.a IN (SELECT c FROM t2); 556END 557// 558 559CREATE PROCEDURE p4(v1 int) 560BEGIN 561 SELECT 1 562 FROM 563 t1 t01,t1 t02,t1 t03,t1 t04,t1 t05,t1 t06,t1 t07,t1 t08, 564 t1 t09,t1 t10,t1 t11,t1 t12,t1 t13,t1 t14,t1 t15,t1 t16, 565 t1 t17,t1 t18,t1 t19,t1 t20,t1 t21,t1 t22,t1 t23,t1 t24, 566 t1 t25,t1 t26,t1 t27,t1 t28,t1 t29,t1 t30,t1 t31,t1 t32, 567 t1 t33,t1 t34,t1 t35,t1 t36,t1 t37,t1 t38,t1 t39,t1 t40, 568 t1 t41,t1 t42,t1 t43,t1 t44,t1 t45,t1 t46,t1 t47,t1 t48, 569 t1 t49,t1 t50,t1 t51,t1 t52,t1 t53,t1 t54,t1 t55,t1 t56, 570 t1 t57,t1 t58,t1 t59,t1 t60 571 WHERE t01.a = v1 AND t01.a IN (SELECT c FROM t2); 572END 573// 574 575DELIMITER ;// 576 577CALL p1(1); 578CALL p2(1); 579CALL p3(1); 580CALL p4(1); 581 582DROP TABLE t1, t2; 583DROP PROCEDURE p1; 584DROP PROCEDURE p2; 585DROP PROCEDURE p3; 586DROP PROCEDURE p4; 587 588 589# 590# BUG#35160 "Subquery optimization: table pullout is not reflected in EXPLAIN EXTENDED" 591# 592create table t0 (a int); 593insert into t0 values (0),(1),(2),(3),(4); 594 595create table t1 (a int, b int, key(a)); 596insert into t1 select a,a from t0; 597insert into t1 select a+5,a from t0; 598 599create table t2 (a int, b int, primary key(a)); 600insert into t2 select * from t1; 601 602# Table t2 should be pulled out because t2.a=t0.a equality 603--echo Table t2, unlike table t1, should be displayed as pulled out 604explain extended select * from t0 605where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and 606t1.b=t2.b); 607 608# 609# BUG#46556 "Returning incorrect, empty results for some IN subqueries 610# w/ semijoin=on" 611# 612 613# The above query did not have a valid plan before the fix of BUG#46556. 614# Add some data that would cause wrong result with the old plan. 615update t1 set a=3, b=11 where a=4; 616update t2 set b=11 where a=3; 617 618if (`select @@join_cache_level=6`) 619{ 620 --echo # Not anymore: 621 --echo # The following query gives wrong result due to Bug#49129 622} 623select * from t0 where t0.a in 624 (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); 625 626drop table t0, t1, t2; 627 628# 629# BUG#35767: Processing of uncorrelated subquery with semi-join cause wrong result and crash 630# 631CREATE TABLE t1 ( 632 id int(11) NOT NULL, 633 PRIMARY KEY (id)); 634 635CREATE TABLE t2 ( 636 id int(11) NOT NULL, 637 fid int(11) NOT NULL, 638 PRIMARY KEY (id)); 639 640insert into t1 values(1); 641insert into t2 values(1,7503),(2,1); 642 643--error 1054 644explain select count(*) 645from t1 646where fid IN (select fid from t2 where (id between 7502 and 8420) order by fid ); 647 648drop table t1, t2; 649 650# 651# BUG#36137 "virtual longlong Item_in_subselect::val_int(): Assertion `0' failed." 652# 653create table t1 (a int, b int, key (a), key (b)); 654insert into t1 values (2,4),(2,4),(2,4); 655select t1.a from t1 656where 657 t1.a in (select 1 from t1 where t1.a in (select 1 from t1) group by t1.a); 658drop table t1; 659 660# 661# BUG#36128: not in subquery causes crash in cleanup.. 662# 663create table t1(a int,b int,key(a),key(b)); 664insert into t1 values (1,1),(2,2),(3,3); 665select 1 from t1 666where t1.a not in (select 1 from t1 667 where t1.a in (select 1 from t1) 668 group by t1.b); 669drop table t1; 670 671# 672# BUG#33743 "nested subqueries, unique index, wrong result" 673# 674CREATE TABLE t1 675 (EMPNUM CHAR(3) NOT NULL, 676 EMPNAME CHAR(20), 677 GRADE DECIMAL(4), 678 CITY CHAR(15)); 679 680CREATE TABLE t2 681 (PNUM CHAR(3) NOT NULL, 682 PNAME CHAR(20), 683 PTYPE CHAR(6), 684 BUDGET DECIMAL(9), 685 CITY CHAR(15)); 686 687CREATE TABLE t3 688 (EMPNUM CHAR(3) NOT NULL, 689 PNUM CHAR(3) NOT NULL, 690 HOURS DECIMAL(5)); 691 692INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); 693INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); 694INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); 695INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); 696INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); 697 698INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); 699INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); 700INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); 701INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); 702INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); 703INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); 704 705INSERT INTO t3 VALUES ('E1','P1',40); 706INSERT INTO t3 VALUES ('E1','P2',20); 707INSERT INTO t3 VALUES ('E1','P3',80); 708INSERT INTO t3 VALUES ('E1','P4',20); 709INSERT INTO t3 VALUES ('E1','P5',12); 710INSERT INTO t3 VALUES ('E1','P6',12); 711INSERT INTO t3 VALUES ('E2','P1',40); 712INSERT INTO t3 VALUES ('E2','P2',80); 713INSERT INTO t3 VALUES ('E3','P2',20); 714INSERT INTO t3 VALUES ('E4','P2',20); 715INSERT INTO t3 VALUES ('E4','P4',40); 716INSERT INTO t3 VALUES ('E4','P5',80); 717 718 719SELECT * FROM t1; 720CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); 721--sorted_result 722SELECT EMPNAME 723FROM t1 724WHERE EMPNUM IN 725 (SELECT EMPNUM 726 FROM t3 727 WHERE PNUM IN 728 (SELECT PNUM 729 FROM t2 730 WHERE PTYPE = 'Design')); 731 732DROP INDEX t1_IDX ON t1; 733CREATE INDEX t1_IDX ON t1(EMPNUM); 734--sorted_result 735SELECT EMPNAME 736FROM t1 737WHERE EMPNUM IN 738 (SELECT EMPNUM 739 FROM t3 740 WHERE PNUM IN 741 (SELECT PNUM 742 FROM t2 743 WHERE PTYPE = 'Design')); 744 745DROP INDEX t1_IDX ON t1; 746--sorted_result 747SELECT EMPNAME 748FROM t1 749WHERE EMPNUM IN 750 (SELECT EMPNUM 751 FROM t3 752 WHERE PNUM IN 753 (SELECT PNUM 754 FROM t2 755 WHERE PTYPE = 'Design')); 756 757DROP TABLE t1, t2, t3; 758 759# 760# BUG#33245 "Crash on VIEW referencing FROM table in an IN clause" 761# 762CREATE TABLE t1 (f1 INT NOT NULL); 763CREATE VIEW v1 (a) AS SELECT f1 IN (SELECT f1 FROM t1) FROM t1; 764SELECT * FROM v1; 765drop view v1; 766drop table t1; 767 768 769# 770# BUG#35550 "Semi-join subquery in ON clause and no WHERE crashes the server" 771# 772create table t0 (a int); 773insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 774 775create table t1(a int, b int); 776insert into t1 values (0,0),(1,1),(2,2); 777create table t2 as select * from t1; 778 779create table t3 (pk int, a int, primary key(pk)); 780insert into t3 select a,a from t0; 781 782explain 783select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); 784 785drop table t0, t1, t2, t3; 786 787# 788# BUG#34799: crash or/and memory overrun with dependant subquery and some joins 789# 790create table t1 (a int); 791insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 792 793create table t2 (a char(200), b char(200), c char(200), primary key (a,b,c)) engine=innodb; 794insert into t2 select concat(a, repeat('X',198)),repeat('B',200),repeat('B',200) from t1; 795insert into t2 select concat(a, repeat('Y',198)),repeat('B',200),repeat('B',200) from t1; 796alter table t2 add filler1 int; 797 798insert into t1 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; 799 800set @save_join_buffer_size=@@join_buffer_size; 801--disable_warnings 802set join_buffer_size=1; 803--enable_warnings 804 805select * from t2 where filler1 in ( select a from t1); 806set join_buffer_size=@save_join_buffer_size; 807 808drop table t1, t2; 809# 810# BUG#33509: Server crashes with number of recursive subqueries=61 811# (the query may or may not fail with an error so we're using it with SP 812# 813create table t1 (a int not null); 814 815--disable_warnings 816drop procedure if exists p1; 817--enable_warnings 818 819delimiter |; 820 821CREATE PROCEDURE p1() 822BEGIN 823 DECLARE EXIT HANDLER FOR SQLEXCEPTION select a from t1; 824 prepare s1 from ' 825 select a from t1 where a in ( 826 select a from t1 where a in ( 827 select a from t1 where a in ( 828 select a from t1 where a in ( 829 select a from t1 where a in ( 830 select a from t1 where a in ( 831 select a from t1 where a in ( 832 select a from t1 where a in ( 833 select a from t1 where a in ( 834 select a from t1 where a in ( 835 select a from t1 where a in ( 836 select a from t1 where a in ( 837 select a from t1 where a in ( 838 select a from t1 where a in ( 839 select a from t1 where a in ( 840 select a from t1 where a in ( 841 select a from t1 where a in ( 842 select a from t1 where a in ( 843 select a from t1 where a in ( 844 select a from t1 where a in ( 845 select a from t1 where a in ( 846 select a from t1 where a in ( 847 select a from t1 where a in ( 848 select a from t1 where a in ( 849 select a from t1 where a in ( 850 select a from t1 where a in ( 851 select a from t1 where a in ( 852 select a from t1 where a in ( 853 select a from t1 where a in ( 854 select a from t1 where a in ( 855 select a from t1 where a in ( 856 select a from t1 where a in ( 857 select a from t1 where a in ( 858 select a from t1 where a in ( 859 select a from t1 where a in ( 860 select a from t1 where a in ( 861 select a from t1 where a in ( 862 select a from t1 where a in ( 863 select a from t1 where a in ( 864 select a from t1 where a in ( 865 select a from t1 where a in ( 866 select a from t1 where a in ( 867 select a from t1 where a in ( 868 select a from t1 where a in ( 869 select a from t1 where a in ( 870 select a from t1 where a in ( 871 select a from t1 where a in ( 872 select a from t1 where a in ( 873 select a from t1 where a in ( 874 select a from t1 where a in ( 875 select a from t1 where a in ( 876 select a from t1 where a in ( 877 select a from t1 where a in ( 878 select a from t1 where a in ( 879 select a from t1 where a in ( 880 select a from t1 where a in ( 881 select a from t1 where a in ( 882 select a from t1 where a in ( 883 select a from t1 where a in ( 884 select a from t1 where a in ( 885 select a from t1 where a in ( 886 select a from t1 where a in ( select a from t1) 887 )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))'; 888 execute s1; 889END; 890| 891delimiter ;| 892 893call p1(); 894drop procedure p1; 895drop table t1; 896 897# 898# BUG#35468 "Slowdown and wrong result for uncorrelated subquery w/o where" 899# 900 901create table t0 (a int); 902insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 903create table t1 (a int) as select A.a + 10 *(B.a + 10*C.a) as a from t0 A, t0 B, t0 C; 904create table t2 (id int, a int, primary key(id), key(a)) as select a as id, a as a from t1; 905show create table t2; 906set @a=0; 907create table t3 as select * from t2 limit 0; 908insert into t3 select @a:=@a+1, t2.a from t2, t0; 909insert into t3 select @a:=@a+1, t2.a from t2, t0; 910insert into t3 select @a:=@a+1, t2.a from t2, t0; 911 912alter table t3 add primary key(id), add key(a); 913--echo The following must use loose index scan over t3, key a: 914explain select count(a) from t2 where a in ( SELECT a FROM t3); 915select count(a) from t2 where a in ( SELECT a FROM t3); 916 917drop table t0,t1,t2,t3; 918 919--echo 920--echo BUG#42740: crash in optimize_semijoin_nests 921--echo 922create table t1 (c6 timestamp,key (c6)) engine=innodb; 923create table t2 (c2 double) engine=innodb; 924explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null) ; 925drop table t1, t2; 926 927--echo # 928--echo # BUG#42742: crash in setup_sj_materialization, Copy_field::set 929--echo # 930create table t3 ( c1 year) engine=innodb; 931insert into t3 values (2135),(2142); 932create table t2 (c1 tinytext,c2 text,c6 timestamp) engine=innodb; 933-- echo # The following must not crash, EXPLAIN should show one SJ strategy, not a mix: 934explain select 1 from t2 where 935 c2 in (select 1 from t3, t2) and 936 c1 in (select convert(c6,char(1)) from t2); 937drop table t2, t3; 938 939 940--echo # 941--echo # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 942--echo # 943 944CREATE TABLE t1 ( f1 int NOT NULL , f10 int) ; 945INSERT IGNORE INTO t1 VALUES (25,0),(29,0); 946 947CREATE TABLE t2 ( f10 int) ENGINE=InnoDB; 948 949CREATE TABLE t3 ( f11 int) ; 950INSERT IGNORE INTO t3 VALUES (0); 951 952SELECT alias1.f10 AS field2 953FROM t2 AS alias1 954JOIN ( 955 t3 AS alias2 956 JOIN t1 AS alias3 957 ON alias3.f10 958) ON alias3.f1 959WHERE alias2.f11 IN ( 960 SELECT SQ4_alias1.f10 961 FROM t1 AS SQ4_alias1 962 LEFT JOIN t2 AS SQ4_alias3 ON SQ4_alias3.f10 963) 964GROUP BY field2; 965drop table t1, t2, t3; 966 967--echo # 968--echo # BUG#849763: Wrong result with second execution of prepared statement with semijoin + view 969--echo # 970CREATE TABLE t1 ( c varchar(1)) engine=innodb; 971INSERT INTO t1 VALUES ('r'); 972 973CREATE TABLE t2 ( a integer, b varchar(1), c varchar(1)) engine=innodb; 974INSERT INTO t2 VALUES (1,'r','r'); 975 976CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1; 977 978PREPARE st1 FROM 'SELECT * FROM t2 WHERE a = SOME (SELECT a FROM v1 WHERE v1.c = t2.c)'; 979EXECUTE st1; 980EXECUTE st1; 981 982DROP VIEW v1; 983DROP TABLE t1, t2; 984 985--echo # 986--echo # BUG#858732: Wrong result with semijoin + loosescan + comma join 987--echo # 988CREATE TABLE t1 (f13 int(11) NOT NULL , PRIMARY KEY (f13)) ENGINE=InnoDB; 989INSERT INTO t1 VALUES (16),(24); 990 991CREATE TABLE t2 (f14 int(11) NOT NULL, f12 varchar(1) NOT NULL, KEY (f12,f14)) ENGINE=InnoDB; 992INSERT INTO t2 VALUES (6,'y'); 993 994CREATE TABLE t3 (f12 varchar(1) NOT NULL) ENGINE=InnoDB; 995INSERT INTO t3 VALUES ('r'),('s'),('t'),('v'),('w'),('x'),('y'); 996 997analyze table t1,t2,t3 persistent for all; 998--echo # The following must use LooseScan but not join buffering 999 1000--replace_column 9 # 1001explain 1002SELECT * FROM t3 1003WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); 1004 1005SELECT * FROM t3 1006WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); 1007 1008DROP TABLE t1,t2,t3; 1009 1010--echo # 1011--echo # BUG#869012: Wrong result with semijoin + materialization + AND in WHERE 1012--echo # 1013CREATE TABLE t1 (f3 varchar(1) , f4 varchar(1) ) engine=InnoDB; 1014INSERT IGNORE INTO t1 VALUES ('x','x'),('x','x'); 1015CREATE TABLE t2 ( f4 varchar(1) ) ; 1016INSERT IGNORE INTO t2 VALUES ('g'); 1017CREATE TABLE t3 (f4 varchar(1) ) Engine=InnoDB; 1018INSERT IGNORE INTO t3 VALUES ('x'); 1019 1020set @tmp_869012=@@optimizer_switch; 1021SET optimizer_switch='semijoin=on,materialization=on'; 1022SELECT * 1023FROM t1 , t2 1024WHERE ( t1.f4 ) IN ( SELECT f4 FROM t3 ) 1025AND t2.f4 != t1.f3 ; 1026set optimizer_switch= @tmp_869012; 1027 1028DROP TABLE t1,t2,t3; 1029 1030 1031--echo # 1032--echo # BUG#869001: Wrong result with semijoin + materialization + firstmatch + multipart key 1033--echo # 1034set @tmp869001_jcl= @@join_cache_level; 1035set @tmp869001_os= @@optimizer_switch; 1036SET join_cache_level=0; 1037SET optimizer_switch='materialization=on,semijoin=on,firstmatch=on,loosescan=off'; 1038 1039CREATE TABLE t1 ( f2 int, f3 varchar(1), KEY (f3,f2)) engine=innodb; 1040INSERT INTO t1 VALUES (8,'x'),(NULL,'x'),(8,'c'); 1041 1042CREATE TABLE t2 ( f4 varchar(1)) engine=innodb; 1043INSERT INTO t2 VALUES ('x'); 1044 1045CREATE TABLE t3 ( f1 int) engine=innodb; 1046INSERT INTO t3 VALUES (8),(6),(2),(9),(6); 1047 1048CREATE TABLE t4 ( f3 varchar(1)) engine=innodb; 1049INSERT INTO t4 VALUES ('p'),('j'),('c'); 1050 1051SELECT * 1052FROM t1 JOIN t2 ON (t2.f4 = t1.f3 ) 1053WHERE ( 8 ) IN ( 1054 SELECT t3.f1 FROM t3 , t4 1055); 1056 1057DROP TABLE t1, t2, t3, t4; 1058set join_cache_level= @tmp869001_jcl; 1059set optimizer_switch= @tmp869001_os; 1060 1061--echo # 1062--echo # Bug #881318: join cache + duplicate elimination + left join 1063--echo # with empty materialized derived inner table 1064--echo # 1065 1066CREATE TABLE t1 (b varchar(1)) ENGINE=InnoDB; 1067 1068CREATE TABLE t2 (a varchar(1)) ENGINE=InnoDB; 1069INSERT INTO t2 VALUES ('a'); 1070 1071CREATE TABLE t3 (a varchar(1), b varchar(1)) ENGINE=InnoDB; 1072INSERT INTO t3 VALUES ('c','c'); 1073 1074CREATE TABLE t4 (b varchar(1)) ENGINE=InnoDB; 1075INSERT INTO t4 VALUES ('c'), ('b'); 1076 1077CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 1078 1079EXPLAIN 1080SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a 1081 WHERE t3.b IN (SELECT b FROM t4); 1082SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a 1083 WHERE t3.b IN (SELECT b FROM t4); 1084 1085DROP VIEW v1; 1086DROP TABLE t1,t2,t3,t4; 1087 1088--echo # 1089--echo # BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ... 1090--echo # 1091CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) ); 1092INSERT INTO t1 VALUES (1),(2),(3),(4); 1093 1094# t2 needs to be InnoDB 1095CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB; 1096INSERT INTO t2 VALUES (1,1); 1097 1098SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b); 1099 1100DROP TABLE t1,t2; 1101 1102--echo # 1103--echo # BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 1104--echo # with index_merge+index_merge_sort_union+loosescan+semijoin 1105--echo # 1106CREATE TABLE t1 ( 1107 a INT, b VARCHAR(1), c INT, 1108 KEY(a), KEY(b) 1109) ENGINE=InnoDB; 1110 1111INSERT INTO t1 VALUES 1112(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5), 1113(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9), 1114(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), 1115(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); 1116 1117CREATE TABLE t2 ( 1118 pk INT, d VARCHAR(1), e INT, 1119 PRIMARY KEY(pk), KEY(d,e) 1120) ENGINE=InnoDB; 1121 1122INSERT INTO t2 VALUES 1123(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), 1124(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), 1125(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), 1126(15,'g',6),(16,'x',7),(17,'f',8); 1127 1128analyze table t1,t2; 1129 1130explain 1131SELECT * FROM t1 WHERE b IN ( 1132 SELECT d FROM t2, t1 1133 WHERE a = d AND ( pk < 2 OR d = 'z' ) 1134); 1135SELECT * FROM t1 WHERE b IN ( 1136 SELECT d FROM t2, t1 1137 WHERE a = d AND ( pk < 2 OR d = 'z' ) 1138); 1139 1140DROP TABLE t1, t2; 1141 1142--echo # 1143--echo # BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view 1144--echo # 1145CREATE TABLE t1 ( 1146 a VARCHAR(1), 1147 b VARCHAR(1) NOT NULL, 1148 KEY(a) 1149) ENGINE=InnoDB; 1150INSERT INTO t1 VALUES 1151('j','j'),('v','v'),('c','c'),('m','m'),('d','d'), 1152('y','y'),('t','t'),('d','d'),('s','s'),('r','r'), 1153('m','m'),('b','b'),('x','x'),('g','g'),('p','p'), 1154('q','q'),('w','w'),('d','d'),('e','e'); 1155 1156ANALYZE TABLE t1; 1157 1158CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 1159 1160--echo # This query returned 6 rows instead of 19 1161--sorted_result 1162SELECT * FROM v1 1163WHERE ( a, a ) IN ( 1164 SELECT alias2.b, alias2.a 1165 FROM t1 AS alias1, t1 AS alias2 1166 WHERE alias2.b = alias1.a 1167 AND ( alias1.b >= alias1.a OR alias2.b = 'z' ) 1168); 1169 1170--echo # Another testcase, without the VIEW: 1171CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB; 1172INSERT INTO t2 SELECT * FROM t1; 1173INSERT INTO t2 SELECT * FROM t1; 1174 1175ANALYZE TABLE t2; 1176 1177EXPLAIN 1178SELECT * FROM t2 1179 WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 1180 WHERE 1181 alias2.b = alias1.a AND 1182 (alias1.b >= alias1.a OR alias2.b = 'z')); 1183 1184--sorted_result 1185SELECT * FROM t2 1186 WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 1187 WHERE 1188 alias2.b = alias1.a AND 1189 (alias1.b >= alias1.a OR alias2.b = 'z')); 1190 1191DROP VIEW v1; 1192DROP TABLE t1, t2; 1193 1194--echo # 1195--echo # BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR 1196--echo # (this is a regression caused by the fix for BUG#951937) 1197CREATE TABLE t1 ( a INT, b INT, c INT, d INT ); 1198INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8); 1199 1200SELECT * FROM t1 1201WHERE a = d AND ( b = 50 AND b = d OR a = c ); 1202DROP TABLE t1; 1203 1204--echo # 1205--echo # BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery 1206--echo # 1207set @tmp_951283=@@optimizer_prune_level; 1208SET optimizer_prune_level=0; 1209 1210CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; 1211INSERT INTO t1 VALUES 1212 (10),(11),(12),(13),(14),(15),(16), 1213 (17),(18),(19),(20),(21),(22),(23); 1214 1215CREATE TABLE t2 ( 1216 b INT PRIMARY KEY, 1217 c VARCHAR(1), 1218 d VARCHAR(1), 1219 KEY(c) 1220) ENGINE=InnoDB; 1221 1222INSERT INTO t2 VALUES 1223 (1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'), 1224 (5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'), 1225 (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'), 1226 (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'), 1227 (17,'q','q'),(18,'w','w'),(19,'d','d'); 1228 1229analyze table t1 persistent for all; 1230analyze table t2 persistent for all; 1231--replace_column 9 # 1232EXPLAIN 1233SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1234WHERE alias3.d IN ( 1235 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1236 WHERE alias5.b = alias4.b 1237 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) 1238); 1239 1240SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1241WHERE alias3.d IN ( 1242 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1243 WHERE alias5.b = alias4.b 1244 AND ( alias5.b >= alias3.b OR alias5.c != alias3.c ) 1245); 1246 1247# MDEV-8189 field<>const and const<>field are not symmetric 1248# Do the same EXPLAIN SELECT and SELECT 1249# with "alias3.c != alias5.c" instead of "alias5.c != alias3.c" 1250 1251--replace_column 9 # 1252EXPLAIN 1253SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1254WHERE alias3.d IN ( 1255 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1256 WHERE alias5.b = alias4.b 1257 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) 1258); 1259 1260SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3 1261WHERE alias3.d IN ( 1262 SELECT alias4.c FROM t2 AS alias4, t2 AS alias5 1263 WHERE alias5.b = alias4.b 1264 AND ( alias5.b >= alias3.b OR alias3.c != alias5.c ) 1265); 1266 1267set optimizer_prune_level=@tmp_951283; 1268DROP TABLE t1,t2; 1269 1270--echo # 1271--echo # Bug mdev-5135: crash on semijoin with nested outer joins 1272--echo # 1273 1274CREATE TABLE t1 (i1 int) ENGINE=MyISAM; 1275INSERT INTO t1 VALUES (1),(2); 1276 1277CREATE TABLE t2 (i2 int, INDEX(i2)) ENGINE=MyISAM; 1278 1279CREATE TABLE t3 (i3 int, c varchar(1), INDEX(i3), INDEX(c)) ENGINE=MyISAM; 1280INSERT INTO t3 VALUES (3,'x'),(4,'y'); 1281 1282SELECT * FROM t1 WHERE ( 1, 1 ) IN ( 1283 SELECT i2, i2 FROM t2 LEFT OUTER JOIN ( 1284 t3 AS t3a INNER JOIN t3 AS t3b ON ( t3a.i3 = t3b.i3 ) 1285 ) ON ( t3a.c = t3b.c ) 1286); 1287 1288DROP TABLE t1,t2,t3; 1289 1290--echo # 1291--echo # MDEV-5582: Plugin 'MEMORY' has ref_count=1 after shutdown with materialization+semijoin 1292--echo # 1293CREATE TABLE t1 (a INT) engine=innodb; 1294INSERT INTO t1 VALUES (8),(9); 1295 1296CREATE TABLE t2 (b INT) engine=innodb; 1297INSERT INTO t2 VALUES (2),(3); 1298 1299CREATE TABLE t3 (c INT, INDEX(c)) engine=innodb; 1300INSERT INTO t2 VALUES (4),(5); 1301 1302explain 1303SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); 1304SELECT * FROM t1 WHERE 9 IN ( SELECT b FROM t2 WHERE 1 IN ( SELECT MIN(c) FROM t3 ) ); 1305 1306DROP TABLE t1,t2,t3; 1307 1308--source include/have_innodb.inc 1309 1310--disable_warnings 1311DROP TABLE IF EXISTS t1,t2,t3,t4; 1312--enable_warnings 1313 1314--echo # 1315--echo # MDEV-4782: Valgrind warnings (Conditional jump or move depends on uninitialised value) with InnoDB, semijoin 1316--echo # 1317CREATE TABLE t1 ( t1_pk1 varchar(3), t1_pk2 varchar(52), PRIMARY KEY (t1_pk1,t1_pk2)) ENGINE=InnoDB; 1318INSERT INTO t1 VALUES ('CHN','Chinese'),('USA','English'); 1319 1320CREATE TABLE t2 ( t2_i int(11), t2_c varchar(52)) ENGINE=InnoDB; 1321INSERT INTO t2 VALUES (86,'English'); 1322 1323CREATE TABLE t3 ( t3_i int(11), t3_c varchar(35)) ENGINE=InnoDB; 1324INSERT INTO t3 VALUES (3989,'Abilene'),(3873,'Akron'); 1325 1326create table t4 like t1; 1327insert into t4 select * from t1; 1328 1329SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); 1330 1331explain 1332SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); 1333 1334DROP TABLE t1,t2,t3,t4; 1335 1336--echo # 1337--echo # MDEV-6263: Wrong result when using IN subquery with order by 1338--echo # 1339CREATE TABLE t1 ( 1340 id int(11) NOT NULL, 1341 nombre varchar(255) NOT NULL, 1342 PRIMARY KEY (id) 1343) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1344 1345INSERT INTO t1 (id, nombre) VALUES 1346(1, 'row 1'),(2, 'row 2'),(3, 'row 3'), 1347(4, 'row 4'),(5, 'row 5'),(6, 'row 6'); 1348 1349CREATE TABLE t2 ( 1350 id_algo int(11) NOT NULL, 1351 id_agente int(11) NOT NULL, 1352 PRIMARY KEY (id_algo,id_agente), 1353 KEY another_data (id_agente) 1354) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1355 1356INSERT INTO t2 (id_algo, id_agente) VALUES 1357(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1); 1358 1359SELECT * FROM t1 WHERE id in (select distinct id_agente from t2) ORDER BY nombre ASC; 1360 1361SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); 1362 1363DROP TABLE t1, t2; 1364 1365--echo # 1366--echo # MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth 1367--echo # 1368 1369CREATE TABLE t1 ( 1370 t1id BIGINT(20) NOT NULL, 1371 code VARCHAR(20), 1372 PRIMARY KEY (t1id) 1373) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1374 1375CREATE TABLE t2 ( 1376 t2id BIGINT(20) NOT NULL, 1377 t1idref BIGINT(20) NOT NULL, 1378 code VARCHAR(20), 1379 PRIMARY KEY (t2id), 1380 INDEX FK_T2_T1Id (t1idref), 1381 CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) 1382) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1383 1384CREATE TABLE t3 ( 1385 t3idref BIGINT(20) NOT NULL, 1386 t2idref BIGINT(20) NOT NULL, 1387 sequencenumber INT(10) NOT NULL, 1388 PRIMARY KEY (t3idref, t2idref), 1389 INDEX FK_T3_T2Id (t2idref), 1390 CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) 1391) COLLATE='utf8mb4_bin' ENGINE=InnoDB; 1392 1393# Load up dummy data (needed to reproduce issue) 1394INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), 1395(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), 1396(100043),(100044),(100045),(100046),(100047); 1397 1398INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; 1399 1400# Now the test Data 1401INSERT IGNORE INTO t1 VALUES (200001, 'a'); 1402INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); 1403INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); 1404 1405ANALYZE TABLE t1,t2,t3; 1406 1407set @tmp7474= @@optimizer_search_depth; 1408SET SESSION optimizer_search_depth = 1; 1409 1410let $query= 1411SELECT SQL_NO_CACHE 1412T2_0_.t1idref, 1413T2_0_.t2id 1414FROM 1415 t2 T2_0_ 1416WHERE 1417 T2_0_.t1idref IN ( 1418 SELECT 1419 T1_1_.t1id 1420 FROM 1421 t3 T3_0_ 1422 INNER JOIN 1423 t2 T2_1_ 1424 ON T3_0_.t2idref=T2_1_.t2id 1425 INNER JOIN 1426 t1 T1_1_ 1427 ON T2_1_.t1idref=T1_1_.t1id 1428 WHERE 1429 T3_0_.t3idref= 1 1430); 1431 1432eval $query; 1433eval explain $query; 1434 1435drop table t3,t2,t1; 1436set optimizer_search_depth=@tmp7474; 1437 1438--echo # 1439--echo # 1440--echo # 1441CREATE TABLE t1 ( 1442 id int(16) NOT NULL AUTO_INCREMENT, 1443 PRIMARY KEY (id) 1444) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1445 1446CREATE TABLE t2 ( 1447 id int(16) NOT NULL AUTO_INCREMENT, 1448 t3_id int(16) NOT NULL DEFAULT '0', 1449 t1_id int(16) NOT NULL DEFAULT '0', 1450 PRIMARY KEY (id), 1451 KEY t3_idx (t3_id), 1452 KEY t1_idx (t1_id) 1453) ENGINE=MyISAM DEFAULT CHARSET=utf8; 1454 1455CREATE TABLE t3 ( 1456 id int(16) NOT NULL AUTO_INCREMENT, 1457 PRIMARY KEY (id) 1458) ENGINE=MyISAM DEFAULT CHARSET=utf8; 1459 1460 1461INSERT INTO t3 VALUES (1); 1462 1463INSERT INTO t2 VALUES (1, 1, 1); 1464INSERT INTO t2 VALUES (2, 1, 2); 1465INSERT INTO t2 VALUES (3, 1, 2); 1466INSERT INTO t2 VALUES (4, 1, 1); 1467 1468INSERT INTO t1 VALUES (1); 1469INSERT INTO t1 VALUES (2); 1470 1471SELECT * FROM t1 WHERE t1.id IN ( 1472 SELECT t2.t1_id FROM t3 JOIN t2 ON t3.id = t2.t3_id WHERE t3.id = 1 1473); 1474 1475drop table t1,t2,t3; 1476 1477--echo # 1478--echo # MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table 1479--echo # 1480 1481CREATE TABLE t1 (a INT) ENGINE=InnoDB; 1482CREATE TABLE t2 (pk BLOB, b INT, PRIMARY KEY(pk(1000))) ENGINE=InnoDB; 1483CREATE TABLE t3 (c INT) ENGINE=InnoDB; 1484CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; 1485 1486INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1487SELECT * FROM t1, t2 1488WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; 1489DROP TABLE t1,t2,t3; 1490DROP VIEW v3; 1491 1492--echo # This must be the last in the file: 1493set global innodb_stats_persistent= @innodb_stats_persistent_save; 1494set global innodb_stats_persistent_sample_pages= 1495 @innodb_stats_persistent_sample_pages_save; 1496set optimizer_switch=@subselect_sj2_tmp; 1497