1--disable_warnings 2DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 3DROP DATABASE IF EXISTS world; 4--enable_warnings 5 6set names utf8; 7 8CREATE DATABASE world; 9 10use world; 11 12--source include/world_schema1.inc 13 14--disable_query_log 15--disable_result_log 16--disable_warnings 17--source include/world.inc 18--enable_warnings 19--enable_result_log 20--enable_query_log 21 22SELECT COUNT(*) FROM Country; 23SELECT COUNT(*) FROM City; 24SELECT COUNT(*) FROM CountryLanguage; 25 26show variables like 'join_buffer_size'; 27 28EXPLAIN 29SELECT City.Name, Country.Name FROM City,Country 30 WHERE City.Country=Country.Code AND 31 Country.Name LIKE 'L%' AND City.Population > 100000; 32 33--sorted_result 34SELECT City.Name, Country.Name FROM City,Country 35 WHERE City.Country=Country.Code AND 36 Country.Name LIKE 'L%' AND City.Population > 100000; 37 38EXPLAIN 39SELECT City.Name, Country.Name, CountryLanguage.Language 40 FROM City,Country,CountryLanguage 41 WHERE City.Country=Country.Code AND 42 CountryLanguage.Country=Country.Code AND 43 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 44 CountryLanguage.Percentage > 50; 45 46--sorted_result 47SELECT City.Name, Country.Name, CountryLanguage.Language 48 FROM City,Country,CountryLanguage 49 WHERE City.Country=Country.Code AND 50 CountryLanguage.Country=Country.Code AND 51 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 52 CountryLanguage.Percentage > 50; 53 54set join_buffer_size=256; 55show variables like 'join_buffer_size'; 56 57EXPLAIN 58SELECT City.Name, Country.Name FROM City,Country 59 WHERE City.Country=Country.Code AND 60 Country.Name LIKE 'L%' AND City.Population > 100000; 61 62--sorted_result 63SELECT City.Name, Country.Name FROM City,Country 64 WHERE City.Country=Country.Code AND 65 Country.Name LIKE 'L%' AND City.Population > 100000; 66 67EXPLAIN 68SELECT City.Name, Country.Name, CountryLanguage.Language 69 FROM City,Country,CountryLanguage 70 WHERE City.Country=Country.Code AND 71 CountryLanguage.Country=Country.Code AND 72 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 73 CountryLanguage.Percentage > 50; 74 75--sorted_result 76SELECT City.Name, Country.Name, CountryLanguage.Language 77 FROM City,Country,CountryLanguage 78 WHERE City.Country=Country.Code AND 79 CountryLanguage.Country=Country.Code AND 80 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 81 CountryLanguage.Percentage > 50; 82 83set join_buffer_size=default; 84show variables like 'join_buffer_size'; 85 86DROP DATABASE world; 87 88 89CREATE DATABASE world; 90 91use world; 92 93--source include/world_schema.inc 94 95--disable_query_log 96--disable_result_log 97--disable_warnings 98--source include/world.inc 99--enable_warnings 100--enable_result_log 101--enable_query_log 102 103show variables like 'join_buffer_size'; 104 105EXPLAIN 106SELECT City.Name, Country.Name FROM City,Country 107 WHERE City.Country=Country.Code AND 108 Country.Name LIKE 'L%' AND City.Population > 100000; 109 110--sorted_result 111SELECT City.Name, Country.Name FROM City,Country 112 WHERE City.Country=Country.Code AND 113 Country.Name LIKE 'L%' AND City.Population > 100000; 114 115--replace_result 185 # 188 # 116EXPLAIN 117SELECT City.Name, Country.Name, CountryLanguage.Language 118 FROM City,Country,CountryLanguage 119 WHERE City.Country=Country.Code AND 120 CountryLanguage.Country=Country.Code AND 121 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 122 CountryLanguage.Percentage > 50; 123 124--sorted_result 125SELECT City.Name, Country.Name, CountryLanguage.Language 126 FROM City,Country,CountryLanguage 127 WHERE City.Country=Country.Code AND 128 CountryLanguage.Country=Country.Code AND 129 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 130 CountryLanguage.Percentage > 50; 131 132EXPLAIN 133SELECT Name FROM City 134 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 135 City.Population > 100000; 136 137--sorted_result 138SELECT Name FROM City 139 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 140 City.Population > 100000; 141 142EXPLAIN 143SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 144 FROM Country LEFT JOIN CountryLanguage ON 145 (CountryLanguage.Country=Country.Code AND Language='English') 146 WHERE 147 Country.Population > 10000000; 148 149--sorted_result 150SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) 151 FROM Country LEFT JOIN CountryLanguage ON 152 (CountryLanguage.Country=Country.Code AND Language='English') 153 WHERE 154 Country.Population > 10000000; 155 156set join_buffer_size=256; 157show variables like 'join_buffer_size'; 158 159EXPLAIN 160SELECT City.Name, Country.Name FROM City,Country 161 WHERE City.Country=Country.Code AND 162 Country.Name LIKE 'L%' AND City.Population > 100000; 163 164--sorted_result 165SELECT City.Name, Country.Name FROM City,Country 166 WHERE City.Country=Country.Code AND 167 Country.Name LIKE 'L%' AND City.Population > 100000; 168 169--replace_result 185 # 188 # 170EXPLAIN 171SELECT City.Name, Country.Name, CountryLanguage.Language 172 FROM City,Country,CountryLanguage 173 WHERE City.Country=Country.Code AND 174 CountryLanguage.Country=Country.Code AND 175 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 176 CountryLanguage.Percentage > 50; 177 178--sorted_result 179SELECT City.Name, Country.Name, CountryLanguage.Language 180 FROM City,Country,CountryLanguage 181 WHERE City.Country=Country.Code AND 182 CountryLanguage.Country=Country.Code AND 183 City.Name LIKE 'L%' AND Country.Population > 3000000 AND 184 CountryLanguage.Percentage > 50; 185 186EXPLAIN 187SELECT Name FROM City 188 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 189 City.Population > 100000; 190 191--sorted_result 192SELECT Name FROM City 193 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND 194 City.Population > 100000; 195 196set join_buffer_size=default; 197show variables like 'join_buffer_size'; 198 199--sorted_result 200SELECT City.Name, Country.Name FROM City,Country 201 WHERE City.Country=Country.Code AND City.Population > 3000000; 202 203set join_buffer_size=256; 204 205--replace_column 9 # 206EXPLAIN 207SELECT City.Name, Country.Name FROM City,Country 208 WHERE City.Country=Country.Code AND City.Population > 3000000; 209 210--sorted_result 211SELECT City.Name, Country.Name FROM City,Country 212 WHERE City.Country=Country.Code AND City.Population > 3000000; 213 214set join_buffer_size=default; 215 216ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default ''; 217 218--sorted_result 219SELECT City.Name, Country.Name FROM City,Country 220 WHERE City.Country=Country.Code AND 221 Country.Name LIKE 'L%' AND City.Population > 100000; 222 223ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default ''; 224 225--sorted_result 226SELECT City.Name, Country.Name FROM City,Country 227 WHERE City.Country=Country.Code AND 228 Country.Name LIKE 'L%' AND City.Population > 100000; 229 230ALTER TABLE Country ADD COLUMN PopulationBar text; 231UPDATE Country 232 SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int)); 233 234--sorted_result 235SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 236 WHERE City.Country=Country.Code AND 237 Country.Name LIKE 'L%' AND City.Population > 100000; 238 239set join_buffer_size=256; 240 241--sorted_result 242SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country 243 WHERE City.Country=Country.Code AND 244 Country.Name LIKE 'L%' AND City.Population > 100000; 245 246set join_buffer_size=default; 247 248DROP DATABASE world; 249 250use test; 251 252# 253# Bug #35685: assertion abort when initializing a BKA cache 254# 255 256CREATE TABLE t1( 257 affiliatetometaid int NOT NULL default '0', 258 uniquekey int NOT NULL default '0', 259 metaid int NOT NULL default '0', 260 affiliateid int NOT NULL default '0', 261 xml text, 262 isactive char(1) NOT NULL default 'Y', 263 PRIMARY KEY (affiliatetometaid) 264); 265CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey); 266CREATE INDEX t1_affiliateid ON t1(affiliateid); 267CREATE INDEX t1_metaid on t1 (metaid); 268INSERT INTO t1 VALUES 269 (1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y'); 270 271CREATE TABLE t2( 272 metaid int NOT NULL default '0', 273 name varchar(80) NOT NULL default '', 274 dateadded timestamp NOT NULL , 275 xml text, 276 status int default NULL, 277 origin int default NULL, 278 gid int NOT NULL default '1', 279 formattypeid int default NULL, 280 PRIMARY KEY (metaid) 281); 282CREATE INDEX t2_status ON t2(status); 283CREATE INDEX t2_gid ON t2(gid); 284CREATE INDEX t2_formattypeid ON t2(formattypeid); 285INSERT INTO t2 VALUES 286 (1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL), 287 (1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL); 288 289CREATE TABLE t3( 290 mediaid int NOT NULL , 291 metaid int NOT NULL default '0', 292 formatid int NOT NULL default '0', 293 status int default NULL, 294 path varchar(100) NOT NULL default '', 295 datemodified timestamp NOT NULL , 296 resourcetype int NOT NULL default '1', 297 parameters text, 298 signature int default NULL, 299 quality int NOT NULL default '255', 300 PRIMARY KEY (mediaid) 301); 302CREATE INDEX t3_metaid ON t3(metaid); 303CREATE INDEX t3_formatid ON t3(formatid); 304CREATE INDEX t3_status ON t3(status); 305CREATE INDEX t3_metaidformatid ON t3(metaid,formatid); 306CREATE INDEX t3_signature ON t3(signature); 307CREATE INDEX t3_quality ON t3(quality); 308INSERT INTO t3 VALUES 309 (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), 310 (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); 311 312CREATE TABLE t4( 313 formatid int NOT NULL , 314 name varchar(60) NOT NULL default '', 315 formatclassid int NOT NULL default '0', 316 mime varchar(60) default NULL, 317 extension varchar(10) default NULL, 318 priority int NOT NULL default '0', 319 canaddtocapability char(1) NOT NULL default 'Y', 320 PRIMARY KEY (formatid) 321); 322CREATE INDEX t4_formatclassid ON t4(formatclassid); 323CREATE INDEX t4_formats_idx ON t4(canaddtocapability); 324INSERT INTO t4 VALUES 325 (19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'), 326 (54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y'); 327 328CREATE TABLE t5( 329 formatclassid int NOT NULL , 330 name varchar(60) NOT NULL default '', 331 priority int NOT NULL default '0', 332 formattypeid int NOT NULL default '0', 333 PRIMARY KEY (formatclassid) 334); 335CREATE INDEX t5_formattypeid on t5(formattypeid); 336INSERT INTO t5 VALUES 337 (11, "Info", 0, 4), (13, "Digital Audio", 0, 2); 338 339CREATE TABLE t6( 340 formattypeid int NOT NULL , 341 name varchar(60) NOT NULL default '', 342 priority int default NULL, 343 PRIMARY KEY (formattypeid) 344); 345INSERT INTO t6 VALUES 346 (2, "Ringtones", 0); 347 348CREATE TABLE t7( 349 metaid int NOT NULL default '0', 350 artistid int NOT NULL default '0', 351 PRIMARY KEY (metaid,artistid) 352); 353INSERT INTO t7 VALUES 354 (4, 5), (3, 4); 355 356CREATE TABLE t8( 357 artistid int NOT NULL , 358 name varchar(80) NOT NULL default '', 359 PRIMARY KEY (artistid) 360); 361INSERT INTO t8 VALUES 362 (5, "Anastacia"), (4, "John Mayer"); 363 364CREATE TABLE t9( 365 subgenreid int NOT NULL default '0', 366 metaid int NOT NULL default '0', 367 PRIMARY KEY (subgenreid,metaid) 368) ; 369CREATE INDEX t9_subgenreid ON t9(subgenreid); 370CREATE INDEX t9_metaid ON t9(metaid); 371INSERT INTO t9 VALUES 372 (138, 4), (31, 3); 373 374CREATE TABLE t10( 375 subgenreid int NOT NULL , 376 genreid int NOT NULL default '0', 377 name varchar(80) NOT NULL default '', 378 PRIMARY KEY (subgenreid) 379) ; 380CREATE INDEX t10_genreid ON t10(genreid); 381INSERT INTO t10 VALUES 382 (138, 19, ''), (31, 3, ''); 383 384CREATE TABLE t11( 385 genreid int NOT NULL default '0', 386 name char(80) NOT NULL default '', 387 priority int NOT NULL default '0', 388 masterclip char(1) default NULL, 389 PRIMARY KEY (genreid) 390) ; 391CREATE INDEX t11_masterclip ON t11( masterclip); 392INSERT INTO t11 VALUES 393 (19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y'); 394 395EXPLAIN 396SELECT t1.uniquekey, t1.xml AS affiliateXml, 397 t8.name AS artistName, t8.artistid, 398 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 399 t10.subgenreid, t10.name AS subgenreName, 400 t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 401 t4.priority + t5.priority + t6.priority AS overallPriority, 402 t3.path AS path, t3.mediaid, 403 t4.formatid, t4.name AS formatName, 404 t5.formatclassid, t5.name AS formatclassName, 405 t6.formattypeid, t6.name AS formattypeName 406FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 407WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 408 t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 409 t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 410 t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 411 t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 412 t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 413 t1.metaid = t2.metaid AND t1.affiliateid = '2'; 414 415SELECT t1.uniquekey, t1.xml AS affiliateXml, 416 t8.name AS artistName, t8.artistid, 417 t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, 418 t10.subgenreid, t10.name AS subgenreName, 419 t2.name AS metaName, t2.metaid, t2.xml AS metaXml, 420 t4.priority + t5.priority + t6.priority AS overallPriority, 421 t3.path AS path, t3.mediaid, 422 t4.formatid, t4.name AS formatName, 423 t5.formatclassid, t5.name AS formatclassName, 424 t6.formattypeid, t6.name AS formattypeName 425FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 426WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND 427 t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 428 t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND 429 t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND 430 t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND 431 t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND 432 t1.metaid = t2.metaid AND t1.affiliateid = '2'; 433 434DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; 435 436# 437# Bug #37131: 3-way join query with BKA used with a small buffer and 438# only for the third table 439# 440 441CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' ); 442CREATE TABLE t2 ( 443 a2 int, b2 int, filler2 char(64) default ' ', 444 PRIMARY KEY idx(a2,b2,filler2) 445) ; 446CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); 447 448INSERT INTO t1(a1) VALUES 449 (4), (7), (1), (9), (8), (5), (3), (6), (2); 450INSERT INTO t2(a2,b2) VALUES 451 (1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56), 452 (4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81), 453 (5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51), 454 (9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79), 455 (3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11); 456INSERT INTO t3 VALUES 457 (30,302), (92,923), (18,187), (45,459), (30,309), 458 (39,393), (68,685), (45,458), (21,210), (81,817), 459 (40,405), (61,618), (73,738), (92,929), (27,275), 460 (18,188), (84,846), (56,564), (14,144), (76,763), 461 (98,982), (55,551), (17,174), (99,998), (51,513), 462 (28,282), (52,527), (33,336), (13,138), (87,878), 463 (43,431), (91,916), (62,624), (79,797), (49,494), 464 (93,933), (34,347), (82,829), (78,780), (63,634), 465 (32,329), (22,228), (11,114), (74,749), (23,236); 466 467EXPLAIN 468SELECT a1<>a2, a1, a2, b2, b3, c3, 469 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 470FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 471 472--sorted_result 473SELECT a1<>a2, a1, a2, b2, b3, c3, 474 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 475FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 476 477set join_buffer_size=512; 478 479EXPLAIN 480SELECT a1<>a2, a1, a2, b2, b3, c3, 481 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 482FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 483 484--sorted_result 485SELECT a1<>a2, a1, a2, b2, b3, c3, 486 SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 487FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; 488 489DROP TABLE t1,t2,t3; 490 491# 492# Bug #37690: crash with a tiny buffer when using BKA_JOIN_CACHE_UNIQUE 493# 494 495CREATE TABLE t1 (a int, b int, INDEX idx(b)); 496CREATE TABLE t2 (a int, b int, INDEX idx(a)); 497INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); 498INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); 499INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); 500 501set join_buffer_size=32; 502 503EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 504--sorted_result 505SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; 506 507DROP TABLE t1,t2; 508 509--echo 510--echo BUG#40136: Group by is ignored when join buffer is used for an outer join 511--echo 512create table t1(a int PRIMARY KEY, b int); 513insert into t1 values 514 (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); 515create table t2 (p int, a int, INDEX i_a(a)); 516insert into t2 values 517 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), 518 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); 519explain 520select t1.a, count(t2.p) as count 521 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 522select t1.a, count(t2.p) as count 523 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; 524drop table t1, t2; 525 526--echo # 527--echo # Bug #40134: outer join with not exists optimization and join buffer 528--echo # 529 530set join_buffer_size=default; 531 532CREATE TABLE t1 (a int NOT NULL); 533INSERT INTO t1 VALUES (2), (4), (3), (5), (1); 534CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); 535INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); 536 537EXPLAIN 538SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 539SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; 540 541DROP TABLE t1, t2; 542 543--echo # 544--echo # BUG#40268: Nested outer join with not null-rejecting where condition 545--echo # over an inner table which is not the last in the nest 546--echo # 547 548CREATE TABLE t2 (a int, b int, c int); 549CREATE TABLE t3 (a int, b int, c int); 550CREATE TABLE t4 (a int, b int, c int); 551 552INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); 553INSERT INTO t3 VALUES (1,2,0), (2,2,0); 554INSERT INTO t4 VALUES (3,2,0), (4,2,0); 555 556--sorted_result 557SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b 558 FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b 559 WHERE t3.a+2<t2.a OR t3.c IS NULL; 560 561DROP TABLE t2, t3, t4; 562 563--echo # 564--echo # Bug #40192: outer join with where clause when using BNL 565--echo # 566 567create table t1 (a int, b int); 568insert into t1 values (2, 20), (3, 30), (1, 10); 569create table t2 (a int, c int); 570insert into t2 values (1, 101), (3, 102), (1, 100); 571 572--sorted_result 573select * from t1 left join t2 on t1.a=t2.a; 574explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 575--sorted_result 576select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; 577 578drop table t1, t2; 579 580--echo # 581--echo # Bug #40317: outer join with with constant on expression equal to FALSE 582--echo # 583 584create table t1 (a int); 585insert into t1 values (30), (40), (20); 586create table t2 (b int); 587insert into t2 values (200), (100); 588 589select * from t1 left join t2 on (1=0); 590explain select * from t1 left join t2 on (1=0) where a=40; 591select * from t1 left join t2 on (1=0) where a=40; 592 593drop table t1, t2; 594 595--echo # 596--echo # Bug #41204: small buffer with big rec_per_key for ref access 597--echo # 598 599CREATE TABLE t1 (a int); 600 601INSERT INTO t1 VALUES (0); 602INSERT INTO t1(a) SELECT a FROM t1; 603INSERT INTO t1(a) SELECT a FROM t1; 604INSERT INTO t1(a) SELECT a FROM t1; 605INSERT INTO t1(a) SELECT a FROM t1; 606INSERT INTO t1(a) SELECT a FROM t1; 607INSERT INTO t1(a) SELECT a FROM t1; 608INSERT INTO t1(a) SELECT a FROM t1; 609INSERT INTO t1(a) SELECT a FROM t1; 610INSERT INTO t1(a) SELECT a FROM t1; 611INSERT INTO t1(a) SELECT a FROM t1; 612INSERT INTO t1(a) SELECT a FROM t1; 613INSERT INTO t1 VALUES (20000), (10000); 614 615CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b)); 616INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5); 617INSERT INTO t2(b,c) SELECT b,c FROM t2; 618INSERT INTO t2(b,c) SELECT b,c FROM t2; 619INSERT INTO t2(b,c) SELECT b,c FROM t2; 620INSERT INTO t2(b,c) SELECT b,c FROM t2; 621INSERT INTO t2(b,c) SELECT b,c FROM t2; 622INSERT INTO t2(b,c) SELECT b,c FROM t2; 623INSERT INTO t2(b,c) SELECT b,c FROM t2; 624INSERT INTO t2(b,c) SELECT b,c FROM t2; 625 626--disable_result_log 627ANALYZE TABLE t1,t2; 628--enable_result_log 629 630set join_buffer_size=1024; 631 632EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 633SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; 634 635set join_buffer_size=default; 636 637DROP TABLE t1, t2; 638 639--echo # 640--echo # Bug #41894: big join buffer of level 7 used to join records 641--echo # with null values in place of varchar strings 642--echo # 643 644CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY, 645 b varchar(127) DEFAULT NULL); 646 647INSERT INTO t1(a) VALUES (1); 648INSERT INTO t1(b) SELECT b FROM t1; 649INSERT INTO t1(b) SELECT b FROM t1; 650INSERT INTO t1(b) SELECT b FROM t1; 651INSERT INTO t1(b) SELECT b FROM t1; 652INSERT INTO t1(b) SELECT b FROM t1; 653INSERT INTO t1(b) SELECT b FROM t1; 654INSERT INTO t1(b) SELECT b FROM t1; 655INSERT INTO t1(b) SELECT b FROM t1; 656INSERT INTO t1(b) SELECT b FROM t1; 657INSERT INTO t1(b) SELECT b FROM t1; 658INSERT INTO t1(b) SELECT b FROM t1; 659INSERT INTO t1(b) SELECT b FROM t1; 660INSERT INTO t1(b) SELECT b FROM t1; 661INSERT INTO t1(b) SELECT b FROM t1; 662 663CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 664INSERT INTO t2 SELECT * FROM t1; 665 666CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); 667INSERT INTO t3 SELECT * FROM t1; 668 669set join_buffer_size=1024*1024; 670 671EXPLAIN 672SELECT COUNT(*) FROM t1,t2,t3 673 WHERE t1.a=t2.a AND t2.a=t3.a AND 674 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 675 676SELECT COUNT(*) FROM t1,t2,t3 677 WHERE t1.a=t2.a AND t2.a=t3.a AND 678 t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; 679 680set join_buffer_size=default; 681 682DROP TABLE t1,t2,t3; 683 684--echo # 685--echo # Bug #42020: join buffer is used for outer join with fields of 686--echo # several outer tables in join buffer 687--echo # 688 689CREATE TABLE t1 ( 690 a bigint NOT NULL, 691 PRIMARY KEY (a) 692); 693INSERT INTO t1 VALUES 694 (2), (1); 695 696CREATE TABLE t2 ( 697 a bigint NOT NULL, 698 b bigint NOT NULL, 699 PRIMARY KEY (a,b) 700); 701INSERT INTO t2 VALUES 702 (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 703 (1,10), (1, 20), (1,30), (1,40), (1,50); 704 705CREATE TABLE t3 ( 706 pk bigint NOT NULL AUTO_INCREMENT, 707 a bigint NOT NULL, 708 b bigint NOT NULL, 709 val bigint DEFAULT '0', 710 PRIMARY KEY (pk), 711 KEY idx (a,b) 712); 713INSERT INTO t3(a,b) VALUES 714 (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), 715 (4,30), (4,40), (4,50), (4,60), (4,70), (4,80), 716 (5,30), (5,40), (5,50), (5,60), (5,70), (5,80), 717 (7,30), (7,40), (7,50), (7,60), (7,70), (7,80); 718 719--sorted_result 720SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 721 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 722 WHERE t1.a=t2.a; 723 724set join_buffer_size=256; 725 726EXPLAIN 727SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 728 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 729 WHERE t1.a=t2.a; 730--sorted_result 731SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 732 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 733 WHERE t1.a=t2.a; 734 735DROP INDEX idx ON t3; 736 737EXPLAIN 738SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 739 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 740 WHERE t1.a=t2.a; 741 742--sorted_result 743SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 744 FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 745 WHERE t1.a=t2.a; 746 747set join_buffer_size=default; 748DROP TABLE t1,t2,t3; 749 750# 751# WL#4424 Full index condition pushdown with batched key access join 752# 753create table t1(f1 int, f2 int); 754insert into t1 values (1,1),(2,2),(3,3); 755create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2)); 756insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 757insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 758 (2,4, 'qwerty'),(2,5, 'qwerty'); 759insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 760insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 761 (4,4, 'qwerty'); 762insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); 763insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), 764 (2,4, 'qwerty'),(2,5, 'qwerty'); 765insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); 766insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), 767 (4,4, 'qwerty'); 768 769--sorted_result 770select t2.f1, t2.f2, t2.f3 from t1,t2 771where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; 772 773explain select t2.f1, t2.f2, t2.f3 from t1,t2 774where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; 775 776drop table t1,t2; 777 778--echo # 779--echo # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 780--echo # 781 782create table t1 (d int, id1 int, index idx1 (d, id1)); 783insert into t1 values 784 (3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); 785 786create table t2 (id1 int, id2 int, index idx2 (id1)); 787insert into t2 values 788 (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), 789 (40, 200), (30, 300), (10, 400), (20, 200), (20, 300); 790 791explain 792select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 793 where t1.d=3 group by t1.id1; 794 795select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 796 where t1.d=3 group by t1.id1; 797 798explain 799select t1.id1 from t1 join t2 on t1.id1=t2.id1 800 where t1.d=3 and t2.id2 > 200 order by t1.id1; 801 802select t1.id1 from t1 join t2 on t1.id1=t2.id1 803 where t1.d=3 and t2.id2 > 200 order by t1.id1; 804 805drop table t1,t2; 806 807--echo # 808--echo # Bug #44019: star-like multi-join query executed optimizer_join_cache_level=6 809--echo # 810 811create table t1 (a int, b int, c int, d int); 812create table t2 (b int, e varchar(16), index idx(b)); 813create table t3 (d int, f varchar(16), index idx(d)); 814create table t4 (c int, g varchar(16), index idx(c)); 815 816insert into t1 values 817 (5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000), 818 (2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800), 819 (7, 70, 700, 7000); 820insert into t2 values 821 (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), 822 (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), 823 (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); 824insert into t3 values 825 (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), 826 (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), 827 (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); 828insert into t4 values 829 (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), 830 (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), 831 (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); 832 833--disable_result_log 834--disable_warnings 835analyze table t2,t3,t4; 836--enable_warnings 837--enable_result_log 838 839explain 840select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 841 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 842 843select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 844 where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; 845 846drop table t1,t2,t3,t4; 847 848--echo # 849--echo # Bug #44250: Corruption of linked join buffers when using BKA 850--echo # 851 852CREATE TABLE t1 ( 853 id1 bigint(20) DEFAULT NULL, 854 id2 bigint(20) DEFAULT NULL, 855 id3 bigint(20) DEFAULT NULL, 856 num1 bigint(20) DEFAULT NULL, 857 num2 int(11) DEFAULT NULL, 858 num3 bigint(20) DEFAULT NULL 859); 860 861CREATE TABLE t2 ( 862 id3 bigint(20) NOT NULL DEFAULT '0', 863 id4 bigint(20) DEFAULT NULL, 864 enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL, 865 PRIMARY KEY (id3) 866); 867 868CREATE TABLE t3 ( 869 id4 bigint(20) NOT NULL DEFAULT '0', 870 text1 text, 871 PRIMARY KEY (id4) 872); 873 874CREATE TABLE t4 ( 875 id2 bigint(20) NOT NULL DEFAULT '0', 876 dummy int(11) DEFAULT '0', 877 PRIMARY KEY (id2) 878); 879 880CREATE TABLE t5 ( 881 id1 bigint(20) NOT NULL DEFAULT '0', 882 id2 bigint(20) NOT NULL DEFAULT '0', 883 enum2 enum('Active','Deleted','Paused') DEFAULT NULL, 884 PRIMARY KEY (id1,id2) 885); 886 887--disable_query_log 888--disable_result_log 889--disable_warnings 890 891INSERT INTO t1 VALUES 892(228172702,72485641,2667134182,10,1,14),(228172702,94266195,2667134182,134,0,134), 893(228172702,94266195,2667134182,15,0,15),(228172702,94266195,2667134182,2,0,3), 894(228172702,818095880,2667134182,1,1,1),(228172702,1004959639,2667134182,3,0,3), 895(228172702,1297484422,2667134182,1,2,1),(228172702,1730911800,2667134182,11,0,28), 896(228172702,1730911800,2667134182,4,0,4),(228172702,2182755982,2667134182,5,0,15), 897(228172702,2182755982,2667134182,1,0,1),(228172702,2968841184,2667134182,1,0,1), 898(228172702,4765525626,2667134182,2,0,3),(228172702,4765525626,2667134182,29,0,38), 899(228172702,4765525626,2667134182,7,0,7),(228172702,4765525626,2667134182,7,0,8), 900(228172702,5330573302,2667134182,1,0,1),(228512602,191149872,935692942,3,0,17), 901(228512602,259118753,935692942,13,7,13),(228512602,259118753,935692942,83,33,83), 902(228512602,585705465,935692942,1,0,1),(228512602,585716775,935692942,1,0,1), 903(228512602,585716775,935692942,6,6,6),(228512602,585716775,935692942,1,1,1), 904(228512602,1105371172,935692942,2,0,3),(228512602,1105371172,935692942,7,2,7), 905(228512602,1314223462,935692942,1,0,1),(228512602,1314223642,935692942,1,1,1), 906(228512602,1411060522,935692942,1,0,1),(228512602,1467398182,935692942,1,0,1), 907(228512602,1467398182,935692942,3,0,4),(228512602,1467398242,935692942,10,0,41), 908(228512602,1467398242,935692942,28,0,40),(228512602,1467398242,935692942,0,0,0), 909(228512602,1467398242,935692942,29,2,33),(228512602,1734178942,935692942,1,0,1), 910(228512602,1734179122,935692942,1,0,4),(228512602,1734179122,935692942,3,0,6), 911(228512602,1953612870,935692942,1,0,1),(228512602,2271510562,935692942,1,1,1), 912(228512602,2271525022,935692942,0,0,0),(228512602,3058831402,935692942,1,1,1), 913(228512602,3723638842,935692942,1,1,1),(228512602,3723638842,935692942,4,3,4), 914(228512602,3723836602,935692942,1,1,1),(228512602,3723836842,935692942,1,1,1), 915(228512602,3723836962,935692942,1,1,1),(228512602,3723988102,935692942,11,4,11), 916(228512602,3723989182,935692942,8,3,8),(228512602,5920283002,935692942,1,0,1), 917(228512602,5920314232,935692942,1,0,1),(228512602,191149872,1241589892,0,0,0), 918(228512602,191149872,1241589892,2,0,4),(228512602,191149872,1241589892,0,0,0), 919(228512602,259118753,1241589892,8,4,8),(228512602,259118753,1241589892,70,33,70), 920(228512602,259118753,1241589892,1,1,1),(228512602,585716775,1241589892,8,7,8), 921(228512602,1105371172,1241589892,1,0,1),(228512602,1105371172,1241589892,9,0,9), 922(228512602,1314223462,1241589892,1,0,1),(228512602,1411060522,1241589892,1,1,1), 923(228512602,1467398182,1241589892,1,0,1),(228512602,1467398182,1241589892,4,1,4), 924(228512602,1467398182,1241589892,1,0,1),(228512602,1467398242,1241589892,10,0,28), 925(228512602,1467398242,1241589892,37,1,78),(228512602,1467398242,1241589892,28,9,30), 926(228512602,1467398242,1241589892,5,0,6),(228512602,1734179122,1241589892,3,1,18), 927(228512602,1734179122,1241589892,1,1,1),(228512602,1734179122,1241589892,2,0,3), 928(228512602,1953611430,1241589892,1,1,1),(228512602,1953611430,1241589892,1,1,1), 929(228512602,1953612870,1241589892,1,0,1),(228512602,2026844250,1241589892,1,0,1), 930(228512602,2271510562,1241589892,1,1,1),(228512602,2271525022,1241589892,1,0,1), 931(228512602,2941612417,1241589892,1,0,1),(228512602,3723988102,1241589892,1,0,1); 932INSERT INTO t1 VALUES 933(228512602,3723988102,1241589892,11,4,11),(228512602,3723989002,1241589892,1,0,1), 934(228512602,3752960902,1241589892,2,2,4),(228808822,17304242,935693782,6,0,17), 935(228808822,17304242,935693782,28,1,50),(228808822,17304242,935693782,29,3,61), 936(228808822,17304242,935693782,6,0,13),(228808822,30931012,935693782,21,0,60), 937(228808822,30931012,935693782,5,0,13),(228808822,37254452,935693782,3,0,3), 938(228808822,42726891,935693782,1,0,4),(228808822,42726891,935693782,3,0,6), 939(228808822,76261151,935693782,8,0,18),(228808822,88240139,935693782,1,0,1), 940(228808822,88240139,935693782,3,0,3),(228808822,94730895,935693782,2,0,4), 941(228808822,179737402,935693782,10,0,13),(228808822,179737402,935693782,7,0,8), 942(228808822,179737402,935693782,3,0,4),(228808822,271288782,935693782,1,0,6), 943(228808822,304690943,935693782,5,2,10),(228808822,304691183,935693782,4,0,16), 944(228808822,568994960,935693782,1,0,1),(228808822,631705925,935693782,1,0,1), 945(228808822,631745165,935693782,1,0,1),(228808822,631749605,935693782,1,0,4), 946(228808822,1057787002,935693782,1,0,1),(228808822,1057787002,935693782,2,1,4), 947(228808822,1057787002,935693782,12,1,20),(228808822,1057788022,935693782,2,0,40), 948(228808822,1057788022,935693782,2,1,3),(228808822,1057788022,935693782,9,2,16), 949(228808822,1335646822,935693782,3,1,6),(228808822,1335646882,935693782,1,0,3), 950(228808822,1335646882,935693782,1,0,3),(228808822,1335646942,935693782,7,2,15), 951(228808822,5510586183,935693782,1,1,1),(228808822,17304242,2482416112,11,0,28), 952(228808822,17304242,2482416112,34,0,62),(228808822,17304242,2482416112,43,2,89), 953(228808822,17304242,2482416112,9,0,19),(228808822,30931012,2482416112,32,2,84), 954(228808822,30931012,2482416112,6,0,14),(228808822,30931012,2482416112,2,0,9), 955(228808822,37254452,2482416112,1,1,1),(228808822,42726891,2482416112,2,0,10), 956(228808822,76261151,2482416112,11,0,26),(228808822,88240139,2482416112,3,0,3), 957(228808822,88240139,2482416112,1,0,1),(228808822,88240139,2482416112,3,0,4), 958(228808822,94730895,2482416112,1,0,3),(228808822,125469602,2482416112,0,0,0), 959(228808822,179737402,2482416112,4,0,10),(228808822,179737402,2482416112,8,1,9), 960(228808822,179737402,2482416112,7,1,9),(228808822,179737402,2482416112,1,0,1), 961(228808822,271288782,2482416112,2,0,14),(228808822,304690943,2482416112,3,0,6), 962(228808822,304691183,2482416112,1,0,4),(228808822,555689643,2482416112,2,1,8), 963(228808822,555689643,2482416112,1,0,4),(228808822,631705925,2482416112,1,0,1), 964(228808822,631712555,2482416112,1,0,1),(228808822,631745165,2482416112,1,0,1), 965(228808822,710348755,2482416112,1,0,1),(228808822,753718113,2482416112,1,0,1), 966(228808822,1057787002,2482416112,1,0,4),(228808822,1057787002,2482416112,1,0,1), 967(228808822,1057787002,2482416112,4,1,7),(228808822,1057788022,2482416112,7,0,12), 968(228808822,1057788022,2482416112,3,0,37),(228808822,1057788022,2482416112,0,0,0), 969(228808822,1057788022,2482416112,12,0,15),(228808822,1335646822,2482416112,14,1,28), 970(228808822,1335646882,2482416112,1,1,3),(228808822,1335646942,2482416112,5,1,9), 971(228808822,1335646942,2482416112,1,0,1),(230941762,16069490,2691187582,0,0,0), 972(230941762,16705991,2691187582,16,0,30),(230941762,16705991,2691187582,12,3,12); 973INSERT INTO t1 VALUES 974(230941762,16705991,2691187582,1,0,1),(230941762,27714032,2691187582,6,0,16), 975(230941762,27714032,2691187582,1,0,1),(230941762,27714032,2691187582,9,0,14), 976(230941762,28676710,2691187582,3,1,4),(230941762,370319272,2691187582,7,0,7), 977(230941762,1409814802,2691187582,1,0,3),(230941762,1409814982,2691187582,1,0,1), 978(230941762,1409814982,2691187582,1,1,1),(230941762,2069703256,2691187582,1,0,3), 979(230941762,16705991,2691187672,8,1,20),(230941762,16705991,2691187672,11,6,11), 980(230941762,16705991,2691187672,1,0,1),(230941762,27714032,2691187672,5,0,20), 981(230941762,27714032,2691187672,1,0,10),(230941762,27714032,2691187672,12,2,17), 982(230941762,28676710,2691187672,1,0,1),(230941762,142889951,2691187672,2,0,10), 983(230941762,172526592,2691187672,1,1,1),(230941762,293109282,2691187672,1,0,1), 984(230941762,370319272,2691187672,10,0,10),(230941762,1409814802,2691187672,1,0,3), 985(230941762,1409814922,2691187672,1,0,1),(230941762,1409814982,2691187672,1,0,1), 986(230941762,16069490,2694472582,1,1,1),(230941762,16069490,2694472582,1,1,1), 987(230941762,16705991,2694472582,15,0,45),(230941762,16705991,2694472582,13,2,15), 988(230941762,27714032,2694472582,9,0,34),(230941762,27714032,2694472582,2,0,4), 989(230941762,27714032,2694472582,10,2,14),(230941762,28676710,2694472582,4,0,12), 990(230941762,28676710,2694472582,1,0,1),(230941762,172526592,2694472582,1,0,4), 991(230941762,293109282,2694472582,1,0,1),(230941762,370319272,2694472582,6,0,6), 992(230941762,1409814802,2694472582,1,0,3),(230941762,1409814862,2694472582,1,0,4), 993(230941762,1409814982,2694472582,1,0,1),(230941762,2680867980,2694472582,1,0,3), 994(230942122,25451690,935695702,1,0,9),(230942122,31549341,935695702,2,0,18), 995(230942122,31549341,935695702,2,0,4),(230942122,38900150,935695702,4,0,29), 996(230942122,38900150,935695702,4,1,13),(230942122,906919252,935695702,39,0,271), 997(230942122,906919252,935695702,20,0,83),(230942122,906919252,935695702,2,1,9), 998(230942122,1409816782,935695702,3,0,18),(230942122,1409816842,935695702,1,0,7), 999(230942122,1409816842,935695702,1,0,3),(230942122,1409816902,935695702,1,0,6), 1000(230942122,2145075862,935695702,4,1,4),(230942122,25451690,935695822,2,0,16), 1001(230942122,38900150,935695822,3,0,26),(230942122,38900150,935695822,1,0,3), 1002(230942122,906919252,935695822,24,0,176),(230942122,906919252,935695822,20,0,74), 1003(230942122,906919252,935695822,1,0,3),(230942122,1409816782,935695822,2,0,21), 1004(230942122,1409816782,935695822,2,0,21),(230942122,1409816842,935695822,1,0,3), 1005(230942122,1409816902,935695822,1,0,7),(231112162,1413675742,935696902,1,0,1), 1006(231112162,1413675742,935696962,0,0,0),(231112162,1413675742,935696962,4,2,4), 1007(231112162,1413675922,935696962,1,0,1),(231112162,1413675922,935696962,1,0,1), 1008(231112162,1413675742,1248588922,1,0,1),(231112162,1413675922,1248588922,3,0,3), 1009(233937022,12641121,935697562,2,0,13),(233937022,12653871,935697562,1,0,1), 1010(233937022,12693551,935697562,1,0,1),(233937022,12910461,935697562,2,0,6), 1011(233937022,12910461,935697562,26,0,65),(233937022,12910461,935697562,44,8,45), 1012(233937022,12910481,935697562,12,0,19),(233937022,12910481,935697562,7,2,9), 1013(233937022,12910481,935697562,1,0,1),(233937022,12910511,935697562,8,0,8); 1014INSERT INTO t1 VALUES 1015(233937022,12910511,935697562,20,6,22),(233937022,30879781,935697562,34,0,34), 1016(233937022,30879781,935697562,3,0,4),(233937022,30879781,935697562,1,0,1), 1017(233937022,45631730,935697562,8,0,39),(233937022,54079090,935697562,12,0,12), 1018(233937022,54079090,935697562,7,0,11),(233937022,54079090,935697562,14,0,16), 1019(233937022,94431735,935697562,6,0,31),(233937022,96876131,935697562,3,0,4), 1020(233937022,105436492,935697562,4,0,4),(233937022,128981555,935697562,3,0,3), 1021(233937022,145211004,935697562,1,0,1),(233937022,146382622,935697562,1,0,1), 1022(233937022,175678702,935697562,1,0,4),(233937022,298998998,935697562,1,0,1), 1023(233937022,335995773,935697562,3,0,3),(233937022,335995773,935697562,2,0,3), 1024(233937022,347447636,935697562,0,0,0),(233937022,459295955,935697562,3,0,3), 1025(233937022,459376625,935697562,1,0,1),(233937022,495877773,935697562,1,0,1), 1026(233937022,497008702,935697562,1,0,3),(233937022,561944105,935697562,1,0,1), 1027(233937022,561944105,935697562,1,0,1),(233937022,586535965,935697562,3,0,3), 1028(233937022,631549775,935697562,1,0,7),(233937022,647138479,935697562,1,0,1), 1029(233937022,655870453,935697562,4,0,7),(233937022,694832725,935697562,1,0,1), 1030(233937022,864475057,935697562,1,0,1),(233937022,1010757503,935697562,1,0,4), 1031(233937022,1010847736,935697562,2,0,9),(233937022,1287437116,935697562,2,0,4), 1032(233937022,1337693056,935697562,1,0,1),(233937022,1569279742,935697562,1,1,1), 1033(233937022,1569280102,935697562,2,0,7),(233937022,1569280882,935697562,2,1,3), 1034(233937022,1569281062,935697562,1,0,1),(233937022,1569281962,935697562,1,0,3), 1035(233937022,2823580588,935697562,2,0,8),(233937022,2823580588,935697562,3,1,10), 1036(233937022,2842066134,935697562,1,0,1),(233937022,2904542181,935697562,1,0,1), 1037(233937022,3058483627,935697562,1,0,1),(233937022,4507287318,935697562,1,0,1), 1038(233937022,5283489892,935697562,1,0,1),(233937022,11890554322,935697562,16,0,16), 1039(233937022,11890756102,935697562,3,1,3),(233937022,12641121,953996482,1,0,7), 1040(233937022,12641851,953996482,1,0,1),(233937022,12641851,953996482,1,0,1), 1041(233937022,12910461,953996482,4,0,14),(233937022,12910461,953996482,20,2,23), 1042(233937022,12910461,953996482,43,5,43),(233937022,12910461,953996482,1,0,1), 1043(233937022,12910481,953996482,17,2,30),(233937022,12910511,953996482,7,1,8), 1044(233937022,12910511,953996482,23,5,23),(233937022,14913951,953996482,2,0,3), 1045(233937022,21835210,953996482,1,1,1),(233937022,26481052,953996482,1,1,1), 1046(233937022,26481052,953996482,1,0,1),(233937022,30879781,953996482,2,0,3), 1047(233937022,30879781,953996482,22,0,22),(233937022,35617681,953996482,1,0,1), 1048(233937022,45631730,953996482,3,0,11),(233937022,54079090,953996482,13,0,13), 1049(233937022,54079090,953996482,11,0,16),(233937022,54079090,953996482,29,0,34), 1050(233937022,94431735,953996482,3,0,9),(233937022,96876131,953996482,3,0,4), 1051(233937022,105436492,953996482,1,0,1),(233937022,105437952,953996482,3,1,3), 1052(233937022,123639716,953996482,1,0,6),(233937022,145211004,953996482,2,0,3), 1053(233937022,145211004,953996482,2,1,3),(233937022,146382622,953996482,1,0,1), 1054(233937022,146382622,953996482,1,0,1),(233937022,155454324,953996482,1,0,1); 1055INSERT INTO t1 VALUES 1056(233937022,298998998,953996482,1,1,1),(233937022,335995773,953996482,1,0,1), 1057(233937022,335995773,953996482,7,2,9),(233937022,459295955,953996482,2,0,4), 1058(233937022,561944105,953996482,1,0,1),(233937022,655870453,953996482,5,0,9), 1059(233937022,694832725,953996482,1,0,1),(233937022,694832725,953996482,1,0,1), 1060(233937022,864475057,953996482,4,1,4),(233937022,897886118,953996482,1,0,1), 1061(233937022,897886118,953996482,1,0,3),(233937022,1005147016,953996482,1,0,1), 1062(233937022,1010757503,953996482,1,0,1),(233937022,1082217873,953996482,1,0,1), 1063(233937022,1286925326,953996482,1,0,1),(233937022,1337693056,953996482,4,0,4), 1064(233937022,1407236408,953996482,2,0,3),(233937022,1569280102,953996482,1,0,6), 1065(233937022,1569280222,953996482,1,0,1),(233937022,1569281062,953996482,1,0,1), 1066(233937022,1569284362,953996482,1,0,3),(233937022,2823580588,953996482,1,0,3), 1067(233937022,2904542181,953996482,3,0,7),(233937022,4371581485,953996482,1,0,1), 1068(233937022,5283491332,953996482,1,0,1),(233937022,7300486013,953996482,1,1,1), 1069(233937022,11890554322,953996482,16,0,16),(233937022,11890754392,953996482,1,0,1), 1070(233937022,11890754392,953996482,0,0,0); 1071 1072INSERT INTO t2 VALUES 1073(2667134182,2567095402,'Enabled'),(935692942,826927822,'Enabled'), 1074(1241589892,1130891152,'Enabled'),(935693782,826928662,'Enabled'), 1075(2482416112,2381969632,'Enabled'),(2691187582,2591198842,'Enabled'), 1076(2691187672,2591198932,'Enabled'),(2694472582,2594492212,'Paused'), 1077(935695702,826930582,'Enabled'),(935695822,826930702,'Enabled'), 1078(935696902,826931782,'Enabled'),(935696962,826931842,'Enabled'), 1079(1248588922,1137805582,'Enabled'),(935697562,826932442,'Paused'), 1080(953996482,845181202,'Enabled'),(2702549092,2602579882,'Enabled'), 1081(2702549182,2602579972,'Enabled'),(2702550712,2602581502,'Enabled'), 1082(1125312412,1015179502,'Enabled'),(2708245462,2608290202,'Enabled'), 1083(2708247262,2608292002,'Enabled'),(935699242,826934122,'Enabled'), 1084(1125312502,1015179592,'Enabled'),(1125312592,1015179682,'Enabled'), 1085(2711450452,2611502302,'Enabled'),(2711452252,2611504102,'Enabled'), 1086(935699902,826934782,'Enabled'),(935700262,826935142,'Enabled'), 1087(1215381442,1104677032,'Enabled'),(2503848082,2403457762,'Enabled'), 1088(935701762,826936642,'Enabled'),(935701822,826936702,'Enabled'), 1089(1468810282,1355227402,'Enabled'),(935702842,826937722,'Enabled'), 1090(1125312682,1015179772,'Enabled'),(2713816102,2613869392,'Enabled'), 1091(2688452032,2588455012,'Enabled'),(2688452212,2588455192,'Enabled'), 1092(2701527412,2601556942,'Enabled'),(1623918712,1510242412,'Enabled'), 1093(2701521922,2601551452,'Enabled'),(2701527772,2601557302,'Enabled'); 1094 1095INSERT INTO `t3` VALUES 1096(2567095402,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'), 1097(826927822,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'),(1130891152,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1098(826928662,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), 1099(2381969632,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), 1100(2591198842,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'), 1101(2591198932,'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE'), 1102(2594492212,'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'), 1103(826930582,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), 1104(826930702,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), 1105(826931782,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1106(826931842,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), 1107(1137805582,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'); 1108 1109INSERT INTO t4 VALUES 1110(12618121,0),(12641121,0),(12641851,0),(12653871,0),(12665801,0),(12666811,0), 1111(12693551,0),(12910461,0),(12910481,0),(12910511,0),(14787251,0),(14913941,0), 1112(14913951,0),(16069490,0),(16705901,0),(16705991,0),(17291062,0),(17304242,0), 1113(20737411,0),(21524370,0),(21835210,0),(25300361,0),(25451690,0),(25728842,0), 1114(26481052,0),(27714032,0),(28676710,0),(30879781,0),(30931012,0),(31549341,0), 1115(35617681,0),(37254452,0),(38619430,0),(38895490,0),(38900150,0),(39798990,0), 1116(42726891,0),(42867050,0),(43439030,0),(45631730,0),(47171711,0),(49539832,0), 1117(54079090,0),(60442241,0),(65320501,0),(72485641,0),(76261151,0),(87949714,0), 1118(88240139,0),(94266195,0),(94431735,0),(94730895,0),(96876131,0); 1119 1120INSERT INTO t5 VALUES 1121(228172702,72485641,'Active'),(228172702,94266195,'Active'), 1122(228172702,818095880,'Active'),(228172702,1004959639,'Active'), 1123(228172702,1297484242,'Active'),(228172702,1297484422,'Active'), 1124(228172702,1730911800,'Active'),(228172702,1808277389,'Active'), 1125(228172702,2182755982,'Active'),(228172702,2968841184,'Active'), 1126(228172702,3015116542,'Active'),(228172702,3752383170,'Active'), 1127(228172702,4765525626,'Active'),(228172702,5330573302,'Active'), 1128(228512602,191149872,'Active'),(228512602,259118753,'Active'), 1129(228512602,585705465,'Active'),(228512602,585716775,'Active'), 1130(228512602,1105371172,'Active'),(228512602,1314223462,'Active'), 1131(228512602,1314223642,'Active'),(228512602,1411060522,'Active'), 1132(228512602,1467398182,'Active'),(228512602,1467398242,'Active'), 1133(228512602,1734178942,'Active'),(228512602,1734179122,'Active'), 1134(228512602,1953612870,'Active'),(228512602,2271510562,'Active'), 1135(228512602,2271525022,'Active'),(228512602,2941612417,'Active'), 1136(228512602,3058831402,'Active'),(228512602,3723638842,'Active'), 1137(228512602,3723836602,'Active'),(228512602,3723836842,'Active'), 1138(228512602,3723836962,'Active'),(228512602,3723988102,'Active'), 1139(228512602,3723989182,'Active'),(228512602,5920283002,'Active'), 1140(228512602,5920314232,'Active'),(228512602,585717615,'Active'), 1141(228512602,1953611430,'Active'),(228512602,2026844250,'Active'), 1142(228512602,3058831462,'Active'),(228512602,3723836902,'Active'), 1143(228512602,3723989002,'Active'),(228512602,3752960902,'Active'), 1144(228808822,17304242,'Active'),(228808822,30931012,'Active'), 1145(228808822,37254452,'Active'),(228808822,42726891,'Active'), 1146(228808822,76261151,'Active'),(228808822,88240139,'Active'), 1147(228808822,94730895,'Active'),(228808822,125469622,'Active'), 1148(228808822,179737402,'Active'),(228808822,271288782,'Active'), 1149(228808822,304690943,'Active'),(228808822,304691183,'Active'), 1150(228808822,496123368,'Active'),(228808822,555689643,'Active'), 1151(228808822,568994960,'Active'),(228808822,631705925,'Active'), 1152(228808822,631745165,'Active'),(228808822,631749605,'Active'), 1153(228808822,1057787002,'Active'),(228808822,1057788022,'Active'), 1154(228808822,1335646822,'Active'),(228808822,1335646882,'Active'), 1155(228808822,1335646942,'Active'),(228808822,1612792238,'Active'), 1156(228808822,5510586183,'Active'),(228808822,47171711,'Active'), 1157(228808822,125469602,'Active'),(228808822,631712555,'Active'), 1158(228808822,710348755,'Active'),(228808822,753718113,'Active'), 1159(230941762,16069490,'Active'),(230941762,16705991,'Active'), 1160(230941762,27714032,'Active'),(230941762,28676710,'Active'); 1161INSERT INTO t5 VALUES 1162(230941762,370319272,'Active'),(230941762,1409814802,'Active'), 1163(230941762,1409814982,'Active'),(230941762,2069703256,'Active'), 1164(230941762,142889951,'Active'),(230941762,172526592,'Active'), 1165(230941762,293109282,'Active'),(230941762,1409814922,'Active'), 1166(230941762,1409814862,'Active'),(230941762,2680867980,'Active'), 1167(230942122,25451690,'Active'),(230942122,31549341,'Active'), 1168(230942122,38900150,'Active'),(230942122,464554745,'Active'), 1169(230942122,906919252,'Active'),(230942122,1409816782,'Active'), 1170(230942122,1409816842,'Active'),(230942122,1409816902,'Active'), 1171(230942122,2145075862,'Active'),(231112162,1413675742,'Active'), 1172(231112162,1413675922,'Active'),(231112162,1413675562,'Active'), 1173(231112162,1413675802,'Active'),(233937022,12641121,'Active'), 1174(233937022,12653871,'Active'),(233937022,12693551,'Active'), 1175(233937022,12910461,'Active'),(233937022,12910481,'Active'), 1176(233937022,12910511,'Active'),(233937022,14913941,'Active'), 1177(233937022,30879781,'Active'),(233937022,45631730,'Active'), 1178(233937022,54079090,'Active'),(233937022,65320501,'Active'), 1179(233937022,94431735,'Active'),(233937022,96876131,'Active'), 1180(233937022,105436492,'Active'),(233937022,105437952,'Active'), 1181(233937022,128981555,'Active'),(233937022,145211004,'Active'), 1182(233937022,146382622,'Active'),(233937022,148832422,'Active'), 1183(233937022,175678702,'Active'),(233937022,260507673,'Active'), 1184(233937022,298998998,'Active'),(233937022,335995773,'Active'), 1185(233937022,347447636,'Active'),(233937022,459295955,'Active'), 1186(233937022,459376625,'Active'),(233937022,495877773,'Active'), 1187(233937022,497008702,'Active'),(233937022,561944105,'Active'), 1188(233937022,586535965,'Active'),(233937022,631549775,'Active'), 1189(233937022,647138479,'Active'),(233937022,655870453,'Active'), 1190(233937022,694832725,'Active'),(233937022,835712045,'Active'), 1191(233937022,864475057,'Active'),(233937022,864484777,'Active'), 1192(233937022,1010757503,'Active'),(233937022,1010847736,'Active'), 1193(233937022,1091554836,'Active'),(233937022,1287437116,'Active'), 1194(233937022,1337693056,'Active'),(233937022,1569279742,'Active'), 1195(233937022,1569280102,'Active'),(233937022,1569280222,'Active'), 1196(233937022,1569280582,'Active'),(233937022,1569280882,'Active'), 1197(233937022,1569281062,'Active'),(233937022,1569281962,'Active'), 1198(233937022,1569284362,'Active'),(233937022,1743317015,'Active'), 1199(233937022,2698799002,'Active'),(233937022,2698800742,'Active'), 1200(233937022,2823580588,'Active'),(233937022,2842066134,'Active'), 1201(233937022,2904542181,'Active'),(233937022,3058483627,'Active'); 1202INSERT INTO t5 VALUES 1203(233937022,4507287318,'Active'),(233937022,5283489892,'Active'), 1204(233937022,11890554322,'Active'),(233937022,11890756102,'Active'), 1205(233937022,12641851,'Active'),(233937022,14913951,'Active'), 1206(233937022,21835210,'Active'),(233937022,26481052,'Active'), 1207(233937022,35617681,'Active'),(233937022,123639716,'Active'), 1208(233937022,155454324,'Active'),(233937022,299001668,'Active'), 1209(233937022,897886118,'Active'),(233937022,1005147016,'Active'), 1210(233937022,1082217873,'Active'),(233937022,1286925326,'Active'), 1211(233937022,1407236408,'Active'),(233937022,4371581485,'Active'), 1212(233937022,5283491332,'Active'),(233937022,7300486013,'Active'), 1213(233937022,11890754392,'Active'); 1214 1215--enable_warnings 1216--enable_result_log 1217--enable_query_log 1218 1219set join_buffer_size=2048; 1220 1221EXPLAIN 1222SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1223 FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1224 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1225 t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1226 1227--sorted_result 1228SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy 1229 FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 1230 WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND 1231 t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; 1232 1233set join_buffer_size=default; 1234 1235DROP TABLE t1,t2,t3,t4,t5; 1236 1237--echo # 1238--echo # Bug #46328: Use of aggregate function without GROUP BY clause 1239--echo # returns many rows (vs. one ) 1240--echo # 1241 1242CREATE TABLE t1 ( 1243 int_key int(11) NOT NULL, 1244 KEY int_key (int_key) 1245); 1246 1247INSERT INTO t1 VALUES 1248(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9); 1249 1250CREATE TABLE t2 ( 1251 int_key int(11) NOT NULL, 1252 KEY int_key (int_key) 1253); 1254 1255INSERT INTO t2 VALUES (2),(3); 1256 1257--echo 1258 1259--echo # The query shall return 1 record with a max value 9 and one of the 1260--echo # int_key values inserted above (undefined which one). A changed 1261--echo # execution plan may change the value in the second column 1262SELECT MAX(t1.int_key), t1.int_key 1263FROM t1 STRAIGHT_JOIN t2 1264ORDER BY t1.int_key; 1265 1266--echo 1267 1268explain 1269SELECT MAX(t1.int_key), t1.int_key 1270FROM t1 STRAIGHT_JOIN t2 1271ORDER BY t1.int_key; 1272 1273--echo 1274 1275DROP TABLE t1,t2; 1276 1277--echo # 1278--echo # Bug #45019: join buffer contains two blob columns one of which is 1279--echo # used in the key employed to access the joined table 1280--echo # 1281 1282CREATE TABLE t1 (c1 int, c2 int, key (c2)); 1283INSERT INTO t1 VALUES (1,1); 1284INSERT INTO t1 VALUES (2,2); 1285 1286CREATE TABLE t2 (c1 text, c2 text); 1287INSERT INTO t2 VALUES('tt', 'uu'); 1288INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); 1289 1290--disable_result_log 1291ANALYZE TABLE t1,t2; 1292--enable_result_log 1293 1294SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 1295 WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); 1296 1297DROP TABLE t1,t2; 1298 1299--echo # 1300--echo # Regression test for 1301--echo # Bug#46733 - NULL value not returned for aggregate on empty result 1302--echo # set w/ semijoin on 1303CREATE TABLE t1 ( 1304 i int(11) NOT NULL, 1305 v varchar(1) DEFAULT NULL, 1306 PRIMARY KEY (i) 1307); 1308 1309INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d'); 1310 1311CREATE TABLE t2 ( 1312 i int(11) NOT NULL, 1313 v varchar(1) DEFAULT NULL, 1314 PRIMARY KEY (i) 1315); 1316 1317INSERT INTO t2 VALUES (1,'x'),(2,'y'); 1318 1319--echo 1320 1321SELECT MAX(t1.i) 1322FROM t1 JOIN t2 ON t2.v 1323ORDER BY t2.v; 1324 1325--echo 1326 1327EXPLAIN 1328SELECT MAX(t1.i) 1329FROM t1 JOIN t2 ON t2.v 1330ORDER BY t2.v; 1331 1332--echo 1333 1334DROP TABLE t1,t2; 1335 1336--echo # 1337--echo # Bug#51092: Linked join buffer gives wrong result 1338--echo # for 3-way cross join 1339--echo # 1340 1341CREATE TABLE t1 (a INT, b INT); 1342INSERT INTO t1 VALUES (1,1),(2,2); 1343 1344CREATE TABLE t2 (a INT, b INT); 1345INSERT INTO t2 VALUES (1,1),(2,2); 1346 1347CREATE TABLE t3 (a INT, b INT); 1348INSERT INTO t3 VALUES (1,1),(2,2); 1349 1350EXPLAIN SELECT t1.* FROM t1,t2,t3; 1351SELECT t1.* FROM t1,t2,t3; 1352 1353DROP TABLE t1,t2,t3; 1354 1355--echo # 1356--echo # BUG#52394 Segfault in JOIN_CACHE::get_offset () at sql_select.h:445 1357--echo # 1358 1359CREATE TABLE C(a int); 1360INSERT INTO C VALUES(1),(2),(3),(4),(5); 1361 1362CREATE TABLE D (a int(11), b varchar(1)); 1363INSERT INTO D VALUES (6,'r'),(27,'o'); 1364 1365CREATE TABLE E (a int(11) primary key, b varchar(1)); 1366INSERT INTO E VALUES 1367(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'c'); 1368 1369SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b; 1370DROP TABLE C,D,E; 1371 1372--echo # 1373--echo # BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883 1374--echo # 1375 1376CREATE TABLE t1 (a int); 1377INSERT INTO t1 VALUES (2); 1378CREATE TABLE t2 (a varchar(10)); 1379INSERT INTO t2 VALUES ('f'),('x'); 1380CREATE TABLE t3 (pk int(11) PRIMARY KEY); 1381INSERT INTO t3 VALUES (2); 1382CREATE TABLE t4 (a varchar(10)); 1383 1384EXPLAIN SELECT 1 1385FROM t2 LEFT JOIN 1386 ((t1 JOIN t3 ON t1.a = t3.pk) 1387 LEFT JOIN t4 ON 1 ) 1388 ON 1 ; 1389 1390SELECT 1 1391FROM t2 LEFT JOIN 1392 ((t1 JOIN t3 ON t1.a = t3.pk) 1393 LEFT JOIN t4 ON 1 ) 1394 ON 1 ; 1395 1396DROP TABLE t1,t2,t3,t4; 1397 1398--echo # 1399--echo # Bug#51084: Batched key access crashes for SELECT with 1400--echo # derived table and LEFT JOIN 1401--echo # 1402 1403CREATE TABLE t1 ( 1404 carrier int, 1405 id int PRIMARY KEY 1406); 1407INSERT INTO t1 VALUES (1,11),(1,12),(2,13); 1408 1409CREATE TABLE t2 ( 1410 scan_date int, 1411 package_id int 1412); 1413INSERT INTO t2 VALUES (2008,21),(2008,22); 1414 1415CREATE TABLE t3 ( 1416 carrier int PRIMARY KEY, 1417 id int 1418); 1419INSERT INTO t3 VALUES (1,31); 1420 1421CREATE TABLE t4 ( 1422 carrier_id int, 1423 INDEX carrier_id(carrier_id) 1424); 1425INSERT INTO t4 VALUES (31),(32); 1426 1427--echo 1428SELECT COUNT(*) 1429 FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 1430 ON t3.carrier = t1.carrier; 1431 1432--echo 1433EXPLAIN 1434SELECT COUNT(*) 1435 FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) 1436 ON t3.carrier = t1.carrier; 1437--echo 1438DROP TABLE t1,t2,t3,t4; 1439 1440--echo # 1441--echo # Bug#45267: Incomplete check caused wrong result. 1442--echo # 1443CREATE TABLE t1 ( 1444 `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 1445); 1446CREATE TABLE t3 ( 1447 `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY 1448); 1449INSERT INTO t3 VALUES 1450(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15), 1451(16),(17),(18),(19),(20); 1452CREATE TABLE t2 ( 1453 `pk` int(11) NOT NULL AUTO_INCREMENT, 1454 `int_nokey` int(11) NOT NULL, 1455 `time_key` time NOT NULL, 1456 PRIMARY KEY (`pk`), 1457 KEY `time_key` (`time_key`) 1458); 1459INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46'); 1460 1461SELECT DISTINCT t1.`pk` 1462FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey` ON t2.`time_key` 1463GROUP BY 1; 1464 1465DROP TABLE IF EXISTS t1, t2, t3; 1466 1467--echo # 1468--echo # BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8 1469--echo # 1470 1471CREATE TABLE t1 (b int); 1472INSERT INTO t1 VALUES (NULL),(3); 1473 1474CREATE TABLE t2 (a int, b int, KEY (b)); 1475INSERT INTO t2 VALUES (100,NULL),(150,200); 1476 1477let $query= SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b = t1.b; 1478--eval EXPLAIN $query 1479--sorted_result 1480--eval $query 1481 1482# test crash when no key is worth collecting by BKA for t2's ref 1483delete from t1; 1484INSERT INTO t1 VALUES (NULL),(NULL); 1485 1486--eval EXPLAIN $query 1487--sorted_result 1488--eval $query 1489 1490DROP TABLE t1,t2; 1491 1492# test varchar keys 1493CREATE TABLE t1 (b varchar(100)); 1494INSERT INTO t1 VALUES (NULL),("some varchar"); 1495 1496CREATE TABLE t2 (a int, b varchar(100), KEY (b)); 1497INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); 1498 1499explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 1500--sorted_result 1501SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; 1502 1503DROP TABLE t1,t2; 1504 1505--echo # 1506--echo # BUG#54359 "Extra rows with join_cache_level=7,8 and two joins 1507--echo # --and multi-column index" 1508--echo # 1509 1510CREATE TABLE t1 ( 1511 `pk` int(11) NOT NULL, 1512 `col_int_key` int(11) DEFAULT NULL, 1513 `col_varchar_key` varchar(1) DEFAULT NULL, 1514 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1515 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)) 1516; 1517 1518INSERT INTO t1 VALUES (4,9,'k','k'); 1519INSERT INTO t1 VALUES (12,5,'k','k'); 1520 1521let $query_i= SELECT table2 .`col_int_key` FROM t1 table2, 1522t1 table3 force index (`col_varchar_key`) 1523where table3 .`pk` and table3 .`col_int_key` >= table2 .`pk` 1524 and table3 .`col_varchar_key` = table2 .`col_varchar_nokey`; 1525 1526eval explain $query_i; 1527eval $query_i; 1528 1529drop table t1; 1530 1531--echo # 1532--echo # BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT 1533--echo # and join_cache_level=5-8" 1534--echo # 1535 1536CREATE TABLE t1 ( 1537 `col_int_key` int, 1538 `col_datetime` datetime, 1539 KEY `col_int_key` (`col_int_key`) 1540); 1541 1542INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41'); 1543INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49'); 1544INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57'); 1545 1546CREATE TABLE t2 ( 1547 `col_int` int, 1548 `col_int_key` int, 1549 KEY `col_int_key` (`col_int_key`) 1550); 1551 1552INSERT INTO t2 VALUES (14,1); 1553INSERT INTO t2 VALUES (98,1); 1554 1555# The WHERE clause is true for all rows of t2 1556# but is needed to trigger the desired plan. 1557# Query uses BKA. 1558let $query=SELECT t1.col_int_key, t1.col_datetime 1559FROM t1,t2 1560WHERE t2.col_int_key = 1 AND t2.col_int >= 3 1561GROUP BY t1.col_int_key 1562ORDER BY t1.col_int_key, t1.col_datetime 1563LIMIT 2; 1564 1565eval explain $query; 1566eval $query; 1567 1568# by disabling one index and forcing another, we hit 1569# block-nested-loop join and see the same bug 1570let $query=SELECT t1.col_int_key, t1.col_datetime 1571FROM t1 force index (col_int_key), t2 ignore index (col_int_key) 1572WHERE t2.col_int_key = 1 AND t2.col_int >= 3 1573GROUP BY t1.col_int_key 1574ORDER BY t1.col_int_key, t1.col_datetime 1575LIMIT 2; 1576 1577eval explain $query; 1578eval $query; 1579 1580drop table t1,t2; 1581 1582--echo 1583--echo # Bug#11766522 "59651: ASSERTION `TABLE_REF->HAS_RECORD' FAILED 1584--echo # WITH JOIN_CACHE_LEVEL=3" 1585--echo 1586 1587CREATE TABLE t1 ( 1588 b varchar(20) 1589) ; 1590INSERT INTO t1 VALUES ('1'),('1'); 1591 1592CREATE TABLE t4 ( 1593 col253 text 1594) ; 1595INSERT INTO t4 VALUES (''),('pf'); 1596 1597CREATE TABLE t6 ( 1598 col282 timestamp 1599) ; 1600INSERT INTO t6 VALUES ('2010-11-07 01:04:45'),('2010-12-13 01:36:32'); 1601 1602CREATE TABLE t7 ( 1603 col319 timestamp NOT NULL, 1604 UNIQUE KEY idx263 (col319) 1605) ; 1606# zero rows would do, if there was no const-table optimization 1607insert into t7 values("2000-01-01"),("2000-01-02"); 1608 1609CREATE TABLE t3 ( 1610 col582 char(230) CHARACTER SET utf8 DEFAULT NULL 1611) ; 1612# one single row would do, if there was no const-table optimization 1613INSERT INTO t3 VALUES ('cymej'),('spb'); 1614 1615CREATE TABLE t5 ( 1616 col712 time 1617) ; 1618# zero rows would do, if there was no const-table optimization 1619insert into t5 values(0),(0); 1620 1621CREATE TABLE t8 ( 1622 col804 char(169), 1623 col805 varchar(51) 1624) ; 1625INSERT INTO t8 VALUES ('tmqcb','pwk'); 1626 1627CREATE TABLE t2 ( 1628 col841 varchar(10) 1629) ; 1630# one single row would do, if there was no const-table optimization 1631INSERT INTO t2 VALUES (''),(''); 1632 1633# Small buffer, to trigger "full buffer" in both caches of t8 and t6. 1634# Setting to 1 will actually set to the smallest allowed value, 1635# with a "rounding" warning message. 1636set join_buffer_size=1; 1637select @@join_buffer_size; 1638 1639--disable_warnings 1640select count(*) from 1641(t1 join t2 join t3) 1642left join t4 on 1 1643left join t5 on 1 like t4.col253 1644left join t6 on t5.col712 is null 1645left join t7 on t1.b <=>t7.col319 1646left join t8 on t3.col582 <= 1; 1647--enable_warnings 1648 1649drop table t1,t2,t3,t4,t5,t6,t7,t8; 1650 1651--echo # 1652--echo # Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 1653--echo # RETURNED WHEN JCL>=7 1654--echo # 1655 1656CREATE TABLE t1 (t1a int, t1b int); 1657INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0); 1658 1659CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b)); 1660INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL); 1661 1662let $query1= SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b; 1663let $query2= SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b; 1664 1665--echo 1666--echo # t2b is NULL-able 1667--echo 1668--eval EXPLAIN $query1 1669--eval $query1 1670--echo 1671--eval EXPLAIN $query2 1672--eval $query2 1673--echo 1674 1675DROP TABLE t2; 1676 1677CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b)); 1678INSERT INTO t2 VALUES (100,0),(150,200),(999, 0); 1679 1680--echo 1681--echo # t2b is NOT NULL 1682--echo 1683--eval EXPLAIN $query1 1684--eval $query1 1685--echo 1686--eval EXPLAIN $query2 1687--eval $query2 1688--echo 1689 1690DROP TABLE t1,t2; 1691 1692--echo # 1693--echo # BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5 1694--echo # 1695 1696CREATE TABLE t1 ( 1697 c1 INTEGER NOT NULL, 1698 c2_key INTEGER NOT NULL, 1699 KEY col_int_key (c2_key) 1700) ENGINE=InnoDB; 1701 1702INSERT INTO t1 VALUES (24,204); 1703 1704CREATE TABLE t2 ( 1705 pk INTEGER NOT NULL, 1706 PRIMARY KEY (pk) 1707) ENGINE=InnoDB; 1708 1709INSERT INTO t2 VALUES (10); 1710 1711CREATE TABLE t3 ( 1712 c1 INTEGER, 1713 KEY k1 (c1) 1714) ENGINE=InnoDB; 1715 1716INSERT INTO t3 VALUES (NULL), (NULL); 1717 1718# Bug was specific of IN->EXISTS: 1719set @old_opt_switch=@@optimizer_switch; 1720--disable_query_log 1721if (`select locate('materialization', @@optimizer_switch) > 0`) 1722{ 1723 set optimizer_switch='materialization=off'; 1724} 1725--enable_query_log 1726 1727--echo 1728 1729let query_in= 1730SELECT t3.c1 FROM t3 1731WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 1732XOR TRUE; 1733 1734#BKA is OK for this query 1735let query_in_toplevel= 1736SELECT t3.c1 FROM t3 1737WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 1738 1739let query_notin= 1740SELECT t3.c1 FROM t3 1741WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1); 1742 1743let query_any= 1744SELECT t3.c1 FROM t3 1745WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 1746XOR TRUE; 1747 1748let query_some= 1749SELECT t3.c1 FROM t3 1750WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1) 1751XOR TRUE; 1752 1753eval explain $query_some; 1754eval explain $query_any; 1755eval explain $query_in; 1756eval explain $query_notin; 1757eval explain $query_in_toplevel; 1758eval $query_some; 1759eval $query_any; 1760eval $query_in; 1761eval $query_notin; 1762eval $query_in_toplevel; 1763 1764 1765--echo 1766set @@optimizer_switch=@old_opt_switch; 1767DROP TABLE t1, t2, t3; 1768 1769set @@join_buffer_size=default; 1770 1771--echo 1772--echo # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH 1773--echo # JCL>=5 AND MRR ENABLED" 1774--echo 1775 1776CREATE TABLE t1 ( col_int_key int(11) NOT NULL, 1777 col_varchar_key varchar(1) NOT NULL, 1778 KEY col_int_key (col_int_key), 1779 KEY col_varchar_key (col_varchar_key,col_int_key) 1780) ENGINE=innodb; 1781 1782INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d'); 1783 1784CREATE TABLE t2 ( 1785 col_datetime_key datetime NOT NULL, 1786 col_varchar_key varchar(1) NOT NULL, 1787 KEY col_varchar_key (col_varchar_key) 1788) ENGINE=innodb; 1789 1790INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b'); 1791 1792-- disable_query_log 1793-- disable_result_log 1794ANALYZE TABLE t1; 1795ANALYZE TABLE t2; 1796-- enable_query_log 1797-- enable_result_log 1798 1799# need to force the index, or it picks BNL for t2 (lower cost), 1800# whereas we want to test BKA 1801let $query= 1802SELECT MIN(t2.col_datetime_key) AS field1, 1803 t1.col_int_key AS field2 1804FROM t1 1805 LEFT JOIN t2 force index (col_varchar_key) 1806 ON t1.col_varchar_key = t2.col_varchar_key 1807GROUP BY field2 1808ORDER BY field1; 1809 1810eval explain $query; 1811eval $query; 1812 1813DROP TABLE t1,t2; 1814 1815--echo 1816--echo # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5" 1817--echo 1818 1819CREATE TABLE t1 ( 1820col_int_key int(11) NOT NULL, 1821col_datetime_key datetime NOT NULL, 1822col_varchar_nokey varchar(1) NOT NULL, 1823KEY col_int_key (col_int_key), 1824KEY col_datetime_key (col_datetime_key) 1825); 1826INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v'); 1827INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s'); 1828INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l'); 1829INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y'); 1830INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c'); 1831INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i'); 1832INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h'); 1833INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q'); 1834INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a'); 1835INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v'); 1836INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u'); 1837INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s'); 1838INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y'); 1839INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z'); 1840INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h'); 1841INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p'); 1842INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e'); 1843INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i'); 1844INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y'); 1845INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w'); 1846 1847CREATE TABLE t2 ( 1848pk int(11) NOT NULL, 1849col_varchar_key varchar(1) NOT NULL, 1850PRIMARY KEY (pk) 1851); 1852INSERT INTO t2 VALUES 1853(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'), 1854(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'), 1855(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e'); 1856 1857let $query=SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4 1858FROM t1 1859RIGHT JOIN t2 ON t2.pk = t1.col_int_key 1860GROUP BY field1 , field4 1861ORDER BY t1.col_datetime_key ; 1862 1863eval explain $query; 1864# even though there is ORDER BY, it does not cover all columns, so 1865# there is still randomness, so we have to sort client-side: 1866--sorted_result 1867eval $query; 1868 1869DROP TABLE t1,t2; 1870 1871--echo 1872--echo # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5" 1873--echo 1874 1875CREATE TABLE t1 (col_varchar_key varchar(1)); 1876CREATE TABLE t2 ( 1877 pk int(11) NOT NULL, 1878 col_int_nokey int(11) NOT NULL, 1879 col_int_key int(11) NOT NULL, 1880 PRIMARY KEY (pk), 1881 KEY col_int_key (col_int_key) 1882); 1883INSERT INTO t2 VALUES (5,3,9); 1884INSERT INTO t2 VALUES (6,246,24); 1885INSERT INTO t2 VALUES (7,2,6); 1886INSERT INTO t2 VALUES (8,9,1); 1887INSERT INTO t2 VALUES (9,3,6); 1888INSERT INTO t2 VALUES (10,8,2); 1889INSERT INTO t2 VALUES (11,1,4); 1890INSERT INTO t2 VALUES (12,8,8); 1891INSERT INTO t2 VALUES (13,8,4); 1892INSERT INTO t2 VALUES (14,5,4); 1893INSERT INTO t2 VALUES (15,7,7); 1894INSERT INTO t2 VALUES (16,5,4); 1895INSERT INTO t2 VALUES (17,1,1); 1896INSERT INTO t2 VALUES (18,6,9); 1897INSERT INTO t2 VALUES (19,2,4); 1898INSERT INTO t2 VALUES (20,9,8); 1899 1900let $query=SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 1901FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk = 1902alias2.col_int_nokey 1903left join t1 1904ON alias3.col_int_nokey 1905GROUP BY field1, field4 1906LIMIT 15; 1907 1908eval explain $query; 1909--sorted_result 1910eval $query; 1911 1912DROP TABLE t1,t2; 1913 1914--echo 1915--echo # BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT 1916--echo # JCL=6 ONLY [NULL VERSUS NULL+#INTS] 1917--echo 1918 1919CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k)); 1920CREATE TABLE t2 LIKE t1; 1921CREATE TABLE t3 LIKE t1; 1922CREATE TABLE t4 LIKE t1; 1923 1924INSERT INTO t1 VALUES (6,NULL,6),(0,1,11); 1925INSERT INTO t2 VALUES (1,NULL,NULL),(4,7,NULL); 1926INSERT INTO t3 VALUES (2,3,0),(3,4,4); 1927INSERT INTO t4 VALUES (1,9,-1),(4,7,NULL); 1928 1929let $query=SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i 1930 as t4_i FROM t1 1931 LEFT JOIN t2 ON t1.k = t2.pk 1932 LEFT JOIN t3 ON t3.i 1933 LEFT JOIN t4 ON t4.pk = t2.pk; 1934 1935eval EXPLAIN $query; 1936eval $query; 1937 1938DROP TABLE t1, t2, t3, t4; 1939 1940--echo 1941--echo # BUG#12827509 - BNL/BKA: SELECT LEFT/RIGHT JOIN QUERY GIVES 1942--echo # DIFFERENT OUTPUT ON BNL=OFF+BKA=ON 1943--echo # (Duplicate of BUG#12722133) 1944--echo 1945CREATE TABLE t1 ( 1946 col_int INTEGER 1947); 1948INSERT INTO t1 VALUES (3), (7), (2), (8), (6); 1949 1950CREATE TABLE t2 ( 1951 pk INTEGER, 1952 col_int INTEGER, 1953 PRIMARY KEY (pk) 1954); 1955INSERT INTO t2 VALUES (1,5), (2,8), (6,3), (8,7), (9,9); 1956 1957CREATE TABLE t3 ( 1958 pk INTEGER, 1959 col_int INTEGER, 1960 PRIMARY KEY (pk) 1961); 1962INSERT INTO t3 VALUES (3,2), (4,3), (8,2); 1963 1964CREATE TABLE t4 ( 1965 pk INTEGER, 1966 col_int INTEGER, 1967 PRIMARY KEY (pk) 1968); 1969INSERT INTO t4 VALUES (2,3), (6,1), (8,2); 1970 1971let query= 1972SELECT t4.col_int 1973 FROM t1 1974 LEFT JOIN t2 ON t1.col_int = t2.col_int 1975 LEFT JOIN t3 ON t2.pk = t3.pk 1976 LEFT JOIN t4 ON t4.pk = t2.pk 1977 WHERE t1.col_int OR t3.col_int; 1978 1979eval EXPLAIN $query; 1980eval $query; 1981 1982DROP TABLE t1, t2, t3, t4; 1983 1984--echo # 1985--echo # Bug#12997905: VALGRIND: SYSCALL PARAM PWRITE64(BUF) 1986--echo # POINTS TO UNINITIALISED BYTE(S) 1987--echo # 1988 1989CREATE TABLE t1 ( 1990 col1 varchar(10), 1991 col2 varchar(1024) 1992) ENGINE=innodb; 1993 1994INSERT INTO t1 VALUES ('a','a'); 1995 1996CREATE TABLE t2 (i varchar(10)) ENGINE=innodb; 1997INSERT INTO t2 VALUES ('a'); 1998 1999SELECT t1.col1 2000FROM t1 JOIN t2 ON t1.col1 = t2.i 2001GROUP BY t1.col2; 2002 2003DROP TABLE t1,t2; 2004 2005--echo # End of Bug#12997905 2006 2007--echo # 2008--echo # Bug 13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE + IS 2009--echo # NULL WHEN SEMIJOIN + BNL IS ON 2010--echo # 2011 2012CREATE TABLE t1 ( 2013 col_int_nokey int 2014); 2015INSERT INTO t1 VALUES(-1),(-1); 2016 2017CREATE TABLE t2 ( 2018 col_int_nokey int, 2019 col_datetime_nokey datetime NOT NULL, 2020 col_varchar_key varchar(1), 2021 KEY col_varchar_key (col_varchar_key) 2022); 2023 2024INSERT INTO t2 VALUES (9, '2002-08-25 20:35:06', 'e'), 2025 (9, '2002-08-25 20:35:06', 'e'); 2026 2027set @optimizer_switch_saved=@@session.optimizer_switch; 2028set @@session.optimizer_switch='semijoin=off'; 2029 2030let $query=SELECT PARENT1.col_varchar_key 2031FROM t2 AS PARENT1 LEFT JOIN t1 USING (col_int_nokey) 2032WHERE PARENT1.col_varchar_key IN 2033 ( SELECT col_varchar_key FROM t2 AS CHILD1 2034 WHERE PARENT1.col_datetime_nokey IS NULL 2035 AND t1.col_int_nokey IS NULL ) 2036; 2037eval EXPLAIN $query; 2038eval $query; 2039 2040set @@session.optimizer_switch=@optimizer_switch_saved; 2041 2042DROP TABLE t1,t2; 2043