1drop table if exists t0, t1, t2, t3, t4, t5, t6; 2drop view if exists v1, v2; 3SET @save_optimizer_switch=@@optimizer_switch; 4SET optimizer_switch='outer_join_with_cache=off'; 5create table t1 (a int); 6insert into t1 values (0),(1),(2),(3); 7create table t0 as select * from t1; 8create table t2 (a int primary key, b int) 9as select a, a as b from t1 where a in (1,2); 10create table t3 (a int primary key, b int) 11as select a, a as b from t1 where a in (1,3); 12# This will be eliminated: 13explain select t1.a from t1 left join t2 on t2.a=t1.a; 14id select_type table type possible_keys key key_len ref rows Extra 151 SIMPLE t1 ALL NULL NULL NULL NULL 4 16explain extended select t1.a from t1 left join t2 on t2.a=t1.a; 17id select_type table type possible_keys key key_len ref rows filtered Extra 181 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 19Warnings: 20Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 21select t1.a from t1 left join t2 on t2.a=t1.a; 22a 230 241 252 263 27# This will not be eliminated as t2.b is in in select list: 28explain select * from t1 left join t2 on t2.a=t1.a; 29id select_type table type possible_keys key key_len ref rows Extra 301 SIMPLE t1 ALL NULL NULL NULL NULL 4 311 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 32# This will not be eliminated as t2.b is in in order list: 33explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; 34id select_type table type possible_keys key key_len ref rows Extra 351 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 361 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 37# This will not be eliminated as t2.b is in group list: 38explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; 39id select_type table type possible_keys key key_len ref rows Extra 401 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 411 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 42# This will not be eliminated as t2.b is in the WHERE 43explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; 44id select_type table type possible_keys key key_len ref rows Extra 451 SIMPLE t1 ALL NULL NULL NULL NULL 4 461 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 47# Elimination of multiple tables: 48explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; 49id select_type table type possible_keys key key_len ref rows Extra 501 SIMPLE t1 ALL NULL NULL NULL NULL 4 51# Elimination of multiple tables (2): 52explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; 53id select_type table type possible_keys key key_len ref rows Extra 541 SIMPLE t1 ALL NULL NULL NULL NULL 4 55# Elimination when done within an outer join nest: 56explain extended 57select t0.* 58from 59t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and 60t3.a=t1.a) on t0.a=t1.a; 61id select_type table type possible_keys key key_len ref rows filtered Extra 621 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00 631 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where 64Warnings: 65Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on(`test`.`t1`.`a` = `test`.`t0`.`a`) where 1 66# Elimination with aggregate functions 67explain select count(*) from t1 left join t2 on t2.a=t1.a; 68id select_type table type possible_keys key key_len ref rows Extra 691 SIMPLE t1 ALL NULL NULL NULL NULL 4 70explain select count(1) from t1 left join t2 on t2.a=t1.a; 71id select_type table type possible_keys key key_len ref rows Extra 721 SIMPLE t1 ALL NULL NULL NULL NULL 4 73explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a; 74id select_type table type possible_keys key key_len ref rows Extra 751 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 76This must not use elimination: 77explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; 78id select_type table type possible_keys key key_len ref rows Extra 791 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort 801 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index 81drop table t0, t1, t2, t3; 82create table t0 ( id integer, primary key (id)); 83create table t1 ( 84id integer, 85attr1 integer, 86primary key (id), 87key (attr1) 88); 89create table t2 ( 90id integer, 91attr2 integer, 92fromdate date, 93primary key (id, fromdate), 94key (attr2,fromdate) 95); 96insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 97insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0; 98insert into t1 select id, id from t0; 99insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0; 100insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0; 101create view v1 as 102select 103f.id, a1.attr1, a2.attr2 104from 105t0 f 106left join t1 a1 on a1.id=f.id 107left join t2 a2 on a2.id=f.id and 108a2.fromdate=(select MAX(fromdate) from 109t2 where id=a2.id); 110create view v2 as 111select 112f.id, a1.attr1, a2.attr2 113from 114t0 f 115left join t1 a1 on a1.id=f.id 116left join t2 a2 on a2.id=f.id and 117a2.fromdate=(select MAX(fromdate) from 118t2 where id=f.id); 119This should use one table: 120explain select id from v1 where id=2; 121id select_type table type possible_keys key key_len ref rows Extra 1221 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index 123This should use one table: 124explain extended select id from v1 where id in (1,2,3,4); 125id select_type table type possible_keys key key_len ref rows filtered Extra 1261 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index 127Warnings: 128Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 129Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4) 130This should use facts and a1 tables: 131explain extended select id from v1 where attr1 between 12 and 14; 132id select_type table type possible_keys key key_len ref rows filtered Extra 1331 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition 1341 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index 135Warnings: 136Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 137Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14 138This should use facts, a2 and its subquery: 139explain extended select id from v1 where attr2 between 12 and 14; 140id select_type table type possible_keys key key_len ref rows filtered Extra 1411 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 4 100.00 Using index condition; Using where 1421 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 1433 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index 144Warnings: 145Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 146Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`a2`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `a2`.`id`)) 147This should use one table: 148explain select id from v2 where id=2; 149id select_type table type possible_keys key key_len ref rows Extra 1501 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index 151This should use one table: 152explain extended select id from v2 where id in (1,2,3,4); 153id select_type table type possible_keys key key_len ref rows filtered Extra 1541 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index 155Warnings: 156Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 157Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` where `f`.`id` in (1,2,3,4) 158This should use facts and a1 tables: 159explain extended select id from v2 where attr1 between 12 and 14; 160id select_type table type possible_keys key key_len ref rows filtered Extra 1611 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition 1621 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index 163Warnings: 164Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 165Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where `f`.`id` = `a1`.`id` and `a1`.`attr1` between 12 and 14 166This should use facts, a2 and its subquery: 167explain extended select id from v2 where attr2 between 12 and 14; 168id select_type table type possible_keys key key_len ref rows filtered Extra 1691 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 4 100.00 Using index condition 1701 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 1713 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index 172Warnings: 173Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 174Note 1003 /* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`f`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `f`.`id`)) 175drop view v1, v2; 176drop table t0, t1, t2; 177create table t1 (a int); 178insert into t1 values (0),(1),(2),(3); 179create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3)); 180insert into t2 select a,a,a,a from t1; 181This must use only t1: 182explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 183t2.pk2=t2.pk1+1 and 184t2.pk3=t2.pk2+1; 185id select_type table type possible_keys key key_len ref rows Extra 1861 SIMPLE t1 ALL NULL NULL NULL NULL 4 187This must use only t1: 188explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 189t2.pk3=t2.pk1+1 and 190t2.pk2=t2.pk3+1; 191id select_type table type possible_keys key key_len ref rows Extra 1921 SIMPLE t1 ALL NULL NULL NULL NULL 4 193This must use both: 194explain select t1.* from t1 left join t2 on t2.pk1=t1.a and 195t2.pk3=t2.pk1+1 and 196t2.pk2=t2.pk3+t2.col; 197id select_type table type possible_keys key key_len ref rows Extra 1981 SIMPLE t1 ALL NULL NULL NULL NULL 4 1991 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 Using where 200This must use only t1: 201explain select t1.* from t1 left join t2 on t2.pk2=t1.a and 202t2.pk1=t2.pk2+1 and 203t2.pk3=t2.pk1; 204id select_type table type possible_keys key key_len ref rows Extra 2051 SIMPLE t1 ALL NULL NULL NULL NULL 4 206drop table t1, t2; 207create table t1 (pk int primary key, col int); 208insert into t1 values (1,1),(2,2); 209create table t2 like t1; 210insert into t2 select * from t1; 211create table t3 like t1; 212insert into t3 select * from t1; 213explain 214select t1.* from t1 left join ( t2 left join t3 on t3.pk=t2.col) on t2.col=t1.col; 215id select_type table type possible_keys key key_len ref rows Extra 2161 SIMPLE t1 ALL NULL NULL NULL NULL 2 2171 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 218explain 219select t1.*, t2.* from t1 left join (t2 left join t3 on t3.pk=t2.col) on t2.pk=t1.col; 220id select_type table type possible_keys key key_len ref rows Extra 2211 SIMPLE t1 ALL NULL NULL NULL NULL 2 2221 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where 223explain select t1.* 224from 225t1 left join ( t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 226on t2.col=t1.col or t2.col=t1.col; 227id select_type table type possible_keys key key_len ref rows Extra 2281 SIMPLE t1 ALL NULL NULL NULL NULL 2 2291 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 230explain select t1.*, t2.* 231from 232t1 left join 233(t2 left join t3 on t3.pk=t2.col or t3.pk=t2.col) 234on t2.pk=t1.col or t2.pk=t1.col; 235id select_type table type possible_keys key key_len ref rows Extra 2361 SIMPLE t1 ALL NULL NULL NULL NULL 2 2371 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.col 1 Using where 238drop table t1, t2, t3; 239# 240# Check things that look like functional dependencies but really are not 241# 242create table t1 (a char(10) character set latin1 collate latin1_general_ci primary key); 243insert into t1 values ('foo'); 244insert into t1 values ('bar'); 245create table t2 (a char(10) character set latin1 collate latin1_general_cs primary key); 246insert into t2 values ('foo'); 247insert into t2 values ('FOO'); 248this must not use table elimination: 249explain select t1.* from t1 left join t2 on t2.a='foo' collate latin1_general_ci; 250id select_type table type possible_keys key key_len ref rows Extra 2511 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index 2521 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index 253this must not use table elimination: 254explain select t1.* from t1 left join t2 on t2.a=t1.a collate latin1_general_ci; 255id select_type table type possible_keys key key_len ref rows Extra 2561 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index 2571 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 258drop table t1,t2; 259create table t1 (a int primary key); 260insert into t1 values (1),(2); 261create table t2 (a char(10) primary key); 262insert into t2 values ('1'),('1.0'); 263this must not use table elimination: 264explain select t1.* from t1 left join t2 on t2.a=1; 265id select_type table type possible_keys key key_len ref rows Extra 2661 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 2671 SIMPLE t2 index PRIMARY PRIMARY 10 NULL 2 Using where; Using index 268this must not use table elimination: 269explain select t1.* from t1 left join t2 on t2.a=t1.a; 270id select_type table type possible_keys key key_len ref rows Extra 2711 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index 2721 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Range checked for each record (index map: 0x1) 273drop table t1, t2; 274create table t1 (a char(10) primary key); 275insert into t1 values ('foo'),('bar'); 276create table t2 (a char(10), unique key(a(2))); 277insert into t2 values 278('foo'),('bar'),('boo'),('car'),('coo'),('par'),('doo'),('tar'); 279explain select t1.* from t1 left join t2 on t2.a=t1.a; 280id select_type table type possible_keys key key_len ref rows Extra 2811 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index 2821 SIMPLE t2 eq_ref a a 3 test.t1.a 1 Using where 283drop table t1, t2; 284# 285# check UPDATE/DELETE that look like they could be eliminated 286# 287create table t1 (a int primary key, b int); 288insert into t1 values (1,1),(2,2),(3,3); 289create table t2 like t1; 290insert into t2 select * from t1; 291update t1 left join t2 using (a) set t2.a=t2.a+100; 292select * from t1; 293a b 2941 1 2952 2 2963 3 297select * from t2; 298a b 299101 1 300102 2 301103 3 302delete from t2; 303insert into t2 select * from t1; 304delete t2 from t1 left join t2 using (a); 305select * from t1; 306a b 3071 1 3082 2 3093 3 310select * from t2; 311a b 312drop table t1, t2; 313# 314# Tests with various edge-case ON expressions 315# 316create table t1 (a int, b int, c int, d int); 317insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); 318create table t2 (pk int primary key, b int) 319as select a as pk, a as b from t1 where a in (1,2); 320create table t3 (pk1 int, pk2 int, b int, unique(pk1,pk2)); 321insert into t3 select a as pk1, a as pk2, a as b from t1 where a in (1,3); 322explain select t1.a from t1 left join t2 on t2.pk=t1.a and t2.b<t1.b; 323id select_type table type possible_keys key key_len ref rows Extra 3241 SIMPLE t1 ALL NULL NULL NULL NULL 4 325explain select t1.a from t1 left join t2 on t2.pk=t1.a or t2.b<t1.b; 326id select_type table type possible_keys key key_len ref rows Extra 3271 SIMPLE t1 ALL NULL NULL NULL NULL 4 3281 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where 329explain select t1.a from t1 left join t2 on t2.b<t1.b or t2.pk=t1.a; 330id select_type table type possible_keys key key_len ref rows Extra 3311 SIMPLE t1 ALL NULL NULL NULL NULL 4 3321 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where 333explain select t1.a from t1 left join t2 on t2.pk between 10 and 20; 334id select_type table type possible_keys key key_len ref rows Extra 3351 SIMPLE t1 ALL NULL NULL NULL NULL 4 3361 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index 337explain select t1.a from t1 left join t2 on t2.pk between 0.5 and 1.5; 338id select_type table type possible_keys key key_len ref rows Extra 3391 SIMPLE t1 ALL NULL NULL NULL NULL 4 3401 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index 341explain select t1.a from t1 left join t2 on t2.pk between 10 and 10; 342id select_type table type possible_keys key key_len ref rows Extra 3431 SIMPLE t1 ALL NULL NULL NULL NULL 4 344explain select t1.a from t1 left join t2 on t2.pk in (10); 345id select_type table type possible_keys key key_len ref rows Extra 3461 SIMPLE t1 ALL NULL NULL NULL NULL 4 347explain select t1.a from t1 left join t2 on t2.pk in (t1.a); 348id select_type table type possible_keys key key_len ref rows Extra 3491 SIMPLE t1 ALL NULL NULL NULL NULL 4 350explain select t1.a from t1 left join t2 on TRUE; 351id select_type table type possible_keys key key_len ref rows Extra 3521 SIMPLE t1 ALL NULL NULL NULL NULL 4 3531 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using where; Using index 354explain select t1.a from t1 left join t3 on t3.pk1=t1.a and t3.pk2 IS NULL; 355id select_type table type possible_keys key key_len ref rows Extra 3561 SIMPLE t1 ALL NULL NULL NULL NULL 4 357drop table t1,t2,t3; 358# 359# Multi-equality tests 360# 361create table t1 (a int, b int, c int, d int); 362insert into t1 values (0,0,0,0),(1,1,1,1),(2,2,2,2),(3,3,3,3); 363create table t2 (pk int primary key, b int, c int); 364insert into t2 select a,a,a from t1 where a in (1,2); 365explain 366select t1.* 367from t1 left join t2 on t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b 368where t1.d=1; 369id select_type table type possible_keys key key_len ref rows Extra 3701 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 371explain 372select t1.* 373from 374t1 375left join 376t2 377on (t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) or 378(t2.pk=t2.c and t2.b=t1.a and t1.a=t1.b and t2.c=t2.b) 379where t1.d=1; 380id select_type table type possible_keys key key_len ref rows Extra 3811 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 382#This can't be eliminated: 383explain 384select t1.* 385from 386t1 387left join 388t2 389on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or 390(t2.pk=t2.c and t1.a=t1.b and t2.c=t1.b) 391where t1.d=1; 392id select_type table type possible_keys key key_len ref rows Extra 3931 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 3941 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where 395explain 396select t1.* 397from 398t1 399left join 400t2 401on (t2.pk=t2.c and t2.b=t1.a and t2.c=t1.b) or 402(t2.pk=t2.c and t2.c=t1.b) 403; 404id select_type table type possible_keys key key_len ref rows Extra 4051 SIMPLE t1 ALL NULL NULL NULL NULL 4 406explain 407select t1.* 408from t1 left join t2 on t2.pk=3 or t2.pk= 4; 409id select_type table type possible_keys key key_len ref rows Extra 4101 SIMPLE t1 ALL NULL NULL NULL NULL 4 4111 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index 412explain 413select t1.* 414from t1 left join t2 on t2.pk=3 or t2.pk= 3; 415id select_type table type possible_keys key key_len ref rows Extra 4161 SIMPLE t1 ALL NULL NULL NULL NULL 4 417explain 418select t1.* 419from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); 420id select_type table type possible_keys key key_len ref rows Extra 4211 SIMPLE t1 ALL NULL NULL NULL NULL 4 4221 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where 423drop table t1, t2; 424# 425# LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB 426# 427CREATE TABLE t1 ( 428`pk` int(11) NOT NULL AUTO_INCREMENT, 429`col_int_nokey` int(11) DEFAULT NULL, 430`col_int_key` int(11) DEFAULT NULL, 431`col_date_key` date DEFAULT NULL, 432`col_date_nokey` date DEFAULT NULL, 433`col_time_key` time DEFAULT NULL, 434`col_time_nokey` time DEFAULT NULL, 435`col_datetime_key` datetime DEFAULT NULL, 436`col_datetime_nokey` datetime DEFAULT NULL, 437`col_varchar_key` varchar(1) DEFAULT NULL, 438`col_varchar_nokey` varchar(1) DEFAULT NULL, 439PRIMARY KEY (`pk`), 440KEY `col_int_key` (`col_int_key`), 441KEY `col_date_key` (`col_date_key`), 442KEY `col_time_key` (`col_time_key`), 443KEY `col_datetime_key` (`col_datetime_key`), 444KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) 445); 446CREATE TABLE t2 LIKE t1; 447INSERT INTO t1 VALUES 448(10,7,8,NULL,NULL,'01:27:35','01:27:35','2002-02-26 06:14:37','2002-02-26 06:14:37','v','v'), 449(11,1,9,'2006-06-14','2006-06-14','19:48:31','19:48:31','1900-01-01 00:00:00','1900-01-01 00:00:00','r','r'); 450INSERT INTO t2 SELECT * FROM t1; 451SELECT table2.col_int_key AS field1 452FROM ( 453t2 AS table1 454RIGHT OUTER JOIN 455( 456( t1 AS table2 STRAIGHT_JOIN 457t1 AS table3 ON ( 458(table3.col_varchar_nokey = table2.col_varchar_key ) AND 459(table3.pk = table2.col_int_key)) 460) 461) ON 462( 463(table3.col_varchar_key = table2.col_varchar_key) OR 464(table3.col_int_key = table2.pk) 465) 466) 467HAVING field1 < 216; 468field1 469DROP TABLE t1, t2; 470# 471# LPBUG#524025 Running RQG outer_join test leads to crash 472# 473CREATE TABLE t0 ( 474pk int(11) NOT NULL AUTO_INCREMENT, 475PRIMARY KEY (pk) 476); 477CREATE TABLE t1 ( 478col_int int(11) DEFAULT NULL, 479col_int_key int(11) DEFAULT NULL, 480pk int(11) NOT NULL AUTO_INCREMENT, 481col_varchar_10_latin1 varchar(10) DEFAULT NULL, 482PRIMARY KEY (pk) 483); 484INSERT INTO t1 VALUES (5,5,1,'t'), (NULL,NULL,2,'y'); 485CREATE TABLE t2 ( 486col_int int(11) DEFAULT NULL 487); 488INSERT INTO t2 VALUES (8), (4); 489CREATE TABLE t3 ( 490pk int(11) NOT NULL AUTO_INCREMENT, 491PRIMARY KEY (pk) 492); 493INSERT INTO t3 VALUES (1),(8); 494CREATE TABLE t4 ( 495pk int(11) NOT NULL AUTO_INCREMENT, 496col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, 497col_int int(11) DEFAULT NULL, 498PRIMARY KEY (pk) 499); 500INSERT INTO t4 VALUES (1,'o',1), (2,'w',2); 501CREATE TABLE t5 ( 502col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, 503col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, 504col_varchar_10_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL, 505pk int(11) NOT NULL AUTO_INCREMENT, 506col_int_key int(11) DEFAULT NULL, 507PRIMARY KEY (pk) 508); 509INSERT INTO t5 VALUES ('k','a','z',1,2),('x','a','w',2,7); 510CREATE TABLE t6 ( 511col_int int(11) DEFAULT NULL, 512col_int_key int(11) DEFAULT NULL 513); 514INSERT INTO t6 VALUES (6,1),(8,3); 515SELECT 516table3.col_int AS field1, 517table1.col_int AS field2, 518table1.col_int_key AS field3, 519table1.pk AS field4, 520table1.col_int AS field5, 521table2.col_int AS field6 522FROM 523t1 AS table1 524LEFT OUTER JOIN 525t4 AS table2 526LEFT JOIN t6 AS table3 527RIGHT JOIN t3 AS table4 528LEFT JOIN t5 AS table5 ON table4.pk = table5.pk 529LEFT JOIN t0 AS table6 ON table5.col_int_key = table6.pk 530ON table3.col_int_key = table5.pk 531ON table2.col_varchar_1024_latin1_key = table5.col_varchar_10_utf8_key 532LEFT JOIN t6 AS table7 ON table2.pk = table7.col_int 533ON table1.col_varchar_10_latin1 = table5.col_varchar_1024_latin1_key 534LEFT JOIN t2 AS table8 ON table3.col_int = table8.col_int 535WHERE 536table1.col_int_key < table2.pk 537HAVING 538field4 != 6; 539field1 field2 field3 field4 field5 field6 540drop table t0,t1,t2,t3,t4,t5,t6; 541# 542# BUG#675118: Elimination of a table results in an invalid execution plan 543# 544CREATE TABLE t1 (f1 int(11), PRIMARY KEY (f1)) ; 545CREATE TABLE t2 (f4 varchar(1024), KEY (f4)) ; 546Warnings: 547Note 1071 Specified key was too long; max key length is 1000 bytes 548INSERT IGNORE INTO t2 VALUES ('xcddwntkbxyorzdv'), 549('cnxxcddwntkbxyor'),('r'),('r'), ('did'),('I'),('when'), 550('hczkfqjeggivdvac'),('e'),('okay'),('up'); 551CREATE TABLE t3 (f4 varchar(1024), f1 int(11), f2 int(11)) ; 552INSERT IGNORE INTO t3 VALUES ('f','4','0'),('n','5','-996540416'); 553CREATE TABLE t4 (f1 int(11), f3 varchar(10)) ; 554INSERT IGNORE INTO t4 VALUES ('8','n'),('9','nwzcerzsgx'),('10','c'); 555CREATE TABLE t5 (f5 int(11), KEY (f5)) ; 556EXPLAIN 557SELECT t3.f2 558FROM t2 559LEFT JOIN t3 560LEFT JOIN t4 561LEFT JOIN t1 ON t4.f1 = t1.f1 562JOIN t5 ON t4.f3 ON t3.f1 = t5.f5 ON t2.f4 = t3.f4 563WHERE t3.f2 ; 564id select_type table type possible_keys key key_len ref rows Extra 5651 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where 5661 SIMPLE t5 ref f5 f5 5 test.t3.f1 2 Using where; Using index 5671 SIMPLE t4 ALL NULL NULL NULL NULL 3 Using where 5681 SIMPLE t2 ALL f4 NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) 569# ^^ The above must not produce a QEP of t3,t5,t2,t4 570# as that violates the "no interleaving of outer join nests" rule. 571DROP TABLE t1,t2,t3,t4,t5; 572# 573# BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)' 574# failed in greedy_search with LEFT JOINs and unique keys 575# 576CREATE TABLE t1 (a1 INT); 577CREATE TABLE t2 (b1 INT); 578CREATE TABLE t3 (c1 INT, UNIQUE KEY(c1)); 579CREATE TABLE t4 (d1 INT, UNIQUE KEY(d1)); 580CREATE TABLE t5 (e1 INT); 581INSERT INTO t1 VALUES (1),(2); 582INSERT INTO t2 VALUES (2),(3); 583INSERT INTO t3 VALUES (3),(4); 584INSERT INTO t4 VALUES (4),(5); 585INSERT INTO t5 VALUES (5),(6); 586SELECT a1 FROM t1 LEFT JOIN t2 LEFT JOIN t3 LEFT JOIN t4 587ON c1 = d1 ON d1 = b1 ON a1 = b1 588LEFT JOIN t5 ON a1 = e1 ; 589a1 5901 5912 592DROP TABLE t1,t2,t3,t4,t5; 593# 594# BUG#884184: Wrong result with RIGHT JOIN + derived_merge 595# 596CREATE TABLE t1 (a int(11), b varchar(1)) ; 597INSERT IGNORE INTO t1 VALUES (0,'g'); 598CREATE TABLE t3 ( a varchar(1)) ; 599INSERT IGNORE INTO t3 VALUES ('g'); 600CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)); 601INSERT INTO t2 VALUES (9), (10); 602create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; 603SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); 604a b 605NULL NULL 606EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); 607id select_type table type possible_keys key key_len ref rows Extra 6081 SIMPLE t3 system NULL NULL NULL NULL 1 6091 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where 6101 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index 611drop view v1; 612DROP TABLE t1,t2,t3; 613# 614# MDEV-5344: LEFT OUTER JOIN table data is lost in ON DUPLICATE KEY UPDATE section 615# 616create table t1 ( 617id int(10) unsigned NOT NULL DEFAULT '0', 618v int(10) unsigned DEFAULT '0', 619PRIMARY KEY (id) 620); 621create table t2 ( 622id int(10) unsigned NOT NULL DEFAULT '0', 623PRIMARY KEY (id) 624) ; 625create table t3 ( 626id int(10) unsigned NOT NULL DEFAULT '0', 627v int(10) unsigned DEFAULT '0', 628PRIMARY KEY (id) 629); 630insert into t1 values (1, 10), (2, 10); 631insert into t2 values (1), (2); 632insert into t3 values (1, 20); 633insert into t1 634select t2.id, 5 from t2 LEFT OUTER JOIN t3 ON t2.id = t3.id 635on duplicate key update t1.v = t3.v; 636select * from t1; 637id v 6381 20 6392 NULL 640drop table t1,t2,t3; 641# 642# BUG#919878: Assertion `!eliminated_tables... 643# 644CREATE TABLE t1 ( a INT ); 645INSERT INTO t1 VALUES (1); 646CREATE TABLE t2 647( b INT, UNIQUE INDEX(b) ); 648INSERT INTO t2 VALUES (1),(2); 649EXPLAIN EXTENDED 650SELECT * FROM t2 651WHERE b IN ( 652SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a 653); 654id select_type table type possible_keys key key_len ref rows filtered Extra 6551 PRIMARY t2 index NULL b 5 NULL 2 100.00 Using where; Using index 6562 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 657Warnings: 658Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select sum(1) from dual where 1 having <cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))) 659DROP TABLE t1,t2; 660# 661# MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables 662# 663CREATE TABLE t1 (alpha3 VARCHAR(3)); 664INSERT INTO t1 VALUES ('USA'),('CAN'); 665CREATE TABLE t2 ( t3_code VARCHAR(3), name VARCHAR(64)); 666INSERT INTO t2 VALUES ('USA','Austin'),('USA','Boston'); 667CREATE TABLE t3 ( code VARCHAR(3), name VARCHAR(64), PRIMARY KEY (code), UNIQUE KEY (name)); 668INSERT INTO t3 VALUES ('CAN','Canada'),('USA','United States'); 669SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; 670alpha3 t3_code name code name 671USA USA Austin USA United States 672USA USA Boston USA United States 673CAN NULL NULL NULL NULL 674SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) ON t1.alpha3 = t3.code; 675alpha3 676USA 677USA 678CAN 679DROP TABLE t1, t2, t3; 680SET optimizer_switch=@save_optimizer_switch; 681# 682# MDEV-7893: table_elimination works wrong with on computed expression and compound unique key 683# (just a testcase) 684CREATE TABLE t1 ( 685PostID int(10) unsigned NOT NULL 686) DEFAULT CHARSET=utf8; 687INSERT INTO t1 (PostID) VALUES (1), (2); 688CREATE TABLE t2 ( 689VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, 690EntityID int(10) unsigned NOT NULL, 691UserID int(10) unsigned NOT NULL, 692UNIQUE KEY EntityID (EntityID,UserID) 693) DEFAULT CHARSET=utf8; 694INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); 695SELECT t1.*, T.Voted as Voted 696FROM 697t1 LEFT JOIN ( 698SELECT 1 AS Voted, EntityID 699FROM t2 700WHERE t2.UserID = '20' ) AS T 701ON T.EntityID = t1.PostID 702WHERE t1.PostID='1' 703LIMIT 1; 704PostID Voted 7051 NULL 706DROP TABLE t1,t2; 707