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