1# 2# Test cases for bugs related to the implementation of 3# MWL#89 cost-based choice between the materialization and in-to-exists 4# 5--source include/default_optimizer_switch.inc 6 7--echo # 8--echo # LP BUG#643424 valgrind warning in choose_subquery_plan() 9--echo # 10 11CREATE TABLE t1 ( 12 pk int(11) NOT NULL AUTO_INCREMENT, 13 c1 int(11) DEFAULT NULL, 14 c2 int(11) DEFAULT NULL, 15 PRIMARY KEY (pk), 16 KEY c2 (c2)); 17 18INSERT INTO t1 VALUES (1,NULL,2); 19INSERT INTO t1 VALUES (2,7,9); 20INSERT INTO t1 VALUES (9,NULL,8); 21 22CREATE TABLE t2 ( 23 pk int(11) NOT NULL AUTO_INCREMENT, 24 c1 int(11) DEFAULT NULL, 25 c2 int(11) DEFAULT NULL, 26 PRIMARY KEY (pk), 27 KEY c2 (c2)); 28 29INSERT INTO t2 VALUES (1,1,7); 30 31set @save_optimizer_switch=@@optimizer_switch; 32set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; 33 34SELECT pk FROM t1 WHERE (c2, c1) IN (SELECT c2, c2 FROM t2); 35 36set session optimizer_switch=@save_optimizer_switch; 37 38drop table t1, t2; 39 40 41--echo # 42--echo # LP BUG#652727 Crash in create_ref_for_key() 43--echo # 44 45CREATE TABLE t2 ( 46 pk int(11) NOT NULL AUTO_INCREMENT, 47 c1 int(11) DEFAULT NULL, 48 PRIMARY KEY (pk)); 49 50INSERT INTO t2 VALUES (10,7); 51INSERT INTO t2 VALUES (11,1); 52INSERT INTO t2 VALUES (17,NULL); 53 54CREATE TABLE t1 ( 55 pk int(11) NOT NULL AUTO_INCREMENT, 56 c1 int(11) DEFAULT NULL, 57 PRIMARY KEY (pk)); 58 59INSERT INTO t1 VALUES (15,1); 60INSERT INTO t1 VALUES (19,NULL); 61 62CREATE TABLE t3 (c2 int(11) DEFAULT NULL, KEY c2 (c2)); 63INSERT INTO t3 VALUES (1); 64 65set @save_optimizer_switch=@@optimizer_switch; 66set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; 67 68SELECT c2 69FROM t3 70WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FROM t1 STRAIGHT_JOIN t2 ON t2.pk = t1.pk); 71 72set session optimizer_switch=@save_optimizer_switch; 73drop table t1, t2, t3; 74 75 76--echo # 77--echo # LP BUG#641245 Crash in Item_equal::contains 78--echo # 79 80CREATE TABLE t1 ( 81 pk int(11) NOT NULL AUTO_INCREMENT, 82 c1 int(11) DEFAULT NULL, 83 c2 int(11) DEFAULT NULL, 84 c3 varchar(1) DEFAULT NULL, 85 c4 varchar(1) DEFAULT NULL, 86 PRIMARY KEY (pk), 87 KEY c2 (c2), 88 KEY c3 (c3,c2)); 89 90INSERT INTO t1 VALUES (10,7,8,'v','v'); 91INSERT INTO t1 VALUES (11,1,9,'r','r'); 92INSERT INTO t1 VALUES (12,5,9,'a','a'); 93INSERT INTO t1 VALUES (13,7,18,'v','v'); 94INSERT INTO t1 VALUES (14,1,19,'r','r'); 95INSERT INTO t1 VALUES (15,5,29,'a','a'); 96INSERT INTO t1 VALUES (17,7,38,'v','v'); 97INSERT INTO t1 VALUES (18,1,39,'r','r'); 98INSERT INTO t1 VALUES (19,5,49,'a','a'); 99 100create table t1a like t1; 101insert into t1a select * from t1; 102 103create table t1b like t1; 104insert into t1b select * from t1; 105 106CREATE TABLE t2 ( 107 pk int(11) NOT NULL AUTO_INCREMENT, 108 c1 int(11) DEFAULT NULL, 109 c2 int(11) DEFAULT NULL, 110 c3 varchar(1) DEFAULT NULL, 111 c4 varchar(1) DEFAULT NULL, 112 PRIMARY KEY (pk), 113 KEY c2 (c2), 114 KEY c3 (c3,c2)); 115 116INSERT INTO t2 VALUES (1,NULL,2,'w','w'); 117INSERT INTO t2 VALUES (2,7,9,'m','m'); 118 119set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; 120 121let $query= 122SELECT pk 123FROM t1 124WHERE c1 IN 125 (SELECT t1a.c1 126 FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN 127 t1a ON (t1a.c2 = t1b.pk AND 2) 128 WHERE t1.pk) ; 129eval EXPLAIN EXTENDED $query; 130eval $query; 131 132DROP TABLE t1, t1a, t1b, t2; 133 134--echo # 135--echo # LP BUG#714808 Assertion `outer_lookup_keys <= outer_record_count' 136--echo # failed with materialization 137 138CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ; 139CREATE TABLE t2 ( f2 int(11)) ; 140CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ; 141INSERT INTO t3 VALUES (7,'f'); 142 143set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; 144 145EXPLAIN 146SELECT t1.* 147FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 148WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); 149 150SELECT t1.* 151FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 152WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); 153 154drop table t1,t2,t3; 155 156--echo # 157--echo # LP BUG#714999 Second crash in select_describe() with nested subqueries 158--echo # 159 160CREATE TABLE t1 ( pk int(11)) ; 161INSERT INTO t1 VALUES (29); 162 163CREATE TABLE t2 ( f1 varchar(1)) ; 164INSERT INTO t2 VALUES ('f'),('d'); 165 166CREATE TABLE t3 ( f2 varchar(1)) ; 167 168EXPLAIN SELECT f2 FROM t3 WHERE ( 169 SELECT MAX( pk ) FROM t1 170 WHERE EXISTS ( 171 SELECT max(f1) 172 FROM t2 GROUP BY f1 173 ) 174) IS NULL ; 175 176drop table t1, t2, t3; 177 178--echo # 179--echo # LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed 180--echo # 181 182CREATE TABLE t2 ( f2 int(11)) ; 183 184CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; 185INSERT INTO t1 VALUES (6),(4); 186 187set @tmp_optimizer_switch=@@optimizer_switch; 188set optimizer_switch='derived_merge=off,derived_with_keys=off'; 189 190EXPLAIN 191SELECT * FROM (SELECT * FROM t2) AS a2 192WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); 193 194insert into t2 values (1),(2); 195EXPLAIN 196SELECT * FROM (SELECT * FROM t2) AS a2 197WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); 198 199set optimizer_switch=@tmp_optimizer_switch; 200drop table t1,t2; 201 202--echo # 203--echo # LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed 204--echo # 205 206CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; 207INSERT INTO t1 VALUES (28),(29); 208 209CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; 210INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); 211 212set @tmp_optimizer_switch=@@optimizer_switch; 213set optimizer_switch='derived_merge=off,derived_with_keys=off'; 214 215EXPLAIN 216SELECT alias2.f2 AS field1 217FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 218WHERE ( 219 SELECT t2.f2 220 FROM t2 JOIN t1 ON t1.f1 221 WHERE t1.f1 AND alias2.f10 222) 223ORDER BY field1 ; 224 225SELECT alias2.f2 AS field1 226FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 227WHERE ( 228 SELECT t2.f2 229 FROM t2 JOIN t1 ON t1.f1 230 WHERE t1.f1 AND alias2.f10 231) 232ORDER BY field1 ; 233 234set optimizer_switch=@tmp_optimizer_switch; 235drop table t1,t2; 236 237--echo # 238--echo # LP BUG#718578 Yet another Assertion `!table || 239--echo # (!table->read_set || bitmap_is_set(table->read_set, field_index))' 240 241CREATE TABLE t1 ( f1 int(11), f2 int(11), f3 int(11)) ; 242INSERT IGNORE INTO t1 VALUES (28,5,6),(29,NULL,4); 243 244CREATE TABLE t2 ( f10 varchar(1) ); 245INSERT IGNORE INTO t2 VALUES (NULL); 246 247SELECT f1 AS field1 248FROM ( SELECT * FROM t1 ) AS alias1 249WHERE (SELECT t1.f1 250 FROM t2 JOIN t1 ON t1.f2 251 WHERE alias1.f3 AND t1.f3) AND f2 252ORDER BY field1; 253 254drop table t1,t2; 255 256--echo # 257--echo # LP BUG#601124 Bug in eliminate_item_equal 258--echo # leads to crash in Item_func::Item_func 259 260CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; 261INSERT INTO t1 VALUES (5,'m'),(NULL,'c'); 262 263CREATE TABLE t2 ( f2 int(11), f3 varchar(1)) ; 264INSERT INTO t2 VALUES (6,'f'),(2,'d'); 265 266CREATE TABLE t3 ( f2 int(11), f3 varchar(1)) ; 267INSERT INTO t3 VALUES (6,'f'),(2,'d'); 268 269SELECT * FROM t3 270WHERE ( f2 ) IN (SELECT t1.f1 271 FROM t1 STRAIGHT_JOIN t2 ON t2.f3 = t1.f3 272 WHERE t2.f3 = 'c'); 273drop table t1,t2,t3; 274 275 276--echo # 277--echo # LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> 278--echo # Item_field::find_item_equal -> Item_equal::contains 279--echo # 280 281set @save_optimizer_switch=@@optimizer_switch; 282SET @@optimizer_switch = 'semijoin=off'; 283 284CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; 285INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); 286 287CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; 288insert into t2 values (1,2), (3,4); 289 290EXPLAIN 291SELECT * FROM t2 292WHERE ( f12 ) IN ( 293 SELECT alias2.f3 294 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 295 WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 296); 297SELECT * FROM t2 298WHERE ( f12 ) IN ( 299 SELECT alias2.f3 300 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 301 WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 302); 303 304EXPLAIN 305SELECT * FROM t2 306WHERE ( f12 ) IN ( 307 SELECT alias2.f3 308 FROM t1 AS alias1, t1 AS alias2 309 WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); 310SELECT * FROM t2 311WHERE ( f12 ) IN ( 312 SELECT alias2.f3 313 FROM t1 AS alias1, t1 AS alias2 314 WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); 315 316set @@optimizer_switch=@save_optimizer_switch; 317drop table t1, t2; 318 319 320--echo # 321--echo # MWL#89: test introduced after Sergey Petrunia's review - test that 322--echo # keyparts wihtout index prefix are used with the IN-EXISTS strategy. 323--echo # 324 325create table t1 (c1 int); 326insert into t1 values (1), (2), (3); 327 328create table t2 (kp1 int, kp2 int, c2 int, filler char(100)); 329insert into t2 values (0,0,0,'filler'),(0,1,1,'filler'),(0,2,2,'filler'),(0,3,3,'filler'); 330 331create index key1 on t2 (kp1, kp2); 332create index key2 on t2 (kp1); 333create index key3 on t2 (kp2); 334 335SET @@optimizer_switch='materialization=off,semijoin=off,in_to_exists=on'; 336 337analyze table t2; 338 339explain 340select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; 341select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; 342 343set @@optimizer_switch='default'; 344 345drop table t1, t2; 346 347--echo # 348--echo # LP BUG#800679: Assertion `outer_join->table_count > 0' failed in 349--echo # JOIN::choose_subquery_plan() with materialization=on,semijoin=off 350--echo # 351 352CREATE TABLE t1 ( f1 int); 353insert into t1 values (1),(2); 354CREATE TABLE t2 ( f1 int); 355insert into t2 values (1),(2); 356 357SET @@optimizer_switch='materialization=on,semijoin=off'; 358 359EXPLAIN 360SELECT * FROM t1 361WHERE (f1) IN (SELECT f1 FROM t2) 362LIMIT 0; 363 364SELECT * FROM t1 365WHERE (f1) IN (SELECT f1 FROM t2) 366LIMIT 0; 367 368set @@optimizer_switch='default'; 369drop table t1, t2; 370 371--echo # 372--echo # LP BUG#834492: Crash in fix_semijoin_strategies_for_picked_join_order 373--echo # with nested subqueries and LooseScan=ON 374--echo # 375 376CREATE TABLE t3 (b int) ; 377INSERT INTO t3 VALUES (0),(0); 378 379CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ; 380INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0); 381 382CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ; 383INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0); 384 385SET @@optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF'; 386 387EXPLAIN SELECT * 388FROM t3 389WHERE t3.b > ALL ( 390 SELECT c 391 FROM t4 392 WHERE t4.a >= t3.b 393 AND a = SOME (SELECT b FROM t5)); 394 395SELECT * 396FROM t3 397WHERE t3.b > ALL ( 398 SELECT c 399 FROM t4 400 WHERE t4.a >= t3.b 401 AND a = SOME (SELECT b FROM t5)); 402 403set @@optimizer_switch='default'; 404drop table t3, t4, t5; 405 406--echo # 407--echo # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch 408--echo # 409 410set @optimizer_switch_save= @@optimizer_switch; 411 412create table t1 (c1 char(2) not null, c2 char(2)); 413create table t2 (c3 char(2), c4 char(2)); 414 415insert into t1 values ('a1', 'b1'); 416insert into t1 values ('a2', 'b2'); 417 418insert into t2 values ('x1', 'y1'); 419insert into t2 values ('a2', null); 420 421set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; 422explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); 423select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); 424 425set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; 426explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); 427select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); 428 429drop table t1, t2; 430 431set optimizer_switch= @optimizer_switch_save; 432 433--echo # 434--echo # MDEV-12673: cost-based choice between materialization and in-to-exists 435--echo # 436 437CREATE TABLE t1 ( 438 pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) 439) ENGINE=MyISAM; 440INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); 441 442CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; 443INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); 444 445SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); 446SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); 447EXPLAIN 448SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); 449 450DROP TABLE t1,t2; 451 452CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; 453INSERT INTO t1 VALUES (1); 454 455CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; 456INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); 457 458CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; 459INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); 460 461SELECT * FROM t1 WHERE i1 NOT IN ( 462 SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 463); 464 465EXPLAIN 466SELECT * FROM t1 WHERE i1 NOT IN ( 467 SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 468); 469 470DROP TABLE t1,t2,t3; 471 472--echo # 473--echo # MDEV-7599: in-to-exists chosen after min/max optimization 474--echo # 475 476set @optimizer_switch_save= @@optimizer_switch; 477 478CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; 479INSERT INTO t1 VALUES (1),(2); 480 481CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; 482INSERT INTO t2 VALUES (1,6),(2,4), (8,9); 483 484let $q= 485SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); 486 487eval $q; 488eval EXPLAIN EXTENDED $q; 489set optimizer_switch= 'materialization=off'; 490eval $q; 491eval EXPLAIN EXTENDED $q; 492set optimizer_switch= @optimizer_switch_save; 493 494DROP TABLE t1,t2; 495 496CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM; 497INSERT INTO t1 VALUES ('foo'),('bar'); 498 499CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM; 500INSERT INTO t2 VALUES ('baz'),('qux'); 501 502CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM; 503INSERT INTO t3 VALUES ('abc'),('def'); 504 505SELECT * FROM t1 506 WHERE f1 = ALL( SELECT MAX(t2a.f2) 507 FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 508 ON (f3 = t2b.f2) ); 509 510DROP TABLE t1,t2,t3; 511 512--echo # 513--echo # MDEV-12963: min/max optimization optimizing away all tables employed 514--echo # for uncorrelated IN subquery used in a disjunct of WHERE 515--echo # 516 517create table t1 (a int, index idx(a)) engine=myisam; 518insert into t1 values (4),(7),(1),(3),(9); 519 520select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; 521explain 522select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; 523 524drop table t1; 525 526--echo # 527--echo # MDEV-13135: subquery with ON expression subject to 528--echo # semi-join optimizations 529--echo # 530 531CREATE TABLE t1 (a INT); 532CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a AS v_a FROM t1; 533INSERT INTO t1 VALUES (1),(3); 534 535CREATE TABLE t2 (b INT, KEY(b)); 536INSERT INTO t2 VALUES (3),(4); 537 538SELECT * FROM t1 WHERE a NOT IN ( 539 SELECT b FROM t2 INNER JOIN v1 ON (b IN ( SELECT a FROM t1 )) 540 WHERE v_a = b 541); 542 543DROP VIEW v1; 544DROP TABLE t1,t2; 545 546