1--disable_warnings 2drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 3drop view if exists v1; 4--enable_warnings 5 6SET @save_optimizer_switch=@@optimizer_switch; 7SET optimizer_switch='outer_join_with_cache=off'; 8SET optimizer_switch='semijoin_with_cache=off'; 9 10set optimizer_switch='subquery_cache=on'; 11 12create table t1 (a int, b int); 13insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); 14create table t2 (c int, d int); 15insert into t2 values (2,3),(3,4),(5,6),(4,1); 16 17--echo * 18--echo * Test subquery as top item in different clauses 19--echo * 20--echo #single value subquery test (SELECT list) 21flush status; 22select a, (select d from t2 where b=c) from t1; 23 24show status like "subquery_cache%"; 25show status like '%Handler_read%'; 26 27--source include/analyze-format.inc 28analyze format=json 29select a, (select d from t2 where b=c) from t1; 30--source include/analyze-format.inc 31analyze format=json 32select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1; 33explain format=json 34select a, (select d from t2 where b=c) from t1; 35explain format=json 36select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1; 37set optimizer_switch='subquery_cache=off'; 38flush status; 39 40select a, (select d from t2 where b=c) from t1; 41 42show status like "subquery_cache%"; 43show status like '%Handler_read%'; 44set optimizer_switch='subquery_cache=on'; 45 46 47--echo #single value subquery test (where) 48flush status; 49select a from t1 where (select d from t2 where b=c); 50 51show status like "subquery_cache%"; 52show status like '%Handler_read%'; 53 54set optimizer_switch='subquery_cache=off'; 55flush status; 56 57select a from t1 where (select d from t2 where b=c); 58 59show status like "subquery_cache%"; 60show status like '%Handler_read%'; 61set optimizer_switch='subquery_cache=on'; 62 63--echo #single value subquery test (having) 64flush status; 65select a from t1 where a > 0 having (select d from t2 where b=c); 66 67show status like "subquery_cache%"; 68show status like '%Handler_read%'; 69 70set optimizer_switch='subquery_cache=off'; 71flush status; 72 73select a from t1 where a > 0 having (select d from t2 where b=c); 74 75show status like "subquery_cache%"; 76show status like '%Handler_read%'; 77set optimizer_switch='subquery_cache=on'; 78 79--echo #single value subquery test (OUTER JOIN ON) 80flush status; 81select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); 82 83show status like "subquery_cache%"; 84show status like '%Handler_read%'; 85 86set optimizer_switch='subquery_cache=off'; 87flush status; 88 89select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); 90 91show status like "subquery_cache%"; 92show status like '%Handler_read%'; 93set optimizer_switch='subquery_cache=on'; 94 95--echo #single value subquery test (GROUP BY) 96flush status; 97select max(a) from t1 GROUP BY (select d from t2 where b=c); 98 99show status like "subquery_cache%"; 100show status like '%Handler_read%'; 101set optimizer_switch='subquery_cache=off'; 102 103flush status; 104select max(a) from t1 GROUP BY (select d from t2 where b=c); 105 106show status like "subquery_cache%"; 107show status like '%Handler_read%'; 108set optimizer_switch='subquery_cache=on'; 109 110--echo #single value subquery test (distinct GROUP BY) 111flush status; 112select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); 113 114show status like "subquery_cache%"; 115show status like '%Handler_read%'; 116set optimizer_switch='subquery_cache=off'; 117 118flush status; 119select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); 120 121show status like "subquery_cache%"; 122show status like '%Handler_read%'; 123set optimizer_switch='subquery_cache=on'; 124 125--echo #single value subquery test (ORDER BY) 126flush status; 127select a from t1 ORDER BY (select d from t2 where b=c); 128 129show status like "subquery_cache%"; 130show status like '%Handler_read%'; 131set optimizer_switch='subquery_cache=off'; 132 133flush status; 134select a from t1 ORDER BY (select d from t2 where b=c); 135 136show status like "subquery_cache%"; 137show status like '%Handler_read%'; 138set optimizer_switch='subquery_cache=on'; 139 140--echo #single value subquery test (distinct ORDER BY) 141flush status; 142select distinct a from t1 ORDER BY (select d from t2 where b=c); 143 144show status like "subquery_cache%"; 145show status like '%Handler_read%'; 146set optimizer_switch='subquery_cache=off'; 147 148flush status; 149select distinct a from t1 ORDER BY (select d from t2 where b=c); 150 151show status like "subquery_cache%"; 152show status like '%Handler_read%'; 153set optimizer_switch='subquery_cache=on'; 154 155--echo #single value subquery test (LEFT JOIN ON) 156flush status; 157select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); 158 159show status like "subquery_cache%"; 160show status like '%Handler_read%'; 161 162set optimizer_switch='subquery_cache=off'; 163flush status; 164 165select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); 166 167show status like "subquery_cache%"; 168show status like '%Handler_read%'; 169set optimizer_switch='subquery_cache=on'; 170 171--echo #single value subquery test (PS) 172prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; 173execute stmt1; 174show status like "subquery_cache%"; 175execute stmt1; 176show status like "subquery_cache%"; 177deallocate prepare stmt1; 178 179--echo #single value subquery test (SP) 180CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; 181 182call p1; 183call p1; 184 185drop procedure p1; 186 187--echo #IN subquery test 188flush status; 189 190show status like "subquery_cache%"; 191select a, b , b in (select d from t2) as SUBS from t1; 192show status like "subquery_cache%"; 193 194insert into t1 values (7,8),(9,NULL); 195select a, b , b in (select d from t2) as SUBS from t1; 196show status like "subquery_cache%"; 197 198insert into t2 values (8,NULL); 199select a, b , b in (select d from t2) as SUBS from t1; 200show status like "subquery_cache%"; 201 202--echo # multicolumn NOT IN with NULLs 203flush status; 204set optimizer_switch='subquery_cache=off'; 205select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; 206show status like "subquery_cache%"; 207 208set optimizer_switch='subquery_cache=on'; 209select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; 210show status like "subquery_cache%"; 211 212--echo # multicolumn NOT IN with NULLs (other order) 213flush status; 214set optimizer_switch='subquery_cache=off'; 215select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; 216show status like "subquery_cache%"; 217 218set optimizer_switch='subquery_cache=on'; 219select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; 220show status like "subquery_cache%"; 221 222--echo # multicolumn IN with NULLs 223flush status; 224set optimizer_switch='subquery_cache=off'; 225select a, b, (b, a) in (select d, c from t2) as SUBS from t1; 226show status like "subquery_cache%"; 227 228set optimizer_switch='subquery_cache=on'; 229select a, b, (b, a) in (select d, c from t2) as SUBS from t1; 230show status like "subquery_cache%"; 231 232--echo # multicolumn IN with NULLs (other order) 233flush status; 234set optimizer_switch='subquery_cache=off'; 235select a, b, (a, b) in (select d, c from t2) as SUBS from t1; 236show status like "subquery_cache%"; 237 238set optimizer_switch='subquery_cache=on'; 239select a, b, (a, b) in (select d, c from t2) as SUBS from t1; 240show status like "subquery_cache%"; 241 242--echo #IN subquery test (PS) 243delete from t1 where a > 6; 244delete from t2 where c > 6; 245 246prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; 247execute stmt1; 248show status like "subquery_cache%"; 249execute stmt1; 250show status like "subquery_cache%"; 251 252insert into t1 values (7,8),(9,NULL); 253execute stmt1; 254show status like "subquery_cache%"; 255execute stmt1; 256show status like "subquery_cache%"; 257 258insert into t2 values (8,NULL); 259execute stmt1; 260show status like "subquery_cache%"; 261execute stmt1; 262show status like "subquery_cache%"; 263 264deallocate prepare stmt1; 265 266 267--echo #IN subquery test (SP) 268delete from t1 where a > 6; 269delete from t2 where c > 6; 270 271CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; 272 273call p1(); 274show status like "subquery_cache%"; 275call p1(); 276show status like "subquery_cache%"; 277 278insert into t1 values (7,8),(9,NULL); 279call p1(); 280show status like "subquery_cache%"; 281call p1(); 282show status like "subquery_cache%"; 283 284insert into t2 values (8,NULL); 285call p1(); 286show status like "subquery_cache%"; 287call p1(); 288show status like "subquery_cache%"; 289 290drop procedure p1; 291 292 293--echo # test of simple exists 294select a, b , exists (select * from t2 where b=d) as SUBS from t1; 295 296--echo # test of prepared statement exists 297show status like "subquery_cache%"; 298prepare stmt1 from 'select a, b , exists (select * from t2 where b=d) as SUBS from t1'; 299execute stmt1; 300show status like "subquery_cache%"; 301execute stmt1; 302show status like "subquery_cache%"; 303deallocate prepare stmt1; 304 305--echo # test of stored procedure exists 306CREATE PROCEDURE p1() select a, b , exists (select * from t2 where b=d) as SUBS from t1; 307call p1; 308call p1; 309drop procedure p1; 310 311--echo #several subqueries 312set optimizer_switch='subquery_cache=off'; 313flush status; 314select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; 315show status like "subquery_cache%"; 316show status like '%Handler_read%'; 317 318set optimizer_switch='subquery_cache=on'; 319flush status; 320select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; 321show status like "subquery_cache%"; 322show status like '%Handler_read%'; 323 324--echo #several subqueries (several levels) 325set optimizer_switch='subquery_cache=off'; 326flush status; 327 328set optimizer_switch='subquery_cache=off'; 329flush status; 330select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; 331show status like "subquery_cache%"; 332show status like '%Handler_read%'; 333 334 335set optimizer_switch='subquery_cache=on'; 336flush status; 337select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; 338show status like "subquery_cache%"; 339show status like '%Handler_read%'; 340 341 342--echo #clean up 343drop table t1,t2; 344 345--echo test different types 346--echo #int 347CREATE TABLE t1 ( a int, b int); 348INSERT INTO t1 VALUES(1,1),(2,2),(3,3); 349SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); 350DROP TABLE t1; 351 352--echo #char 353CREATE TABLE t1 ( a char(1), b char (1)); 354INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); 355SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); 356DROP TABLE t1; 357 358--echo #decimal 359CREATE TABLE t1 ( a decimal(3,1), b decimal(3,1)); 360INSERT INTO t1 VALUES(1,1),(2,2),(3,3); 361SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); 362DROP TABLE t1; 363 364--echo #date 365CREATE TABLE t1 ( a date, b date); 366INSERT INTO t1 VALUES('1000-01-01','1000-01-01'),('2000-02-01','2000-02-01'),('3000-03-03','3000-03-03'); 367SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-01'); 368DROP TABLE t1; 369 370--echo #datetime 371CREATE TABLE t1 ( a datetime, b datetime); 372INSERT INTO t1 VALUES('1000-01-01 01:01:01','1000-01-01 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('3000-03-03 03:03:03','3000-03-03 03:03:03'); 373SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); 374DROP TABLE t1; 375 376--echo #time 377CREATE TABLE t1 ( a time, b time); 378INSERT INTO t1 VALUES('01:01:01','01:01:01'),('02:02:02','02:02:02'),('03:03:03','03:03:03'); 379SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '02:02:02'); 380DROP TABLE t1; 381 382--echo #timestamp 383CREATE TABLE t1 ( a timestamp, b timestamp); 384INSERT INTO t1 VALUES('2000-02-02 01:01:01','2000-02-02 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('2000-02-02 03:03:03','2000-02-02 03:03:03'); 385SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); 386DROP TABLE t1; 387 388--echo #bit 389CREATE TABLE t1 ( a bit(20), b bit(20)); 390INSERT INTO t1 VALUES(1,1),(2,2),(3,3); 391SELECT a+0 FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); 392DROP TABLE t1; 393 394--echo #enum 395CREATE TABLE t1 ( a enum('1','2','3'), b enum('1','2','3')); 396INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); 397SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); 398DROP TABLE t1; 399 400--echo #set 401CREATE TABLE t1 ( a set('1','2','3'), b set('1','2','3')); 402INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); 403SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); 404DROP TABLE t1; 405 406--echo #blob 407CREATE TABLE t1 ( a blob, b blob); 408INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); 409SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); 410DROP TABLE t1; 411 412--echo #geometry 413CREATE TABLE t1 ( a geometry, b geometry); 414INSERT INTO t1 VALUES(POINT(1,1),POINT(1,1)),(POINT(2,2),POINT(2,2)),(POINT(3,3),POINT(3,3)); 415SELECT astext(a) FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = POINT(2,2)); 416DROP TABLE t1; 417 418 419--echo #uncacheable queries test (random and side effect) 420flush status; 421CREATE TABLE t1 (a int); 422INSERT INTO t1 VALUES (2), (4), (1), (3); 423select a, a in (select a from t1) from t1 as ext; 424show status like "subquery_cache%"; 425select a, a in (select a from t1 where -1 < rand()) from t1 as ext; 426show status like "subquery_cache%"; 427select a, a in (select a from t1 where -1 < benchmark(a,100)) from t1 as ext; 428show status like "subquery_cache%"; 429drop table t1; 430 431--echo #test of sql_big_tables switch and outer table reference in subquery with grouping 432set big_tables=1; 433CREATE TABLE t1 (a INT PRIMARY KEY, b INT); 434INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); 435SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; 436drop table t1; 437set big_tables=0; 438 439--echo #test of function reference to outer query 440set local group_concat_max_len=400; 441create table t2 (a int, b int); 442insert into t2 values (1,1), (2,2); 443select b x, (select group_concat(x) from t2) from t2; 444drop table t2; 445set local group_concat_max_len=default; 446 447--echo #aggregate functions 448CREATE TABLE t1 (a int, b INT); 449CREATE TABLE t2 (c int, d INT); 450 451insert into t1 values (2,1), (3,1), (2,4), (3,4), (10,2), (20,2), (2,5), 452(3,5), (100,3), (200,3), (10,6), (20,6), (20,7), (100,8), (200,8); 453insert into t2 values (1,1),(3,3),(20,20); 454 455--echo aggregate function as parameter of subquery 456set optimizer_switch='subquery_cache=off'; 457flush status; 458select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; 459show status like "subquery_cache%"; 460show status like '%Handler_read%'; 461set optimizer_switch='subquery_cache=on'; 462flush status; 463select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; 464show status like "subquery_cache%"; 465show status like '%Handler_read%'; 466 467--echo argument of aggregate function as parameter of subquery (illegal use) 468set optimizer_switch='subquery_cache=off'; 469flush status; 470select max(a), (select a from t2 where a=c) from t1 group by b; 471show status like "subquery_cache%"; 472show status like '%Handler_read%'; 473 474set optimizer_switch='subquery_cache=on'; 475flush status; 476select max(a), (select a from t2 where a=c) from t1 group by b; 477show status like "subquery_cache%"; 478show status like '%Handler_read%'; 479 480drop table t1,t2; 481 482--echo #test of flattening subquery optimisations and cache 483create table t0 (a int); 484insert into t0 values (9),(8),(7),(6),(5),(4),(3),(2),(1),(0); 485 486create table t1(a int, b int); 487insert into t1 values 488(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2); 489 490create table t2 (pk int, a int, primary key(pk)); 491insert into t2 select a,a from t0; 492 493set optimizer_switch='default,semijoin=on,materialization=on,subquery_cache=on'; 494flush status; 495select * from t1 where a in (select pk from t2); 496show status like "subquery_cache%"; 497show status like '%Handler_read%'; 498 499alter table t2 drop primary key; 500set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=off'; 501 502explain select * from t1 where a in (select pk from t2); 503flush status; 504select * from t1 where a in (select pk from t2); 505show status like "subquery_cache%"; 506show status like '%Handler_read%'; 507 508set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on'; 509 510explain select * from t1 where a in (select pk from t2); 511flush status; 512select * from t1 where a in (select pk from t2); 513show status like "subquery_cache%"; 514show status like '%Handler_read%'; 515 516#TODO: switch off cache if materialization used 517set optimizer_switch='default,semijoin=off,materialization=on,subquery_cache=on'; 518 519explain select * from t1 where a in (select pk from t2); 520flush status; 521select * from t1 where a in (select pk from t2); 522show status like "subquery_cache%"; 523show status like '%Handler_read%'; 524 525drop table t0,t1,t2; 526 527set optimizer_switch='default'; 528 529# 530--echo #launchpad BUG#608834 531# 532CREATE TABLE `t2` ( 533 `pk` int(11) NOT NULL AUTO_INCREMENT, 534 `col_int_nokey` int(11) DEFAULT NULL, 535 `col_int_key` int(11) DEFAULT NULL, 536 `col_time_key` time DEFAULT NULL, 537 `col_varchar_key` varchar(1) DEFAULT NULL, 538 `col_varchar_nokey` varchar(1) DEFAULT NULL, 539 PRIMARY KEY (`pk`), 540 KEY `col_int_key` (`col_int_key`), 541 KEY `col_time_key` (`col_time_key`), 542 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 543) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; 544INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v'); 545INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r'); 546INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a'); 547INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m'); 548INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y'); 549INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j'); 550INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d'); 551INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z'); 552INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e'); 553INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h'); 554INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b'); 555INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s'); 556INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e'); 557INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j'); 558INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e'); 559INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f'); 560INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v'); 561INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x'); 562INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m'); 563INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c'); 564CREATE TABLE `t1` ( 565 `pk` int(11) NOT NULL AUTO_INCREMENT, 566 `col_int_nokey` int(11) DEFAULT NULL, 567 `col_int_key` int(11) DEFAULT NULL, 568 `col_time_key` time DEFAULT NULL, 569 `col_varchar_key` varchar(1) DEFAULT NULL, 570 `col_varchar_nokey` varchar(1) DEFAULT NULL, 571 PRIMARY KEY (`pk`), 572 KEY `col_int_key` (`col_int_key`), 573 KEY `col_time_key` (`col_time_key`), 574 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 575) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 576INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w'); 577INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m'); 578INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m'); 579INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k'); 580INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r'); 581INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t'); 582INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j'); 583INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u'); 584INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h'); 585INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o'); 586INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL); 587INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k'); 588INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e'); 589INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n'); 590INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t'); 591INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c'); 592INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m'); 593INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y'); 594INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f'); 595INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d'); 596 597set @@optimizer_switch='subquery_cache=off'; 598 599/* cache is off */ SELECT ( 600SELECT 4 601FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , ( 602SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1 603FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) ) 604WHERE SUBQUERY2_t2 . `col_varchar_nokey` <= table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_int_nokey` < table1 . `pk` ) AS field3 , table1 . `col_time_key` AS field4 , table1 . `col_int_key` AS field5 , CONCAT ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) AS field6 605FROM ( t1 AS table1 INNER JOIN ( ( t1 AS table2 LEFT JOIN t2 AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_nokey` ) ) 606WHERE ( table2 . `col_varchar_nokey` NOT IN ( 607SELECT 'd' UNION 608SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key` 609GROUP BY field1, field3, field4, field5, field6 610ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6 611; 612 613set @@optimizer_switch='subquery_cache=on'; 614 615/* cache is on */ SELECT ( 616SELECT 4 617FROM DUAL ) AS field1 , SUM( DISTINCT table1 . `pk` ) AS field2 , ( 618SELECT MAX( SUBQUERY2_t1 . `col_int_nokey` ) AS SUBQUERY2_field1 619FROM ( t1 AS SUBQUERY2_t1 INNER JOIN t1 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `pk` ) ) 620WHERE SUBQUERY2_t2 . `col_varchar_nokey` <= table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_int_nokey` < table1 . `pk` ) AS field3 , table1 . `col_time_key` AS field4 , table1 . `col_int_key` AS field5 , CONCAT ( table2 . `col_varchar_nokey` , table1 . `col_varchar_key` ) AS field6 621FROM ( t1 AS table1 INNER JOIN ( ( t1 AS table2 LEFT JOIN t2 AS table3 ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_nokey` ) ) 622WHERE ( table2 . `col_varchar_nokey` NOT IN ( 623SELECT 'd' UNION 624SELECT 'u' ) ) OR table3 . `col_varchar_nokey` <= table1 . `col_varchar_key` 625GROUP BY field1, field3, field4, field5, field6 626ORDER BY table1 . `col_int_key` , field1, field2, field3, field4, field5, field6 627; 628 629drop table t1,t2; 630set @@optimizer_switch= default; 631 632# 633--echo #launchpad BUG#609045 634# 635CREATE TABLE `t1` ( 636 `pk` int(11) NOT NULL AUTO_INCREMENT, 637 `col_int_nokey` int(11) DEFAULT NULL, 638 `col_int_key` int(11) DEFAULT NULL, 639 `col_date_key` date DEFAULT NULL, 640 `col_date_nokey` date DEFAULT NULL, 641 `col_time_key` time DEFAULT NULL, 642 `col_time_nokey` time DEFAULT NULL, 643 `col_datetime_key` datetime DEFAULT NULL, 644 `col_datetime_nokey` datetime DEFAULT NULL, 645 `col_varchar_key` varchar(1) DEFAULT NULL, 646 `col_varchar_nokey` varchar(1) DEFAULT NULL, 647 PRIMARY KEY (`pk`), 648 KEY `col_int_key` (`col_int_key`), 649 KEY `col_date_key` (`col_date_key`), 650 KEY `col_time_key` (`col_time_key`), 651 KEY `col_datetime_key` (`col_datetime_key`), 652 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 653) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 654 655INSERT INTO `t1` VALUES (1,NULL,2,NULL,NULL,'11:28:45','11:28:45','2004-10-11 18:13:16','2004-10-11 18:13:16','w','w'); 656INSERT INTO `t1` VALUES (2,7,9,'2001-09-19','2001-09-19','20:25:14','20:25:14',NULL,NULL,'m','m'); 657INSERT INTO `t1` VALUES (3,9,3,'2004-09-12','2004-09-12','13:47:24','13:47:24','1900-01-01 00:00:00','1900-01-01 00:00:00','m','m'); 658INSERT INTO `t1` VALUES (4,7,9,NULL,NULL,'19:24:11','19:24:11','2009-07-25 00:00:00','2009-07-25 00:00:00','k','k'); 659INSERT INTO `t1` VALUES (5,4,NULL,'2002-07-19','2002-07-19','15:59:13','15:59:13',NULL,NULL,'r','r'); 660INSERT INTO `t1` VALUES (6,2,9,'2002-12-16','2002-12-16','00:00:00','00:00:00','2008-07-27 00:00:00','2008-07-27 00:00:00','t','t'); 661INSERT INTO `t1` VALUES (7,6,3,'2006-02-08','2006-02-08','15:15:04','15:15:04','2002-11-13 16:37:31','2002-11-13 16:37:31','j','j'); 662INSERT INTO `t1` VALUES (8,8,8,'2006-08-28','2006-08-28','11:32:06','11:32:06','1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'); 663INSERT INTO `t1` VALUES (9,NULL,8,'2001-04-14','2001-04-14','18:32:33','18:32:33','2003-12-10 00:00:00','2003-12-10 00:00:00','h','h'); 664INSERT INTO `t1` VALUES (10,5,53,'2000-01-05','2000-01-05','15:19:25','15:19:25','2001-12-21 22:38:22','2001-12-21 22:38:22','o','o'); 665INSERT INTO `t1` VALUES (11,NULL,0,'2003-12-06','2003-12-06','19:03:19','19:03:19','2008-12-13 23:16:44','2008-12-13 23:16:44',NULL,NULL); 666INSERT INTO `t1` VALUES (12,6,5,'1900-01-01','1900-01-01','00:39:46','00:39:46','2005-08-15 12:39:41','2005-08-15 12:39:41','k','k'); 667INSERT INTO `t1` VALUES (13,188,166,'2002-11-27','2002-11-27',NULL,NULL,NULL,NULL,'e','e'); 668INSERT INTO `t1` VALUES (14,2,3,NULL,NULL,'00:00:00','00:00:00','2006-09-11 12:06:14','2006-09-11 12:06:14','n','n'); 669INSERT INTO `t1` VALUES (15,1,0,'2003-05-27','2003-05-27','13:12:11','13:12:11','2007-12-15 12:39:34','2007-12-15 12:39:34','t','t'); 670INSERT INTO `t1` VALUES (16,1,1,'2005-05-03','2005-05-03','04:56:48','04:56:48','2005-08-09 00:00:00','2005-08-09 00:00:00','c','c'); 671INSERT INTO `t1` VALUES (17,0,9,'2001-04-18','2001-04-18','19:56:05','19:56:05','2001-09-02 22:50:02','2001-09-02 22:50:02','m','m'); 672INSERT INTO `t1` VALUES (18,9,5,'2005-12-27','2005-12-27','19:35:19','19:35:19','2005-12-16 22:58:11','2005-12-16 22:58:11','y','y'); 673INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'); 674INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'); 675 676CREATE TABLE `t2` ( 677 `pk` int(11) NOT NULL AUTO_INCREMENT, 678 `col_int_nokey` int(11) DEFAULT NULL, 679 `col_int_key` int(11) DEFAULT NULL, 680 `col_date_key` date DEFAULT NULL, 681 `col_date_nokey` date DEFAULT NULL, 682 `col_time_key` time DEFAULT NULL, 683 `col_time_nokey` time DEFAULT NULL, 684 `col_datetime_key` datetime DEFAULT NULL, 685 `col_datetime_nokey` datetime DEFAULT NULL, 686 `col_varchar_key` varchar(1) DEFAULT NULL, 687 `col_varchar_nokey` varchar(1) DEFAULT NULL, 688 PRIMARY KEY (`pk`), 689 KEY `col_int_key` (`col_int_key`), 690 KEY `col_date_key` (`col_date_key`), 691 KEY `col_time_key` (`col_time_key`), 692 KEY `col_datetime_key` (`col_datetime_key`), 693 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 694); 695 696INSERT INTO `t2` VALUES (10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'); 697INSERT INTO `t2` VALUES (11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'); 698INSERT INTO `t2` VALUES (12,5,9,'2002-09-12','2002-09-12','00:00:00','00:00:00','2006-12-03 09:37:26','2006-12-03 09:37:26','a','a'); 699INSERT INTO `t2` VALUES (13,3,186,'2005-02-15','2005-02-15','19:53:05','19:53:05','2008-05-26 12:27:10','2008-05-26 12:27:10','m','m'); 700INSERT INTO `t2` VALUES (14,6,NULL,NULL,NULL,'19:18:56','19:18:56','2004-12-14 16:37:30','2004-12-14 16:37:30','y','y'); 701INSERT INTO `t2` VALUES (15,92,2,'2008-11-04','2008-11-04','10:55:12','10:55:12','2003-02-11 21:19:41','2003-02-11 21:19:41','j','j'); 702INSERT INTO `t2` VALUES (16,7,3,'2004-09-04','2004-09-04','00:25:00','00:25:00','2009-10-18 02:27:49','2009-10-18 02:27:49','d','d'); 703INSERT INTO `t2` VALUES (17,NULL,0,'2006-06-05','2006-06-05','12:35:47','12:35:47','2000-09-26 07:45:57','2000-09-26 07:45:57','z','z'); 704INSERT INTO `t2` VALUES (18,3,133,'1900-01-01','1900-01-01','19:53:03','19:53:03',NULL,NULL,'e','e'); 705INSERT INTO `t2` VALUES (19,5,1,'1900-01-01','1900-01-01','17:53:30','17:53:30','2005-11-10 12:40:29','2005-11-10 12:40:29','h','h'); 706INSERT INTO `t2` VALUES (20,1,8,'1900-01-01','1900-01-01','11:35:49','11:35:49','2009-04-25 00:00:00','2009-04-25 00:00:00','b','b'); 707INSERT INTO `t2` VALUES (21,2,5,'2005-01-13','2005-01-13',NULL,NULL,'2002-11-27 00:00:00','2002-11-27 00:00:00','s','s'); 708INSERT INTO `t2` VALUES (22,NULL,5,'2006-05-21','2006-05-21','06:01:40','06:01:40','2004-01-26 20:32:32','2004-01-26 20:32:32','e','e'); 709INSERT INTO `t2` VALUES (23,1,8,'2003-09-08','2003-09-08','05:45:11','05:45:11','2007-10-26 11:41:40','2007-10-26 11:41:40','j','j'); 710INSERT INTO `t2` VALUES (24,0,6,'2006-12-23','2006-12-23','00:00:00','00:00:00','2005-10-07 00:00:00','2005-10-07 00:00:00','e','e'); 711INSERT INTO `t2` VALUES (25,210,51,'2006-10-15','2006-10-15','00:00:00','00:00:00','2000-07-15 05:00:34','2000-07-15 05:00:34','f','f'); 712INSERT INTO `t2` VALUES (26,8,4,'2005-04-06','2005-04-06','06:11:01','06:11:01','2000-04-03 16:33:32','2000-04-03 16:33:32','v','v'); 713INSERT INTO `t2` VALUES (27,7,7,'2008-04-07','2008-04-07','13:02:46','13:02:46',NULL,NULL,'x','x'); 714INSERT INTO `t2` VALUES (28,5,6,'2006-10-10','2006-10-10','21:44:25','21:44:25','2001-04-25 01:26:12','2001-04-25 01:26:12','m','m'); 715INSERT INTO `t2` VALUES (29,NULL,4,'1900-01-01','1900-01-01','22:43:58','22:43:58','2000-12-27 00:00:00','2000-12-27 00:00:00','c','c'); 716 717CREATE TABLE `t3` ( 718 `pk` int(11) NOT NULL AUTO_INCREMENT, 719 `col_int_nokey` int(11) DEFAULT NULL, 720 `col_int_key` int(11) DEFAULT NULL, 721 `col_date_key` date DEFAULT NULL, 722 `col_date_nokey` date DEFAULT NULL, 723 `col_time_key` time DEFAULT NULL, 724 `col_time_nokey` time DEFAULT NULL, 725 `col_datetime_key` datetime DEFAULT NULL, 726 `col_datetime_nokey` datetime DEFAULT NULL, 727 `col_varchar_key` varchar(1) DEFAULT NULL, 728 `col_varchar_nokey` varchar(1) DEFAULT NULL, 729 PRIMARY KEY (`pk`), 730 KEY `col_int_key` (`col_int_key`), 731 KEY `col_date_key` (`col_date_key`), 732 KEY `col_time_key` (`col_time_key`), 733 KEY `col_datetime_key` (`col_datetime_key`), 734 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 735); 736 737INSERT INTO `t3` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f'); 738 739CREATE TABLE `t4` ( 740 `pk` int(11) NOT NULL AUTO_INCREMENT, 741 `col_int_nokey` int(11) DEFAULT NULL, 742 `col_int_key` int(11) DEFAULT NULL, 743 `col_date_key` date DEFAULT NULL, 744 `col_date_nokey` date DEFAULT NULL, 745 `col_time_key` time DEFAULT NULL, 746 `col_time_nokey` time DEFAULT NULL, 747 `col_datetime_key` datetime DEFAULT NULL, 748 `col_datetime_nokey` datetime DEFAULT NULL, 749 `col_varchar_key` varchar(1) DEFAULT NULL, 750 `col_varchar_nokey` varchar(1) DEFAULT NULL, 751 PRIMARY KEY (`pk`), 752 KEY `col_int_key` (`col_int_key`), 753 KEY `col_date_key` (`col_date_key`), 754 KEY `col_time_key` (`col_time_key`), 755 KEY `col_datetime_key` (`col_datetime_key`), 756 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 757); 758 759INSERT INTO `t4` VALUES (1,6,NULL,'2003-05-12','2003-05-12',NULL,NULL,'2000-09-12 00:00:00','2000-09-12 00:00:00','r','r'); 760INSERT INTO `t4` VALUES (2,8,0,'2003-01-07','2003-01-07','14:34:45','14:34:45','2004-08-10 09:09:31','2004-08-10 09:09:31','c','c'); 761INSERT INTO `t4` VALUES (3,6,0,NULL,NULL,'11:49:48','11:49:48','2005-03-21 04:31:40','2005-03-21 04:31:40','o','o'); 762INSERT INTO `t4` VALUES (4,6,7,'2005-03-12','2005-03-12','18:12:55','18:12:55','2002-10-25 23:50:35','2002-10-25 23:50:35','c','c'); 763INSERT INTO `t4` VALUES (5,3,8,'2000-08-02','2000-08-02','18:30:05','18:30:05','2001-04-01 21:14:04','2001-04-01 21:14:04','d','d'); 764INSERT INTO `t4` VALUES (6,9,4,'1900-01-01','1900-01-01','14:19:30','14:19:30','2005-03-12 06:02:34','2005-03-12 06:02:34','v','v'); 765INSERT INTO `t4` VALUES (7,2,6,'2006-07-06','2006-07-06','05:20:04','05:20:04','2001-05-06 14:49:12','2001-05-06 14:49:12','m','m'); 766INSERT INTO `t4` VALUES (8,1,5,'2006-12-24','2006-12-24','20:29:31','20:29:31','2004-04-25 00:00:00','2004-04-25 00:00:00','j','j'); 767INSERT INTO `t4` VALUES (9,8,NULL,'2004-11-16','2004-11-16','07:08:09','07:08:09','2001-03-22 18:38:43','2001-03-22 18:38:43','f','f'); 768INSERT INTO `t4` VALUES (10,0,NULL,'2002-09-09','2002-09-09','14:49:14','14:49:14','2006-04-25 21:03:02','2006-04-25 21:03:02','n','n'); 769INSERT INTO `t4` VALUES (11,9,8,NULL,NULL,'00:00:00','00:00:00','2009-09-07 18:40:43','2009-09-07 18:40:43','z','z'); 770INSERT INTO `t4` VALUES (12,8,8,'2008-06-24','2008-06-24','09:58:06','09:58:06','2004-03-23 00:00:00','2004-03-23 00:00:00','h','h'); 771INSERT INTO `t4` VALUES (13,NULL,8,'2001-04-21','2001-04-21',NULL,NULL,'2009-04-15 00:08:29','2009-04-15 00:08:29','q','q'); 772INSERT INTO `t4` VALUES (14,0,1,'2003-11-22','2003-11-22','18:24:16','18:24:16','2000-04-21 00:00:00','2000-04-21 00:00:00','w','w'); 773INSERT INTO `t4` VALUES (15,5,1,'2004-09-12','2004-09-12','17:39:57','17:39:57','2000-02-17 19:41:23','2000-02-17 19:41:23','z','z'); 774INSERT INTO `t4` VALUES (16,1,5,'2006-06-20','2006-06-20','08:23:21','08:23:21','2003-09-20 07:38:14','2003-09-20 07:38:14','j','j'); 775INSERT INTO `t4` VALUES (17,1,2,NULL,NULL,NULL,NULL,'2000-11-28 20:42:12','2000-11-28 20:42:12','a','a'); 776INSERT INTO `t4` VALUES (18,6,7,'2001-11-25','2001-11-25','21:50:46','21:50:46','2005-06-12 11:13:17','2005-06-12 11:13:17','m','m'); 777INSERT INTO `t4` VALUES (19,6,6,'2004-10-26','2004-10-26','12:33:17','12:33:17','1900-01-01 00:00:00','1900-01-01 00:00:00','n','n'); 778INSERT INTO `t4` VALUES (20,1,4,'2005-01-19','2005-01-19','03:06:43','03:06:43','2006-02-09 20:41:06','2006-02-09 20:41:06','e','e'); 779INSERT INTO `t4` VALUES (21,8,7,'2008-07-06','2008-07-06','03:46:14','03:46:14','2004-05-22 01:05:57','2004-05-22 01:05:57','u','u'); 780INSERT INTO `t4` VALUES (22,1,0,'1900-01-01','1900-01-01','20:34:52','20:34:52','2004-03-04 13:46:31','2004-03-04 13:46:31','s','s'); 781INSERT INTO `t4` VALUES (23,0,9,'1900-01-01','1900-01-01',NULL,NULL,'1900-01-01 00:00:00','1900-01-01 00:00:00','u','u'); 782INSERT INTO `t4` VALUES (24,4,3,'2004-06-08','2004-06-08','10:41:20','10:41:20','2004-10-20 07:20:19','2004-10-20 07:20:19','r','r'); 783INSERT INTO `t4` VALUES (25,9,5,'2007-02-20','2007-02-20','08:43:11','08:43:11','2006-04-17 00:00:00','2006-04-17 00:00:00','g','g'); 784INSERT INTO `t4` VALUES (26,8,1,'2008-06-18','2008-06-18',NULL,NULL,'2000-10-27 00:00:00','2000-10-27 00:00:00','o','o'); 785INSERT INTO `t4` VALUES (27,5,1,'2008-05-15','2008-05-15','10:17:51','10:17:51','2007-04-14 08:54:06','2007-04-14 08:54:06','w','w'); 786INSERT INTO `t4` VALUES (28,9,5,'2005-10-06','2005-10-06','06:34:09','06:34:09','2008-04-12 17:03:52','2008-04-12 17:03:52','b','b'); 787INSERT INTO `t4` VALUES (29,5,9,NULL,NULL,'21:22:47','21:22:47','2007-02-19 17:37:09','2007-02-19 17:37:09',NULL,NULL); 788INSERT INTO `t4` VALUES (30,NULL,2,'2006-10-12','2006-10-12','04:02:32','04:02:32','1900-01-01 00:00:00','1900-01-01 00:00:00','y','y'); 789INSERT INTO `t4` VALUES (31,NULL,5,'2005-01-24','2005-01-24','02:33:14','02:33:14','2001-10-10 08:32:27','2001-10-10 08:32:27','y','y'); 790INSERT INTO `t4` VALUES (32,105,248,'2009-06-27','2009-06-27','16:32:56','16:32:56',NULL,NULL,'u','u'); 791INSERT INTO `t4` VALUES (33,0,0,NULL,NULL,'21:32:42','21:32:42','2001-12-16 05:31:53','2001-12-16 05:31:53','p','p'); 792INSERT INTO `t4` VALUES (34,3,8,NULL,NULL,'23:04:47','23:04:47','2003-07-19 18:03:28','2003-07-19 18:03:28','s','s'); 793INSERT INTO `t4` VALUES (35,1,1,'1900-01-01','1900-01-01','22:05:43','22:05:43','2001-03-27 11:44:10','2001-03-27 11:44:10','e','e'); 794INSERT INTO `t4` VALUES (36,75,255,'2005-12-22','2005-12-22','02:05:45','02:05:45','2008-06-15 02:13:00','2008-06-15 02:13:00','d','d'); 795INSERT INTO `t4` VALUES (37,9,9,'2005-05-03','2005-05-03','00:00:00','00:00:00','2009-03-14 21:29:56','2009-03-14 21:29:56','d','d'); 796INSERT INTO `t4` VALUES (38,7,9,'2003-05-27','2003-05-27','18:09:07','18:09:07','2005-01-02 00:00:00','2005-01-02 00:00:00','c','c'); 797INSERT INTO `t4` VALUES (39,NULL,3,'2006-05-25','2006-05-25','10:54:06','10:54:06','2007-07-16 04:44:07','2007-07-16 04:44:07','b','b'); 798INSERT INTO `t4` VALUES (40,NULL,9,NULL,NULL,'23:15:50','23:15:50','2003-08-26 21:38:26','2003-08-26 21:38:26','t','t'); 799INSERT INTO `t4` VALUES (41,4,6,'2009-01-04','2009-01-04','10:17:40','10:17:40','2004-04-19 04:18:47','2004-04-19 04:18:47',NULL,NULL); 800INSERT INTO `t4` VALUES (42,0,4,'2009-02-14','2009-02-14','03:37:09','03:37:09','2000-01-06 20:32:48','2000-01-06 20:32:48','y','y'); 801INSERT INTO `t4` VALUES (43,204,60,'2003-01-16','2003-01-16','22:26:06','22:26:06','2006-06-23 13:27:17','2006-06-23 13:27:17','c','c'); 802INSERT INTO `t4` VALUES (44,0,7,'1900-01-01','1900-01-01','17:10:38','17:10:38','2007-11-27 00:00:00','2007-11-27 00:00:00','d','d'); 803INSERT INTO `t4` VALUES (45,9,1,'2007-06-26','2007-06-26','00:00:00','00:00:00','2002-04-03 12:06:51','2002-04-03 12:06:51','x','x'); 804INSERT INTO `t4` VALUES (46,8,6,'2004-03-27','2004-03-27','17:08:49','17:08:49','2008-12-28 09:47:42','2008-12-28 09:47:42','p','p'); 805INSERT INTO `t4` VALUES (47,7,4,NULL,NULL,'19:04:40','19:04:40','2002-04-04 10:07:54','2002-04-04 10:07:54','e','e'); 806INSERT INTO `t4` VALUES (48,8,NULL,'2005-06-06','2005-06-06','20:53:28','20:53:28','2003-04-26 02:55:13','2003-04-26 02:55:13','g','g'); 807INSERT INTO `t4` VALUES (49,NULL,8,'2003-03-02','2003-03-02','11:46:03','11:46:03',NULL,NULL,'x','x'); 808INSERT INTO `t4` VALUES (50,6,0,'2004-05-13','2004-05-13',NULL,NULL,'2009-02-19 03:17:06','2009-02-19 03:17:06','s','s'); 809INSERT INTO `t4` VALUES (51,5,8,'2005-09-13','2005-09-13','10:58:07','10:58:07','1900-01-01 00:00:00','1900-01-01 00:00:00','e','e'); 810INSERT INTO `t4` VALUES (52,2,151,'2005-10-03','2005-10-03','00:00:00','00:00:00','2000-11-10 08:20:01','2000-11-10 08:20:01','l','l'); 811INSERT INTO `t4` VALUES (53,3,7,'2005-10-14','2005-10-14','09:43:15','09:43:15','2008-02-10 00:00:00','2008-02-10 00:00:00','p','p'); 812INSERT INTO `t4` VALUES (54,7,6,NULL,NULL,'21:40:32','21:40:32','1900-01-01 00:00:00','1900-01-01 00:00:00','h','h'); 813INSERT INTO `t4` VALUES (55,NULL,NULL,'2005-09-16','2005-09-16','00:17:44','00:17:44',NULL,NULL,'m','m'); 814INSERT INTO `t4` VALUES (56,145,23,'2005-03-10','2005-03-10','16:47:26','16:47:26','2001-02-05 02:01:50','2001-02-05 02:01:50','n','n'); 815INSERT INTO `t4` VALUES (57,0,2,'2000-06-19','2000-06-19','00:00:00','00:00:00','2000-10-28 08:44:25','2000-10-28 08:44:25','v','v'); 816INSERT INTO `t4` VALUES (58,1,4,'2002-11-03','2002-11-03','05:25:59','05:25:59','2005-03-20 10:53:59','2005-03-20 10:53:59','b','b'); 817INSERT INTO `t4` VALUES (59,7,NULL,'2009-01-05','2009-01-05','00:00:00','00:00:00','2001-06-02 13:54:13','2001-06-02 13:54:13','x','x'); 818INSERT INTO `t4` VALUES (60,3,NULL,'2003-05-22','2003-05-22','20:33:04','20:33:04','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'); 819INSERT INTO `t4` VALUES (61,NULL,77,'2005-07-02','2005-07-02','00:46:12','00:46:12','2009-07-16 13:05:43','2009-07-16 13:05:43','t','t'); 820INSERT INTO `t4` VALUES (62,2,NULL,'1900-01-01','1900-01-01','00:00:00','00:00:00','2009-03-26 23:16:20','2009-03-26 23:16:20','w','w'); 821INSERT INTO `t4` VALUES (63,2,NULL,'2006-06-21','2006-06-21','02:13:59','02:13:59','2003-02-06 18:12:15','2003-02-06 18:12:15','w','w'); 822INSERT INTO `t4` VALUES (64,2,7,NULL,NULL,'02:54:47','02:54:47','2006-06-05 03:22:51','2006-06-05 03:22:51','k','k'); 823INSERT INTO `t4` VALUES (65,8,1,'2005-12-16','2005-12-16','18:13:59','18:13:59','2002-02-10 05:47:27','2002-02-10 05:47:27','a','a'); 824INSERT INTO `t4` VALUES (66,6,9,'2004-11-05','2004-11-05','13:53:08','13:53:08','2001-08-01 08:50:52','2001-08-01 08:50:52','t','t'); 825INSERT INTO `t4` VALUES (67,1,6,NULL,NULL,'22:21:30','22:21:30','1900-01-01 00:00:00','1900-01-01 00:00:00','z','z'); 826INSERT INTO `t4` VALUES (68,NULL,2,'2004-09-14','2004-09-14','11:41:50','11:41:50',NULL,NULL,'e','e'); 827INSERT INTO `t4` VALUES (69,1,3,'2002-04-06','2002-04-06','15:20:02','15:20:02','1900-01-01 00:00:00','1900-01-01 00:00:00','q','q'); 828INSERT INTO `t4` VALUES (70,0,0,NULL,NULL,NULL,NULL,'2000-09-23 00:00:00','2000-09-23 00:00:00','e','e'); 829INSERT INTO `t4` VALUES (71,4,NULL,'2002-11-13','2002-11-13',NULL,NULL,'2007-07-09 08:32:49','2007-07-09 08:32:49','v','v'); 830INSERT INTO `t4` VALUES (72,1,6,'2006-05-27','2006-05-27','07:51:52','07:51:52','2000-01-05 00:00:00','2000-01-05 00:00:00','d','d'); 831INSERT INTO `t4` VALUES (73,1,3,'2000-12-22','2000-12-22','00:00:00','00:00:00','2000-09-24 00:00:00','2000-09-24 00:00:00','u','u'); 832INSERT INTO `t4` VALUES (74,27,195,'2004-02-21','2004-02-21',NULL,NULL,'2005-05-06 00:00:00','2005-05-06 00:00:00','o','o'); 833INSERT INTO `t4` VALUES (75,4,5,'2009-05-15','2009-05-15',NULL,NULL,'2000-03-11 00:00:00','2000-03-11 00:00:00','b','b'); 834INSERT INTO `t4` VALUES (76,6,2,'2008-12-12','2008-12-12','12:31:05','12:31:05','2001-09-02 16:17:35','2001-09-02 16:17:35','c','c'); 835INSERT INTO `t4` VALUES (77,2,7,'2000-04-15','2000-04-15','00:00:00','00:00:00','2006-04-25 05:43:44','2006-04-25 05:43:44','q','q'); 836INSERT INTO `t4` VALUES (78,248,25,NULL,NULL,'01:16:45','01:16:45','2009-10-25 22:04:02','2009-10-25 22:04:02',NULL,NULL); 837INSERT INTO `t4` VALUES (79,NULL,NULL,'2001-10-18','2001-10-18','20:38:54','20:38:54','2004-08-06 00:00:00','2004-08-06 00:00:00','h','h'); 838INSERT INTO `t4` VALUES (80,9,0,'2008-05-25','2008-05-25','00:30:15','00:30:15','2001-11-27 05:07:57','2001-11-27 05:07:57','d','d'); 839INSERT INTO `t4` VALUES (81,75,98,'2004-12-02','2004-12-02','23:46:36','23:46:36','2009-06-28 03:18:39','2009-06-28 03:18:39','w','w'); 840INSERT INTO `t4` VALUES (82,2,6,'2002-02-15','2002-02-15','19:03:13','19:03:13','2000-03-12 00:00:00','2000-03-12 00:00:00','m','m'); 841INSERT INTO `t4` VALUES (83,9,5,'2002-03-03','2002-03-03','10:54:27','10:54:27',NULL,NULL,'i','i'); 842INSERT INTO `t4` VALUES (84,4,0,NULL,NULL,'00:25:47','00:25:47','2007-10-20 00:00:00','2007-10-20 00:00:00','w','w'); 843INSERT INTO `t4` VALUES (85,0,3,'2003-01-26','2003-01-26','08:44:27','08:44:27','2009-09-27 00:00:00','2009-09-27 00:00:00','f','f'); 844INSERT INTO `t4` VALUES (86,0,1,'2001-12-19','2001-12-19','08:15:38','08:15:38','2002-07-16 00:00:00','2002-07-16 00:00:00','k','k'); 845INSERT INTO `t4` VALUES (87,1,1,'2001-08-07','2001-08-07','19:56:21','19:56:21','2005-02-20 00:00:00','2005-02-20 00:00:00','v','v'); 846INSERT INTO `t4` VALUES (88,119,147,'2005-02-16','2005-02-16','00:00:00','00:00:00',NULL,NULL,'c','c'); 847INSERT INTO `t4` VALUES (89,1,3,'2006-06-10','2006-06-10','20:50:52','20:50:52','2001-07-16 00:00:00','2001-07-16 00:00:00','y','y'); 848INSERT INTO `t4` VALUES (90,7,3,NULL,NULL,'03:54:39','03:54:39','2009-05-20 21:04:12','2009-05-20 21:04:12','h','h'); 849INSERT INTO `t4` VALUES (91,2,NULL,'2005-04-06','2005-04-06','23:58:17','23:58:17','2002-03-13 10:55:40','2002-03-13 10:55:40',NULL,NULL); 850INSERT INTO `t4` VALUES (92,7,2,'2003-04-27','2003-04-27','12:54:58','12:54:58','2005-07-12 00:00:00','2005-07-12 00:00:00','t','t'); 851INSERT INTO `t4` VALUES (93,2,1,'2005-10-13','2005-10-13','04:02:43','04:02:43','2006-07-22 09:46:34','2006-07-22 09:46:34','l','l'); 852INSERT INTO `t4` VALUES (94,6,8,'2003-10-02','2003-10-02','11:31:12','11:31:12','2001-09-01 00:00:00','2001-09-01 00:00:00','a','a'); 853INSERT INTO `t4` VALUES (95,4,8,'2005-09-09','2005-09-09','20:20:04','20:20:04','2002-05-27 18:38:45','2002-05-27 18:38:45','r','r'); 854INSERT INTO `t4` VALUES (96,5,8,NULL,NULL,'00:22:24','00:22:24',NULL,NULL,'s','s'); 855INSERT INTO `t4` VALUES (97,7,0,'2006-02-15','2006-02-15','10:09:31','10:09:31',NULL,NULL,'z','z'); 856INSERT INTO `t4` VALUES (98,1,1,'1900-01-01','1900-01-01',NULL,NULL,'2009-08-08 22:38:53','2009-08-08 22:38:53','j','j'); 857INSERT INTO `t4` VALUES (99,7,8,'2003-12-24','2003-12-24','18:45:35','18:45:35',NULL,NULL,'c','c'); 858INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f'); 859 860SET @@optimizer_switch='subquery_cache=off'; 861 862/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( 863SELECT SUBQUERY2_t1 .`col_int_key` 864FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` 865WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 866FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 867GROUP BY field10 ; 868 869SET @@optimizer_switch='subquery_cache=on'; 870 871/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( 872SELECT SUBQUERY2_t1 .`col_int_key` 873FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` 874WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 875FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 876GROUP BY field10 ; 877 878drop table t1,t2,t3,t4; 879set @@optimizer_switch= default; 880 881# 882--echo #launchpad BUG#609045 883# 884CREATE TABLE `t2` ( 885 `pk` int(11) NOT NULL AUTO_INCREMENT, 886 `col_int_nokey` int(11) DEFAULT NULL, 887 `col_int_key` int(11) DEFAULT NULL, 888 `col_varchar_key` varchar(1) DEFAULT NULL, 889 `col_varchar_nokey` varchar(1) DEFAULT NULL, 890 PRIMARY KEY (`pk`), 891 KEY `col_int_key` (`col_int_key`), 892 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 893) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; 894INSERT INTO `t2` VALUES (10,7,8,'v','v'); 895INSERT INTO `t2` VALUES (11,1,9,'r','r'); 896INSERT INTO `t2` VALUES (12,5,9,'a','a'); 897INSERT INTO `t2` VALUES (13,3,186,'m','m'); 898INSERT INTO `t2` VALUES (14,6,NULL,'y','y'); 899INSERT INTO `t2` VALUES (15,92,2,'j','j'); 900INSERT INTO `t2` VALUES (16,7,3,'d','d'); 901INSERT INTO `t2` VALUES (17,NULL,0,'z','z'); 902INSERT INTO `t2` VALUES (18,3,133,'e','e'); 903INSERT INTO `t2` VALUES (19,5,1,'h','h'); 904INSERT INTO `t2` VALUES (20,1,8,'b','b'); 905INSERT INTO `t2` VALUES (21,2,5,'s','s'); 906INSERT INTO `t2` VALUES (22,NULL,5,'e','e'); 907INSERT INTO `t2` VALUES (23,1,8,'j','j'); 908INSERT INTO `t2` VALUES (24,0,6,'e','e'); 909INSERT INTO `t2` VALUES (25,210,51,'f','f'); 910INSERT INTO `t2` VALUES (26,8,4,'v','v'); 911INSERT INTO `t2` VALUES (27,7,7,'x','x'); 912INSERT INTO `t2` VALUES (28,5,6,'m','m'); 913INSERT INTO `t2` VALUES (29,NULL,4,'c','c'); 914CREATE TABLE `t1` ( 915 `pk` int(11) NOT NULL AUTO_INCREMENT, 916 `col_int_nokey` int(11) DEFAULT NULL, 917 `col_int_key` int(11) DEFAULT NULL, 918 `col_varchar_key` varchar(1) DEFAULT NULL, 919 `col_varchar_nokey` varchar(1) DEFAULT NULL, 920 PRIMARY KEY (`pk`), 921 KEY `col_int_key` (`col_int_key`), 922 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 923) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 924INSERT INTO `t1` VALUES (1,NULL,2,'w','w'); 925INSERT INTO `t1` VALUES (2,7,9,'m','m'); 926INSERT INTO `t1` VALUES (3,9,3,'m','m'); 927INSERT INTO `t1` VALUES (4,7,9,'k','k'); 928INSERT INTO `t1` VALUES (5,4,NULL,'r','r'); 929INSERT INTO `t1` VALUES (6,2,9,'t','t'); 930INSERT INTO `t1` VALUES (7,6,3,'j','j'); 931INSERT INTO `t1` VALUES (8,8,8,'u','u'); 932INSERT INTO `t1` VALUES (9,NULL,8,'h','h'); 933INSERT INTO `t1` VALUES (10,5,53,'o','o'); 934INSERT INTO `t1` VALUES (11,NULL,0,NULL,NULL); 935INSERT INTO `t1` VALUES (12,6,5,'k','k'); 936INSERT INTO `t1` VALUES (13,188,166,'e','e'); 937INSERT INTO `t1` VALUES (14,2,3,'n','n'); 938INSERT INTO `t1` VALUES (15,1,0,'t','t'); 939INSERT INTO `t1` VALUES (16,1,1,'c','c'); 940INSERT INTO `t1` VALUES (17,0,9,'m','m'); 941INSERT INTO `t1` VALUES (18,9,5,'y','y'); 942INSERT INTO `t1` VALUES (19,NULL,6,'f','f'); 943INSERT INTO `t1` VALUES (20,4,2,'d','d'); 944 945SET @@optimizer_switch = 'subquery_cache=off'; 946 947/* cache is off */ SELECT SUM( DISTINCT table1 .`pk` ) , ( 948 SELECT MAX( `col_int_nokey` ) 949 FROM t1 950 WHERE table1 .`pk` ) field3 951FROM t1 table1 952JOIN ( 953 t1 table2 954 JOIN t2 table3 955 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 956) 957ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` 958GROUP BY field3 ; 959 960SET @@optimizer_switch = 'subquery_cache=on'; 961 962/* cache is on */ SELECT SUM( DISTINCT table1 .`pk` ) , ( 963 SELECT MAX( `col_int_nokey` ) 964 FROM t1 965 WHERE table1 .`pk` ) field3 966FROM t1 table1 967JOIN ( 968 t1 table2 969 JOIN t2 table3 970 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 971) 972ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` 973GROUP BY field3 ; 974 975drop table t1,t2; 976set @@optimizer_switch= default; 977 978# 979--echo #launchpad BUG#609052 980# 981CREATE TABLE `t2` ( 982 `pk` int(11) NOT NULL AUTO_INCREMENT, 983 `col_int_nokey` int(11) DEFAULT NULL, 984 `col_int_key` int(11) DEFAULT NULL, 985 `col_time_key` time DEFAULT NULL, 986 `col_varchar_key` varchar(1) DEFAULT NULL, 987 `col_varchar_nokey` varchar(1) DEFAULT NULL, 988 PRIMARY KEY (`pk`), 989 KEY `col_int_key` (`col_int_key`), 990 KEY `col_time_key` (`col_time_key`), 991 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 992) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; 993INSERT INTO `t2` VALUES (10,7,8,'01:27:35','v','v'); 994INSERT INTO `t2` VALUES (11,1,9,'19:48:31','r','r'); 995INSERT INTO `t2` VALUES (12,5,9,'00:00:00','a','a'); 996INSERT INTO `t2` VALUES (13,3,186,'19:53:05','m','m'); 997INSERT INTO `t2` VALUES (14,6,NULL,'19:18:56','y','y'); 998INSERT INTO `t2` VALUES (15,92,2,'10:55:12','j','j'); 999INSERT INTO `t2` VALUES (16,7,3,'00:25:00','d','d'); 1000INSERT INTO `t2` VALUES (17,NULL,0,'12:35:47','z','z'); 1001INSERT INTO `t2` VALUES (18,3,133,'19:53:03','e','e'); 1002INSERT INTO `t2` VALUES (19,5,1,'17:53:30','h','h'); 1003INSERT INTO `t2` VALUES (20,1,8,'11:35:49','b','b'); 1004INSERT INTO `t2` VALUES (21,2,5,NULL,'s','s'); 1005INSERT INTO `t2` VALUES (22,NULL,5,'06:01:40','e','e'); 1006INSERT INTO `t2` VALUES (23,1,8,'05:45:11','j','j'); 1007INSERT INTO `t2` VALUES (24,0,6,'00:00:00','e','e'); 1008INSERT INTO `t2` VALUES (25,210,51,'00:00:00','f','f'); 1009INSERT INTO `t2` VALUES (26,8,4,'06:11:01','v','v'); 1010INSERT INTO `t2` VALUES (27,7,7,'13:02:46','x','x'); 1011INSERT INTO `t2` VALUES (28,5,6,'21:44:25','m','m'); 1012INSERT INTO `t2` VALUES (29,NULL,4,'22:43:58','c','c'); 1013CREATE TABLE `t4` ( 1014 `pk` int(11) NOT NULL AUTO_INCREMENT, 1015 `col_int_nokey` int(11) DEFAULT NULL, 1016 `col_int_key` int(11) DEFAULT NULL, 1017 `col_time_key` time DEFAULT NULL, 1018 `col_varchar_key` varchar(1) DEFAULT NULL, 1019 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1020 PRIMARY KEY (`pk`), 1021 KEY `col_int_key` (`col_int_key`), 1022 KEY `col_time_key` (`col_time_key`), 1023 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1024) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; 1025INSERT INTO `t4` VALUES (1,6,NULL,NULL,'r','r'); 1026INSERT INTO `t4` VALUES (2,8,0,'14:34:45','c','c'); 1027INSERT INTO `t4` VALUES (3,6,0,'11:49:48','o','o'); 1028INSERT INTO `t4` VALUES (4,6,7,'18:12:55','c','c'); 1029INSERT INTO `t4` VALUES (5,3,8,'18:30:05','d','d'); 1030INSERT INTO `t4` VALUES (6,9,4,'14:19:30','v','v'); 1031INSERT INTO `t4` VALUES (7,2,6,'05:20:04','m','m'); 1032INSERT INTO `t4` VALUES (8,1,5,'20:29:31','j','j'); 1033INSERT INTO `t4` VALUES (9,8,NULL,'07:08:09','f','f'); 1034INSERT INTO `t4` VALUES (10,0,NULL,'14:49:14','n','n'); 1035INSERT INTO `t4` VALUES (11,9,8,'00:00:00','z','z'); 1036INSERT INTO `t4` VALUES (12,8,8,'09:58:06','h','h'); 1037INSERT INTO `t4` VALUES (13,NULL,8,NULL,'q','q'); 1038INSERT INTO `t4` VALUES (14,0,1,'18:24:16','w','w'); 1039INSERT INTO `t4` VALUES (15,5,1,'17:39:57','z','z'); 1040INSERT INTO `t4` VALUES (16,1,5,'08:23:21','j','j'); 1041INSERT INTO `t4` VALUES (17,1,2,NULL,'a','a'); 1042INSERT INTO `t4` VALUES (18,6,7,'21:50:46','m','m'); 1043INSERT INTO `t4` VALUES (19,6,6,'12:33:17','n','n'); 1044INSERT INTO `t4` VALUES (20,1,4,'03:06:43','e','e'); 1045INSERT INTO `t4` VALUES (21,8,7,'03:46:14','u','u'); 1046INSERT INTO `t4` VALUES (22,1,0,'20:34:52','s','s'); 1047INSERT INTO `t4` VALUES (23,0,9,NULL,'u','u'); 1048INSERT INTO `t4` VALUES (24,4,3,'10:41:20','r','r'); 1049INSERT INTO `t4` VALUES (25,9,5,'08:43:11','g','g'); 1050INSERT INTO `t4` VALUES (26,8,1,NULL,'o','o'); 1051INSERT INTO `t4` VALUES (27,5,1,'10:17:51','w','w'); 1052INSERT INTO `t4` VALUES (28,9,5,'06:34:09','b','b'); 1053INSERT INTO `t4` VALUES (29,5,9,'21:22:47',NULL,NULL); 1054INSERT INTO `t4` VALUES (30,NULL,2,'04:02:32','y','y'); 1055INSERT INTO `t4` VALUES (31,NULL,5,'02:33:14','y','y'); 1056INSERT INTO `t4` VALUES (32,105,248,'16:32:56','u','u'); 1057INSERT INTO `t4` VALUES (33,0,0,'21:32:42','p','p'); 1058INSERT INTO `t4` VALUES (34,3,8,'23:04:47','s','s'); 1059INSERT INTO `t4` VALUES (35,1,1,'22:05:43','e','e'); 1060INSERT INTO `t4` VALUES (36,75,255,'02:05:45','d','d'); 1061INSERT INTO `t4` VALUES (37,9,9,'00:00:00','d','d'); 1062INSERT INTO `t4` VALUES (38,7,9,'18:09:07','c','c'); 1063INSERT INTO `t4` VALUES (39,NULL,3,'10:54:06','b','b'); 1064INSERT INTO `t4` VALUES (40,NULL,9,'23:15:50','t','t'); 1065INSERT INTO `t4` VALUES (41,4,6,'10:17:40',NULL,NULL); 1066INSERT INTO `t4` VALUES (42,0,4,'03:37:09','y','y'); 1067INSERT INTO `t4` VALUES (43,204,60,'22:26:06','c','c'); 1068INSERT INTO `t4` VALUES (44,0,7,'17:10:38','d','d'); 1069INSERT INTO `t4` VALUES (45,9,1,'00:00:00','x','x'); 1070INSERT INTO `t4` VALUES (46,8,6,'17:08:49','p','p'); 1071INSERT INTO `t4` VALUES (47,7,4,'19:04:40','e','e'); 1072INSERT INTO `t4` VALUES (48,8,NULL,'20:53:28','g','g'); 1073INSERT INTO `t4` VALUES (49,NULL,8,'11:46:03','x','x'); 1074INSERT INTO `t4` VALUES (50,6,0,NULL,'s','s'); 1075INSERT INTO `t4` VALUES (51,5,8,'10:58:07','e','e'); 1076INSERT INTO `t4` VALUES (52,2,151,'00:00:00','l','l'); 1077INSERT INTO `t4` VALUES (53,3,7,'09:43:15','p','p'); 1078INSERT INTO `t4` VALUES (54,7,6,'21:40:32','h','h'); 1079INSERT INTO `t4` VALUES (55,NULL,NULL,'00:17:44','m','m'); 1080INSERT INTO `t4` VALUES (56,145,23,'16:47:26','n','n'); 1081INSERT INTO `t4` VALUES (57,0,2,'00:00:00','v','v'); 1082INSERT INTO `t4` VALUES (58,1,4,'05:25:59','b','b'); 1083INSERT INTO `t4` VALUES (59,7,NULL,'00:00:00','x','x'); 1084INSERT INTO `t4` VALUES (60,3,NULL,'20:33:04','r','r'); 1085INSERT INTO `t4` VALUES (61,NULL,77,'00:46:12','t','t'); 1086INSERT INTO `t4` VALUES (62,2,NULL,'00:00:00','w','w'); 1087INSERT INTO `t4` VALUES (63,2,NULL,'02:13:59','w','w'); 1088INSERT INTO `t4` VALUES (64,2,7,'02:54:47','k','k'); 1089INSERT INTO `t4` VALUES (65,8,1,'18:13:59','a','a'); 1090INSERT INTO `t4` VALUES (66,6,9,'13:53:08','t','t'); 1091INSERT INTO `t4` VALUES (67,1,6,'22:21:30','z','z'); 1092INSERT INTO `t4` VALUES (68,NULL,2,'11:41:50','e','e'); 1093INSERT INTO `t4` VALUES (69,1,3,'15:20:02','q','q'); 1094INSERT INTO `t4` VALUES (70,0,0,NULL,'e','e'); 1095INSERT INTO `t4` VALUES (71,4,NULL,NULL,'v','v'); 1096INSERT INTO `t4` VALUES (72,1,6,'07:51:52','d','d'); 1097INSERT INTO `t4` VALUES (73,1,3,'00:00:00','u','u'); 1098INSERT INTO `t4` VALUES (74,27,195,NULL,'o','o'); 1099INSERT INTO `t4` VALUES (75,4,5,NULL,'b','b'); 1100INSERT INTO `t4` VALUES (76,6,2,'12:31:05','c','c'); 1101INSERT INTO `t4` VALUES (77,2,7,'00:00:00','q','q'); 1102INSERT INTO `t4` VALUES (78,248,25,'01:16:45',NULL,NULL); 1103INSERT INTO `t4` VALUES (79,NULL,NULL,'20:38:54','h','h'); 1104INSERT INTO `t4` VALUES (80,9,0,'00:30:15','d','d'); 1105INSERT INTO `t4` VALUES (81,75,98,'23:46:36','w','w'); 1106INSERT INTO `t4` VALUES (82,2,6,'19:03:13','m','m'); 1107INSERT INTO `t4` VALUES (83,9,5,'10:54:27','i','i'); 1108INSERT INTO `t4` VALUES (84,4,0,'00:25:47','w','w'); 1109INSERT INTO `t4` VALUES (85,0,3,'08:44:27','f','f'); 1110INSERT INTO `t4` VALUES (86,0,1,'08:15:38','k','k'); 1111INSERT INTO `t4` VALUES (87,1,1,'19:56:21','v','v'); 1112INSERT INTO `t4` VALUES (88,119,147,'00:00:00','c','c'); 1113INSERT INTO `t4` VALUES (89,1,3,'20:50:52','y','y'); 1114INSERT INTO `t4` VALUES (90,7,3,'03:54:39','h','h'); 1115INSERT INTO `t4` VALUES (91,2,NULL,'23:58:17',NULL,NULL); 1116INSERT INTO `t4` VALUES (92,7,2,'12:54:58','t','t'); 1117INSERT INTO `t4` VALUES (93,2,1,'04:02:43','l','l'); 1118INSERT INTO `t4` VALUES (94,6,8,'11:31:12','a','a'); 1119INSERT INTO `t4` VALUES (95,4,8,'20:20:04','r','r'); 1120INSERT INTO `t4` VALUES (96,5,8,'00:22:24','s','s'); 1121INSERT INTO `t4` VALUES (97,7,0,'10:09:31','z','z'); 1122INSERT INTO `t4` VALUES (98,1,1,NULL,'j','j'); 1123INSERT INTO `t4` VALUES (99,7,8,'18:45:35','c','c'); 1124INSERT INTO `t4` VALUES (100,2,5,'11:49:25','f','f'); 1125CREATE TABLE `t1` ( 1126 `pk` int(11) NOT NULL AUTO_INCREMENT, 1127 `col_int_nokey` int(11) DEFAULT NULL, 1128 `col_int_key` int(11) DEFAULT NULL, 1129 `col_time_key` time DEFAULT NULL, 1130 `col_varchar_key` varchar(1) DEFAULT NULL, 1131 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1132 PRIMARY KEY (`pk`), 1133 KEY `col_int_key` (`col_int_key`), 1134 KEY `col_time_key` (`col_time_key`), 1135 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1136) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 1137INSERT INTO `t1` VALUES (1,NULL,2,'11:28:45','w','w'); 1138INSERT INTO `t1` VALUES (2,7,9,'20:25:14','m','m'); 1139INSERT INTO `t1` VALUES (3,9,3,'13:47:24','m','m'); 1140INSERT INTO `t1` VALUES (4,7,9,'19:24:11','k','k'); 1141INSERT INTO `t1` VALUES (5,4,NULL,'15:59:13','r','r'); 1142INSERT INTO `t1` VALUES (6,2,9,'00:00:00','t','t'); 1143INSERT INTO `t1` VALUES (7,6,3,'15:15:04','j','j'); 1144INSERT INTO `t1` VALUES (8,8,8,'11:32:06','u','u'); 1145INSERT INTO `t1` VALUES (9,NULL,8,'18:32:33','h','h'); 1146INSERT INTO `t1` VALUES (10,5,53,'15:19:25','o','o'); 1147INSERT INTO `t1` VALUES (11,NULL,0,'19:03:19',NULL,NULL); 1148INSERT INTO `t1` VALUES (12,6,5,'00:39:46','k','k'); 1149INSERT INTO `t1` VALUES (13,188,166,NULL,'e','e'); 1150INSERT INTO `t1` VALUES (14,2,3,'00:00:00','n','n'); 1151INSERT INTO `t1` VALUES (15,1,0,'13:12:11','t','t'); 1152INSERT INTO `t1` VALUES (16,1,1,'04:56:48','c','c'); 1153INSERT INTO `t1` VALUES (17,0,9,'19:56:05','m','m'); 1154INSERT INTO `t1` VALUES (18,9,5,'19:35:19','y','y'); 1155INSERT INTO `t1` VALUES (19,NULL,6,'05:03:03','f','f'); 1156INSERT INTO `t1` VALUES (20,4,2,'18:38:59','d','d'); 1157CREATE TABLE `t3` ( 1158 `pk` int(11) NOT NULL AUTO_INCREMENT, 1159 `col_int_nokey` int(11) DEFAULT NULL, 1160 `col_int_key` int(11) DEFAULT NULL, 1161 `col_time_key` time DEFAULT NULL, 1162 `col_varchar_key` varchar(1) DEFAULT NULL, 1163 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1164 PRIMARY KEY (`pk`), 1165 KEY `col_int_key` (`col_int_key`), 1166 KEY `col_time_key` (`col_time_key`), 1167 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1168) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; 1169INSERT INTO `t3` VALUES (10,8,8,'18:27:58',NULL,NULL); 1170CREATE TABLE `t5` ( 1171 `pk` int(11) NOT NULL AUTO_INCREMENT, 1172 `col_int_nokey` int(11) DEFAULT NULL, 1173 `col_int_key` int(11) DEFAULT NULL, 1174 `col_time_key` time DEFAULT NULL, 1175 `col_varchar_key` varchar(1) DEFAULT NULL, 1176 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1177 PRIMARY KEY (`pk`), 1178 KEY `col_int_key` (`col_int_key`), 1179 KEY `col_time_key` (`col_time_key`), 1180 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1181) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; 1182INSERT INTO `t5` VALUES (1,1,7,'01:13:38','f','f'); 1183 1184 1185SET @@optimizer_switch='subquery_cache=off'; 1186 1187/* cache is off */ SELECT SQL_SMALL_RESULT MAX( DISTINCT table1 . `col_varchar_key` ) AS field1 , MIN( table1 . `col_varchar_nokey` ) AS field2 , COUNT( table1 . `col_varchar_key` ) AS field3 , table2 . `col_time_key` AS field4 , COUNT( DISTINCT table2 . `col_int_key` ) AS field5 , ( 1188SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1 1189FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) ) 1190WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , ( 1191SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1 1192FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) ) 1193WHERE SUBQUERY2_t2 . `col_varchar_nokey` != table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_varchar_nokey` >= 'o' ) AS field9 , CONCAT ( table1 . `col_varchar_key` , table2 . `col_varchar_nokey` ) AS field10 1194FROM ( t4 AS table1 LEFT JOIN ( ( t1 AS table2 STRAIGHT_JOIN t1 AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) 1195WHERE ( EXISTS ( 1196SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1 1197FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) ) 1198WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND ( table1 . `pk` > 116 AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey` 1199GROUP BY field4, field6, field9, field10 1200HAVING field10 = 'c' 1201; 1202 1203SET @@optimizer_switch='subquery_cache=on'; 1204 1205/* cache is on */ SELECT SQL_SMALL_RESULT MAX( DISTINCT table1 . `col_varchar_key` ) AS field1 , MIN( table1 . `col_varchar_nokey` ) AS field2 , COUNT( table1 . `col_varchar_key` ) AS field3 , table2 . `col_time_key` AS field4 , COUNT( DISTINCT table2 . `col_int_key` ) AS field5 , ( 1206SELECT MAX( SUBQUERY1_t2 . `col_int_nokey` ) AS SUBQUERY1_field1 1207FROM ( t3 AS SUBQUERY1_t1 INNER JOIN t1 AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_varchar_key` = SUBQUERY1_t1 . `col_varchar_nokey` ) ) 1208WHERE SUBQUERY1_t2 . `pk` < SUBQUERY1_t2 . `pk` ) AS field6 , COUNT( table1 . `col_varchar_nokey` ) AS field7 , COUNT( table2 . `pk` ) AS field8 , ( 1209SELECT MAX( SUBQUERY2_t1 . `col_int_key` ) AS SUBQUERY2_field1 1210FROM ( t5 AS SUBQUERY2_t1 LEFT JOIN t2 AS SUBQUERY2_t2 ON (SUBQUERY2_t2 . `col_int_key` = SUBQUERY2_t1 . `col_int_key` ) ) 1211WHERE SUBQUERY2_t2 . `col_varchar_nokey` != table1 . `col_varchar_key` OR SUBQUERY2_t1 . `col_varchar_nokey` >= 'o' ) AS field9 , CONCAT ( table1 . `col_varchar_key` , table2 . `col_varchar_nokey` ) AS field10 1212FROM ( t4 AS table1 LEFT JOIN ( ( t1 AS table2 STRAIGHT_JOIN t1 AS table3 ON (table3 . `col_int_nokey` = table2 . `pk` ) ) ) ON (table3 . `col_varchar_key` = table2 . `col_varchar_key` ) ) 1213WHERE ( EXISTS ( 1214SELECT SUBQUERY3_t1 . `pk` AS SUBQUERY3_field1 1215FROM ( t4 AS SUBQUERY3_t1 INNER JOIN t4 AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_varchar_key` = SUBQUERY3_t1 . `col_varchar_key` ) ) 1216WHERE SUBQUERY3_t1 . `col_int_key` > table3 . `pk` AND SUBQUERY3_t1 . `pk` != table3 . `pk` ) ) AND ( table1 . `pk` > 116 AND table1 . `pk` < ( 116 + 175 ) OR table1 . `pk` IN (251) ) OR table1 . `col_int_nokey` = table1 . `col_int_nokey` 1217GROUP BY field4, field6, field9, field10 1218HAVING field10 = 'c' 1219; 1220 1221drop table t1,t2,t3,t4,t5; 1222set @@optimizer_switch= default; 1223 1224 1225# 1226--echo #launchpad BUG#609043 1227# 1228CREATE TABLE `t1` ( 1229 `pk` int(11) NOT NULL AUTO_INCREMENT, 1230 `col_int_nokey` int(11) DEFAULT NULL, 1231 `col_int_key` int(11) DEFAULT NULL, 1232 `col_date_key` date DEFAULT NULL, 1233 `col_date_nokey` date DEFAULT NULL, 1234 `col_time_key` time DEFAULT NULL, 1235 `col_time_nokey` time DEFAULT NULL, 1236 `col_datetime_key` datetime DEFAULT NULL, 1237 `col_datetime_nokey` datetime DEFAULT NULL, 1238 `col_varchar_key` varchar(1) DEFAULT NULL, 1239 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1240 PRIMARY KEY (`pk`), 1241 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1242) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 1243INSERT INTO `t1` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f'); 1244INSERT INTO `t1` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'); 1245 1246CREATE TABLE `t2` ( 1247 `pk` int(11) NOT NULL AUTO_INCREMENT, 1248 `col_int_nokey` int(11) DEFAULT NULL, 1249 `col_int_key` int(11) DEFAULT NULL, 1250 `col_date_key` date DEFAULT NULL, 1251 `col_date_nokey` date DEFAULT NULL, 1252 `col_time_key` time DEFAULT NULL, 1253 `col_time_nokey` time DEFAULT NULL, 1254 `col_datetime_key` datetime DEFAULT NULL, 1255 `col_datetime_nokey` datetime DEFAULT NULL, 1256 `col_varchar_key` varchar(1) DEFAULT NULL, 1257 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1258 PRIMARY KEY (`pk`), 1259 KEY `col_int_key` (`col_int_key`), 1260 KEY `col_date_key` (`col_date_key`), 1261 KEY `col_time_key` (`col_time_key`), 1262 KEY `col_datetime_key` (`col_datetime_key`), 1263 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1264) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; 1265 1266CREATE TABLE `t3` ( 1267 `pk` int(11) NOT NULL AUTO_INCREMENT, 1268 `col_int_nokey` int(11) DEFAULT NULL, 1269 `col_int_key` int(11) DEFAULT NULL, 1270 `col_date_key` date DEFAULT NULL, 1271 `col_date_nokey` date DEFAULT NULL, 1272 `col_time_key` time DEFAULT NULL, 1273 `col_time_nokey` time DEFAULT NULL, 1274 `col_datetime_key` datetime DEFAULT NULL, 1275 `col_datetime_nokey` datetime DEFAULT NULL, 1276 `col_varchar_key` varchar(1) DEFAULT NULL, 1277 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1278 PRIMARY KEY (`pk`), 1279 KEY `col_int_key` (`col_int_key`), 1280 KEY `col_date_key` (`col_date_key`), 1281 KEY `col_time_key` (`col_time_key`), 1282 KEY `col_datetime_key` (`col_datetime_key`), 1283 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1284) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; 1285 1286CREATE TABLE `t4` ( 1287 `pk` int(11) NOT NULL AUTO_INCREMENT, 1288 `col_int_nokey` int(11) DEFAULT NULL, 1289 `col_int_key` int(11) DEFAULT NULL, 1290 `col_date_key` date DEFAULT NULL, 1291 `col_date_nokey` date DEFAULT NULL, 1292 `col_time_key` time DEFAULT NULL, 1293 `col_time_nokey` time DEFAULT NULL, 1294 `col_datetime_key` datetime DEFAULT NULL, 1295 `col_datetime_nokey` datetime DEFAULT NULL, 1296 `col_varchar_key` varchar(1) DEFAULT NULL, 1297 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1298 PRIMARY KEY (`pk`), 1299 KEY `col_int_key` (`col_int_key`), 1300 KEY `col_date_key` (`col_date_key`), 1301 KEY `col_time_key` (`col_time_key`), 1302 KEY `col_datetime_key` (`col_datetime_key`), 1303 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1304) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; 1305INSERT INTO `t4` VALUES (100,2,5,'2001-07-26','2001-07-26','11:49:25','11:49:25','2007-04-25 05:08:49','2007-04-25 05:08:49','f','f'); 1306 1307SET @@optimizer_switch = 'subquery_cache=off'; 1308 1309/* cache is off */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( 1310SELECT SUBQUERY2_t1 .`col_int_key` 1311FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` 1312WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 1313FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 1314GROUP BY field10 ; 1315 1316SET @@optimizer_switch = 'subquery_cache=on'; 1317 1318/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , ( 1319SELECT SUBQUERY2_t1 .`col_int_key` 1320FROM t3 SUBQUERY2_t1 JOIN t2 ON SUBQUERY2_t1 .`col_int_key` 1321WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10 1322FROM t4 table1 JOIN ( t1 table2 STRAIGHT_JOIN t1 table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` 1323GROUP BY field10 ; 1324 1325drop table t1,t2,t3,t4; 1326set @@optimizer_switch= default; 1327 1328# 1329--echo #launchpad BUG#611625 1330# 1331CREATE TABLE `t1` ( 1332 `pk` int(11) NOT NULL AUTO_INCREMENT, 1333 `col_int_nokey` int(11) DEFAULT NULL, 1334 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1335 PRIMARY KEY (`pk`) 1336) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 1337INSERT INTO `t1` VALUES (1,NULL,'w'); 1338INSERT INTO `t1` VALUES (2,7,'m'); 1339INSERT INTO `t1` VALUES (3,9,'m'); 1340INSERT INTO `t1` VALUES (4,7,'k'); 1341INSERT INTO `t1` VALUES (5,4,'r'); 1342INSERT INTO `t1` VALUES (6,2,'t'); 1343INSERT INTO `t1` VALUES (7,6,'j'); 1344INSERT INTO `t1` VALUES (8,8,'u'); 1345INSERT INTO `t1` VALUES (9,NULL,'h'); 1346INSERT INTO `t1` VALUES (10,5,'o'); 1347INSERT INTO `t1` VALUES (11,NULL,NULL); 1348INSERT INTO `t1` VALUES (12,6,'k'); 1349INSERT INTO `t1` VALUES (13,188,'e'); 1350INSERT INTO `t1` VALUES (14,2,'n'); 1351INSERT INTO `t1` VALUES (15,1,'t'); 1352INSERT INTO `t1` VALUES (16,1,'c'); 1353INSERT INTO `t1` VALUES (17,0,'m'); 1354INSERT INTO `t1` VALUES (18,9,'y'); 1355INSERT INTO `t1` VALUES (19,NULL,'f'); 1356INSERT INTO `t1` VALUES (20,4,'d'); 1357CREATE TABLE `t3` ( 1358 `pk` int(11) NOT NULL AUTO_INCREMENT, 1359 `col_int_nokey` int(11) DEFAULT NULL, 1360 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1361 PRIMARY KEY (`pk`) 1362) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; 1363INSERT INTO `t3` VALUES (1,6,'r'); 1364INSERT INTO `t3` VALUES (2,8,'c'); 1365INSERT INTO `t3` VALUES (3,6,'o'); 1366INSERT INTO `t3` VALUES (4,6,'c'); 1367INSERT INTO `t3` VALUES (5,3,'d'); 1368INSERT INTO `t3` VALUES (6,9,'v'); 1369INSERT INTO `t3` VALUES (7,2,'m'); 1370INSERT INTO `t3` VALUES (8,1,'j'); 1371INSERT INTO `t3` VALUES (9,8,'f'); 1372INSERT INTO `t3` VALUES (10,0,'n'); 1373INSERT INTO `t3` VALUES (11,9,'z'); 1374INSERT INTO `t3` VALUES (12,8,'h'); 1375INSERT INTO `t3` VALUES (13,NULL,'q'); 1376INSERT INTO `t3` VALUES (14,0,'w'); 1377INSERT INTO `t3` VALUES (15,5,'z'); 1378INSERT INTO `t3` VALUES (16,1,'j'); 1379INSERT INTO `t3` VALUES (17,1,'a'); 1380INSERT INTO `t3` VALUES (18,6,'m'); 1381INSERT INTO `t3` VALUES (19,6,'n'); 1382INSERT INTO `t3` VALUES (20,1,'e'); 1383INSERT INTO `t3` VALUES (21,8,'u'); 1384INSERT INTO `t3` VALUES (22,1,'s'); 1385INSERT INTO `t3` VALUES (23,0,'u'); 1386INSERT INTO `t3` VALUES (24,4,'r'); 1387INSERT INTO `t3` VALUES (25,9,'g'); 1388INSERT INTO `t3` VALUES (26,8,'o'); 1389INSERT INTO `t3` VALUES (27,5,'w'); 1390INSERT INTO `t3` VALUES (28,9,'b'); 1391INSERT INTO `t3` VALUES (29,5,NULL); 1392INSERT INTO `t3` VALUES (30,NULL,'y'); 1393INSERT INTO `t3` VALUES (31,NULL,'y'); 1394INSERT INTO `t3` VALUES (32,105,'u'); 1395INSERT INTO `t3` VALUES (33,0,'p'); 1396INSERT INTO `t3` VALUES (34,3,'s'); 1397INSERT INTO `t3` VALUES (35,1,'e'); 1398INSERT INTO `t3` VALUES (36,75,'d'); 1399INSERT INTO `t3` VALUES (37,9,'d'); 1400INSERT INTO `t3` VALUES (38,7,'c'); 1401INSERT INTO `t3` VALUES (39,NULL,'b'); 1402INSERT INTO `t3` VALUES (40,NULL,'t'); 1403INSERT INTO `t3` VALUES (41,4,NULL); 1404INSERT INTO `t3` VALUES (42,0,'y'); 1405INSERT INTO `t3` VALUES (43,204,'c'); 1406INSERT INTO `t3` VALUES (44,0,'d'); 1407INSERT INTO `t3` VALUES (45,9,'x'); 1408INSERT INTO `t3` VALUES (46,8,'p'); 1409INSERT INTO `t3` VALUES (47,7,'e'); 1410INSERT INTO `t3` VALUES (48,8,'g'); 1411INSERT INTO `t3` VALUES (49,NULL,'x'); 1412INSERT INTO `t3` VALUES (50,6,'s'); 1413INSERT INTO `t3` VALUES (51,5,'e'); 1414INSERT INTO `t3` VALUES (52,2,'l'); 1415INSERT INTO `t3` VALUES (53,3,'p'); 1416INSERT INTO `t3` VALUES (54,7,'h'); 1417INSERT INTO `t3` VALUES (55,NULL,'m'); 1418INSERT INTO `t3` VALUES (56,145,'n'); 1419INSERT INTO `t3` VALUES (57,0,'v'); 1420INSERT INTO `t3` VALUES (58,1,'b'); 1421INSERT INTO `t3` VALUES (59,7,'x'); 1422INSERT INTO `t3` VALUES (60,3,'r'); 1423INSERT INTO `t3` VALUES (61,NULL,'t'); 1424INSERT INTO `t3` VALUES (62,2,'w'); 1425INSERT INTO `t3` VALUES (63,2,'w'); 1426INSERT INTO `t3` VALUES (64,2,'k'); 1427INSERT INTO `t3` VALUES (65,8,'a'); 1428INSERT INTO `t3` VALUES (66,6,'t'); 1429INSERT INTO `t3` VALUES (67,1,'z'); 1430INSERT INTO `t3` VALUES (68,NULL,'e'); 1431INSERT INTO `t3` VALUES (69,1,'q'); 1432INSERT INTO `t3` VALUES (70,0,'e'); 1433INSERT INTO `t3` VALUES (71,4,'v'); 1434INSERT INTO `t3` VALUES (72,1,'d'); 1435INSERT INTO `t3` VALUES (73,1,'u'); 1436INSERT INTO `t3` VALUES (74,27,'o'); 1437INSERT INTO `t3` VALUES (75,4,'b'); 1438INSERT INTO `t3` VALUES (76,6,'c'); 1439INSERT INTO `t3` VALUES (77,2,'q'); 1440INSERT INTO `t3` VALUES (78,248,NULL); 1441INSERT INTO `t3` VALUES (79,NULL,'h'); 1442INSERT INTO `t3` VALUES (80,9,'d'); 1443INSERT INTO `t3` VALUES (81,75,'w'); 1444INSERT INTO `t3` VALUES (82,2,'m'); 1445INSERT INTO `t3` VALUES (83,9,'i'); 1446INSERT INTO `t3` VALUES (84,4,'w'); 1447INSERT INTO `t3` VALUES (85,0,'f'); 1448INSERT INTO `t3` VALUES (86,0,'k'); 1449INSERT INTO `t3` VALUES (87,1,'v'); 1450INSERT INTO `t3` VALUES (88,119,'c'); 1451INSERT INTO `t3` VALUES (89,1,'y'); 1452INSERT INTO `t3` VALUES (90,7,'h'); 1453INSERT INTO `t3` VALUES (91,2,NULL); 1454INSERT INTO `t3` VALUES (92,7,'t'); 1455INSERT INTO `t3` VALUES (93,2,'l'); 1456INSERT INTO `t3` VALUES (94,6,'a'); 1457INSERT INTO `t3` VALUES (95,4,'r'); 1458INSERT INTO `t3` VALUES (96,5,'s'); 1459INSERT INTO `t3` VALUES (97,7,'z'); 1460INSERT INTO `t3` VALUES (98,1,'j'); 1461INSERT INTO `t3` VALUES (99,7,'c'); 1462INSERT INTO `t3` VALUES (100,2,'f'); 1463CREATE TABLE `t2` ( 1464 `pk` int(11) NOT NULL AUTO_INCREMENT, 1465 `col_int_nokey` int(11) DEFAULT NULL, 1466 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1467 PRIMARY KEY (`pk`) 1468) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; 1469INSERT INTO `t2` VALUES (10,8,NULL); 1470 1471set optimizer_switch='subquery_cache=off'; 1472 1473SELECT ( 1474SELECT `col_int_nokey` 1475FROM t3 1476WHERE table1 .`col_varchar_nokey` ) field13 1477FROM t2 table1 JOIN t1 table2 ON table2 .`pk` 1478ORDER BY field13; 1479 1480set optimizer_switch='subquery_cache=on'; 1481 1482SELECT 1483 (SELECT `col_int_nokey` 1484 FROM t3 1485 WHERE table1 .`col_varchar_nokey` ) field13 1486FROM t2 table1 JOIN t1 table2 ON table2 .`pk` 1487ORDER BY field13; 1488 1489drop table t1,t2,t3; 1490set @@optimizer_switch= default; 1491 1492# 1493--echo # LP BUG#615760 (part 1: double transformation) 1494# 1495create table t1 (a int); 1496insert into t1 values (1),(2); 1497create table t2 (b int); 1498insert into t2 values (1),(2); 1499set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on'; 1500 1501explain extended 1502select * from t1 where a in (select b from t2); 1503 1504drop table t1,t2; 1505set @@optimizer_switch= default; 1506 1507# 1508--echo # LP BUG#615760 (part 2: incorrect heap table index flags) 1509# 1510SET SESSION optimizer_switch = 'index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_condition_pushdown=off,firstmatch=off,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=on'; 1511 1512CREATE TABLE `t1` ( 1513 `pk` int(11) NOT NULL AUTO_INCREMENT, 1514 `col_int_nokey` int(11) DEFAULT NULL, 1515 `col_int_key` int(11) DEFAULT NULL, 1516 `col_varchar_key` varchar(1) DEFAULT NULL, 1517 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1518 PRIMARY KEY (`pk`), 1519 KEY `col_int_key` (`col_int_key`), 1520 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1521) AUTO_INCREMENT=30 DEFAULT CHARSET=latin1; 1522INSERT INTO `t1` VALUES (10,7,8,'v','v'); 1523INSERT INTO `t1` VALUES (11,1,9,'r','r'); 1524INSERT INTO `t1` VALUES (12,5,9,'a','a'); 1525INSERT INTO `t1` VALUES (13,3,186,'m','m'); 1526INSERT INTO `t1` VALUES (14,6,NULL,'y','y'); 1527INSERT INTO `t1` VALUES (15,92,2,'j','j'); 1528INSERT INTO `t1` VALUES (16,7,3,'d','d'); 1529INSERT INTO `t1` VALUES (17,NULL,0,'z','z'); 1530INSERT INTO `t1` VALUES (18,3,133,'e','e'); 1531INSERT INTO `t1` VALUES (19,5,1,'h','h'); 1532INSERT INTO `t1` VALUES (20,1,8,'b','b'); 1533INSERT INTO `t1` VALUES (21,2,5,'s','s'); 1534INSERT INTO `t1` VALUES (22,NULL,5,'e','e'); 1535INSERT INTO `t1` VALUES (23,1,8,'j','j'); 1536INSERT INTO `t1` VALUES (24,0,6,'e','e'); 1537INSERT INTO `t1` VALUES (25,210,51,'f','f'); 1538INSERT INTO `t1` VALUES (26,8,4,'v','v'); 1539INSERT INTO `t1` VALUES (27,7,7,'x','x'); 1540INSERT INTO `t1` VALUES (28,5,6,'m','m'); 1541INSERT INTO `t1` VALUES (29,NULL,4,'c','c'); 1542CREATE TABLE `t2` ( 1543 `pk` int(11) NOT NULL AUTO_INCREMENT, 1544 `col_int_nokey` int(11) DEFAULT NULL, 1545 `col_int_key` int(11) DEFAULT NULL, 1546 `col_varchar_key` varchar(1) DEFAULT NULL, 1547 `col_varchar_nokey` varchar(1) DEFAULT NULL, 1548 PRIMARY KEY (`pk`), 1549 KEY `col_int_key` (`col_int_key`), 1550 KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 1551) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; 1552INSERT INTO `t2` VALUES (1,NULL,2,'w','w'); 1553INSERT INTO `t2` VALUES (2,7,9,'m','m'); 1554INSERT INTO `t2` VALUES (3,9,3,'m','m'); 1555INSERT INTO `t2` VALUES (4,7,9,'k','k'); 1556INSERT INTO `t2` VALUES (5,4,NULL,'r','r'); 1557INSERT INTO `t2` VALUES (6,2,9,'t','t'); 1558INSERT INTO `t2` VALUES (7,6,3,'j','j'); 1559INSERT INTO `t2` VALUES (8,8,8,'u','u'); 1560INSERT INTO `t2` VALUES (9,NULL,8,'h','h'); 1561INSERT INTO `t2` VALUES (10,5,53,'o','o'); 1562INSERT INTO `t2` VALUES (11,NULL,0,NULL,NULL); 1563INSERT INTO `t2` VALUES (12,6,5,'k','k'); 1564INSERT INTO `t2` VALUES (13,188,166,'e','e'); 1565INSERT INTO `t2` VALUES (14,2,3,'n','n'); 1566INSERT INTO `t2` VALUES (15,1,0,'t','t'); 1567INSERT INTO `t2` VALUES (16,1,1,'c','c'); 1568INSERT INTO `t2` VALUES (17,0,9,'m','m'); 1569INSERT INTO `t2` VALUES (18,9,5,'y','y'); 1570INSERT INTO `t2` VALUES (19,NULL,6,'f','f'); 1571INSERT INTO `t2` VALUES (20,4,2,'d','d'); 1572 1573# Here we just need plenty of different parameters to overflow 1574# temporary heap table of expression cache 1575--disable_warnings 1576SELECT table1 .`col_varchar_nokey` 1577FROM t2 table1 RIGHT JOIN t1 LEFT JOIN ( 1578SELECT SUBQUERY1_t2 .* 1579FROM t1 SUBQUERY1_t1 LEFT JOIN t2 SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`col_int_nokey` ) table3 STRAIGHT_JOIN ( ( 1580SELECT * 1581FROM t1 ) table4 JOIN ( t1 table5 JOIN t2 table6 ON table5 .`pk` ) ON table5 .`col_varchar_nokey` ) ON table6 .`pk` = table5 .`col_int_key` ON table5 .`col_varchar_nokey` ON table5 .`col_varchar_key` 1582WHERE table3 .`col_varchar_key` IN ( 1583SELECT `col_varchar_key` 1584FROM t2 ) AND table1 .`col_varchar_key` OR table1 .`pk` ; 1585--enable_warnings 1586 1587drop table t1,t2; 1588set @@optimizer_switch= default; 1589 1590set optimizer_switch='subquery_cache=on'; 1591# 1592--echo # LP BUG#615378 (incorrect NULL result returning in Item_cache) 1593# 1594# if bug present here will be valgrind warnings (due to attempt to process 1595# uninialized decimal value) but the result will be correct (due to 1596# Item::null_value) 1597 1598CREATE TABLE `t1` ( 1599 `pk` int(11) NOT NULL AUTO_INCREMENT, 1600 `col_varchar_key` varchar(1) DEFAULT NULL, 1601 PRIMARY KEY (`pk`), 1602 KEY `col_varchar_key` (`col_varchar_key`) 1603) DEFAULT CHARSET=latin1; 1604INSERT INTO `t1` VALUES (10,'v'); 1605INSERT INTO `t1` VALUES (11,'r'); 1606CREATE TABLE `t2` ( 1607 `pk` int(11) NOT NULL AUTO_INCREMENT, 1608 `col_varchar_key` varchar(1) DEFAULT NULL, 1609 PRIMARY KEY (`pk`), 1610 KEY `col_varchar_key` (`col_varchar_key`) 1611) DEFAULT CHARSET=latin1; 1612INSERT INTO `t2` VALUES (1,'r'); 1613INSERT INTO `t2` VALUES (2,'c'); 1614CREATE TABLE `t3` ( 1615 `pk` int(11) NOT NULL AUTO_INCREMENT, 1616 `col_varchar_key` varchar(1) DEFAULT NULL, 1617 PRIMARY KEY (`pk`), 1618 KEY `col_varchar_key` (`col_varchar_key`) 1619) DEFAULT CHARSET=latin1; 1620INSERT INTO `t3` VALUES (1,'w'); 1621 1622# We may get warnings about 'h' not beeing a double here 1623--disable_warnings 1624SELECT SUM( DISTINCT table2 . `pk` ) AS field2 , 1625(SELECT SUM( SUBQUERY1_t2 . `pk` ) AS SUBQUERY1_field1 1626 FROM t2 AS SUBQUERY1_t2 STRAIGHT_JOIN 1627 t3 AS SUBQUERY1_t3 ON (SUBQUERY1_t3 . `pk` = SUBQUERY1_t2 . `pk` ) 1628 WHERE table1 . `col_varchar_key` ) AS field3 1629FROM ( t1 AS table1 LEFT JOIN 1630 ( t2 AS table2 STRAIGHT_JOIN 1631 t3 AS table3 ON (table3 . `pk` = table2 . `pk` ) ) 1632 ON (table3 . `col_varchar_key` = table1 . `col_varchar_key` ) ) 1633WHERE ( table1 . `pk` < 5 ) OR ( table1 . `col_varchar_key` IS NOT NULL) 1634GROUP BY field3 1635HAVING (field3 <= 'h' AND field2 != 4) ; 1636--enable_warnings 1637drop tables t1, t2, t3; 1638 1639--echo # 1640--echo # Test aggregate functions as parameters to subquery cache 1641--echo # 1642 1643CREATE TABLE t1 ( a INT, b INT, c INT, KEY (a, b)); 1644 1645INSERT INTO t1 VALUES 1646 ( 1, 1, 1 ), 1647 ( 1, 2, 2 ), 1648 ( 1, 3, 3 ), 1649 ( 1, 4, 6 ), 1650 ( 1, 5, 5 ), 1651 ( 1, 9, 13 ), 1652 1653 ( 2, 1, 6 ), 1654 ( 2, 2, 7 ), 1655 ( 2, 3, 8 ); 1656 1657SELECT a, AVG(t1.b), 1658(SELECT t11.c FROM t1 t11 WHERE t11.a = t1.a AND t11.b = AVG(t1.b)) AS t11c 1659FROM t1 GROUP BY a; 1660 1661DROP TABLE t1; 1662 1663--echo # 1664--echo # Test of LP BUG#800696 (deleting list of Items (OR arguments) 1665--echo # in optimization) 1666--echo # 1667 1668set optimizer_switch='subquery_cache=on,in_to_exists=on'; 1669CREATE TABLE t1 ( f3 int) ; 1670INSERT INTO t1 VALUES (0),(0); 1671 1672CREATE TABLE t3 ( f3 int) ; 1673INSERT INTO t3 VALUES (0),(0); 1674 1675CREATE TABLE t2 ( f1 int, f2 int, f3 int) ; 1676INSERT INTO t2 VALUES (7,0,0); 1677 1678SELECT * 1679FROM t2, t3 1680WHERE t2.f2 OR t3.f3 IN 1681( 1682SELECT t2.f2 1683FROM t1 1684WHERE t2.f1 OR t2.f3 ); 1685drop tables t1, t2, t3; 1686 1687--echo # 1688--echo # Test of LP BUG#872775 view with "outer references" bug 1689--echo # 1690set @@optimizer_switch= default; 1691set optimizer_switch='subquery_cache=on'; 1692CREATE TABLE t1 (a int) ; 1693 1694CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; 1695INSERT INTO t2 VALUES (1,'x'),(2,'y'); 1696 1697CREATE TABLE t3 (a int) ; 1698 1699CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; 1700INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); 1701 1702CREATE OR REPLACE VIEW v1 AS 1703SELECT t2.b 1704FROM t1 1705JOIN t2 1706WHERE t2 .c > ( 1707 SELECT t2.c FROM t3 1708 ); 1709 1710SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); 1711 1712drop view v1; 1713drop table t1,t2,t3,t4; 1714 1715SET optimizer_switch=@save_optimizer_switch; 1716 1717--echo # restore default 1718set @@optimizer_switch= default; 1719