1-- source include/have_innodb.inc 2 3# Note: the tests uses only non-semijoin subqueries so semi-join switch 4# settings are not relevant. 5set @subselect_innodb_tmp=@@optimizer_switch; 6set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; 7--disable_warnings 8drop table if exists t1,t2,t3; 9--enable_warnings 10 11# 12# key field overflow test 13# 14CREATE TABLE t1 15( 16FOLDERID VARCHAR(32)BINARY NOT NULL 17, FOLDERNAME VARCHAR(255)BINARY NOT NULL 18, CREATOR VARCHAR(255)BINARY 19, CREATED TIMESTAMP NOT NULL 20, DESCRIPTION VARCHAR(255)BINARY 21, FOLDERTYPE INTEGER NOT NULL 22, MODIFIED TIMESTAMP 23, MODIFIER VARCHAR(255)BINARY 24, FOLDERSIZE INTEGER NOT NULL 25, PARENTID VARCHAR(32)BINARY 26, REPID VARCHAR(32)BINARY 27, ORIGINATOR INTEGER 28 29, PRIMARY KEY ( FOLDERID ) 30) ENGINE=InnoDB; 31CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID); 32CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID); 33INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); 34INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "2003-06-09 10:52:02", "The default content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "03eea05112b845949f3fd03278b5fe43", "1"); 35INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL); 36SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1'); 37drop table t1; 38 39# 40# UNION unlocking test 41# 42create table t1 (a int) engine=innodb; 43create table t2 (a int) engine=innodb; 44create table t3 (a int) engine=innodb; 45insert into t1 values (1),(2),(3),(4); 46insert into t2 values (10),(20),(30),(40); 47insert into t3 values (1),(2),(10),(50); 48select a from t3 where t3.a in (select a from t1 where a <= 3 union select * from t2 where a <= 30); 49drop table t1,t2,t3; 50 51 52CREATE TABLE t1 ( 53 processor_id INTEGER NOT NULL, 54 PRIMARY KEY (processor_id) 55) ENGINE=InnoDB; 56CREATE TABLE t3 ( 57 yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, 58 login_processor INTEGER UNSIGNED , 59 PRIMARY KEY (yod_id) 60) ENGINE=InnoDB; 61CREATE TABLE t2 ( 62 processor_id INTEGER NOT NULL, 63 yod_id BIGINT UNSIGNED NOT NULL, 64 PRIMARY KEY (processor_id, yod_id), 65 INDEX (processor_id), 66 INDEX (yod_id), 67 FOREIGN KEY (processor_id) REFERENCES t1(processor_id), 68 FOREIGN KEY (yod_id) REFERENCES t3(yod_id) 69) ENGINE=InnoDB; 70INSERT INTO t1 VALUES (1),(2),(3); 71INSERT INTO t3 VALUES (1,1),(2,2),(3,3); 72INSERT INTO t2 VALUES (1,1),(2,2),(3,3); 73SELECT distinct p1.processor_id, (SELECT y.yod_id FROM t1 p2, t2 y WHERE p2.processor_id = p1.processor_id and p2.processor_id = y.processor_id) FROM t1 p1; 74drop table t2,t1,t3; 75 76# 77# innodb locking 78# 79CREATE TABLE t1 ( 80 id int(11) NOT NULL default '0', 81 b int(11) default NULL, 82 c char(3) default NULL, 83 PRIMARY KEY (id), 84 KEY t2i1 (b) 85) ENGINE=innodb DEFAULT CHARSET=latin1; 86INSERT INTO t1 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); 87CREATE TABLE t2 ( 88 id int(11) NOT NULL default '0', 89 b int(11) default NULL, 90 c char(3) default NULL, 91 PRIMARY KEY (id), 92 KEY t2i (b) 93) ENGINE=innodb DEFAULT CHARSET=latin1; 94INSERT INTO t2 VALUES (0,0,'GPL'),(1,0,'GPL'),(2,1,'GPL'),(3,2,'GPL'); 95select (select max(id) from t2 where b=1 group by b) as x,b from t1 where b=1; 96drop table t1,t2; 97 98# 99# reiniting innodb tables 100# 101create table t1 (id int not null, value char(255), primary key(id)) engine=innodb; 102create table t2 (id int not null, value char(255)) engine=innodb; 103insert into t1 values (1,'a'),(2,'b'); 104insert into t2 values (1,'z'),(2,'x'); 105select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; 106select t2.id,t2.value,(select t1.value from t1 where t1.id=t2.id) from t2; 107drop table t1,t2; 108 109# 110# unlocking tables with subqueries in HAVING 111# 112create table t1 (a int, b int) engine=innodb; 113insert into t1 values (1,2), (1,3), (2,3), (2,4), (2,5), (3,4), (4,5), (4,100); 114create table t2 (a int) engine=innodb; 115insert into t2 values (1),(2),(3),(4); 116select a, sum(b) as b from t1 group by a having b > (select max(a) from t2); 117drop table t1, t2; 118 119# 120# bug #5220 test suite 121# 122CREATE TABLE `t1` ( `unit` varchar(50) NOT NULL default '', `ingredient` varchar(50) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; 123 124CREATE TABLE `t2` ( `ingredient` varchar(50) NOT NULL default '', `unit` varchar(50) NOT NULL default '', PRIMARY KEY (ingredient, unit)) ENGINE=InnoDB DEFAULT CHARSET=latin1; 125 126INSERT INTO `t1` VALUES ('xx','yy'); 127INSERT INTO `t2` VALUES ('yy','xx'); 128 129SELECT R.unit, R.ingredient FROM t1 R WHERE R.ingredient IN (SELECT N.ingredient FROM t2 N WHERE N.unit = R.unit); 130 131drop table t1, t2; 132 133# 134# possible early unlock 135# 136CREATE TABLE t1 ( 137 id INT NOT NULL auto_increment, 138 date1 DATE, coworkerid INT, 139 description VARCHAR(255), 140 sum_used DOUBLE, 141 sum_remaining DOUBLE, 142 comments VARCHAR(255), 143 PRIMARY KEY(id) 144) engine=innodb; 145insert into t1 values (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1999-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '1998-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'), (NULL, '2004-01-01', 1,'test', 22, 33, 'comment'); 146SELECT DISTINCT 147 (SELECT sum(sum_used) FROM t1 WHERE sum_used > 0 AND year(date1) <= '2004') as somallontvangsten, 148 (SELECT sum(sum_used) FROM t1 WHERE sum_used < 0 AND year(date1) <= '2004') as somalluitgaven 149 FROM t1; 150select * from t1; 151drop table t1; 152 153# 154# cleaning up of results of subselects (BUG#8125) 155# 156CREATE TABLE `t1` ( `a` char(3) NOT NULL default '', `b` char(3) NOT NULL default '', `c` char(3) NOT NULL default '', PRIMARY KEY (`a`,`b`,`c`)) ENGINE=InnoDB; 157CREATE TABLE t2 LIKE t1; 158INSERT INTO t1 VALUES (1,1,1); 159INSERT INTO t2 VALUES (1,1,1); 160PREPARE my_stmt FROM "SELECT t1.b, count(*) FROM t1 group by t1.b having 161count(*) > ALL (SELECT COUNT(*) FROM t2 WHERE t2.a=1 GROUP By t2.b)"; 162EXECUTE my_stmt; 163EXECUTE my_stmt; 164deallocate prepare my_stmt; 165drop table t1,t2; 166 167# End of 4.1 tests 168 169CREATE TABLE t1 ( 170 school_name varchar(45) NOT NULL, 171 country varchar(45) NOT NULL, 172 funds_requested float NOT NULL, 173 schooltype varchar(45) NOT NULL 174) ENGINE=InnoDB DEFAULT CHARSET=utf8; 175 176insert into t1 values ("the school", "USA", 1200, "Human"); 177 178select count(country) as countrycount, sum(funds_requested) as smcnt, 179 country, (select sum(funds_requested) from t1) as total_funds 180from t1 181group by country; 182 183select count(country) as countrycount, sum(funds_requested) as smcnt, 184 country, (select sum(funds_requested) from t1) as total_funds 185from t1 186group by country; 187 188drop table t1; 189 190# 191# BUG#14342: wrong placement of subquery internals in complex queries 192# 193CREATE TABLE `t1` ( 194 `t3_id` int NOT NULL, 195 `t1_id` int NOT NULL, 196 PRIMARY KEY (`t1_id`) 197); 198CREATE TABLE `t2` ( 199 `t2_id` int NOT NULL, 200 `t1_id` int NOT NULL, 201 `b` int NOT NULL, 202 PRIMARY KEY (`t2_id`), 203 UNIQUE KEY `idx_t2_t1_b` (`t1_id`,`b`) 204) ENGINE=InnoDB; 205CREATE TABLE `t3` ( 206 `t3_id` int NOT NULL 207); 208INSERT INTO `t3` VALUES (3); 209select 210 (SELECT rs.t2_id 211 FROM t2 rs 212 WHERE rs.t1_id= 213 (SELECT lt.t1_id 214 FROM t1 lt 215 WHERE lt.t3_id=a.t3_id) 216 ORDER BY b DESC LIMIT 1) 217from t3 AS a; 218# repeat above query in SP 219--disable_warnings 220DROP PROCEDURE IF EXISTS p1; 221--enable_warnings 222delimiter //; 223create procedure p1() 224begin 225 declare done int default 3; 226 repeat 227 select 228 (SELECT rs.t2_id 229 FROM t2 rs 230 WHERE rs.t1_id= 231 (SELECT lt.t1_id 232 FROM t1 lt 233 WHERE lt.t3_id=a.t3_id) 234 ORDER BY b DESC LIMIT 1) as x 235 from t3 AS a; 236 set done= done-1; 237 until done <= 0 end repeat; 238end// 239delimiter ;// 240call p1(); 241call p1(); 242call p1(); 243drop procedure p1; 244drop tables t1,t2,t3; 245 246--echo # 247--echo # Bug #58756 248--echo # Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT 249--echo # 250 251CREATE TABLE t1 ( 252 col_time_key time DEFAULT NULL, 253 col_datetime_key datetime DEFAULT NULL, 254 col_varchar_nokey varchar(1) DEFAULT NULL, 255 KEY col_time_key (col_time_key), 256 KEY col_datetime_key (col_datetime_key) 257) ENGINE=InnoDB DEFAULT CHARSET=latin1; 258 259INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h'); 260INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b'); 261INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s'); 262INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e'); 263INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j'); 264INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e'); 265INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f'); 266INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v'); 267INSERT INTO t1 VALUES ('13:02:46',NULL,'x'); 268INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m'); 269INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c'); 270 271CREATE TABLE t2 ( 272 col_time_key time DEFAULT NULL, 273 col_datetime_key datetime DEFAULT NULL, 274 col_varchar_nokey varchar(1) DEFAULT NULL, 275 KEY col_time_key (col_time_key), 276 KEY col_datetime_key (col_datetime_key) 277) ENGINE=InnoDB DEFAULT CHARSET=latin1; 278 279INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w'); 280 281SELECT col_time_key, col_datetime_key 282FROM 283( SELECT * FROM t1 ) AS table1 284HAVING ( 'r' , 'e' ) IN 285 ( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 ) 286ORDER BY col_datetime_key 287LIMIT 10; 288 289DROP TABLE t1; 290DROP TABLE t2; 291 292--echo # End of Bug #58756 293 294--echo # 295--echo # Bug#60085 crash in Item::save_in_field() with time data type 296--echo # 297 298CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; 299INSERT INTO t1 VALUES ('2011-05-13', 0); 300SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); 301DROP TABLE t1; 302 303--echo # 304--echo # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE 305--echo # 306 307CREATE TABLE t1 (a INT) ENGINE=INNODB; 308INSERT INTO t1 VALUES (0); 309CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; 310 311SELECT 1 FROM t1 WHERE NOT EXISTS 312(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); 313 314EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS 315(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); 316 317DROP TABLE t2; 318 319CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; 320INSERT INTO t2 VALUES (1, 1); 321 322SELECT 1 FROM t1 323WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); 324 325DROP TABLE t1, t2; 326 327--echo # 328--echo # Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE 329--echo # INDEX 330--echo # 331CREATE TABLE t1 ( 332id int 333) ENGINE=InnoDB; 334INSERT INTO t1 (id) VALUES (11); 335 336CREATE TABLE t2 ( 337t1_id int, 338position int, 339KEY t1_id (t1_id), 340KEY t1_id_position (t1_id,position) 341) ENGINE=InnoDB; 342 343let $query=SELECT 344(SELECT position FROM t2 345WHERE t2.t1_id = t1.id 346ORDER BY t2.t1_id , t2.position 347LIMIT 10,1 348) AS maxkey 349FROM t1 350LIMIT 1; 351 352eval EXPLAIN $query; 353eval $query; 354 355DROP TABLE t1,t2; 356 357--echo End of 5.1 tests 358 359--echo # 360--echo # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries 361--echo # 362 363CREATE TABLE t3 ( b int) ENGINE=InnoDB; 364CREATE TABLE t2 ( c int) ENGINE=InnoDB; 365CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; 366 367EXPLAIN SELECT * 368FROM t1 369WHERE t1.a = ( 370 SELECT SUM( c ) 371 FROM t2 372 WHERE (SELECT DISTINCT b FROM t3) > 0); 373SELECT * 374FROM t1 375WHERE t1.a = ( 376 SELECT SUM( c ) 377 FROM t2 378 WHERE (SELECT DISTINCT b FROM t3) > 0); 379 380DROP TABLE t1, t2, t3; 381 382 383--echo # 384--echo # lp:858148 Fourth crash in select_describe() with nested subqueries 385--echo # 386 387CREATE TABLE t1 ( f1 int(11)) ENGINE=InnoDB; 388CREATE TABLE t2 ( f1 int(11), f2 int(11), PRIMARY KEY (f1)) ; 389CREATE TABLE t3 ( f3 int(11)) ENGINE=InnoDB; 390 391EXPLAIN 392SELECT MAX( f1 ) FROM t2 393WHERE f2 >= ( 394 SELECT SUM( f1 ) 395 FROM t1 396 WHERE EXISTS ( 397 SELECT f3 398 FROM t3 399 GROUP BY 1 400 ) 401); 402 403SELECT MAX( f1 ) FROM t2 404WHERE f2 >= ( 405 SELECT SUM( f1 ) 406 FROM t1 407 WHERE EXISTS ( 408 SELECT f3 409 FROM t3 410 GROUP BY 1 411 ) 412); 413 414drop table t1, t2, t3; 415 416--echo # 417--echo # LP BUG#1006231 crash in select_describe 418--echo # 419 420create table t1(a1 int) ENGINE=InnoDB; 421insert into t1 values (1); 422explain 423select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); 424select 1 from t1 where 1 like (select 1 from t1 where 1 <=> (select 1 from t1 group by a1)); 425drop table t1; 426 427 428--echo # 429--echo # MDEV-3988 crash in create_tmp_table 430--echo # 431 432drop table if exists `t1`,`t2`; 433create table `t1`(`a` char(1) character set utf8)engine=innodb; 434create table `t2`(`b` char(1) character set utf8)engine=memory; 435select distinct (select 1 from `t2` where `a`) `d2` from `t1`; 436select distinct (select 1 from `t2` where `a`) `d2`, a from `t1`; 437select distinct a, (select 1 from `t2` where `a`) `d2` from `t1`; 438select distinct (1 + (select 1 from `t2` where `a`)) `d2` from `t1`; 439 440drop table t1,t2; 441 442--echo # 443--echo # MDEV-4042: Assertion `table->key_read == 0' fails in close_thread_table on EXPLAIN with GROUP BY and HAVING in EXISTS SQ, 444--echo # MDEV-4536: ...sql/sql_base.cc:1598: bool close_thread_table(THD*, TABLE**): Assertion `table->key_read == 0' failed. 445CREATE TABLE t1 (a INT) ENGINE=InnoDB; 446CREATE TABLE t2 (b INT PRIMARY KEY, c INT) ENGINE=InnoDB; 447CREATE TABLE t3 (d INT) ENGINE=InnoDB; 448 449EXPLAIN 450SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); 451SELECT * FROM t1 WHERE EXISTS ( SELECT b FROM t2, t3 GROUP BY b HAVING b != 3 ); 452 453DROP TABLE t1,t2,t3; 454 455CREATE TABLE t1 ( 456 pk int auto_increment primary key, 457 col_int_key int(11), 458 key col_int_key (col_int_key),col_varchar_key varchar(128), 459 key (col_varchar_key) 460) engine=innodb; 461 462EXPLAIN 463SELECT 1 FROM t1 AS alias1 464WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 465 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN 466 t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) 467 ) 468 GROUP BY SQ2_field1 469 HAVING SQ2_alias1 . col_int_key >= 7 470 ); 471 472SELECT 1 FROM t1 AS alias1 473WHERE EXISTS ( SELECT SQ2_alias1 . col_int_key AS SQ2_field1 474 FROM ( t1 AS SQ2_alias1 RIGHT OUTER JOIN 475 t1 AS SQ2_alias2 ON (SQ2_alias2 . col_int_key = SQ2_alias1 . col_int_key ) 476 ) 477 GROUP BY SQ2_field1 478 HAVING SQ2_alias1 . col_int_key >= 7 479 ); 480drop table t1; 481 482 483set optimizer_switch=@subselect_innodb_tmp; 484 485--echo # 486--echo # MDEV-9635:Server crashes in part_of_refkey or assertion 487--echo # `!created && key_to_save < (int)s->keys' failed in 488--echo # TABLE::use_index(int) or with join_cache_level>2 489--echo # 490 491SET join_cache_level=3; 492 493CREATE TABLE t1 (f1 VARCHAR(1024)) ENGINE=InnoDB; 494CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; 495 496CREATE TABLE t2 (f2 VARCHAR(4)) ENGINE=InnoDB; 497INSERT INTO t2 VALUES ('foo'),('bar'); 498 499SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 ); 500 501set join_cache_level = default; 502drop view v1; 503drop table t1,t2; 504 505--echo # 506--echo # MDEV-10693: cost-based choice between materialization and in-to-exists 507--echo # for a subquery from the expression used in ref access 508--echo # 509 510--source include/have_innodb.inc 511 512CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; 513INSERT INTO t1 VALUES (1),(2); 514 515CREATE TABLE t2 (i2 INT) ENGINE=InnoDB; 516 517CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB; 518INSERT INTO t3 VALUES (3); 519 520SELECT * FROM t1 521 WHERE NULL IN ( SELECT i2 FROM t2 522 WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 ); 523 524DROP TABLE t1,t2,t3; 525 526--echo # 527--echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding 528--echo # 529create table t1(a int) engine=innodb; 530insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 531 532create table t2( 533 id int primary key, 534 key1 int, 535 col1 int, 536 key(key1) 537) engine=innodb; 538 539insert into t2 540 select 541 A.a + B.a*10 + C.a*100 + D.a* 1000, 542 A.a + 10*B.a, 543 123456 544from t1 A, t1 B, t1 C, t1 D; 545 546--echo # Table tsubq: 547--echo # - must use 'ref' (not 'index'), and must not use 'Using filesort' 548--echo # - shows a bad estimate for 'rows' (but I'm not sure if one can do better w/o histograms) 549explain select 550 (SELECT 551 concat(id, '-', key1, '-', col1) 552 FROM t2 553 WHERE t2.key1 = t1.a 554 ORDER BY t2.id ASC LIMIT 1) 555from 556 t1; 557 558--echo # 559--echo # MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686) 560--echo # 561 562alter table t2 add key2 int; 563update t2 set key2=key1; 564alter table t2 add key(key2); 565analyze table t2; 566flush tables; 567--echo # Table tsubq must use 'ref' + Using filesort (not 'index' w/o filesort) 568--replace_column 9 # 569explain select 570 (SELECT 571 concat(id, '-', key1, '-', col1) 572 FROM t2 573 WHERE t2.key1 = t1.a 574 ORDER BY t2.key2 ASC LIMIT 1) 575from 576 t1; 577 578drop table t1,t2; 579 580--echo # 581--echo # mdev-12931: semi-join in ON expression of STRAIGHT_JOIN 582--echo # joining a base table and a mergeable derived table 583--echo # 584 585CREATE TABLE t1 (f1 int) ENGINE=InnoDB; 586INSERT INTO t1 VALUES (3),(2); 587 588CREATE TABLE t2 (f2 int) ENGINE=InnoDB; 589INSERT INTO t2 VALUES (1),(4); 590 591CREATE TABLE t3 (f3 int) ENGINE=InnoDB; 592INSERT INTO t3 VALUES (5),(6); 593 594CREATE TABLE t4 (f4 int) ENGINE=InnoDB; 595INSERT INTO t4 VALUES (1),(8); 596 597SELECT * 598FROM t1 599 INNER JOIN 600 ( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq 601 ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) 602 ON ( f1 >= f2 ); 603 604EXPLAIN EXTENDED 605SELECT * 606FROM t1 607 INNER JOIN 608 ( t2 STRAIGHT_JOIN ( SELECT * FROM t3 ) AS sq 609 ON ( 1 IN ( SELECT f4 FROM t4 ) ) ) 610 ON ( f1 >= f2 ); 611 612DROP TABLE t1,t2,t3,t4; 613 614 615--echo # 616--echo # MDEV-23535: SIGSEGV, SIGABRT and SIGILL in typeinfo for Item_func_set_collation (on optimized builds) 617--echo # 618 619set @save_character_set_connection=@@character_set_connection; 620set character_set_connection='utf8'; 621 622CREATE TABLE t1(a DATETIME, b VARCHAR(50)) ENGINE=INNODB; 623INSERT INTO t1 VALUES ('2019-03-10 02:55:05', '2019-03-10 02:55:05'); 624CREATE TABLE t2(a VARCHAR(50)) ENGINE=INNODB; 625INSERT INTO t2 VALUES ('2019-03-10 02:55:05'); 626SELECT * FROM t1 WHERE (SELECT 1,CONCAT(a) FROM t1) = (SELECT 1,CONCAT(a) FROM t2); 627DROP TABLE t1,t2; 628 629set character_set_connection=@save_character_set_connection; 630 631--echo # 632--echo # MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' 633--echo # failed in Item_equal::fix_fields, server crashes after 2nd execution 634--echo # of PS 635--echo # 636 637create table t1 (a int, b int); 638create table t2 (x int, y int); 639 640insert into t1 values (1,1),(2,2); 641insert into t2 values (1,1),(2,2),(2,3); 642 643--echo # here we can see conditions pushdown (see HAVING): 644prepare stmt from " 645explain extended 646SELECT * FROM t1 647WHERE a = b 648 AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; 649 650execute stmt; 651 652--echo # here re-execution of the pushdown does not crash: 653prepare stmt from " 654SELECT * FROM t1 655WHERE a = b 656 AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; 657 658execute stmt; 659execute stmt; 660execute stmt; 661 662drop table t1,t2; 663 664--echo # End of 10.4 tests 665