1drop table if exists t1,t2,t3; 2select * from (select 2 from DUAL) b; 32 42 5SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; 6ERROR 42S22: Unknown column 'a' in 'field list' 7SELECT 1 as a FROM (SELECT a UNION SELECT 1) b; 8ERROR 42S22: Unknown column 'a' in 'field list' 9CREATE TABLE t1 (a int not null, b char (10) not null); 10insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); 11CREATE TABLE t2 (a int not null, b char (10) not null); 12insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); 13select t1.a,t3.y from t1,(select a as y from t2 where b='c') as t3 where t1.a = t3.y; 14a y 153 3 163 3 17select t1.a,t3.a from t1,(select * from t2 where b='c') as t3 where t1.a = t3.a; 18a a 193 3 203 3 21CREATE TABLE t3 (a int not null, b char (10) not null); 22insert into t3 values (3,'f'),(4,'y'),(5,'z'),(6,'c'); 23select t1.a,t4.y from t1,(select t2.a as y from t2,(select t3.b from t3 where t3.a>3) as t5 where t2.b=t5.b) as t4 where t1.a = t4.y; 24a y 253 3 263 3 27SELECT a FROM (SELECT 1 FROM (SELECT 1) a HAVING a=1) b; 28ERROR 42S22: Unknown column 'a' in 'having clause' 29SELECT a,b as a FROM (SELECT '1' as a,'2' as b) b HAVING a=1; 30ERROR 23000: Column 'a' in having clause is ambiguous 31SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=2; 32a a 331 2 34SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1; 35a a 36SELECT 1 FROM (SELECT 1) a WHERE a=2; 37ERROR 42S22: Unknown column 'a' in 'where clause' 38SELECT (SELECT 1) as a FROM (SELECT 1 FROM t1 HAVING a=1) as a; 39ERROR 42S22: Unknown column 'a' in 'having clause' 40select * from t1 as x1, (select * from t1) as x2; 41a b a b 421 a 1 a 432 b 1 a 443 c 1 a 453 c 1 a 461 a 2 b 472 b 2 b 483 c 2 b 493 c 2 b 501 a 3 c 512 b 3 c 523 c 3 c 533 c 3 c 541 a 3 c 552 b 3 c 563 c 3 c 573 c 3 c 58explain select * from t1 as x1, (select * from t1) as x2; 59id select_type table type possible_keys key key_len ref rows Extra 601 PRIMARY x1 ALL NULL NULL NULL NULL 4 NULL 611 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 622 DERIVED t1 ALL NULL NULL NULL NULL 4 NULL 63drop table if exists t2,t3; 64select * from (select 1) as a; 651 661 67select a from (select 1 as a) as b; 68a 691 70select 1 from (select 1) as a; 711 721 73select * from (select * from t1 union select * from t1) a; 74a b 751 a 762 b 773 c 78select * from (select * from t1 union all select * from t1) a; 79a b 801 a 812 b 823 c 833 c 841 a 852 b 863 c 873 c 88select * from (select * from t1 union all select * from t1 limit 2) a; 89a b 901 a 912 b 92explain select * from (select * from t1 union select * from t1) a; 93id select_type table type possible_keys key key_len ref rows Extra 941 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 NULL 952 DERIVED t1 ALL NULL NULL NULL NULL 4 NULL 963 UNION t1 ALL NULL NULL NULL NULL 4 NULL 97NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Using temporary 98explain select * from (select * from t1 union all select * from t1) a; 99id select_type table type possible_keys key key_len ref rows Extra 1001 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 NULL 1012 DERIVED t1 ALL NULL NULL NULL NULL 4 NULL 1023 UNION t1 ALL NULL NULL NULL NULL 4 NULL 103NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Using temporary 104CREATE TABLE t2 (a int not null); 105insert into t2 values(1); 106select * from (select * from t1 where t1.a=(select a from t2 where t2.a=t1.a)) a; 107a b 1081 a 109select * from (select * from t1 where t1.a=(select t2.a from t2 where t2.a=t1.a) union select t1.a, t1.b from t1) a; 110a b 1111 a 1122 b 1133 c 114explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; 115id select_type table type possible_keys key key_len ref rows Extra 1161 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 NULL 1172 DERIVED t2 system NULL NULL NULL NULL 1 NULL 1182 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where 119drop table t1, t2; 120create table t1(a int not null, t char(8), index(a)); 121SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20; 122a t 1231 1 1242 2 1253 3 1264 4 1275 5 1286 6 1297 7 1308 8 1319 9 13210 10 13311 11 13412 12 13513 13 13614 14 13715 15 13816 16 13917 17 14018 18 14119 19 14220 20 143explain select count(*) from t1 as tt1, (select * from t1) as tt2; 144id select_type table type possible_keys key key_len ref rows Extra 1451 PRIMARY tt1 index NULL a 4 NULL 10000 Using index 1461 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000 Using join buffer (Block Nested Loop) 1472 DERIVED t1 ALL NULL NULL NULL NULL 10000 NULL 148drop table t1; 149SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; 150(SELECT * FROM (SELECT 1 as a) as a ) 1511 152select * from (select 1 as a) b left join (select 2 as a) c using(a); 153a 1541 155SELECT * FROM (SELECT 1 UNION SELECT a) b; 156ERROR 42S22: Unknown column 'a' in 'field list' 157SELECT 1 as a FROM (SELECT a UNION SELECT 1) b; 158ERROR 42S22: Unknown column 'a' in 'field list' 159SELECT 1 as a FROM (SELECT 1 UNION SELECT a) b; 160ERROR 42S22: Unknown column 'a' in 'field list' 161select 1 from (select 2) a order by 0; 162ERROR 42S22: Unknown column '0' in 'order clause' 163create table t1 (id int); 164insert into t1 values (1),(2),(3); 165describe select * from (select * from t1 group by id) bar; 166id select_type table type possible_keys key key_len ref rows Extra 1671 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 NULL 1682 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 169drop table t1; 170create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL); 171create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL); 172insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9); 173insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); 174analyze table t1, t2; 175Table Op Msg_type Msg_text 176test.t1 analyze status OK 177test.t2 analyze status OK 178SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 179pla_id mat_id 180100 1 181101 1 182102 1 183103 2 184104 2 185105 3 186SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 187pla_id test 188100 1 189101 1 190102 1 191103 2 192104 2 193105 3 194explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 195id select_type table type possible_keys key key_len ref rows Extra 1961 PRIMARY m2 ALL NULL NULL NULL NULL 9 NULL 1971 PRIMARY <derived2> ref <auto_key0> <auto_key0> 7 test.m2.matintnum 2 NULL 1982 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 1992 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 NULL 200explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; 201id select_type table type possible_keys key key_len ref rows Extra 2021 PRIMARY m2 ALL NULL NULL NULL NULL 9 NULL 2031 PRIMARY <derived2> ref <auto_key0> <auto_key0> 7 test.m2.matintnum 2 NULL 2042 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2052 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 NULL 206drop table t1,t2; 207SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1; 208x 2091 210create user mysqltest_1; 211create table t1 select 1 as a; 212select 2 as a from (select * from t1) b; 213ERROR 3D000: No database selected 214use test; 215select 2 as a from (select * from t1) b; 216a 2172 218drop table t1; 219select mail_id, if(folder.f_description!='', folder.f_description, folder.f_name) as folder_name, date, address_id, phrase, address, subject from folder, (select mail.mail_id as mail_id, date_format(mail.h_date, '%b %e, %Y %h:%i') as date, mail.folder_id, sender.address_id as address_id, sender.phrase as phrase, sender.address as address, mail.h_subject as subject from mail left join mxa as mxa_sender on mail.mail_id=mxa_sender.mail_id and mxa_sender.type='from' left join address as sender on mxa_sender.address_id=sender.address_id mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_recipient.mail_id and mxa_recipient.address_id=recipient.address_id and mxa_recipient.type='to' and match(sender.phrase, sender.address, sender.comment) against ('jeremy' in boolean mode) and match(recipient.phrase, recipient.address, recipient.comment) against ('monty' in boolean mode) order by mail.h_date desc limit 0, 25 ) as query where query.folder_id=folder.folder_id; 220ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mxa as mxa_recipient, address as recipient, where 1 and mail.mail_id=mxa_r' at line 1 221create table t1 (a int); 222insert into t1 values (1),(2),(3); 223update (select * from t1) as t1 set a = 5; 224ERROR HY000: The target table t1 of the UPDATE is not updatable 225delete from (select * from t1); 226ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1)' at line 1 227insert into (select * from t1) values (5); 228ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select * from t1) values (5)' at line 1 229drop table t1; 230create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1) 231); 232insert into t1 VALUES(1,1,1), (2,2,1); 233select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 234count(*) 2352 236explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 237id select_type table type possible_keys key key_len ref rows Extra 2381 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 Using where 2391 PRIMARY <derived2> ref <auto_key0> <auto_key0> 4 test.t1.E1 2 NULL 2402 DERIVED A ALL NULL NULL NULL NULL 2 Using where 2413 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where 242drop table t1; 243create table t1 (a int); 244insert into t1 values (1),(2); 245select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 246a a 2471 1 2482 1 2491 2 2502 2 251explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; 252id select_type table type possible_keys key key_len ref rows Extra 2531 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 NULL 2541 PRIMARY <derived4> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 2554 DERIVED t1 ALL NULL NULL NULL NULL 2 NULL 2565 UNION t1 ALL NULL NULL NULL NULL 2 NULL 257NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL Using temporary 2582 DERIVED t1 ALL NULL NULL NULL NULL 2 NULL 2593 UNION t1 ALL NULL NULL NULL NULL 2 NULL 260NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Using temporary 261drop table t1; 262CREATE TABLE `t1` ( 263`N` int(11) unsigned NOT NULL default '0', 264`M` tinyint(1) default '0' 265) ENGINE=MyISAM DEFAULT CHARSET=latin1; 266INSERT INTO `t1` (N, M) VALUES (1, 0),(1, 0),(1, 0),(2, 0),(2, 0),(3, 0); 267UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; 268select * from t1; 269N M 2701 2 2711 2 2721 2 2732 2 2742 2 2753 0 276UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2; 277ERROR HY000: The target table P2 of the UPDATE is not updatable 278UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2; 279ERROR 42S22: Unknown column 'aaaa' in 'field list' 280delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; 281select * from t1; 282N M 2833 0 284delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N; 285ERROR HY000: The target table p2 of the DELETE is not updatable 286delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; 287ERROR 42S22: Unknown column 'aaa' in 'field list' 288drop table t1; 289CREATE TABLE t1 ( 290OBJECTID int(11) NOT NULL default '0', 291SORTORDER int(11) NOT NULL auto_increment, 292KEY t1_SortIndex (SORTORDER), 293KEY t1_IdIndex (OBJECTID) 294) ENGINE=MyISAM DEFAULT CHARSET=latin1; 295CREATE TABLE t2 ( 296ID int(11) default NULL, 297PARID int(11) default NULL, 298UNIQUE KEY t2_ID_IDX (ID), 299KEY t2_PARID_IDX (PARID) 300) engine=MyISAM DEFAULT CHARSET=latin1; 301INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2); 302CREATE TABLE t3 ( 303ID int(11) default NULL, 304DATA decimal(10,2) default NULL, 305UNIQUE KEY t3_ID_IDX (ID) 306) engine=MyISAM DEFAULT CHARSET=latin1; 307INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75); 308select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP; 309497 ID NULL 310drop table t1, t2, t3; 311CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL); 312INSERT INTO t1 VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5); 313SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; 314name median 315a 7.0000 316b 3.5000 317explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; 318id select_type table type possible_keys key key_len ref rows Extra 3191 PRIMARY <derived2> ALL NULL NULL NULL NULL 289 Using temporary; Using filesort 3202 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort 3212 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer (Block Nested Loop) 322drop table t1; 323create table t2 (a int, b int, primary key (a)); 324insert into t2 values (1,7),(2,7); 325explain select a from t2 where a>1; 326id select_type table type possible_keys key key_len ref rows Extra 3271 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index 328explain select a from (select a from t2 where a>1) tt; 329id select_type table type possible_keys key key_len ref rows Extra 3301 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL 3312 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index 332drop table t2; 333CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); 334insert into t1 values (128, 'rozn', 2, curdate(), 10), 335(128, 'rozn', 1, curdate(), 10); 336SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; 337min max avg 33810.00 10.00 10 339DROP TABLE t1; 340create table t1 (a integer, b integer); 341insert into t1 values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1); 342select distinct sum(b) from t1 group by a; 343sum(b) 3444 345select distinct sum(b) from (select a,b from t1) y group by a; 346sum(b) 3474 348drop table t1; 349CREATE TABLE t1 (a char(10), b char(10)); 350INSERT INTO t1 VALUES ('root','localhost'), ('root','%'); 351SELECT * FROM (SELECT (SELECT a.a FROM t1 AS a WHERE a.a = b.a) FROM t1 AS b) AS c; 352ERROR 21000: Subquery returns more than 1 row 353DROP TABLE t1; 354create table t1(a int); 355create table t2(a int); 356create table t3(a int); 357insert into t1 values(1),(1); 358insert into t2 values(2),(2); 359insert into t3 values(3),(3); 360select * from t1 union distinct select * from t2 union all select * from t3; 361a 3621 3632 3643 3653 366select * from (select * from t1 union distinct select * from t2 union all select * from t3) X; 367a 3681 3692 3703 3713 372drop table t1, t2, t3; 373create table t1 (a int); 374create table t2 (a int); 375select * from (select * from t1,t2) foo; 376ERROR 42S21: Duplicate column name 'a' 377drop table t1,t2; 378create table t1 (ID int unsigned not null auto_increment, 379DATA varchar(5) not null, primary key (ID)); 380create table t2 (ID int unsigned not null auto_increment, 381DATA varchar(5) not null, FID int unsigned not null, 382primary key (ID)); 383select A.* from (t1 inner join (select * from t2) as A on t1.ID = A.FID); 384ID DATA FID 385select t2.* from ((select * from t1) as A inner join t2 on A.ID = t2.FID); 386ID DATA FID 387select t2.* from (select * from t1) as A inner join t2 on A.ID = t2.FID; 388ID DATA FID 389drop table t1, t2; 390drop user mysqltest_1; 391# End of 4.1 tests 392SELECT 0 FROM 393(SELECT 0) t01, (SELECT 0) t02, (SELECT 0) t03, (SELECT 0) t04, (SELECT 0) t05, 394(SELECT 0) t06, (SELECT 0) t07, (SELECT 0) t08, (SELECT 0) t09, (SELECT 0) t10, 395(SELECT 0) t11, (SELECT 0) t12, (SELECT 0) t13, (SELECT 0) t14, (SELECT 0) t15, 396(SELECT 0) t16, (SELECT 0) t17, (SELECT 0) t18, (SELECT 0) t19, (SELECT 0) t20, 397(SELECT 0) t21, (SELECT 0) t22, (SELECT 0) t23, (SELECT 0) t24, (SELECT 0) t25, 398(SELECT 0) t26, (SELECT 0) t27, (SELECT 0) t28, (SELECT 0) t29, (SELECT 0) t30, 399(SELECT 0) t31, (SELECT 0) t32, (SELECT 0) t33, (SELECT 0) t34, (SELECT 0) t35, 400(SELECT 0) t36, (SELECT 0) t37, (SELECT 0) t38, (SELECT 0) t39, (SELECT 0) t40, 401(SELECT 0) t41, (SELECT 0) t42, (SELECT 0) t43, (SELECT 0) t44, (SELECT 0) t45, 402(SELECT 0) t46, (SELECT 0) t47, (SELECT 0) t48, (SELECT 0) t49, (SELECT 0) t50, 403(SELECT 0) t51, (SELECT 0) t52, (SELECT 0) t53, (SELECT 0) t54, (SELECT 0) t55, 404(SELECT 0) t56, (SELECT 0) t57, (SELECT 0) t58, (SELECT 0) t59, (SELECT 0) t60, 405(SELECT 0) t61; 4060 4070 408# 409# A nested materialized derived table is used before being populated. 410# (addon for bug#19077) 411# 412CREATE TABLE t1 (i INT, j BIGINT); 413INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); 414SELECT * FROM (SELECT MIN(i) FROM t1 415WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3; 416MIN(i) 4171 418DROP TABLE t1; 419# End of 5.0 tests 420# 421# Bug#55586: Crash JOIN of two subqueries in FROM + ORDER BY and GROUP BY 422# 423CREATE TABLE C ( 424`col_int_key` int(11) DEFAULT NULL, 425`col_varchar_key` varchar(1) DEFAULT NULL, 426`col_varchar_nokey` varchar(1) DEFAULT NULL, 427KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 428); 429INSERT INTO C VALUES (2,'w','w'); 430INSERT INTO C VALUES (2,'d','d'); 431SELECT SUM(DISTINCT table2.col_int_key) field1, 432table1.col_varchar_key field2 433FROM 434(SELECT * FROM C ) table1 435JOIN (SELECT * FROM C ) table2 436ON table2 .`col_varchar_key` = table1 .`col_varchar_nokey` 437GROUP BY field2 438ORDER BY field1; 439field1 field2 4402 d 4412 w 442DROP TABLE C; 443# End of test for bug#55586 444# 445# Bug#55561: Crash on JOIN with 2 FROM subqueries 446# 447CREATE TABLE C ( 448col_int int DEFAULT NULL, 449col_varchar varchar(1) DEFAULT NULL 450); 451INSERT INTO `C` VALUES (0,NULL); 452INSERT INTO `C` VALUES (5,'y'); 453SELECT table1.col_varchar 454FROM 455( SELECT * FROM C ) table1 456JOIN ( SELECT * FROM C ) table2 ON table2.col_varchar = table1.col_varchar 457WHERE 458table2.col_varchar < table2.col_varchar 459AND table1.col_varchar != 'k' 460LIMIT 1; 461col_varchar 462DROP TABLE C; 463# End on bug#55561 464# 465# Bug#56233: Hang during key generation for derived tables 466# 467CREATE TABLE C ( 468col_varchar_10_key varchar(10) DEFAULT NULL, 469col_int_key int DEFAULT NULL, 470pk int NOT NULL AUTO_INCREMENT, 471col_date_key date DEFAULT NULL, 472PRIMARY KEY (`pk`), 473KEY `col_varchar_10_key` (`col_varchar_10_key`), 474KEY `col_int_key` (`col_int_key`), 475KEY `col_date_key` (`col_date_key`) 476); 477INSERT INTO C VALUES ('ok',3,1,'2003-04-02'); 478CREATE ALGORITHM=TEMPTABLE VIEW viewC AS SELECT * FROM C; 479SELECT table1.col_date_key AS field1 480FROM 481C AS table1 482WHERE 483(table1.col_int_key <=ANY 484( SELECT SUBQUERY1_t1.col_int_key 485FROM viewC AS SUBQUERY1_t1 486WHERE SUBQUERY1_t1.col_varchar_10_key <= table1.col_varchar_10_key 487) 488) 489; 490field1 4912003-04-02 492DROP TABLE C; 493DROP VIEW viewC; 494# 495# 496# Bug#55950: FROM Subquery joined by 2 varchar fields returns empty 497# set 498# 499CREATE TABLE `CC` ( 500`i1` varchar(1) DEFAULT NULL, 501`i2` varchar(1) DEFAULT NULL 502); 503INSERT INTO `CC` VALUES ('m','m'); 504INSERT INTO `CC` VALUES ('c','c'); 505CREATE TABLE `C` ( 506`o1` varchar(1) DEFAULT NULL 507); 508INSERT INTO `C` VALUES ('m'); 509SELECT table1 . o1 510FROM C table1 511JOIN ( C table2 512JOIN ( SELECT * FROM CC ) table3 513ON table3 .`i1` = table2 .o1 514) ON table3 .`i2` = table2 .o1 515; 516o1 517m 518# Ref access to the derived table should be used. 519EXPLAIN SELECT table1 . o1 520FROM C table1 521JOIN ( C table2 522JOIN ( SELECT * FROM CC ) table3 523ON table3 .`i1` = table2 .o1 524) ON table3 .`i2` = table2 .o1 525; 526id select_type table type possible_keys key key_len ref rows Extra 5271 PRIMARY table1 system NULL NULL NULL NULL 1 NULL 5281 PRIMARY table2 system NULL NULL NULL NULL 1 NULL 5291 PRIMARY <derived2> ref <auto_key0> <auto_key0> 8 const,const 0 Using index 5302 DERIVED CC ALL NULL NULL NULL NULL 2 NULL 531DROP TABLE CC; 532DROP TABLE C; 533# End of test for bug#55950 534# 535# Bug#56592: Subquery with DISTINCT in FROM clause returns only partial 536# result 537# 538CREATE TABLE `t1` ( 539`pk` int(11) NOT NULL, 540`col_int_key` int(11) DEFAULT NULL, 541`col_datetime_key` datetime DEFAULT NULL 542) ENGINE=MyISAM; 543INSERT INTO t1 VALUES (2, 9, NULL), (3, 3, '1900-01-01 00:00:00'), 544(8, 8, '1900-01-01 00:00:00'), (15, 0, '2007-12-15 12:39:34'); 545SELECT * FROM ( 546SELECT DISTINCT tableB.col_datetime_key 547FROM t1 tableA LEFT JOIN t1 tableB ON tableA.pk < tableB.col_int_key 548) AS FROM_SUBQUERY; 549col_datetime_key 550NULL 5511900-01-01 00:00:00 552EXPLAIN SELECT * FROM ( 553SELECT DISTINCT tableB.col_datetime_key 554FROM t1 tableA LEFT JOIN t1 tableB ON tableA.pk < tableB.col_int_key 555) AS FROM_SUBQUERY; 556id select_type table type possible_keys key key_len ref rows Extra 5571 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 NULL 5582 DERIVED tableA ALL NULL NULL NULL NULL 4 Using temporary 5592 DERIVED tableB ALL NULL NULL NULL NULL 4 Using where; Distinct; Using join buffer (Block Nested Loop) 560EXPLAIN SELECT * FROM ( 561SELECT DISTINCT tableA.col_datetime_key 562FROM t1 tableA LEFT JOIN t1 tableB ON tableA.pk < tableB.col_int_key 563) AS FROM_SUBQUERY; 564id select_type table type possible_keys key key_len ref rows Extra 5651 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 NULL 5662 DERIVED tableA ALL NULL NULL NULL NULL 4 Using temporary 5672 DERIVED tableB ALL NULL NULL NULL NULL 4 Using where; Distinct; Using join buffer (Block Nested Loop) 568DROP TABLE t1; 569# 570# Bug#58730 Assertion failed: table->key_read == 0 in close_thread_table, 571# temptable views 572# 573CREATE TABLE t1 (a INT); 574CREATE TABLE t2 (b INT, KEY (b)); 575INSERT INTO t1 VALUES (1),(1); 576INSERT INTO t2 VALUES (1),(1); 577CREATE algorithm=temptable VIEW v1 AS 578SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1); 579CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2; 580EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2); 581ERROR 21000: Subquery returns more than 1 row 582DROP TABLE t1, t2; 583DROP VIEW v1, v2; 584# 585# WL#5274: Postpone materialization of views/subqueries in FROM clause. 586# Additional tests. 587# 588CREATE TABLE t1(f1 int, f11 int); 589CREATE TABLE t2(f2 int, f22 int); 590INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); 591INSERT INTO t2 VALUES(1,1),(3,3),(2,2),(4,4),(8,8),(6,6); 592for merged derived tables 593explain for simple derived 594EXPLAIN SELECT * FROM (SELECT * FROM t1) tt; 595id select_type table type possible_keys key key_len ref rows Extra 5961 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 NULL 5972 DERIVED t1 ALL NULL NULL NULL NULL 6 NULL 598SELECT * FROM (SELECT * FROM t1) tt; 599f1 f11 6001 1 6012 2 6023 3 6035 5 6049 9 6057 7 606explain for multitable derived 607EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 JOIN t2 ON f1=f2) tt; 608id select_type table type possible_keys key key_len ref rows filtered Extra 6091 PRIMARY <derived2> ALL NULL NULL NULL NULL 36 100.00 NULL 6102 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 NULL 6112 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (Block Nested Loop) 612Warnings: 613Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from (/* select#2 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`f2` = `test`.`t1`.`f1`)) `tt` 614SELECT * FROM (SELECT * FROM t1 JOIN t2 ON f1=f2) tt; 615f1 f11 f2 f22 6161 1 1 1 6173 3 3 3 6182 2 2 2 619explain for derived with where 620FLUSH STATUS; 621EXPLAIN EXTENDED 622SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt WHERE f11=2; 623id select_type table type possible_keys key key_len ref rows filtered Extra 6241 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 const 0 100.00 NULL 6252 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 626Warnings: 627Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#2 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` in (2,3))) `tt` where (`tt`.`f11` = 2) 628SHOW STATUS LIKE 'Handler_read%'; 629Variable_name Value 630Handler_read_first 0 631Handler_read_key 0 632Handler_read_last 0 633Handler_read_next 0 634Handler_read_prev 0 635Handler_read_rnd 0 636Handler_read_rnd_next 0 637FLUSH STATUS; 638SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt WHERE f11=2; 639f1 f11 6402 2 641SHOW STATUS LIKE 'Handler_read%'; 642Variable_name Value 643Handler_read_first 0 644Handler_read_key 1 645Handler_read_last 0 646Handler_read_next 1 647Handler_read_prev 0 648Handler_read_rnd 0 649Handler_read_rnd_next 7 650join of derived 651EXPLAIN EXTENDED 652SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt JOIN 653(SELECT * FROM t1 WHERE f1 IN (1,2)) aa ON tt.f1=aa.f1; 654id select_type table type possible_keys key key_len ref rows filtered Extra 6551 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where 6561 PRIMARY <derived3> ref <auto_key0> <auto_key0> 5 tt.f1 2 100.00 NULL 6573 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 6582 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 659Warnings: 660Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`aa`.`f1` AS `f1`,`aa`.`f11` AS `f11` from (/* select#2 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` in (2,3))) `tt` join (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` in (1,2))) `aa` where (`aa`.`f1` = `tt`.`f1`) 661SELECT * FROM (SELECT * FROM t1 WHERE f1 IN (2,3)) tt JOIN 662(SELECT * FROM t1 WHERE f1 IN (1,2)) aa ON tt.f1=aa.f1; 663f1 f11 f1 f11 6642 2 2 2 665for merged views 666CREATE VIEW v1 AS SELECT * FROM t1; 667CREATE VIEW v2 AS SELECT * FROM t1 JOIN t2 ON f1=f2; 668CREATE VIEW v3 AS SELECT * FROM t1 WHERE f1 IN (2,3); 669CREATE VIEW v4 AS SELECT * FROM t2 WHERE f2 IN (2,3); 670explain for simple views 671EXPLAIN EXTENDED SELECT * FROM v1; 672id select_type table type possible_keys key key_len ref rows filtered Extra 6731 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 NULL 674Warnings: 675Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` 676SELECT * FROM v1; 677f1 f11 6781 1 6792 2 6803 3 6815 5 6829 9 6837 7 684explain for multitable views 685EXPLAIN EXTENDED SELECT * FROM v2; 686id select_type table type possible_keys key key_len ref rows filtered Extra 6871 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 NULL 6881 SIMPLE t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (Block Nested Loop) 689Warnings: 690Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`f2` = `test`.`t1`.`f1`) 691SELECT * FROM v2; 692f1 f11 f2 f22 6931 1 1 1 6943 3 3 3 6952 2 2 2 696explain for views with where 697EXPLAIN EXTENDED SELECT * FROM v3 WHERE f11 IN (1,3); 698id select_type table type possible_keys key key_len ref rows filtered Extra 6991 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where 700Warnings: 701Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f11` in (1,3)) and (`test`.`t1`.`f1` in (2,3))) 702SELECT * FROM v3 WHERE f11 IN (1,3); 703f1 f11 7043 3 705explain for joined views 706EXPLAIN EXTENDED 707SELECT * FROM v3 JOIN v4 ON f1=f2; 708id select_type table type possible_keys key key_len ref rows filtered Extra 7091 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where 7101 SIMPLE t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (Block Nested Loop) 711Warnings: 712Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f2` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` in (2,3)) and (`test`.`t1`.`f1` in (2,3))) 713SELECT * FROM v3 JOIN v4 ON f1=f2; 714f1 f11 f2 f22 7153 3 3 3 7162 2 2 2 717FLUSH STATUS; 718EXPLAIN EXTENDED SELECT * FROM v4 WHERE f2 IN (1,3); 719id select_type table type possible_keys key key_len ref rows filtered Extra 7201 SIMPLE t2 ALL NULL NULL NULL NULL 6 100.00 Using where 721Warnings: 722Note 1003 /* select#1 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where ((`test`.`t2`.`f2` in (1,3)) and (`test`.`t2`.`f2` in (2,3))) 723SHOW STATUS LIKE 'Handler_read%'; 724Variable_name Value 725Handler_read_first 0 726Handler_read_key 0 727Handler_read_last 0 728Handler_read_next 0 729Handler_read_prev 0 730Handler_read_rnd 0 731Handler_read_rnd_next 0 732FLUSH STATUS; 733SELECT * FROM v4 WHERE f2 IN (1,3); 734f2 f22 7353 3 736SHOW STATUS LIKE 'Handler_read%'; 737Variable_name Value 738Handler_read_first 0 739Handler_read_key 0 740Handler_read_last 0 741Handler_read_next 0 742Handler_read_prev 0 743Handler_read_rnd 0 744Handler_read_rnd_next 7 745for materialized derived tables 746explain for simple derived 747EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM t1 GROUP BY f1) tt; 748id select_type table type possible_keys key key_len ref rows filtered Extra 7491 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 7502 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 751Warnings: 752Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#2 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` group by `test`.`t1`.`f1`) `tt` 753SELECT * FROM (SELECT * FROM t1 HAVING f1=f1) tt; 754f1 f11 7551 1 7562 2 7573 3 7585 5 7599 9 7607 7 761explain showing created indexes and late materialization 762FLUSH STATUS; 763EXPLAIN EXTENDED 764SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2; 765id select_type table type possible_keys key key_len ref rows filtered Extra 7661 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 7671 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t1.f1 2 100.00 NULL 7682 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 769Warnings: 770Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` group by `test`.`t2`.`f2`) `tt` where (`tt`.`f2` = `test`.`t1`.`f1`) 771SHOW STATUS LIKE 'Handler_read%'; 772Variable_name Value 773Handler_read_first 0 774Handler_read_key 0 775Handler_read_last 0 776Handler_read_next 0 777Handler_read_prev 0 778Handler_read_rnd 0 779Handler_read_rnd_next 0 780FLUSH STATUS; 781SELECT * FROM t1 JOIN (SELECT * FROM t2 GROUP BY f2) tt ON f1=f2; 782f1 f11 f2 f22 7831 1 1 1 7842 2 2 2 7853 3 3 3 786SHOW STATUS LIKE 'Handler_read%'; 787Variable_name Value 788Handler_read_first 0 789Handler_read_key 6 790Handler_read_last 0 791Handler_read_next 3 792Handler_read_prev 0 793Handler_read_rnd 6 794Handler_read_rnd_next 21 795for materialized views 796DROP VIEW v1,v2,v3; 797CREATE VIEW v1 AS SELECT * FROM t1 GROUP BY f1; 798CREATE VIEW v2 AS SELECT * FROM t2 GROUP BY f2; 799CREATE VIEW v3 AS SELECT t1.f1,t1.f11 FROM t1 JOIN t1 AS t11 HAVING t1.f1<100; 800explain for simple derived 801EXPLAIN EXTENDED SELECT * FROM v1; 802id select_type table type possible_keys key key_len ref rows filtered Extra 8031 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 8042 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 805Warnings: 806Note 1003 /* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` 807SELECT * FROM v1; 808f1 f11 8091 1 8102 2 8113 3 8125 5 8137 7 8149 9 815explain showing created indexes and late materialization for views 816FLUSH STATUS; 817EXPLAIN EXTENDED SELECT * FROM t1 JOIN v2 ON f1=f2; 818id select_type table type possible_keys key key_len ref rows filtered Extra 8191 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8201 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t1.f1 2 100.00 NULL 8212 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 822Warnings: 823Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v2`.`f2` AS `f2`,`v2`.`f22` AS `f22` from `test`.`t1` join `test`.`v2` where (`v2`.`f2` = `test`.`t1`.`f1`) 824SHOW STATUS LIKE 'Handler_read%'; 825Variable_name Value 826Handler_read_first 0 827Handler_read_key 0 828Handler_read_last 0 829Handler_read_next 0 830Handler_read_prev 0 831Handler_read_rnd 0 832Handler_read_rnd_next 0 833FLUSH STATUS; 834SELECT * FROM t1 JOIN v2 ON f1=f2; 835f1 f11 f2 f22 8361 1 1 1 8372 2 2 2 8383 3 3 3 839SHOW STATUS LIKE 'Handler_read%'; 840Variable_name Value 841Handler_read_first 0 842Handler_read_key 6 843Handler_read_last 0 844Handler_read_next 3 845Handler_read_prev 0 846Handler_read_rnd 6 847Handler_read_rnd_next 21 848EXPLAIN EXTENDED 849SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1; 850id select_type table type possible_keys key key_len ref rows filtered Extra 8511 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 8521 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t1.f1 3 100.00 NULL 8531 PRIMARY <derived3> ref <auto_key0> <auto_key0> 5 test.t1.f1 3 100.00 NULL 8543 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 NULL 8553 DERIVED t11 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (Block Nested Loop) 8562 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 NULL 8572 DERIVED t11 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (Block Nested Loop) 858Warnings: 859Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v31`.`f1` AS `f1`,`v31`.`f11` AS `f11`,`v3`.`f1` AS `f1`,`v3`.`f11` AS `f11` from `test`.`t1` join `test`.`v3` `v31` join `test`.`v3` where ((`v31`.`f1` = `test`.`t1`.`f1`) and (`v3`.`f1` = `test`.`t1`.`f1`)) 860FLUSH STATUS; 861SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1; 862f1 f11 f1 f11 f1 f11 8631 1 1 1 1 1 8641 1 1 1 1 1 8651 1 1 1 1 1 8661 1 1 1 1 1 8671 1 1 1 1 1 8681 1 1 1 1 1 8691 1 1 1 1 1 8701 1 1 1 1 1 8711 1 1 1 1 1 8721 1 1 1 1 1 8731 1 1 1 1 1 8741 1 1 1 1 1 8751 1 1 1 1 1 8761 1 1 1 1 1 8771 1 1 1 1 1 8781 1 1 1 1 1 8791 1 1 1 1 1 8801 1 1 1 1 1 8811 1 1 1 1 1 8821 1 1 1 1 1 8831 1 1 1 1 1 8841 1 1 1 1 1 8851 1 1 1 1 1 8861 1 1 1 1 1 8871 1 1 1 1 1 8881 1 1 1 1 1 8891 1 1 1 1 1 8901 1 1 1 1 1 8911 1 1 1 1 1 8921 1 1 1 1 1 8931 1 1 1 1 1 8941 1 1 1 1 1 8951 1 1 1 1 1 8961 1 1 1 1 1 8971 1 1 1 1 1 8981 1 1 1 1 1 8992 2 2 2 2 2 9002 2 2 2 2 2 9012 2 2 2 2 2 9022 2 2 2 2 2 9032 2 2 2 2 2 9042 2 2 2 2 2 9052 2 2 2 2 2 9062 2 2 2 2 2 9072 2 2 2 2 2 9082 2 2 2 2 2 9092 2 2 2 2 2 9102 2 2 2 2 2 9112 2 2 2 2 2 9122 2 2 2 2 2 9132 2 2 2 2 2 9142 2 2 2 2 2 9152 2 2 2 2 2 9162 2 2 2 2 2 9172 2 2 2 2 2 9182 2 2 2 2 2 9192 2 2 2 2 2 9202 2 2 2 2 2 9212 2 2 2 2 2 9222 2 2 2 2 2 9232 2 2 2 2 2 9242 2 2 2 2 2 9252 2 2 2 2 2 9262 2 2 2 2 2 9272 2 2 2 2 2 9282 2 2 2 2 2 9292 2 2 2 2 2 9302 2 2 2 2 2 9312 2 2 2 2 2 9322 2 2 2 2 2 9332 2 2 2 2 2 9342 2 2 2 2 2 9353 3 3 3 3 3 9363 3 3 3 3 3 9373 3 3 3 3 3 9383 3 3 3 3 3 9393 3 3 3 3 3 9403 3 3 3 3 3 9413 3 3 3 3 3 9423 3 3 3 3 3 9433 3 3 3 3 3 9443 3 3 3 3 3 9453 3 3 3 3 3 9463 3 3 3 3 3 9473 3 3 3 3 3 9483 3 3 3 3 3 9493 3 3 3 3 3 9503 3 3 3 3 3 9513 3 3 3 3 3 9523 3 3 3 3 3 9533 3 3 3 3 3 9543 3 3 3 3 3 9553 3 3 3 3 3 9563 3 3 3 3 3 9573 3 3 3 3 3 9583 3 3 3 3 3 9593 3 3 3 3 3 9603 3 3 3 3 3 9613 3 3 3 3 3 9623 3 3 3 3 3 9633 3 3 3 3 3 9643 3 3 3 3 3 9653 3 3 3 3 3 9663 3 3 3 3 3 9673 3 3 3 3 3 9683 3 3 3 3 3 9693 3 3 3 3 3 9703 3 3 3 3 3 9715 5 5 5 5 5 9725 5 5 5 5 5 9735 5 5 5 5 5 9745 5 5 5 5 5 9755 5 5 5 5 5 9765 5 5 5 5 5 9775 5 5 5 5 5 9785 5 5 5 5 5 9795 5 5 5 5 5 9805 5 5 5 5 5 9815 5 5 5 5 5 9825 5 5 5 5 5 9835 5 5 5 5 5 9845 5 5 5 5 5 9855 5 5 5 5 5 9865 5 5 5 5 5 9875 5 5 5 5 5 9885 5 5 5 5 5 9895 5 5 5 5 5 9905 5 5 5 5 5 9915 5 5 5 5 5 9925 5 5 5 5 5 9935 5 5 5 5 5 9945 5 5 5 5 5 9955 5 5 5 5 5 9965 5 5 5 5 5 9975 5 5 5 5 5 9985 5 5 5 5 5 9995 5 5 5 5 5 10005 5 5 5 5 5 10015 5 5 5 5 5 10025 5 5 5 5 5 10035 5 5 5 5 5 10045 5 5 5 5 5 10055 5 5 5 5 5 10065 5 5 5 5 5 10079 9 9 9 9 9 10089 9 9 9 9 9 10099 9 9 9 9 9 10109 9 9 9 9 9 10119 9 9 9 9 9 10129 9 9 9 9 9 10139 9 9 9 9 9 10149 9 9 9 9 9 10159 9 9 9 9 9 10169 9 9 9 9 9 10179 9 9 9 9 9 10189 9 9 9 9 9 10199 9 9 9 9 9 10209 9 9 9 9 9 10219 9 9 9 9 9 10229 9 9 9 9 9 10239 9 9 9 9 9 10249 9 9 9 9 9 10259 9 9 9 9 9 10269 9 9 9 9 9 10279 9 9 9 9 9 10289 9 9 9 9 9 10299 9 9 9 9 9 10309 9 9 9 9 9 10319 9 9 9 9 9 10329 9 9 9 9 9 10339 9 9 9 9 9 10349 9 9 9 9 9 10359 9 9 9 9 9 10369 9 9 9 9 9 10379 9 9 9 9 9 10389 9 9 9 9 9 10399 9 9 9 9 9 10409 9 9 9 9 9 10419 9 9 9 9 9 10429 9 9 9 9 9 10437 7 7 7 7 7 10447 7 7 7 7 7 10457 7 7 7 7 7 10467 7 7 7 7 7 10477 7 7 7 7 7 10487 7 7 7 7 7 10497 7 7 7 7 7 10507 7 7 7 7 7 10517 7 7 7 7 7 10527 7 7 7 7 7 10537 7 7 7 7 7 10547 7 7 7 7 7 10557 7 7 7 7 7 10567 7 7 7 7 7 10577 7 7 7 7 7 10587 7 7 7 7 7 10597 7 7 7 7 7 10607 7 7 7 7 7 10617 7 7 7 7 7 10627 7 7 7 7 7 10637 7 7 7 7 7 10647 7 7 7 7 7 10657 7 7 7 7 7 10667 7 7 7 7 7 10677 7 7 7 7 7 10687 7 7 7 7 7 10697 7 7 7 7 7 10707 7 7 7 7 7 10717 7 7 7 7 7 10727 7 7 7 7 7 10737 7 7 7 7 7 10747 7 7 7 7 7 10757 7 7 7 7 7 10767 7 7 7 7 7 10777 7 7 7 7 7 10787 7 7 7 7 7 1079SHOW STATUS LIKE 'Handler_read%'; 1080Variable_name Value 1081Handler_read_first 0 1082Handler_read_key 42 1083Handler_read_last 0 1084Handler_read_next 252 1085Handler_read_prev 0 1086Handler_read_rnd 0 1087Handler_read_rnd_next 35 1088explain showing late materialization for views 1089EXPLAIN EXTENDED SELECT * FROM v1 JOIN v4 ON f1=f2; 1090id select_type table type possible_keys key key_len ref rows filtered Extra 10911 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where 10921 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t2.f2 2 100.00 NULL 10932 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 1094Warnings: 1095Note 1003 /* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` in (2,3))) 1096SELECT * FROM v1 JOIN v4 ON f1=f2; 1097f1 f11 f2 f22 10983 3 3 3 10992 2 2 2 1100merged derived in merged derived 1101EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM 1102(SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2) zz; 1103id select_type table type possible_keys key key_len ref rows filtered Extra 11041 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 11052 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where 11063 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 1107Warnings: 1108Note 1003 /* select#1 */ select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7)) `tt` where (`tt`.`f1` > 2)) `zz` 1109SELECT * FROM (SELECT * FROM 1110(SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2) zz; 1111f1 f11 11123 3 11135 5 1114materialized derived in merged derived 1115EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM 1116(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) zz; 1117id select_type table type possible_keys key key_len ref rows filtered Extra 11181 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 11192 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where 11203 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 1121Warnings: 1122Note 1003 /* select#1 */ select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2)) `zz` 1123SELECT * FROM (SELECT * FROM 1124(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) zz; 1125f1 f11 11263 3 11275 5 1128merged derived in materialized derived 1129EXPLAIN extended SELECT * FROM (SELECT * FROM 1130(SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2 GROUP BY f1) zz; 1131id select_type table type possible_keys key key_len ref rows filtered Extra 11321 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 11332 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 11343 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 1135Warnings: 1136Note 1003 /* select#1 */ select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7)) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `zz` 1137SELECT * FROM (SELECT * FROM 1138(SELECT * FROM t1 WHERE f1 < 7) tt WHERE f1 > 2 GROUP BY f1) zz; 1139f1 f11 11403 3 11415 5 1142materialized derived in materialized derived 1143EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM 1144(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) zz; 1145id select_type table type possible_keys key key_len ref rows filtered Extra 11461 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 11472 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 11483 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 1149Warnings: 1150Note 1003 /* select#1 */ select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `zz` 1151SELECT * FROM (SELECT * FROM 1152(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) zz; 1153f1 f11 11543 3 11555 5 1156mat in merged derived join mat in merged derived 1157EXPLAIN EXTENDED SELECT * FROM 1158(SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) x 1159JOIN 1160(SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) z 1161ON x.f1 = z.f1; 1162id select_type table type possible_keys key key_len ref rows filtered Extra 11631 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where 11641 PRIMARY <derived4> ref <auto_key0> <auto_key0> 5 x.f1 2 100.00 NULL 11654 DERIVED <derived5> ALL NULL NULL NULL NULL 6 100.00 Using where 11665 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 11672 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where 11683 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 1169Warnings: 1170Note 1003 /* select#1 */ select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2)) `x` join (/* select#4 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `t1`.`f1`) `tt` where (`tt`.`f1` > 2)) `z` where (`z`.`f1` = `x`.`f1`) 1171FLUSH STATUS; 1172SELECT * FROM 1173(SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) x 1174JOIN 1175(SELECT * FROM (SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2) z 1176ON x.f1 = z.f1; 1177f1 f11 f1 f11 11783 3 3 3 11795 5 5 5 1180SHOW STATUS LIKE 'Handler_read%'; 1181Variable_name Value 1182Handler_read_first 0 1183Handler_read_key 2 1184Handler_read_last 0 1185Handler_read_next 2 1186Handler_read_prev 0 1187Handler_read_rnd 8 1188Handler_read_rnd_next 37 1189FLUSH STATUS; 1190merged in merged derived join merged in merged derived 1191EXPLAIN EXTENDED SELECT * FROM 1192(SELECT * FROM 1193(SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) x 1194JOIN 1195(SELECT * FROM 1196(SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) z 1197ON x.f1 = z.f1; 1198id select_type table type possible_keys key key_len ref rows filtered Extra 11991 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where 12001 PRIMARY <derived4> ref <auto_key0> <auto_key0> 5 x.f1 2 100.00 NULL 12014 DERIVED <derived5> ALL NULL NULL NULL NULL 6 100.00 Using where 12025 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 12032 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where 12043 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where 1205Warnings: 1206Note 1003 /* select#1 */ select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7)) `tt` where (`tt`.`f1` > 2)) `x` join (/* select#4 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7)) `tt` where (`tt`.`f1` > 2)) `z` where (`z`.`f1` = `x`.`f1`) 1207SELECT * FROM 1208(SELECT * FROM 1209(SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) x 1210JOIN 1211(SELECT * FROM 1212(SELECT * FROM t1 WHERE f1 < 7 ) tt WHERE f1 > 2 ) z 1213ON x.f1 = z.f1; 1214f1 f11 f1 f11 12153 3 3 3 12165 5 5 5 1217materialized in materialized derived join 1218materialized in materialized derived 1219EXPLAIN EXTENDED SELECT * FROM 1220(SELECT * FROM 1221(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) x 1222JOIN 1223(SELECT * FROM 1224(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) z 1225ON x.f1 = z.f1; 1226id select_type table type possible_keys key key_len ref rows filtered Extra 12271 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 Using where 12281 PRIMARY <derived4> ref <auto_key0> <auto_key0> 5 x.f1 2 100.00 NULL 12294 DERIVED <derived5> ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 12305 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 12312 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 12323 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 1233Warnings: 1234Note 1003 /* select#1 */ select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (/* select#2 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#3 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `x` join (/* select#4 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#5 */ select `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `z` where (`z`.`f1` = `x`.`f1`) 1235SELECT * FROM 1236(SELECT * FROM 1237(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) x 1238JOIN 1239(SELECT * FROM 1240(SELECT * FROM t1 WHERE f1 < 7 GROUP BY f1) tt WHERE f1 > 2 GROUP BY f1) z 1241ON x.f1 = z.f1; 1242f1 f11 f1 f11 12433 3 3 3 12445 5 5 5 1245merged view in materialized derived 1246EXPLAIN EXTENDED 1247SELECT * FROM (SELECT * FROM v4 GROUP BY 1) tt; 1248id select_type table type possible_keys key key_len ref rows filtered Extra 12491 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 12502 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using temporary; Using filesort 1251Warnings: 1252Note 1003 /* select#1 */ select `tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where (`test`.`t2`.`f2` in (2,3)) group by 1) `tt` 1253SELECT * FROM (SELECT * FROM v4 GROUP BY 1) tt; 1254f2 f22 12552 2 12563 3 1257materialized view in merged derived 1258EXPLAIN EXTENDED 1259SELECT * FROM ( SELECT * FROM v1 WHERE f1 < 7) tt; 1260id select_type table type possible_keys key key_len ref rows filtered Extra 12611 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 12622 DERIVED <derived3> ALL NULL NULL NULL NULL 6 100.00 Using where 12633 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 1264Warnings: 1265Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#2 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` where (`v1`.`f1` < 7)) `tt` 1266SELECT * FROM ( SELECT * FROM v1 WHERE f1 < 7) tt; 1267f1 f11 12681 1 12692 2 12703 3 12715 5 1272merged view in a merged view in a merged derived 1273CREATE VIEW v6 AS SELECT * FROM v4 WHERE f2 < 7; 1274EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM v6) tt; 1275id select_type table type possible_keys key key_len ref rows filtered Extra 12761 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 12772 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using where 1278Warnings: 1279Note 1003 /* select#1 */ select `tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where ((`test`.`t2`.`f2` in (2,3)) and (`test`.`t2`.`f2` < 7))) `tt` 1280SELECT * FROM (SELECT * FROM v6) tt; 1281f2 f22 12823 3 12832 2 1284materialized view in a merged view in a materialized derived 1285CREATE VIEW v7 AS SELECT * FROM v1; 1286EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM v7 GROUP BY 1) tt; 1287id select_type table type possible_keys key key_len ref rows filtered Extra 12881 PRIMARY <derived2> ALL NULL NULL NULL NULL 6 100.00 NULL 12892 DERIVED <derived4> ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 12904 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 1291Warnings: 1292Note 1003 /* select#1 */ select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (/* select#2 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` group by 1) `tt` 1293SELECT * FROM (SELECT * FROM v7 GROUP BY 1) tt; 1294f1 f11 12951 1 12962 2 12973 3 12985 5 12997 7 13009 9 1301JOIN of above two 1302EXPLAIN EXTENDED SELECT * FROM v6 JOIN v7 ON f2=f1; 1303id select_type table type possible_keys key key_len ref rows filtered Extra 13041 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where 13051 PRIMARY <derived5> ref <auto_key0> <auto_key0> 5 test.t2.f2 2 100.00 NULL 13065 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort 1307Warnings: 1308Note 1003 /* select#1 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` in (2,3)) and (`test`.`t2`.`f2` < 7)) 1309SELECT * FROM v6 JOIN v7 ON f2=f1; 1310f2 f22 f1 f11 13113 3 3 3 13122 2 2 2 1313test two keys 1314CREATE TABLE t3(f3 INT, f33 INT); 1315INSERT INTO t1 VALUES(6,6),(8,8); 1316INSERT INTO t3 VALUES(1,1),(2,2),(3,3),(5,5); 1317EXPLAIN EXTENDED SELECT * FROM t1 JOIN (SELECT * FROM t2) tt ON t1.f1=tt.f2 1318JOIN t3 ON tt.f22=t3.f3; 1319id select_type table type possible_keys key key_len ref rows filtered Extra 13201 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where 13211 PRIMARY <derived2> ref <auto_key2> <auto_key2> 5 test.t3.f3 2 100.00 NULL 13221 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where; Using join buffer (Block Nested Loop) 13232 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 NULL 1324Warnings: 1325Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22`,`test`.`t3`.`f3` AS `f3`,`test`.`t3`.`f33` AS `f33` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2`) `tt` join `test`.`t3` where ((`test`.`t1`.`f1` = `tt`.`f2`) and (`tt`.`f22` = `test`.`t3`.`f3`)) 1326SELECT * FROM t1 JOIN (SELECT * FROM t2) tt ON t1.f1=tt.f2 1327JOIN t3 ON tt.f22=t3.f3; 1328f1 f11 f2 f22 f3 f33 13291 1 1 1 1 1 13302 2 2 2 2 2 13313 3 3 3 3 3 1332DROP TABLE t1,t2,t3; 1333DROP VIEW v1,v2,v3,v4,v6,v7; 1334# 1335# 1336# BUG#11783262: CRASH IN ITEM_FIELD::ITEM_FIELD IN ITEM.CC ON SUBQUERY 1337# IN FROM WITH WL5274 1338# 1339CREATE TABLE t1 ( 1340col_int_key INT, 1341col_time_key time, 1342col_varchar_key VARCHAR(1), 1343KEY col_int_key (col_int_key), 1344KEY col_varchar_key (col_varchar_key,col_int_key) 1345) ENGINE=INNODB; 1346SELECT alias1.col_time_key AS field1 1347FROM ( ( SELECT SQ1_alias1.* FROM t1 AS SQ1_alias1 ) AS alias1 1348INNER JOIN t1 AS alias2 1349ON (alias2.col_int_key = alias1.col_int_key) 1350) 1351WHERE alias1.col_int_key = 207 1352ORDER BY alias1.col_varchar_key, field1; 1353field1 1354DROP TABLE t1; 1355# 1356# Bug#11807437: VALGRIND WARNING IN MYSQL_DERIVED_OPTIMIZE() LINE 293 1357# 1358CREATE TABLE t1 ( 1359f1 int(11) DEFAULT NULL 1360); 1361SELECT 1 1362FROM ( 1363SELECT 1, 2 FROM DUAL 1364WHERE EXISTS ( 1365SELECT f1 1366FROM t1 1367)) AS tt 1368; 13691 1370DROP TABLE t1; 1371# 1372# 1373# Bug#11808582: VALGRIND ON WL#5274: INVALID WRITE IN MC_REPLACE_STRMEM.C:493) 1374# 1375CREATE TABLE t1 ( 1376pk INT NOT NULL AUTO_INCREMENT, 1377col_int_key INT, 1378col_time_key time, 1379col_varchar_key VARCHAR(1), 1380PRIMARY KEY (pk), 1381KEY col_int_key (col_int_key), 1382KEY col_varchar_key (col_varchar_key,col_int_key) 1383) ENGINE=InnoDB; 1384SELECT tt.col_time_key 1385FROM ( ( SELECT * FROM t1 ) AS tt 1386INNER JOIN t1 1387ON (t1.col_int_key = tt.col_int_key) 1388) 1389WHERE tt.col_int_key = 207 1390ORDER BY tt.col_varchar_key, tt.pk ASC, 1; 1391col_time_key 1392DROP TABLE t1; 1393# 1394# Bug#11791677 - ASSERTION FAILED IN JOIN_MATERIALIZE_TABLE IN 1395# SQL_SELECT.CC ON NESTED SUBQUERY 1396# 1397CREATE TABLE t1 ( 1398pk int(11) NOT NULL AUTO_INCREMENT, 1399col_int_key int(11) DEFAULT NULL, 1400col_varchar_key varchar(1) DEFAULT NULL, 1401PRIMARY KEY (pk), 1402KEY col_varchar_key (col_varchar_key,col_int_key) 1403); 1404INSERT INTO t1 VALUES (10,8,'v'), (29,4,'c'); 1405CREATE TABLE t2 ( 1406pk int(11) NOT NULL AUTO_INCREMENT, 1407col_int_nokey int(11) DEFAULT NULL, 1408col_varchar_key varchar(1) DEFAULT NULL, 1409PRIMARY KEY (pk) 1410); 1411INSERT INTO t2 VALUES (16,1,'c'), (20,4,'d'); 1412CREATE TABLE t3 ( 1413`field1` varchar(1) DEFAULT NULL, 1414`field2` int(11) DEFAULT NULL 1415); 1416INSERT INTO t3 VALUES ('m',6),('c',4); 1417SELECT * 1418FROM t3 1419WHERE (field1, field2) IN ( 1420SELECT t1.col_varchar_key AS field1, 1421t1.col_int_key AS field2 1422FROM ( t1 INNER JOIN ( 1423SELECT t2.* 1424FROM t2 1425WHERE t2.col_int_nokey < t2.pk ) AS alias2 1426ON (alias2.col_varchar_key = t1.col_varchar_key ) ) 1427GROUP BY field1, field2 1428ORDER BY t1.col_int_key, t1 .pk DESC ) 1429; 1430field1 field2 1431c 4 1432DROP TABLE t1,t2,t3; 1433# 1434# 1435# Bug#11791705 - CRASH IN JOIN_MATERIALIZE_TABLE OR ASSERTION FAIL: 1436# !TAB->SAVE_READ_FIRST_RECORD 1437# 1438CREATE TABLE t1 (a INTEGER); 1439INSERT INTO t1 VALUES (NULL),(NULL); 1440SELECT * FROM t1 1441WHERE (a, a) NOT IN 1442(SELECT * FROM (SELECT 8, 4 UNION SELECT 2, 3) tt) ; 1443a 1444DROP TABLE t1; 1445# 1446# Bug#11791649 - ASSERT: FIXED == 0, IN ITEM.CC ON EXPLAIN WITH VIEW 1447# IN SUBQUERY 1448# 1449CREATE TABLE t1 (pk int); 1450INSERT INTO t1 VALUES (1); 1451CREATE TABLE t2 (col_varchar_nokey varchar(1)); 1452INSERT INTO t2 VALUES ('m'), ('f'); 1453EXPLAIN SELECT pk 1454FROM t1 1455WHERE (2) IN 1456( SELECT * 1457FROM (SELECT COUNT(col_varchar_nokey) FROM t2) d 1458) 1459; 1460id select_type table type possible_keys key key_len ref rows Extra 14611 PRIMARY t1 system NULL NULL NULL NULL 1 NULL 14621 PRIMARY <derived3> ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1) 14633 DERIVED t2 ALL NULL NULL NULL NULL 2 NULL 1464DROP TABLE t1,t2; 1465# 1466# 1467# Bug#12735934 - Lost LIMIT clause caused wrong result. 1468# 1469CREATE TABLE t1 (f1 VARCHAR(1), key(f1)); 1470INSERT INTO t1 VALUES ('a'); 1471CREATE VIEW v1 AS SELECT f1 FROM t1 ORDER BY 1 LIMIT 0; 1472SELECT * FROM v1; 1473f1 1474DROP VIEW v1; 1475DROP TABLE t1; 1476# 1477# 1478# Bug#12726927: An outdated assertion caused server failure. 1479# 1480CREATE TABLE t1 ( 1481pk int(11) NOT NULL AUTO_INCREMENT, 1482col_int_nokey int(11) NOT NULL, 1483col_varchar_key varchar(1) NOT NULL, 1484PRIMARY KEY (pk), 1485KEY col_varchar_key (col_varchar_key) 1486) ENGINE=MyISAM; 1487INSERT INTO t1 VALUES (10,1,'v'), (24,18,'h'); 1488CREATE TABLE t2 ( 1489pk int(11) NOT NULL AUTO_INCREMENT, 1490col_date_key date NOT NULL, 1491col_date_nokey date NOT NULL, 1492col_time_nokey time NOT NULL, 1493col_varchar_key varchar(1) NOT NULL, 1494col_varchar_nokey varchar(1) NOT NULL, 1495PRIMARY KEY (pk), 1496KEY col_date_key (col_date_key), 1497KEY col_varchar_key (col_varchar_key) 1498) ENGINE=MyISAM; 1499INSERT INTO t2 VALUES (1,'1900-01-01','1900-01-01','00:00:00','k','k'); 1500SELECT OUTR.col_date_key 1501FROM t2 AS OUTR2 1502LEFT JOIN t2 AS OUTR ON OUTR2.pk < OUTR.pk 1503WHERE ( OUTR.col_varchar_nokey , OUTR.col_varchar_key ) 1504IN ( 1505SELECT DISTINCT col_varchar_key , col_varchar_key 1506FROM t1 1507WHERE col_int_nokey XOR OUTR.col_time_nokey 1508) 1509XOR OUTR.col_date_nokey IS NULL 1510; 1511col_date_key 1512NULL 1513DROP TABLE t1,t2; 1514# 1515# 1516# Bug#12799731 - CRASH IN END_READ_RECORD. 1517# 1518create table t1(f1 char(255) charset utf8); 1519insert into t1 values('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'); 1520set @save_heap_size= @@max_heap_table_size; 1521set @@max_heap_table_size= 1; 1522Warnings: 1523Warning 1292 Truncated incorrect max_heap_table_size value: '1' 1524flush status; 1525select count(*) from t1 join ( 1526select t1.f1 from t1 join t1 as t2 join t1 as t3) tt on t1.f1 = tt.f1; 1527count(*) 15281000 1529Should be greater than 1000 as it also includes records dumped from 1530heap to myisam. 1531show status like 'Handler_write'; 1532Variable_name Value 1533Handler_write 1011 1534set @@max_heap_table_size= @save_heap_size; 1535drop table t1; 1536# 1537# 1538# Bug#12896124: Crash on rqg_mdl_stability test 1539# 1540CREATE TABLE t1(f1 INT); 1541INSERT INTO t1 VALUES (1),(2),(3); 1542CREATE FUNCTION func1 (param1 INTEGER) RETURNS INT NOT DETERMINISTIC 1543return param1; 1544CREATE FUNCTION func2 (param1 INTEGER) RETURNS INT 1545return param1; 1546SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func1(f1); 1547f1 15481 15492 15503 1551EXPLAIN SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func1(f1); 1552id select_type table type possible_keys key key_len ref rows Extra 15531 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where 15542 DERIVED t1 ALL NULL NULL NULL NULL 3 NULL 1555SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func2(f1); 1556f1 15571 15582 15593 1560EXPLAIN SELECT * FROM (SELECT * FROM t1) tt WHERE f1 = func2(f1); 1561id select_type table type possible_keys key key_len ref rows Extra 15621 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where 15632 DERIVED t1 ALL NULL NULL NULL NULL 3 NULL 1564DROP FUNCTION func1; 1565DROP FUNCTION func2; 1566DROP TABLE t1; 1567# 1568# 1569# Bug#12909844: Missing type cast caused false assertion 1570# 1571CREATE TABLE t1 ( fk INT) ENGINE=INNODB; 1572CREATE TABLE t2 ( 1573f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, 1574f7 INT, f8 INT, f9 INT, f10 INT, f11 INT, f12 INT, 1575f13 INT, f14 INT, f15 INT, f16 INT, f17 INT, f18 INT, 1576f19 INT, f20 INT, f21 INT, f22 INT, f23 INT, f24 INT, 1577f25 INT, f26 INT, f27 INT, f28 INT, f29 INT, f30 INT, 1578f31 INT, f32 TEXT, fk INT) ENGINE=INNODB; 1579SELECT alias2.fk AS field1 FROM t1 AS alias1 JOIN 1580(SELECT * FROM t2 ) AS alias2 ON alias1.fk = alias2.fk; 1581field1 1582EXPLAIN 1583SELECT alias2.fk AS field1 FROM t1 AS alias1 JOIN 1584(SELECT * FROM t2 ) AS alias2 ON alias1.fk = alias2.fk; 1585id select_type table type possible_keys key key_len ref rows Extra 15861 PRIMARY alias1 ALL NULL NULL NULL NULL 1 Using where 15871 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.alias1.fk 2 NULL 15882 DERIVED t2 ALL NULL NULL NULL NULL 1 NULL 1589DROP TABLE t1, t2; 1590# 1591# 1592# Bug#12910039: Incorrect merge caused segmentation fault. 1593# 1594CREATE TABLE t1 (f1 int) ENGINE=myisam; 1595CREATE TABLE t2 (f1 text) ENGINE=innodb; 1596SELECT 1 FROM ( 1597( SELECT * FROM ( SELECT * FROM t2 ) AS alias1 ) AS alias1, 1598( SELECT * FROM t1 ) AS alias2 ); 15991 1600DROP TABLE t1,t2; 1601# 1602# 1603# Bug#12910006: MRR initialization on a derived table caused crash. 1604# 1605SET @save_switch= @@SESSION.optimizer_switch; 1606SET @@SESSION.optimizer_switch="batched_key_access=on"; 1607CREATE TABLE t1 ( pk integer auto_increment, 1608col_blob_key blob, primary key (pk)) ENGINE=innodb; 1609CREATE TABLE t2 (col_tinytext tinytext null, 1610pk integer auto_increment, col_text text, 1611col_blob blob, primary key (pk)) ENGINE=innodb; 1612SELECT alias1.col_text AS field1 , 1613alias1.col_tinytext AS field2 1614FROM t2 AS alias1 1615LEFT OUTER JOIN ( SELECT * FROM t1 ) AS alias2 ON alias1.pk = alias2.pk 1616WHERE alias2.pk >=1 AND alias2.pk < 3 1617ORDER BY field1,field2 ASC; 1618field1 field2 1619SET @@SESSION.optimizer_switch= @save_switch; 1620DROP TABLE t1, t2; 1621# 1622# Bug#13106350: MRR initialization on a derived table caused crash. 1623# 1624CREATE TABLE t1 (pk INTEGER PRIMARY KEY, vc VARCHAR(20)); 1625INSERT INTO t1 VALUES(7, 'seven'), (13, 'thirteen'); 1626CREATE TABLE t2 (pk INTEGER PRIMARY KEY, vc1 VARCHAR(20), vc2 VARCHAR(20)); 1627INSERT INTO t2 VALUES(7, 'seven', 's'), (14, 'fourteen', 'f'); 1628CREATE TABLE t3 (pk INTEGER PRIMARY KEY, vc VARCHAR(20)); 1629INSERT INTO t3 VALUES(5, 'f'), (6, 's'), (7, 's'); 1630explain SELECT derived.vc 1631FROM (SELECT * FROM t1) AS derived 1632WHERE derived.vc IN ( 1633SELECT t2.vc1 1634FROM t2 JOIN t3 ON t2.vc2=t3.vc); 1635id select_type table type possible_keys key key_len ref rows Extra 16361 PRIMARY <subquery3> ALL NULL NULL NULL NULL NULL Using where 16371 PRIMARY <derived2> ref <auto_key0> <auto_key0> 23 <subquery3>.vc1 2 NULL 16383 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 NULL 16393 MATERIALIZED t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop) 16402 DERIVED t1 ALL NULL NULL NULL NULL 2 NULL 1641SELECT derived.vc 1642FROM (SELECT * FROM t1) AS derived 1643WHERE derived.vc IN ( 1644SELECT t2.vc1 1645FROM t2 JOIN t3 ON t2.vc2=t3.vc); 1646vc 1647seven 1648DROP TABLE t1, t2, t3; 1649# 1650# 1651# Bug#13107577: Derived table in a semi-join caused failed assertion. 1652# 1653CREATE TABLE t1 ( 1654`col_int_key` int(11) NOT NULL, 1655`col_varchar_nokey` varchar(1) NOT NULL 1656) ENGINE=MyISAM; 1657INSERT INTO t1 VALUES (8,'m'), (4,'b'), (4,'x'), (7,'g'), (4,'p'); 1658CREATE VIEW v1 AS SELECT * FROM t1; 1659SELECT col_int_key 1660FROM t1 1661WHERE ( NOT EXISTS ( 1662SELECT col_varchar_nokey 1663FROM t1 1664WHERE ( 7 ) IN ( 1665SELECT v1.col_int_key 1666FROM ( v1 JOIN ( SELECT * FROM t1 ) AS d1 1667ON ( d1.col_varchar_nokey = v1.col_varchar_nokey ) ) 1668) 1669) ) 1670; 1671col_int_key 1672DROP VIEW v1; 1673DROP TABLE t1; 1674# 1675# 1676# Bug#13105833: Crash when using LooseScan sj-strategy for a view. 1677# 1678CREATE TABLE t1 (pk int(11)) ENGINE=InnoDB; 1679INSERT INTO t1 VALUES (1); 1680CREATE TABLE t2 (pk int(11)) ENGINE=InnoDB; 1681INSERT INTO t2 VALUES (1), (2), (3); 1682CREATE VIEW v1 AS SELECT DISTINCT pk FROM t1; 1683SELECT pk 1684FROM t2 1685WHERE pk IN ( SELECT * FROM v1 ) ; 1686pk 16871 1688DROP TABLE t1,t2; 1689DROP VIEW v1; 1690# 1691# 1692# Bug#13261277: Unchecked key length caused missing records. 1693# 1694CREATE TABLE t1 ( 1695col_varchar varchar(1024) CHARACTER SET utf8 DEFAULT NULL, 1696stub1 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, 1697stub2 varchar(1024) CHARACTER SET utf8 DEFAULT NULL, 1698stub3 varchar(1024) CHARACTER SET utf8 DEFAULT NULL 1699); 1700INSERT INTO t1 VALUES 1701('d','d','l','ther'), 1702(NULL,'s','NJBIQ','trzetuchv'), 1703(-715390976,'coul','MYWFB','cfhtrzetu'), 1704(1696792576,'f','i\'s','c'), 1705 (1,'i','ltpemcfhtr','gsltpemcf'), 1706 (-663027712,'mgsltpemcf','sa','amgsltpem'), 1707 (-1686700032,'JPRVK','i','vamgsltpe'), 1708 (NULL,'STUNB','UNVJV','u'), 1709 (5,'oka','qyihvamgsl','AXSMD'), 1710 (NULL,'tqwmqyihva','h','yntqwmqyi'), 1711 (3,'EGMJN','e','e'); 1712CREATE TABLE t2 ( 1713col_varchar varchar(10) DEFAULT NULL, 1714col_int INT DEFAULT NULL 1715); 1716INSERT INTO t2 VALUES ('d',9); 1717SET @save_heap_size= @@max_heap_table_size; 1718SET @@max_heap_table_size= 16384; 1719SELECT t2.col_int 1720FROM t2 1721RIGHT JOIN ( SELECT * FROM t1 ) AS dt 1722ON t2.col_varchar = dt.col_varchar 1723WHERE t2.col_int IS NOT NULL ; 1724col_int 17259 1726# Shouldn't use auto_key0 for derived table 1727EXPLAIN 1728SELECT t2.col_int 1729FROM t2 1730RIGHT JOIN ( SELECT * FROM t1 ) AS dt 1731ON t2.col_varchar = dt.col_varchar 1732WHERE t2.col_int IS NOT NULL ; 1733id select_type table type possible_keys key key_len ref rows Extra 17341 PRIMARY t2 system NULL NULL NULL NULL 1 NULL 17351 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 Using where 17362 DERIVED t1 ALL NULL NULL NULL NULL 11 NULL 1737SET @@max_heap_table_size= @save_heap_size; 1738DROP TABLE t1,t2; 1739# 1740# 1741# Bug#13383857: Another crash in memcpy from 1742# join_cache::write_record_data with semijoin 1743# 1744CREATE TABLE t1 ( 1745col_int_key INT DEFAULT NULL, 1746col_time_nokey TIME DEFAULT NULL, 1747col_varchar_key VARCHAR(1) DEFAULT NULL, 1748col_varchar_nokey VARCHAR(1) DEFAULT NULL, 1749KEY col_int_key (col_int_key), 1750KEY col_varchar_key (col_varchar_key,col_int_key) 1751); 1752INSERT INTO t1 VALUES 1753(8,'22:55:23','x','x'), 1754(7,'10:19:31','d','d'), 1755(1,'14:40:36','r','r'), 1756(7,'04:37:47','f','f'), 1757(9,'19:34:06','y','y'), 1758(NULL,'20:35:33','u','u'), 1759(1,NULL,'m','m'), 1760(9,'14:43:37',NULL,NULL), 1761(2,'02:23:09','o','o'), 1762(9,'01:22:45','w','w'), 1763(2,'00:00:00','m','m'), 1764(4,'00:13:25','q','q'), 1765(0,'03:47:16',NULL,NULL), 1766(4,'01:41:48','d','d'), 1767(8,'00:00:00','g','g'), 1768(NULL,'22:32:04','x','x'), 1769(NULL,'16:44:14','f','f'), 1770(0,'17:38:37','p','p'), 1771(NULL,'08:46:48','j','j'), 1772(8,'14:11:27','c','c'); 1773CREATE TABLE t2 ( 1774col_int_key INT DEFAULT NULL, 1775col_time_nokey TIME DEFAULT NULL, 1776col_varchar_key VARCHAR(1) DEFAULT NULL, 1777col_varchar_nokey VARCHAR(1) DEFAULT NULL, 1778KEY col_int_key (col_int_key), 1779KEY col_varchar_key (col_varchar_key,col_int_key) 1780); 1781INSERT INTO t2 VALUES 1782(4,'22:34:09','v','v'), 1783(62,'14:26:02','v','v'), 1784(7,'14:03:03','c','c'), 1785(1,'01:46:09',NULL,NULL), 1786(0,'16:21:18','x','x'), 1787(7,'18:56:33','i','i'), 1788(7,NULL,'e','e'), 1789(1,'09:29:08','p','p'), 1790(7,'19:11:10','s','s'), 1791(1,'11:57:26','j','j'), 1792(5,'00:39:46','z','z'), 1793(2,'03:28:15','c','c'), 1794(0,'06:44:18','a','a'), 1795(1,'14:36:39','q','q'), 1796(8,'18:42:45','y','y'), 1797(1,'02:57:29',NULL,NULL), 1798(1,'16:46:13','r','r'), 1799(9,'19:39:02','v','v'), 1800(1,NULL,NULL,NULL), 1801(5,'20:58:33','r','r'); 1802CREATE TABLE t3 ( 1803col_int_key INT DEFAULT NULL, 1804col_time_nokey TIME DEFAULT NULL, 1805col_varchar_key VARCHAR(1) DEFAULT NULL, 1806col_varchar_nokey VARCHAR(1) DEFAULT NULL, 1807KEY col_int_key (col_int_key), 1808KEY col_varchar_key (col_varchar_key,col_int_key) 1809); 1810INSERT INTO t3 VALUES (8,'04:07:22','g','g'); 1811explain SELECT col_time_nokey AS x 1812FROM (SELECT * FROM t2) AS outr 1813WHERE col_varchar_nokey IN ( 1814SELECT innr.col_varchar_key 1815FROM (SELECT * FROM t3) AS innr2 1816LEFT JOIN (SELECT * FROM t1) AS innr 1817ON innr2.col_varchar_key >= innr.col_varchar_key 1818WHERE outr.col_varchar_nokey = 'e' 1819 ) 1820AND outr.col_varchar_key <> 'r' 1821; 1822id select_type table type possible_keys key key_len ref rows Extra 18231 PRIMARY <derived4> ALL NULL NULL NULL NULL 1 Using where; Start temporary 18241 PRIMARY <derived5> ref <auto_key0> <auto_key0> 4 const 2 Using where 18251 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) 18265 DERIVED t1 ALL NULL NULL NULL NULL 20 NULL 18274 DERIVED t3 system NULL NULL NULL NULL 1 NULL 18282 DERIVED t2 ALL NULL NULL NULL NULL 20 NULL 1829SELECT col_time_nokey AS x 1830FROM (SELECT * FROM t2) AS outr 1831WHERE col_varchar_nokey IN ( 1832SELECT innr.col_varchar_key 1833FROM (SELECT * FROM t3) AS innr2 1834LEFT JOIN (SELECT * FROM t1) AS innr 1835ON innr2.col_varchar_key >= innr.col_varchar_key 1836WHERE outr.col_varchar_nokey = 'e' 1837 ) 1838AND outr.col_varchar_key <> 'r' 1839; 1840x 1841DROP TABLE t1, t2, t3; 1842# 1843# Bug#13354889: Crash on a derived table with more than 64 fields. 1844# 1845create table t1 ( 1846field00 int, field01 int, field02 int, field03 int, 1847field04 int, field05 int, field06 int, field07 int, 1848field10 int, field11 int, field12 int, field13 int, 1849field14 int, field15 int, field16 int, field17 int, 1850field20 int, field21 int, field22 int, field23 int, 1851field24 int, field25 int, field26 int, field27 int, 1852field30 int, field31 int, field32 int, field33 int, 1853field34 int, field35 int, field36 int, field37 int, 1854field40 int, field41 int, field42 int, field43 int, 1855field44 int, field45 int, field46 int, field47 int, 1856field50 int, field51 int, field52 int, field53 int, 1857field54 int, field55 int, field56 int, field57 int, 1858field60 int, field61 int, field62 int, field63 int, 1859field64 int, field65 int, field66 int, field67 int, 1860field70 int, field71 int, field72 int, field73 int, 1861field74 int, field75 int, field76 int, field77 int, 1862field100 int 1863); 1864insert into t1(field100) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0); 1865insert into t1 select * from t1; 1866insert into t1 select * from t1; 1867insert into t1 select * from t1; 1868select tt.field100 from t1 join (select * from t1) tt where t1.field100=tt.field100 1869limit 1; 1870field100 18711 1872Should use auto_key0 and ref access. 1873explain 1874select tt.field100 from t1 join (select * from t1) tt where t1.field100=tt.field100 1875limit 1; 1876id select_type table type possible_keys key key_len ref rows Extra 18771 PRIMARY t1 ALL NULL NULL NULL NULL 80 Using where 18781 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 test.t1.field100 8 NULL 18792 DERIVED t1 ALL NULL NULL NULL NULL 80 NULL 1880drop table t1; 1881# 1882# 1883# Bug#13390138: crash in memcpy from join_cache::write_record_data 1884# 1885CREATE TABLE t1 ( 1886col_varchar_key varchar(1), 1887col_varchar_nokey varchar(1), 1888KEY col_varchar_key (col_varchar_key) 1889) ENGINE=MyISAM; 1890INSERT INTO t1 VALUES ('r','r'); 1891CREATE TABLE t2 ( 1892col_varchar_key varchar(1), 1893col_varchar_nokey varchar(1), 1894KEY col_varchar_key (col_varchar_key) 1895); 1896INSERT INTO t2 VALUES 1897(NULL,NULL), 1898('r','r'); 1899CREATE TABLE t3 ( 1900col_int_key int, 1901col_varchar_key varchar(1), 1902col_varchar_nokey varchar(1), 1903KEY col_int_key (col_int_key), 1904KEY col_varchar_key (col_varchar_key, col_int_key) 1905); 1906INSERT INTO t3 VALUES 1907(9,'f','f'), 1908(4,'y','y'), 1909(3,'u','u'), 1910(2,'m','m'), 1911(NULL,NULL,NULL), 1912(2,'o','o'), 1913(NULL,'r','r'), 1914(6,'m','m'), 1915(7,'q','q'), 1916(6,'c','c'); 1917explain SELECT grandparent.col_varchar_nokey AS g1 1918FROM (SELECT * FROM t3) AS grandparent 1919WHERE grandparent.col_varchar_nokey IN 1920(SELECT parent.col_varchar_key AS p1 1921FROM (SELECT * FROM t2) AS parent 1922WHERE grandparent.col_varchar_key IN ( 1923SELECT child1.col_varchar_key AS c1 1924FROM (SELECT * FROM t1) AS child1 1925LEFT JOIN (SELECT * FROM t2) AS child2 1926ON child1.col_varchar_nokey <> child2.col_varchar_key 1927) 1928AND grandparent.col_int_key IS UNKNOWN 1929) 1930ORDER BY grandparent.col_varchar_nokey; 1931id select_type table type possible_keys key key_len ref rows Extra 19321 PRIMARY <derived2> ref <auto_key0> <auto_key0> 5 const 2 Using where; Using index; Using temporary; Using filesort; Start temporary 19331 PRIMARY <derived6> ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop) 19341 PRIMARY <derived4> ref <auto_key0> <auto_key0> 4 grandparent.col_varchar_nokey 2 NULL 19351 PRIMARY <derived7> ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop) 19367 DERIVED t2 ALL NULL NULL NULL NULL 2 NULL 19376 DERIVED t1 system NULL NULL NULL NULL 1 NULL 19384 DERIVED t2 ALL NULL NULL NULL NULL 2 NULL 19392 DERIVED t3 ALL NULL NULL NULL NULL 10 NULL 1940SELECT grandparent.col_varchar_nokey AS g1 1941FROM (SELECT * FROM t3) AS grandparent 1942WHERE grandparent.col_varchar_nokey IN 1943(SELECT parent.col_varchar_key AS p1 1944FROM (SELECT * FROM t2) AS parent 1945WHERE grandparent.col_varchar_key IN ( 1946SELECT child1.col_varchar_key AS c1 1947FROM (SELECT * FROM t1) AS child1 1948LEFT JOIN (SELECT * FROM t2) AS child2 1949ON child1.col_varchar_nokey <> child2.col_varchar_key 1950) 1951AND grandparent.col_int_key IS UNKNOWN 1952) 1953ORDER BY grandparent.col_varchar_nokey; 1954g1 1955r 1956DROP TABLE t1, t2, t3; 1957# 1958# Bug#13457552: Crash on instantiating a derived table in a query with 1959# empty result. 1960# 1961CREATE TABLE t1 ( pk INT, col_blob BLOB ) ENGINE = MyISAM; 1962CREATE TABLE t2 ( pk INT, col_blob BLOB ) ENGINE = InnoDB; 1963SELECT pk FROM ( SELECT col_blob, pk FROM t2 ) AS A NATURAL JOIN t1; 1964pk 1965EXPLAIN SELECT pk FROM ( SELECT col_blob, pk FROM t2 ) AS A NATURAL JOIN t1; 1966id select_type table type possible_keys key key_len ref rows Extra 19671 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 19682 DERIVED t2 ALL NULL NULL NULL NULL 1 NULL 1969DROP TABLE t1,t2; 1970# 1971# Bug #13801019 ASSERTION `0' FAILED IN CREATE_MYISAM_TMP_TABLE 1972# 1973CREATE TABLE t1 (a INT, b BLOB) ENGINE=InnoDB; 1974CREATE TABLE t2 (c INT); 1975CREATE TABLE t3 (d INT); 1976INSERT INTO t3 VALUES (0); 1977SELECT * FROM (SELECT * FROM t1) AS a1 RIGHT JOIN t3 LEFT JOIN t2 ON d=c ON a=c; 1978a b d c 1979NULL NULL 0 NULL 1980DROP TABLE t1, t2, t3; 1981# 1982# Bug #17814492 - INVALID RESULTS FROM SUBQUERY WITH IN CLAUSE 1983# 1984CREATE TABLE t1 ( 1985a INTEGER NOT NULL, 1986b VARCHAR(1000) NOT NULL, 1987c TEXT NOT NULL 1988)ENGINE=InnoDB; 1989INSERT INTO t1 VALUES (1, 'xxx', 'abc'); 1990INSERT INTO t1 VALUES (2, 'yyy', 'abc'); 1991INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy'; 1992INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy'; 1993INSERT INTO t1 SELECT a, b, c FROM t1 WHERE b='yyy'; 1994CREATE TABLE t2 ( 1995a INTEGER NOT NULL 1996)ENGINE=InnoDB; 1997INSERT INTO t2 VALUES (1), (2); 1998ANALYZE TABLE t1,t2; 1999Table Op Msg_type Msg_text 2000test.t1 analyze status OK 2001test.t2 analyze status OK 2002SET @save_optimizer_switch= @@optimizer_switch; 2003SET @@SESSION.optimizer_switch="index_condition_pushdown=on"; 2004EXPLAIN SELECT a1.a, a1.b, a1.c FROM (SELECT a, b, c FROM t1 ) a1 2005JOIN t2 ON a1.a=t2.a WHERE a1.b='xxx'; 2006id select_type table type possible_keys key key_len ref rows Extra 20071 PRIMARY t2 ALL NULL NULL NULL NULL 2 NULL 20081 PRIMARY <derived2> ref <auto_key1> <auto_key1> 4 test.t2.a 2 Using where 20092 DERIVED t1 ALL NULL NULL NULL NULL 9 NULL 2010SELECT a1.a, a1.b, a1.c FROM (SELECT a, b, c FROM t1 ) a1 2011JOIN t2 ON a1.a=t2.a WHERE a1.b='xxx'; 2012a b c 20131 xxx abc 2014SET @@SESSION.optimizer_switch= @save_optimizer_switch; 2015DROP TABLE t2, t1; 2016# End of test for Bug #17814492 2017# 2018# Bug #18607971 : 5.5 TO 5.6 REGRESSION WITH A SUBQUERY IN THE FROM 2019# CLAUSE. 2020# 2021CREATE TABLE t(id INT PRIMARY KEY, 2022c1 INT, c2 INT, key(c2)) engine=InnoDB; 2023INSERT INTO t(id, c1, c2) VALUES(1, 2, 3), (2, 3, 4), (3, 3, 4), (4, 3, 4); 2024ANALYZE TABLE t; 2025Table Op Msg_type Msg_text 2026test.t analyze status OK 2027EXPLAIN SELECT * FROM 2028(SELECT t1.c1 2029FROM t t1 INNER JOIN t t2 ON t1.c1= 3 2030GROUP BY t1.c1) a, t b 2031WHERE b.id BETWEEN 1 AND 10; 2032id select_type table type possible_keys key key_len ref rows Extra 20331 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20341 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20352 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where 20362 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop) 2037EXPLAIN SELECT * FROM 2038(SELECT t1.id 2039FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id 2040GROUP BY t1.id, t2.c2) a, t b 2041WHERE b.id BETWEEN 1 AND 10; 2042id select_type table type possible_keys key key_len ref rows Extra 20431 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20441 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20452 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 20462 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL 2047EXPLAIN SELECT * FROM 2048(SELECT t1.c1 2049FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3 2050GROUP BY t1.c1) a, t b 2051WHERE b.id BETWEEN 1 AND 10; 2052id select_type table type possible_keys key key_len ref rows Extra 20531 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20541 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20552 DERIVED t2 ref c2 c2 5 const 1 Using index 20562 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2057EXPLAIN SELECT * FROM 2058(SELECT t1.c1 2059FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3 2060GROUP BY t1.c1, t2.c2) a, t b 2061WHERE b.id BETWEEN 1 AND 10; 2062id select_type table type possible_keys key key_len ref rows Extra 20631 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20641 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20652 DERIVED t2 ref c2 c2 5 const 1 Using index 20662 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2067EXPLAIN SELECT * FROM 2068(SELECT t1.c1 2069FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3 2070GROUP BY t1.c1, t1.id) a, t b 2071WHERE b.id BETWEEN 1 AND 10; 2072id select_type table type possible_keys key key_len ref rows Extra 20731 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20741 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 20752 DERIVED t2 ref c2 c2 5 const 1 Using index; Using temporary; Using filesort 20762 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop) 2077EXPLAIN SELECT * FROM 2078(SELECT t1.id 2079FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.c1 2080GROUP BY t2.c1, t1.id) a, t b 2081WHERE b.id BETWEEN 1 AND 10; 2082id select_type table type possible_keys key key_len ref rows Extra 20831 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20841 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20852 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 20862 DERIVED t2 ALL NULL NULL NULL NULL 4 Using where 2087EXPLAIN SELECT * FROM 2088(SELECT t1.id 2089FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id 2090GROUP BY t2.c1, t1.id) a, t b 2091WHERE b.id BETWEEN 1 AND 10; 2092id select_type table type possible_keys key key_len ref rows Extra 20931 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 20941 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 20952 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 20962 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL 2097EXPLAIN SELECT * FROM 2098(SELECT t1.id 2099FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id 2100GROUP BY t2.c2, t1.id) a, t b 2101WHERE b.id BETWEEN 1 AND 10; 2102id select_type table type possible_keys key key_len ref rows Extra 21031 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21041 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21052 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 21062 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL 2107EXPLAIN SELECT * FROM 2108(SELECT t1.id 2109FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id 2110GROUP BY t1.id, t2.c2) a, t b 2111WHERE b.id BETWEEN 1 AND 10; 2112id select_type table type possible_keys key key_len ref rows Extra 21131 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21141 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21152 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 21162 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL 2117EXPLAIN SELECT * FROM 2118(SELECT t1.id 2119FROM t t1 INNER JOIN t t2 INNER JOIN t t3 ON t1.id=1 AND t1.c1=t2.id AND t2.c1=t3.id 2120GROUP BY t1.id, t2.c2, t3.c2) a, t b 2121WHERE b.id BETWEEN 1 AND 10; 2122id select_type table type possible_keys key key_len ref rows Extra 21231 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21241 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21252 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL 21262 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL 21272 DERIVED t3 const PRIMARY PRIMARY 4 const 1 NULL 2128EXPLAIN SELECT * FROM 2129(SELECT DISTINCT t1.id 2130FROM t t1 2131WHERE t1.id= 1) a, t b 2132WHERE b.id BETWEEN 1 AND 10; 2133id select_type table type possible_keys key key_len ref rows Extra 21341 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21351 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21362 DERIVED t1 const PRIMARY PRIMARY 4 const 1 Using index 2137EXPLAIN SELECT * FROM 2138(SELECT t1.id + 1 2139FROM t t1 INNER JOIN t t2 ON t1.id= 1 2140GROUP BY t1.id + 1) a, t b 2141WHERE b.id BETWEEN 1 AND 10; 2142id select_type table type possible_keys key key_len ref rows Extra 21431 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21441 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21452 DERIVED t1 const PRIMARY PRIMARY 4 const 1 Using index 21462 DERIVED t2 index NULL c2 5 NULL 4 Using index 2147EXPLAIN SELECT * FROM 2148(SELECT t1.c1 2149FROM t t1 INNER JOIN t t2 ON t1.c1= 3 2150GROUP BY 1.5) a, t b 2151WHERE b.id BETWEEN 1 AND 10; 2152id select_type table type possible_keys key key_len ref rows Extra 21531 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21541 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21552 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where 21562 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop) 2157EXPLAIN SELECT * FROM 2158(SELECT t1.id 2159FROM t t1 INNER JOIN t t2 ON mod(t1.id,1000)= 1 2160GROUP BY t1.id) a, t b 2161WHERE b.id BETWEEN 1 AND 10; 2162id select_type table type possible_keys key key_len ref rows Extra 21631 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21641 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 Using join buffer (Block Nested Loop) 21652 DERIVED t1 index PRIMARY,c2 c2 5 NULL 4 Using where; Using index; Using temporary; Using filesort 21662 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop) 2167EXPLAIN SELECT * FROM 2168(SELECT t1.id + 1 2169FROM t t1 INNER JOIN t t2 ON t1.id + 1= 2 2170GROUP BY t1.id + 1) a, t b 2171WHERE b.id BETWEEN 1 AND 10; 2172id select_type table type possible_keys key key_len ref rows Extra 21731 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21741 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where 21752 DERIVED t1 index PRIMARY,c2 c2 5 NULL 4 Using where; Using index 21762 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop) 2177CREATE VIEW v1 AS SELECT c1 a FROM t WHERE c1 = 3; 2178CREATE VIEW v2 AS SELECT c2 b FROM t WHERE c2 > 3; 2179EXPLAIN SELECT * FROM (SELECT v1.a 2180FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b 2181GROUP BY v1.a) p, t q 2182WHERE q.id BETWEEN 1 AND 10; 2183id select_type table type possible_keys key key_len ref rows Extra 21841 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 21851 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where 21862 DERIVED t ALL NULL NULL NULL NULL 4 Using where 21872 DERIVED t ref c2 c2 5 const 1 Using where; Using index 2188DROP VIEW v1; 2189CREATE VIEW v1 AS SELECT c1 a FROM t; 2190EXPLAIN SELECT * FROM (SELECT v1.a 2191FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b AND v1.a = 10 2192GROUP BY v1.a) p, t q 2193WHERE q.id BETWEEN 1 AND 10; 2194id select_type table type possible_keys key key_len ref rows Extra 21951 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where 21961 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop) 21972 DERIVED t ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 21982 DERIVED t ref c2 c2 5 func 1 Using where; Using index 2199EXPLAIN SELECT * FROM (SELECT v1.a 2200FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b 2201WHERE v1.a = 3 2202GROUP BY v1.a) p, t q 2203WHERE q.id BETWEEN 1 AND 10; 2204id select_type table type possible_keys key key_len ref rows Extra 22051 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL 22061 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where 22072 DERIVED t ALL NULL NULL NULL NULL 4 Using where 22082 DERIVED t ref c2 c2 5 const 1 Using where; Using index 2209DROP VIEW v1, v2; 2210DROP TABLE t; 2211