1CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), 2INDEX (name)) ENGINE=InnoDB; 3CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), 4FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; 5INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); 6INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); 7EXPLAIN 8SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 9WHERE t1.name LIKE 'A%'; 10id select_type table type possible_keys key key_len ref rows Extra 111 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index 121 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index 13EXPLAIN 14SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 15WHERE t1.name LIKE 'A%' OR FALSE; 16id select_type table type possible_keys key key_len ref rows Extra 171 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index 181 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index 19DROP TABLE t1,t2; 20# 21# BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output 22# in opt_range.h 23# 24CREATE TABLE t1 ( 25col_int INT, 26col_int_key INT, 27pk INT NOT NULL, 28PRIMARY KEY (pk), 29KEY col_int_key (col_int_key) 30) ENGINE=InnoDB; 31INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4); 32INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5); 33CREATE TABLE t2 ( 34pk INT PRIMARY KEY 35) ENGINE=InnoDB; 36 37EXPLAIN SELECT t1.pk 38FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int 39WHERE t1.col_int_key BETWEEN 5 AND 6 40AND t1.pk IS NULL OR t1.pk IN (5) 41ORDER BY pk; 42id select_type table type possible_keys key key_len ref rows Extra 431 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 44 45SELECT t1.pk 46FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int 47WHERE t1.col_int_key BETWEEN 5 AND 6 48AND t1.pk IS NULL OR t1.pk IN (5) 49ORDER BY pk; 50pk 51 52DROP TABLE t1,t2; 53# End BUG#58456 54CREATE TABLE t1(a int, b int, KEY (a), PRIMARY KEY (b)) ENGINE=InnoDB; 55CREATE TABLE t2 (b int, PRIMARY KEY (b)); 56INSERT INTO t2 VALUES (4),(9); 57SELECT STRAIGHT_JOIN t1.a FROM t1 RIGHT JOIN t2 ON t1.b = t2.b 58WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4 59GROUP BY 1; 60a 61DROP TABLE t1,t2; 62# 63Bug #59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN 64# 65CREATE TABLE t1 ( 66pk int(11) NOT NULL, 67col_varchar_10_latin1_key varchar(10) DEFAULT NULL 68) ENGINE=InnoDB DEFAULT CHARSET=latin1; 69INSERT INTO t1 VALUES (1,'1'); 70CREATE TABLE t2 ( 71pk int(11) NOT NULL 72) ENGINE=InnoDB DEFAULT CHARSET=latin1; 73INSERT INTO t2 VALUES (1); 74CREATE TABLE t3 ( 75pk int(11) NOT NULL 76) ENGINE=InnoDB DEFAULT CHARSET=latin1; 77INSERT INTO t3 VALUES (1); 78CREATE TABLE t4 ( 79pk int(11) NOT NULL, 80col_int int(11) DEFAULT NULL, 81col_int_key int(11) DEFAULT NULL, 82col_varchar_10_latin1_key varchar(10) DEFAULT NULL 83) ENGINE=InnoDB DEFAULT CHARSET=latin1; 84INSERT INTO t4 VALUES (1,1,1,'1'); 85CREATE TABLE t5 ( 86col_int int(11) DEFAULT NULL, 87col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL 88) ENGINE=InnoDB DEFAULT CHARSET=latin1; 89INSERT INTO t5 VALUES (1,'1'); 90CREATE TABLE t6 ( 91col_int_key int(11) DEFAULT NULL, 92col_varchar_10_latin1_key varchar(10) DEFAULT NULL, 93pk int(11) NOT NULL 94) ENGINE=InnoDB DEFAULT CHARSET=latin1; 95INSERT INTO t6 VALUES (1,'1',1); 96SELECT STRAIGHT_JOIN t6a.pk, t2.pk 97FROM t6 AS t6a 98LEFT JOIN 99( 100t2 101RIGHT JOIN 102( 103(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) 104LEFT JOIN 105(t5 JOIN t6 AS t6b 106ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) 107ON t1.pk = t5.col_int 108) 109ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key 110AND t5.col_varchar_10_utf8_key = 0 111) 112ON t6a.pk IS TRUE 113WHERE t6b.col_int_key IS TRUE; 114pk pk 1151 NULL 116EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk 117FROM t6 AS t6a 118LEFT JOIN 119( 120t2 121RIGHT JOIN 122( 123(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) 124LEFT JOIN 125(t5 JOIN t6 AS t6b 126ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) 127ON t1.pk = t5.col_int 128) 129ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key 130AND t5.col_varchar_10_utf8_key = 0 131) 132ON t6a.pk IS TRUE 133WHERE t6b.col_int_key IS TRUE; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where 1361 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 1371 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1381 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) 1391 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1401 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1411 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 142SELECT t6a.pk, t2.pk 143FROM t6 AS t6a 144LEFT JOIN 145( 146t2 147RIGHT JOIN 148( 149(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) 150LEFT JOIN 151(t5 JOIN t6 AS t6b 152ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) 153ON t1.pk = t5.col_int 154) 155ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key 156AND t5.col_varchar_10_utf8_key = 0 157) 158ON t6a.pk IS TRUE 159WHERE t6b.col_int_key IS TRUE; 160pk pk 1611 NULL 162EXPLAIN SELECT t6a.pk, t2.pk 163FROM t6 AS t6a 164LEFT JOIN 165( 166t2 167RIGHT JOIN 168( 169(t1 LEFT JOIN (t4 JOIN t3 ON t4.col_int) ON t4.col_int_key = t1.pk) 170LEFT JOIN 171(t5 JOIN t6 AS t6b 172ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key) 173ON t1.pk = t5.col_int 174) 175ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key 176AND t5.col_varchar_10_utf8_key = 0 177) 178ON t6a.pk IS TRUE 179WHERE t6b.col_int_key IS TRUE; 180id select_type table type possible_keys key key_len ref rows Extra 1811 SIMPLE t6a ALL NULL NULL NULL NULL 1 Using where 1821 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 1831 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1841 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join) 1851 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1861 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1871 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 188drop table t1,t2,t3,t4,t5,t6; 189# 190Bug mdev-4318: view over a complex query with outer joins 191# 192CREATE TABLE t1 ( 193a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL, 194a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL, 195a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL, 196PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6), 197KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9) 198) ENGINE=InnoDB DEFAULT CHARSET=utf8; 199INSERT IGNORE INTO t1 VALUES 200(3360,5684,2219,1,316832,1,0,NULL,NULL,NULL), 201(3362,2754,597,2,316844,1,0,NULL,NULL,NULL), 202(3363,369,NULL,1,317295,1,0,NULL,NULL,NULL); 203Warnings: 204Warning 1048 Column 'a10' cannot be null 205Warning 1048 Column 'a10' cannot be null 206Warning 1048 Column 'a10' cannot be null 207CREATE TABLE t2 ( 208b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2) 209) ENGINE=InnoDB DEFAULT CHARSET=utf8; 210CREATE TABLE t3 ( 211c1 int NOT NULL, PRIMARY KEY (c1) 212) ENGINE=InnoDB DEFAULT CHARSET=utf8; 213INSERT INTO t3 VALUES 214(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12), 215(1000),(1001),(1002),(1003),(9999); 216CREATE TABLE t4 ( 217d1 int NOT NULL, PRIMARY KEY (d1) 218) ENGINE=InnoDB DEFAULT CHARSET=utf8; 219INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685); 220CREATE TABLE t5 ( 221e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1) 222) ENGINE=InnoDB DEFAULT CHARSET=utf8; 223INSERT INTO t5 VALUES 224(5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'), 225(5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'), 226(5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c'); 227CREATE TABLE t6 ( 228f1 int NOT NULL, PRIMARY KEY (f1) 229) ENGINE=InnoDB DEFAULT CHARSET=utf8; 230INSERT INTO t6 VALUES (5542),(5620),(5686); 231CREATE TABLE t7 ( 232g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1) 233) ENGINE=InnoDB DEFAULT CHARSET=latin1; 234INSERT INTO t7 VALUES 235(1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'), 236(5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'), 237(9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309'); 238CREATE TABLE t8 ( 239h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1) 240) ENGINE=InnoDB DEFAULT CHARSET=utf8; 241INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e'); 242CREATE TABLE t9 ( 243i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL, 244PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3) 245) ENGINE=InnoDB DEFAULT CHARSET=utf8; 246INSERT INTO t9 VALUES 247(2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'), 248(3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'), 249(1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'), 250(4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'), 251(3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'), 252(2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'), 253(4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'), 254(1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861'); 255CREATE TABLE t10 ( 256j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1) 257) ENGINE=InnoDB DEFAULT CHARSET=utf8; 258INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d'); 259CREATE TABLE t11 ( 260k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL, 261k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4) 262) ENGINE=InnoDB DEFAULT CHARSET=utf8; 263INSERT INTO t11 VALUES 264(317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL), 265(317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1), 266(317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL), 267(317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL), 268(317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL), 269(317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL), 270(317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL), 271(317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL), 272(317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL), 273(317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL), 274(317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL); 275CREATE TABLE t12 ( 276l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1) 277) ENGINE=InnoDB DEFAULT CHARSET=utf8; 278INSERT INTO t12 VALUES 279(552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'), 280(554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'), 281(555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5'); 282CREATE TABLE t13 ( 283m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL, 284PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2) 285) ENGINE=InnoDB DEFAULT CHARSET=utf8; 286INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3); 287CREATE TABLE t14 ( 288n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1) 289) ENGINE=InnoDB DEFAULT CHARSET=utf8; 290INSERT INTO t14 VALUES 291(21,'685bf7ca576af964c7cff564d5e4473b81499b8b'), 292(23,'b8e42dab1ab952406b3accfb47089c61478138a8'), 293(25,'3fea441e411db8c70bf039b50c8f18f59515be53'), 294(27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223'); 295CREATE TABLE t15 ( 296o1 smallint NOT NULL, PRIMARY KEY (o1) 297) ENGINE=InnoDB DEFAULT CHARSET=utf8; 298INSERT INTO t15 VALUES (1),(3); 299CREATE TABLE t16 ( 300p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL, 301PRIMARY KEY (p1,p2) 302) ENGINE=InnoDB DEFAULT CHARSET=utf8; 303INSERT INTO t16 VALUES 304(1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'), 305(1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'), 306(3,'a','386c10e454278c6e27feb16258089166422f79b4'), 307(3,'b','386c10e454278c6e27feb16258089166422f79b4'); 308create view v1 as select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2, 309t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1 310from 311( 312( 313( 314( 315( 316( 317( 318( 319( 320( 321( 322( 323( 324( 325( 326( 327( 328( 329( 330t1 331left join t2 on t1.a1 = t2.b1 332) 333left join t3 on t2.b2 = t3.c1 334) 335left join t4 on t1.a2 = t4.d1 336) 337left join t5 on t4.d1 = t5.e1 338) 339left join t6 on t1.a3 = t6.f1 340) 341left join t5 e2 on t6.f1 = e2.e1 342) 343join t7 on t1.a7 = t7.g1 344) 345join t8 on t1.a4 = t8.h1 346) 347join t9 on t8.h1 = t9.i1 348) 349join t10 on t1.a6 = t10.j1 350) 351join t11 on t1.a5 = t11.k1 352) 353left join t12 on t11.k3 = t12.l1 354) 355left join t12 l2 on t11.k4 = l2.l1 356) 357left join t13 on t1.a1 = t13.m1 and t13.m3 = 4 358) 359left join t12 l4 on l4.l1 = t13.m2 360) 361left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3 362) 363left join t12 l3 on l3.l1 = m2.m2 364) 365left join t14 on t1.a8 = t14.n1 366) 367left join t15 on t1.a9 = t15.o1 368) 369left join t16 on t15.o1 = t16.p1 370where t1.a10 = 1; 371explain select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2, 372t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1 373from 374( 375( 376( 377( 378( 379( 380( 381( 382( 383( 384( 385( 386( 387( 388( 389( 390( 391( 392( 393t1 394left join t2 on t1.a1 = t2.b1 395) 396left join t3 on t2.b2 = t3.c1 397) 398left join t4 on t1.a2 = t4.d1 399) 400left join t5 on t4.d1 = t5.e1 401) 402left join t6 on t1.a3 = t6.f1 403) 404left join t5 e2 on t6.f1 = e2.e1 405) 406join t7 on t1.a7 = t7.g1 407) 408join t8 on t1.a4 = t8.h1 409) 410join t9 on t8.h1 = t9.i1 411) 412join t10 on t1.a6 = t10.j1 413) 414join t11 on t1.a5 = t11.k1 415) 416left join t12 on t11.k3 = t12.l1 417) 418left join t12 l2 on t11.k4 = l2.l1 419) 420left join t13 on t1.a1 = t13.m1 and t13.m3 = 4 421) 422left join t12 l4 on l4.l1 = t13.m2 423) 424left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3 425) 426left join t12 l3 on l3.l1 = m2.m2 427) 428left join t14 on t1.a8 = t14.n1 429) 430left join t15 on t1.a9 = t15.o1 431) 432left join t16 on t15.o1 = t16.p1 433where t1.a10 = 1; 434id select_type table type possible_keys key key_len ref rows Extra 4351 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 4361 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 4371 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index 4381 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index 4391 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where 4401 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 4411 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 4421 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 4431 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 4441 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 4451 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 4461 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 4471 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 4481 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 4491 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 4501 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 4511 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 4521 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 4531 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where 4541 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 455explain select * from v1; 456id select_type table type possible_keys key key_len ref rows Extra 4571 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where 4581 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index 4591 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index 4601 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index 4611 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where 4621 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index 4631 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index 4641 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1 4651 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1 4661 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1 4671 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where 4681 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where 4691 SIMPLE t13 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 4701 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index 4711 SIMPLE m2 ref PRIMARY,m3 m3 8 const,test.t1.a1 1 Using index 4721 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where 4731 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where 4741 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index 4751 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where 4761 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 477drop view v1; 478drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16; 479# 480# MDEV-4270: crash in fix_semijoin_strategies_for_picked_join_order 481# 482drop table if exists t1,t2,t3; 483Warnings: 484Note 1051 Unknown table 'test.t1,test.t2,test.t3' 485create table t2(a int,unique key (a)) engine=innodb; 486create table t3(b int) engine=innodb; 487create table t1(c int,b int)engine=innodb; 488set @mdev4270_opl= @@optimizer_prune_level; 489set @mdev4270_osd= @@optimizer_search_depth; 490set optimizer_prune_level=0; 491set optimizer_search_depth=2; 492select 1 from t1 join t2 a 493natural left join t2 b 494natural right outer join t3; 4951 496drop table t1,t2,t3; 497set optimizer_prune_level=@mdev4270_opl; 498set optimizer_search_depth=@mdev4270_osd; 499# 500# Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE 501# RECORD 502# 503CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ) engine=innodb; 504CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)) engine=innodb; 505INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); 506INSERT INTO t2 SELECT * FROM t1; 507SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 508START TRANSACTION; 509SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; 510c1 c2 c3 c1 c2 c3 5111 2 3 1 2 3 5122 3 4 NULL NULL NULL 5133 4 5 NULL NULL NULL 514UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; 515COMMIT; 516SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 517DROP TABLE t1,t2; 518