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