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