1drop table if exists t0,t1,t2,t3,t4,t5; 2SET @org_optimizer_switch=@@optimizer_switch; 3SET optimizer_switch=ifnull(@optimizer_switch_for_join_outer_test,'outer_join_with_cache=off'); 4set join_cache_level=1; 5CREATE TABLE t1 ( 6grp int(11) default NULL, 7a bigint(20) unsigned default NULL, 8c char(10) NOT NULL default '' 9) ENGINE=MyISAM; 10INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,''); 11create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a)); 12insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7); 13select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a; 14grp a c id a c d 151 1 a 1 1 a 1 163 4 E 3 4 A 4 173 5 C 3 5 B 5 183 6 D 3 6 C 6 19select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c; 20grp a c id a c d 21NULL NULL NULL NULL NULL NULL 221 1 a 1 1 a 1 232 2 b NULL NULL NULL NULL 242 3 c NULL NULL NULL NULL 253 4 E 3 4 A 4 263 5 C 3 5 B 5 273 6 D 3 6 C 6 28select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) }; 29grp a c id a c d 301 1 a 1 1 a 1 313 4 E 3 4 A 4 323 5 C 3 5 B 5 333 6 D 3 6 C 6 34NULL NULL NULL 4 7 D 7 35select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2; 36grp a c id a c d 371 1 a 1 1 a 1 383 4 E 3 4 A 4 393 5 C 3 5 B 5 403 6 D 3 6 C 6 41NULL NULL NULL 4 7 D 7 42select t1.*,t2.* from t1 left join t2 using (a); 43grp a c id a c d 441 1 a 1 1 a 1 452 2 b NULL NULL NULL NULL 462 3 c NULL NULL NULL NULL 473 4 E 3 4 A 4 483 5 C 3 5 B 5 493 6 D 3 6 C 6 50NULL NULL NULL NULL NULL NULL 51select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a; 52grp a c id a c d 531 1 a 1 1 a 1 543 4 E 3 4 A 4 553 5 C 3 5 B 5 563 6 D 3 6 C 6 57select t1.*,t2.* from t1 left join t2 using (a,c); 58grp a c id a c d 591 1 a 1 1 a 1 602 2 b NULL NULL NULL NULL 612 3 c NULL NULL NULL NULL 623 4 E NULL NULL NULL NULL 633 5 C NULL NULL NULL NULL 643 6 D NULL NULL NULL NULL 65NULL NULL NULL NULL NULL NULL 66select t1.*,t2.* from t1 left join t2 using (c); 67grp a c id a c d 681 1 a 1 1 a 1 691 1 a 3 4 A 4 702 2 b 3 5 B 5 712 3 c 3 6 C 6 723 4 E NULL NULL NULL NULL 733 5 C 3 6 C 6 743 6 D 4 7 D 7 75NULL NULL NULL NULL NULL NULL 76select t1.*,t2.* from t1 natural left outer join t2; 77grp a c id a c d 781 1 a 1 1 a 1 792 2 b NULL NULL NULL NULL 802 3 c NULL NULL NULL NULL 813 4 E NULL NULL NULL NULL 823 5 C NULL NULL NULL NULL 833 6 D NULL NULL NULL NULL 84NULL NULL NULL NULL NULL NULL 85select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3; 86grp a c id a c d 873 4 E 3 4 A 4 883 5 C 3 5 B 5 893 6 D 3 6 C 6 90select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null; 91grp a c id a c d 922 2 b NULL NULL NULL NULL 932 3 c NULL NULL NULL NULL 94NULL NULL NULL NULL NULL NULL 95explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1; 96id select_type table type possible_keys key key_len ref rows Extra 971 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 98explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1; 99id select_type table type possible_keys key key_len ref rows Extra 1001 SIMPLE t1 ALL NULL NULL NULL NULL 7 1011 SIMPLE t2 eq_ref PRIMARY PRIMARY 8 test.t1.a 1 Using where 102select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a); 103grp a c id a c d a 1041 1 a 1 1 a 1 1 1052 2 b NULL NULL NULL NULL NULL 1062 3 c NULL NULL NULL NULL NULL 1073 4 E 3 4 A 4 4 1083 5 C 3 5 B 5 5 1093 6 D 3 6 C 6 6 110NULL NULL NULL NULL NULL NULL NULL 111explain select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a); 112ERROR 42S22: Unknown column 't3.a' in 'on clause' 113select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t1.a=t3.a); 114ERROR 42S22: Unknown column 't3.a' in 'on clause' 115select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a); 116ERROR 42S22: Unknown column 't3.a' in 'on clause' 117select t1.*,t2.* from t1 inner join t2 using (a); 118grp a c id a c d 1191 1 a 1 1 a 1 1203 4 E 3 4 A 4 1213 5 C 3 5 B 5 1223 6 D 3 6 C 6 123select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a); 124grp a c id a c d 1251 1 a 1 1 a 1 1263 4 E 3 4 A 4 1273 5 C 3 5 B 5 1283 6 D 3 6 C 6 129select t1.*,t2.* from t1 natural join t2; 130grp a c id a c d 1311 1 a 1 1 a 1 132drop table t1,t2; 133CREATE TABLE t1 ( 134usr_id INT unsigned NOT NULL, 135uniq_id INT unsigned NOT NULL AUTO_INCREMENT, 136start_num INT unsigned NOT NULL DEFAULT 1, 137increment INT unsigned NOT NULL DEFAULT 1, 138PRIMARY KEY (uniq_id), 139INDEX usr_uniq_idx (usr_id, uniq_id), 140INDEX uniq_usr_idx (uniq_id, usr_id) 141); 142CREATE TABLE t2 ( 143id INT unsigned NOT NULL DEFAULT 0, 144usr2_id INT unsigned NOT NULL DEFAULT 0, 145max INT unsigned NOT NULL DEFAULT 0, 146c_amount INT unsigned NOT NULL DEFAULT 0, 147d_max INT unsigned NOT NULL DEFAULT 0, 148d_num INT unsigned NOT NULL DEFAULT 0, 149orig_time INT unsigned NOT NULL DEFAULT 0, 150c_time INT unsigned NOT NULL DEFAULT 0, 151active ENUM ("no","yes") NOT NULL, 152PRIMARY KEY (id,usr2_id), 153INDEX id_idx (id), 154INDEX usr2_idx (usr2_id) 155); 156INSERT INTO t1 VALUES (3,NULL,0,50),(3,NULL,0,200),(3,NULL,0,25),(3,NULL,0,84676),(3,NULL,0,235),(3,NULL,0,10),(3,NULL,0,3098),(3,NULL,0,2947),(3,NULL,0,8987),(3,NULL,0,8347654),(3,NULL,0,20398),(3,NULL,0,8976),(3,NULL,0,500),(3,NULL,0,198); 157SELECT t1.usr_id,t1.uniq_id,t1.increment, 158t2.usr2_id,t2.c_amount,t2.max 159FROM t1 160LEFT JOIN t2 ON t2.id = t1.uniq_id 161WHERE t1.uniq_id = 4 162ORDER BY t2.c_amount; 163usr_id uniq_id increment usr2_id c_amount max 1643 4 84676 NULL NULL NULL 165SELECT t1.usr_id,t1.uniq_id,t1.increment, 166t2.usr2_id,t2.c_amount,t2.max 167FROM t2 168RIGHT JOIN t1 ON t2.id = t1.uniq_id 169WHERE t1.uniq_id = 4 170ORDER BY t2.c_amount; 171usr_id uniq_id increment usr2_id c_amount max 1723 4 84676 NULL NULL NULL 173INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes'); 174INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes'); 175ERROR 23000: Duplicate entry '2-3' for key 'PRIMARY' 176INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes'); 177SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 ORDER BY t2.c_amount; 178usr_id uniq_id increment usr2_id c_amount max 1793 4 84676 NULL NULL NULL 180SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4 GROUP BY t2.c_amount; 181usr_id uniq_id increment usr2_id c_amount max 1823 4 84676 NULL NULL NULL 183SELECT t1.usr_id,t1.uniq_id,t1.increment,t2.usr2_id,t2.c_amount,t2.max FROM t1 LEFT JOIN t2 ON t2.id = t1.uniq_id WHERE t1.uniq_id = 4; 184usr_id uniq_id increment usr2_id c_amount max 1853 4 84676 NULL NULL NULL 186drop table t1,t2; 187CREATE TABLE t1 ( 188cod_asig int(11) DEFAULT '0' NOT NULL, 189desc_larga_cat varchar(80) DEFAULT '' NOT NULL, 190desc_larga_cas varchar(80) DEFAULT '' NOT NULL, 191desc_corta_cat varchar(40) DEFAULT '' NOT NULL, 192desc_corta_cas varchar(40) DEFAULT '' NOT NULL, 193cred_total double(3,1) DEFAULT '0.0' NOT NULL, 194pre_requisit int(11), 195co_requisit int(11), 196preco_requisit int(11), 197PRIMARY KEY (cod_asig) 198); 199INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL); 200INSERT INTO t1 VALUES (10361,'Components i Circuits Electronics I','Componentes y Circuitos Electronicos I','Components i Circuits Electronics I','Comp. i Circ. Electr. I',6.0,NULL,NULL,NULL); 201INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL); 202INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas de Comunicacion Oral y Escrita','Tecniques de Comunicacio Oral i Escrita','Tec. Com. Oral i Escrita',4.5,NULL,NULL,NULL); 203INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL); 204INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL); 205INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL); 206INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); 207CREATE TABLE t2 ( 208idAssignatura int(11) DEFAULT '0' NOT NULL, 209Grup int(11) DEFAULT '0' NOT NULL, 210Places smallint(6) DEFAULT '0' NOT NULL, 211PlacesOcupades int(11) DEFAULT '0', 212PRIMARY KEY (idAssignatura,Grup) 213); 214INSERT INTO t2 VALUES (10360,12,333,0); 215INSERT INTO t2 VALUES (10361,30,2,0); 216INSERT INTO t2 VALUES (10361,40,3,0); 217INSERT INTO t2 VALUES (10360,45,10,0); 218INSERT INTO t2 VALUES (10362,10,12,0); 219INSERT INTO t2 VALUES (10360,55,2,0); 220INSERT INTO t2 VALUES (10360,70,0,0); 221INSERT INTO t2 VALUES (10360,565656,0,0); 222INSERT INTO t2 VALUES (10360,32767,7,0); 223INSERT INTO t2 VALUES (10360,33,8,0); 224INSERT INTO t2 VALUES (10360,7887,85,0); 225INSERT INTO t2 VALUES (11405,88,8,0); 226INSERT INTO t2 VALUES (10360,0,55,0); 227INSERT INTO t2 VALUES (10360,99,0,0); 228INSERT INTO t2 VALUES (11411,30,10,0); 229INSERT INTO t2 VALUES (11404,0,0,0); 230INSERT INTO t2 VALUES (10362,11,111,0); 231INSERT INTO t2 VALUES (10363,33,333,0); 232INSERT INTO t2 VALUES (11412,55,0,0); 233INSERT INTO t2 VALUES (50003,66,6,0); 234INSERT INTO t2 VALUES (11403,5,0,0); 235INSERT INTO t2 VALUES (11406,11,11,0); 236INSERT INTO t2 VALUES (11410,11410,131,0); 237INSERT INTO t2 VALUES (11416,11416,32767,0); 238INSERT INTO t2 VALUES (11409,0,0,0); 239CREATE TABLE t3 ( 240id int(11) NOT NULL auto_increment, 241dni_pasaporte char(16) DEFAULT '' NOT NULL, 242idPla int(11) DEFAULT '0' NOT NULL, 243cod_asig int(11) DEFAULT '0' NOT NULL, 244any smallint(6) DEFAULT '0' NOT NULL, 245quatrimestre smallint(6) DEFAULT '0' NOT NULL, 246estat char(1) DEFAULT 'M' NOT NULL, 247PRIMARY KEY (id), 248UNIQUE dni_pasaporte (dni_pasaporte,idPla), 249UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre) 250); 251INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M'); 252CREATE TABLE t4 ( 253id int(11) NOT NULL auto_increment, 254papa int(11) DEFAULT '0' NOT NULL, 255fill int(11) DEFAULT '0' NOT NULL, 256idPla int(11) DEFAULT '0' NOT NULL, 257PRIMARY KEY (id), 258KEY papa (idPla,papa), 259UNIQUE papa_2 (idPla,papa,fill) 260); 261INSERT INTO t4 VALUES (1,-1,10360,1); 262INSERT INTO t4 VALUES (2,-1,10361,1); 263INSERT INTO t4 VALUES (3,-1,10362,1); 264SELECT DISTINCT fill,desc_larga_cat,cred_total,Grup,Places,PlacesOcupades FROM t4 LEFT JOIN t3 ON t3.cod_asig=fill AND estat='S' AND dni_pasaporte='11111111' AND t3.idPla=1 , t2,t1 WHERE fill=t1.cod_asig AND Places>PlacesOcupades AND fill=idAssignatura AND t4.idPla=1 AND papa=-1; 265fill desc_larga_cat cred_total Grup Places PlacesOcupades 26610360 asdfggfg 6.0 0 55 0 26710360 asdfggfg 6.0 12 333 0 26810360 asdfggfg 6.0 32767 7 0 26910360 asdfggfg 6.0 33 8 0 27010360 asdfggfg 6.0 45 10 0 27110360 asdfggfg 6.0 55 2 0 27210360 asdfggfg 6.0 7887 85 0 27310361 Components i Circuits Electronics I 6.0 30 2 0 27410361 Components i Circuits Electronics I 6.0 40 3 0 27510362 Laboratori d`Ordinadors 4.5 10 12 0 27610362 Laboratori d`Ordinadors 4.5 11 111 0 277SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ; 278fill idPla 27910360 NULL 28010361 NULL 28110362 NULL 282INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S'); 283SELECT DISTINCT fill,t3.idPla FROM t4 LEFT JOIN t3 ON t3.cod_asig=t4.fill AND t3.estat='S' AND t3.dni_pasaporte='1234' AND t3.idPla=1 ; 284fill idPla 28510360 1 28610361 NULL 28710362 NULL 288drop table t1,t2,t3,test.t4; 289CREATE TABLE t1 ( 290id smallint(5) unsigned NOT NULL auto_increment, 291name char(60) DEFAULT '' NOT NULL, 292PRIMARY KEY (id) 293); 294INSERT INTO t1 VALUES (1,'Antonio Paz'); 295INSERT INTO t1 VALUES (2,'Lilliana Angelovska'); 296INSERT INTO t1 VALUES (3,'Thimble Smith'); 297CREATE TABLE t2 ( 298id smallint(5) unsigned NOT NULL auto_increment, 299owner smallint(5) unsigned DEFAULT '0' NOT NULL, 300name char(60), 301PRIMARY KEY (id) 302); 303INSERT INTO t2 VALUES (1,1,'El Gato'); 304INSERT INTO t2 VALUES (2,1,'Perrito'); 305INSERT INTO t2 VALUES (3,3,'Happy'); 306select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner); 307name name id 308Antonio Paz El Gato 1 309Antonio Paz Perrito 2 310Lilliana Angelovska NULL NULL 311Thimble Smith Happy 3 312select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; 313name name id 314Lilliana Angelovska NULL NULL 315explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; 316id select_type table type possible_keys key key_len ref rows Extra 3171 SIMPLE t1 ALL NULL NULL NULL NULL 3 3181 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists 319explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null; 320id select_type table type possible_keys key key_len ref rows Extra 3211 SIMPLE t1 ALL NULL NULL NULL NULL 3 3221 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 323select count(*) from t1 left join t2 on (t1.id = t2.owner); 324count(*) 3254 326select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner); 327name name id 328Antonio Paz El Gato 1 329Antonio Paz Perrito 2 330Lilliana Angelovska NULL NULL 331Thimble Smith Happy 3 332select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; 333name name id 334Lilliana Angelovska NULL NULL 335explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; 336id select_type table type possible_keys key key_len ref rows Extra 3371 SIMPLE t1 ALL NULL NULL NULL NULL 3 3381 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Not exists 339explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null; 340id select_type table type possible_keys key key_len ref rows Extra 3411 SIMPLE t1 ALL NULL NULL NULL NULL 3 3421 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 343select count(*) from t2 right join t1 on (t1.id = t2.owner); 344count(*) 3454 346select t1.name, t2.name, t2.id,t3.id from t2 right join t1 on (t1.id = t2.owner) left join t1 as t3 on t3.id=t2.owner; 347name name id id 348Antonio Paz El Gato 1 1 349Antonio Paz Perrito 2 1 350Lilliana Angelovska NULL NULL NULL 351Thimble Smith Happy 3 3 352select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; 353name name id id 354Antonio Paz El Gato 1 1 355Antonio Paz Perrito 2 1 356NULL NULL NULL 2 357Thimble Smith Happy 3 3 358select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner; 359name name id owner id 360Antonio Paz El Gato 1 1 1 361Antonio Paz Perrito 2 1 1 362NULL NULL NULL NULL 2 363Thimble Smith Happy 3 3 3 364drop table t1,t2; 365create table t1 (id int not null, str char(10), index(str)); 366insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar"); 367select * from t1 where str is not null order by id; 368id str 3693 foo 3704 bar 371select * from t1 where str is null; 372id str 3731 NULL 3742 NULL 375drop table t1; 376CREATE TABLE t1 ( 377t1_id bigint(21) NOT NULL auto_increment, 378PRIMARY KEY (t1_id) 379); 380CREATE TABLE t2 ( 381t2_id bigint(21) NOT NULL auto_increment, 382PRIMARY KEY (t2_id) 383); 384CREATE TABLE t3 ( 385t3_id bigint(21) NOT NULL auto_increment, 386PRIMARY KEY (t3_id) 387); 388CREATE TABLE t4 ( 389seq_0_id bigint(21) DEFAULT '0' NOT NULL, 390seq_1_id bigint(21) DEFAULT '0' NOT NULL, 391KEY seq_0_id (seq_0_id), 392KEY seq_1_id (seq_1_id) 393); 394CREATE TABLE t5 ( 395seq_0_id bigint(21) DEFAULT '0' NOT NULL, 396seq_1_id bigint(21) DEFAULT '0' NOT NULL, 397KEY seq_1_id (seq_1_id), 398KEY seq_0_id (seq_0_id) 399); 400insert into t1 values (1); 401insert into t2 values (1); 402insert into t3 values (1); 403insert into t4 values (1,1); 404insert into t5 values (1,1); 405explain select * from t3 left join t4 on t4.seq_1_id = t2.t2_id left join t1 on t1.t1_id = t4.seq_0_id left join t5 on t5.seq_0_id = t1.t1_id left join t2 on t2.t2_id = t5.seq_1_id where t3.t3_id = 23; 406ERROR 42S22: Unknown column 't2.t2_id' in 'on clause' 407drop table t1,t2,t3,t4,t5; 408create table t1 (n int, m int, o int, key(n)); 409create table t2 (n int not null, m int, o int, primary key(n)); 410insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9); 411insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10); 412select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and 413t1.m = t2.m where t1.n = 1; 414n m o n m o 4151 2 11 1 2 3 4161 2 7 1 2 3 4171 2 9 1 2 3 4181 3 9 NULL NULL NULL 419select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and 420t1.m = t2.m where t1.n = 1 order by t1.o; 421n m o n m o 4221 2 11 1 2 3 4231 2 7 1 2 3 4241 2 9 1 2 3 4251 3 9 NULL NULL NULL 426drop table t1,t2; 427CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT); 428INSERT INTO t1 VALUES (1,'a',1); 429INSERT INTO t1 VALUES (2,'b',1); 430INSERT INTO t1 VALUES (3,'c',2); 431CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1)); 432INSERT INTO t2 VALUES (1,'x'); 433INSERT INTO t2 VALUES (2,'y'); 434INSERT INTO t2 VALUES (3,'z'); 435SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL; 436id2 4373 438SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL; 439id2 4403 441drop table t1,t2; 442create table t1 ( color varchar(20), name varchar(20) ); 443insert into t1 values ( 'red', 'apple' ); 444insert into t1 values ( 'yellow', 'banana' ); 445insert into t1 values ( 'green', 'lime' ); 446insert into t1 values ( 'black', 'grape' ); 447insert into t1 values ( 'blue', 'blueberry' ); 448create table t2 ( count int, color varchar(20) ); 449insert into t2 values (10, 'green'); 450insert into t2 values (5, 'black'); 451insert into t2 values (15, 'white'); 452insert into t2 values (7, 'green'); 453select * from t1; 454color name 455red apple 456yellow banana 457green lime 458black grape 459blue blueberry 460select * from t2; 461count color 46210 green 4635 black 46415 white 4657 green 466select * from t2 natural join t1; 467color count name 468green 10 lime 469green 7 lime 470black 5 grape 471select t2.count, t1.name from t2 natural join t1; 472count name 47310 lime 4747 lime 4755 grape 476select t2.count, t1.name from t2 inner join t1 using (color); 477count name 47810 lime 4797 lime 4805 grape 481drop table t1; 482drop table t2; 483CREATE TABLE t1 ( 484pcode varchar(8) DEFAULT '' NOT NULL 485); 486INSERT INTO t1 VALUES ('kvw2000'),('kvw2001'),('kvw3000'),('kvw3001'),('kvw3002'),('kvw3500'),('kvw3501'),('kvw3502'),('kvw3800'),('kvw3801'),('kvw3802'),('kvw3900'),('kvw3901'),('kvw3902'),('kvw4000'),('kvw4001'),('kvw4002'),('kvw4200'),('kvw4500'),('kvw5000'),('kvw5001'),('kvw5500'),('kvw5510'),('kvw5600'),('kvw5601'),('kvw6000'),('klw1000'),('klw1020'),('klw1500'),('klw2000'),('klw2001'),('klw2002'),('kld2000'),('klw2500'),('kmw1000'),('kmw1500'),('kmw2000'),('kmw2001'),('kmw2100'),('kmw3000'),('kmw3200'); 487CREATE TABLE t2 ( 488pcode varchar(8) DEFAULT '' NOT NULL, 489KEY pcode (pcode) 490); 491INSERT INTO t2 VALUES ('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw2000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3000'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw3500'),('kvw6000'),('kvw6000'),('kld2000'); 492SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 493LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode; 494pcode count 495kld2000 1 496klw1000 0 497klw1020 0 498klw1500 0 499klw2000 0 500klw2001 0 501klw2002 0 502klw2500 0 503kmw1000 0 504kmw1500 0 505kmw2000 0 506kmw2001 0 507kmw2100 0 508kmw3000 0 509kmw3200 0 510kvw2000 26 511kvw2001 0 512kvw3000 36 513kvw3001 0 514kvw3002 0 515kvw3500 26 516kvw3501 0 517kvw3502 0 518kvw3800 0 519kvw3801 0 520kvw3802 0 521kvw3900 0 522kvw3901 0 523kvw3902 0 524kvw4000 0 525kvw4001 0 526kvw4002 0 527kvw4200 0 528kvw4500 0 529kvw5000 0 530kvw5001 0 531kvw5500 0 532kvw5510 0 533kvw5600 0 534kvw5601 0 535kvw6000 2 536SELECT SQL_BIG_RESULT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1 LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode; 537pcode count 538kld2000 1 539klw1000 0 540klw1020 0 541klw1500 0 542klw2000 0 543klw2001 0 544klw2002 0 545klw2500 0 546kmw1000 0 547kmw1500 0 548kmw2000 0 549kmw2001 0 550kmw2100 0 551kmw3000 0 552kmw3200 0 553kvw2000 26 554kvw2001 0 555kvw3000 36 556kvw3001 0 557kvw3002 0 558kvw3500 26 559kvw3501 0 560kvw3502 0 561kvw3800 0 562kvw3801 0 563kvw3802 0 564kvw3900 0 565kvw3901 0 566kvw3902 0 567kvw4000 0 568kvw4001 0 569kvw4002 0 570kvw4200 0 571kvw4500 0 572kvw5000 0 573kvw5001 0 574kvw5500 0 575kvw5510 0 576kvw5600 0 577kvw5601 0 578kvw6000 2 579drop table t1,t2; 580CREATE TABLE t1 ( 581id int(11), 582pid int(11), 583rep_del tinyint(4), 584KEY id (id), 585KEY pid (pid) 586); 587INSERT INTO t1 VALUES (1,NULL,NULL); 588INSERT INTO t1 VALUES (2,1,NULL); 589select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL; 590id pid rep_del id pid rep_del 5911 NULL NULL 2 1 NULL 5922 1 NULL NULL NULL NULL 593create index rep_del ON t1(rep_del); 594select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL; 595id pid rep_del id pid rep_del 5961 NULL NULL 2 1 NULL 5972 1 NULL NULL NULL NULL 598drop table t1; 599CREATE TABLE t1 ( 600id int(11) DEFAULT '0' NOT NULL, 601name tinytext DEFAULT '' NOT NULL, 602UNIQUE id (id) 603); 604INSERT INTO t1 VALUES (1,'yes'),(2,'no'); 605CREATE TABLE t2 ( 606id int(11) DEFAULT '0' NOT NULL, 607idx int(11) DEFAULT '0' NOT NULL, 608UNIQUE id (id,idx) 609); 610INSERT INTO t2 VALUES (1,1); 611explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; 612id select_type table type possible_keys key key_len ref rows Extra 6131 SIMPLE t1 ALL NULL NULL NULL NULL 2 6141 SIMPLE t2 ref id id 4 test.t1.id 1 Using where; Using index; Not exists 615SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL; 616id name id idx 6172 no NULL NULL 618drop table t1,t2; 619create table t1 (bug_id mediumint, reporter mediumint); 620create table t2 (bug_id mediumint, who mediumint, index(who)); 621insert into t2 values (1,1),(1,2); 622insert into t1 values (1,1),(2,1); 623SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id = t2.bug_id AND t2.who = 2) WHERE (t1.reporter = 2 OR t2.who = 2); 624bug_id reporter bug_id who 6251 1 1 2 626drop table t1,t2; 627create table t1 (fooID smallint unsigned auto_increment, primary key (fooID)); 628create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID)); 629insert into t1 (fooID) values (10),(20),(30); 630insert into t2 values (10,1),(20,2),(30,3); 631explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; 632id select_type table type possible_keys key key_len ref rows Extra 6331 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using index 6341 SIMPLE t1 const PRIMARY PRIMARY 2 const 1 Using where; Using index 635select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30; 636fooID barID fooID 63710 1 NULL 63820 2 NULL 63930 3 30 640select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30; 641fooID barID fooID 64210 1 NULL 64320 2 NULL 64430 3 30 645drop table t1,t2; 646create table t1 (i int); 647create table t2 (i int); 648create table t3 (i int); 649insert into t1 values(1),(2); 650insert into t2 values(2),(3); 651insert into t3 values(2),(4); 652select * from t1 natural left join t2 natural left join t3; 653i 6541 6552 656select * from t1 natural left join t2 where (t2.i is not null)=0; 657i 6581 659select * from t1 natural left join t2 where (t2.i is not null) is not null; 660i 6611 6622 663select * from t1 natural left join t2 where (i is not null)=0; 664i 665select * from t1 natural left join t2 where (i is not null) is not null; 666i 6671 6682 669drop table t1,t2,t3; 670create table t1 (f1 integer,f2 integer,f3 integer); 671create table t2 (f2 integer,f4 integer); 672create table t3 (f3 integer,f5 integer); 673select * from t1 674left outer join t2 using (f2) 675left outer join t3 using (f3); 676f3 f2 f1 f4 f5 677drop table t1,t2,t3; 678create table t1 (a1 int, a2 int); 679create table t2 (b1 int not null, b2 int); 680create table t3 (c1 int, c2 int); 681insert into t1 values (1,2), (2,2), (3,2); 682insert into t2 values (1,3), (2,3); 683insert into t3 values (2,4), (3,4); 684select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; 685a1 a2 b1 b2 c1 c2 6861 2 1 3 NULL NULL 6872 2 2 3 NULL NULL 6883 2 NULL NULL 3 4 689explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; 690id select_type table type possible_keys key key_len ref rows Extra 6911 SIMPLE t1 ALL NULL NULL NULL NULL 3 6921 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 6931 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where 694drop table t1, t2, t3; 695create table t1 ( 696a int(11), 697b char(10), 698key (a) 699); 700insert into t1 (a) values (1),(2),(3),(4); 701create table t2 (a int); 702select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a); 703a b a 7041 NULL NULL 7052 NULL NULL 7063 NULL NULL 7074 NULL NULL 708select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a); 709a b a 7101 NULL NULL 7112 NULL NULL 7123 NULL NULL 7134 NULL NULL 714drop table t1,t2; 715create table t1 ( 716match_id tinyint(3) unsigned not null auto_increment, 717home tinyint(3) unsigned default '0', 718unique key match_id (match_id), 719key match_id_2 (match_id) 720); 721insert into t1 values("1", "2"); 722create table t2 ( 723player_id tinyint(3) unsigned default '0', 724match_1_h tinyint(3) unsigned default '0', 725key player_id (player_id) 726); 727insert into t2 values("1", "5"); 728insert into t2 values("2", "9"); 729insert into t2 values("3", "3"); 730insert into t2 values("4", "7"); 731insert into t2 values("5", "6"); 732insert into t2 values("6", "8"); 733insert into t2 values("7", "4"); 734insert into t2 values("8", "12"); 735insert into t2 values("9", "11"); 736insert into t2 values("10", "10"); 737explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 738(t2 s left join t1 m on m.match_id = 1) 739order by m.match_id desc; 740id select_type table type possible_keys key key_len ref rows Extra 7411 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 7421 SIMPLE m const match_id,match_id_2 match_id 1 const 1 743explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 744(t2 s left join t1 m on m.match_id = 1) 745order by UUX desc; 746id select_type table type possible_keys key key_len ref rows Extra 7471 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 7481 SIMPLE m const match_id,match_id_2 match_id 1 const 1 749select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 750(t2 s left join t1 m on m.match_id = 1) 751order by UUX desc; 752player_id match_1_h * match_id home UUX 7538 12 * 1 2 10 7549 11 * 1 2 9 75510 10 * 1 2 8 7562 9 * 1 2 7 7576 8 * 1 2 6 7584 7 * 1 2 5 7595 6 * 1 2 4 7601 5 * 1 2 3 7617 4 * 1 2 2 7623 3 * 1 2 1 763explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 764t2 s straight_join t1 m where m.match_id = 1 765order by UUX desc; 766id select_type table type possible_keys key key_len ref rows Extra 7671 SIMPLE s ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 7681 SIMPLE m const match_id,match_id_2 match_id 1 const 1 769select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 770t2 s straight_join t1 m where m.match_id = 1 771order by UUX desc; 772player_id match_1_h * match_id home UUX 7738 12 * 1 2 10 7749 11 * 1 2 9 77510 10 * 1 2 8 7762 9 * 1 2 7 7776 8 * 1 2 6 7784 7 * 1 2 5 7795 6 * 1 2 4 7801 5 * 1 2 3 7817 4 * 1 2 2 7823 3 * 1 2 1 783drop table t1, t2; 784create table t1 (a int, b int, unique index idx (a, b)); 785create table t2 (a int, b int, c int, unique index idx (a, b)); 786insert into t1 values (1, 10), (1,11), (2,10), (2,11); 787insert into t2 values (1,10,3); 788select t1.a, t1.b, t2.c from t1 left join t2 789on t1.a=t2.a and t1.b=t2.b and t2.c=3 790where t1.a=1 and t2.c is null; 791a b c 7921 11 NULL 793drop table t1, t2; 794CREATE TABLE t1 ( 795ts_id bigint(20) default NULL, 796inst_id tinyint(4) default NULL, 797flag_name varchar(64) default NULL, 798flag_value text, 799UNIQUE KEY ts_id (ts_id,inst_id,flag_name) 800) ENGINE=MyISAM DEFAULT CHARSET=utf8; 801CREATE TABLE t2 ( 802ts_id bigint(20) default NULL, 803inst_id tinyint(4) default NULL, 804flag_name varchar(64) default NULL, 805flag_value text, 806UNIQUE KEY ts_id (ts_id,inst_id,flag_name) 807) ENGINE=MyISAM DEFAULT CHARSET=utf8; 808INSERT INTO t1 VALUES 809(111056548820001, 0, 'flag1', NULL), 810(111056548820001, 0, 'flag2', NULL), 811(2, 0, 'other_flag', NULL); 812INSERT INTO t2 VALUES 813(111056548820001, 3, 'flag1', 'sss'); 814SELECT t1.flag_name,t2.flag_value 815FROM t1 LEFT JOIN t2 816ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND 817t2.inst_id = 3) 818WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND 819t2.flag_value IS NULL; 820flag_name flag_value 821flag2 NULL 822DROP TABLE t1,t2; 823CREATE TABLE t1 ( 824id int(11) unsigned NOT NULL auto_increment, 825text_id int(10) unsigned default NULL, 826PRIMARY KEY (id) 827); 828INSERT INTO t1 VALUES("1", "0"); 829INSERT INTO t1 VALUES("2", "10"); 830CREATE TABLE t2 ( 831text_id char(3) NOT NULL default '', 832language_id char(3) NOT NULL default '', 833text_data text, 834PRIMARY KEY (text_id,language_id) 835); 836INSERT INTO t2 VALUES("0", "EN", "0-EN"); 837INSERT INTO t2 VALUES("0", "SV", "0-SV"); 838INSERT INTO t2 VALUES("10", "EN", "10-EN"); 839INSERT INTO t2 VALUES("10", "SV", "10-SV"); 840SELECT t1.id, t1.text_id, t2.text_data 841FROM t1 LEFT JOIN t2 842ON t1.text_id = t2.text_id 843AND t2.language_id = 'SV' 844 WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%'); 845id text_id text_data 8461 0 0-SV 8472 10 10-SV 848DROP TABLE t1, t2; 849CREATE TABLE t0 (a0 int PRIMARY KEY); 850CREATE TABLE t1 (a1 int PRIMARY KEY); 851CREATE TABLE t2 (a2 int); 852CREATE TABLE t3 (a3 int); 853INSERT INTO t0 VALUES (1); 854INSERT INTO t1 VALUES (1); 855INSERT INTO t2 VALUES (1), (2); 856INSERT INTO t3 VALUES (1), (2); 857SELECT * FROM t1 LEFT JOIN t2 ON a1=0; 858a1 a2 8591 NULL 860EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0; 861id select_type table type possible_keys key key_len ref rows Extra 8621 SIMPLE t1 system NULL NULL NULL NULL 1 8631 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 864SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0; 865a1 a2 a3 8661 NULL NULL 867EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0; 868id select_type table type possible_keys key key_len ref rows Extra 8691 SIMPLE t1 system NULL NULL NULL NULL 1 8701 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 8711 SIMPLE t3 ALL NULL NULL NULL NULL 2 872SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1; 873a0 a1 a2 a3 8741 1 NULL NULL 875EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1; 876id select_type table type possible_keys key key_len ref rows Extra 8771 SIMPLE t0 system PRIMARY NULL NULL NULL 1 8781 SIMPLE t1 system PRIMARY NULL NULL NULL 1 8791 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 8801 SIMPLE t3 ALL NULL NULL NULL NULL 2 881INSERT INTO t0 VALUES (0); 882INSERT INTO t1 VALUES (0); 883SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1; 884a0 a1 a2 a3 8851 1 NULL NULL 886EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1; 887id select_type table type possible_keys key key_len ref rows Extra 8881 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index 8891 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index 8901 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 8911 SIMPLE t3 ALL NULL NULL NULL NULL 2 892drop table t1,t2; 893create table t1 (a int, b int); 894insert into t1 values (1,1),(2,2),(3,3); 895create table t2 (a int, b int); 896insert into t2 values (1,1), (2,2); 897select * from t2 right join t1 on t2.a=t1.a; 898a b a b 8991 1 1 1 9002 2 2 2 901NULL NULL 3 3 902select straight_join * from t2 right join t1 on t2.a=t1.a; 903a b a b 9041 1 1 1 9052 2 2 2 906NULL NULL 3 3 907DROP TABLE t0,t1,t2,t3; 908CREATE TABLE t1 (a int PRIMARY KEY, b int); 909CREATE TABLE t2 (a int PRIMARY KEY, b int); 910INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2); 911INSERT INTO t2 VALUES (1,2), (2,2); 912SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a; 913a b a b 9141 1 1 2 9152 1 2 2 9163 1 NULL NULL 9174 2 NULL NULL 918SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1; 919a b a b 9201 1 1 2 9212 1 2 2 9223 1 NULL NULL 923SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a 924WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1); 925a b a b 9261 1 1 2 9272 1 2 2 9283 1 NULL NULL 929SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1)); 930a b a b 9311 1 1 2 9322 1 2 2 9333 1 NULL NULL 9344 2 NULL NULL 935DROP TABLE t1,t2; 936set group_concat_max_len=5; 937create table t1 (a int, b varchar(20)); 938create table t2 (a int, c varchar(20)); 939insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb"); 940insert into t2 values (1,"cccccccccc"),(2,"dddddddddd"); 941select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a; 942group_concat(t1.b,t2.c) 943aaaaa 944bbbbb 945Warnings: 946Warning 1260 Row 1 was cut by GROUP_CONCAT() 947Warning 1260 Row 2 was cut by GROUP_CONCAT() 948select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a; 949group_concat(t1.b,t2.c) 950aaaaa 951bbbbb 952Warnings: 953Warning 1260 Row 1 was cut by GROUP_CONCAT() 954Warning 1260 Row 2 was cut by GROUP_CONCAT() 955select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a; 956group_concat(t1.b,t2.c) 957aaaaa 958bbbbb 959Warnings: 960Warning 1260 Row 1 was cut by GROUP_CONCAT() 961Warning 1260 Row 2 was cut by GROUP_CONCAT() 962select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a; 963group_concat(t1.b,t2.c) 964aaaaa 965bbbbb 966Warnings: 967Warning 1260 Row 1 was cut by GROUP_CONCAT() 968Warning 1260 Row 2 was cut by GROUP_CONCAT() 969drop table t1, t2; 970set group_concat_max_len=default; 971create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, art int(11) not null, primary key (gid,x,y)); 972insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1); 973create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not null, id int(11) not null, primary key (gid,id,x,y), key id (id)); 974insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1); 975create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null, name char(12) not null, primary key (id,set_id)); 976insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4, 'f'), (1, 5, 'g'), (1, 6, 'h'); 977explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y 978left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8 979and t1.gid =1 and t2.gid =1 and t3.set_id =1; 980id select_type table type possible_keys key key_len ref rows Extra 9811 SIMPLE t1 const PRIMARY PRIMARY 10 const,const,const 1 9821 SIMPLE t2 const PRIMARY,id PRIMARY 14 const,const,const,const 1 Using index 9831 SIMPLE t3 const PRIMARY PRIMARY 3 const,const 1 984drop tables t1,t2,t3; 985CREATE TABLE t1 (EMPNUM INT, GRP INT); 986INSERT INTO t1 VALUES (0, 10); 987INSERT INTO t1 VALUES (2, 30); 988CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5)); 989INSERT INTO t2 VALUES (0, 'KERI'); 990INSERT INTO t2 VALUES (9, 'BARRY'); 991CREATE VIEW v1 AS 992SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP 993FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM; 994SELECT * FROM v1; 995EMPNUM NAME GRP 9960 KERI 10 9979 BARRY NULL 998SELECT * FROM v1 WHERE EMPNUM < 10; 999EMPNUM NAME GRP 10000 KERI 10 10019 BARRY NULL 1002DROP VIEW v1; 1003DROP TABLE t1,t2; 1004CREATE TABLE t1 (c11 int); 1005CREATE TABLE t2 (c21 int); 1006INSERT INTO t1 VALUES (30), (40), (50); 1007INSERT INTO t2 VALUES (300), (400), (500); 1008SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40; 1009c11 c21 101040 NULL 1011DROP TABLE t1, t2; 1012CREATE TABLE t1 (a int PRIMARY KEY, b int); 1013CREATE TABLE t2 (a int PRIMARY KEY, b int); 1014INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10); 1015INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5); 1016SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b; 1017a b a b 10187 8 7 5 1019SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b; 1020a b a b 10217 8 7 5 1022SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b); 1023a b a b 10247 8 7 5 1025SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; 1026a b a b 10272 1 NULL NULL 10283 2 3 0 10294 3 4 1 10306 5 6 4 10318 7 NULL NULL 1032SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; 1033a b a b 10342 1 NULL NULL 10353 2 3 0 10364 3 4 1 10376 5 6 4 10388 7 NULL NULL 1039SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); 1040a b a b 10412 1 NULL NULL 10423 2 3 0 10434 3 4 1 10446 5 6 4 10458 7 NULL NULL 1046SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b; 1047a b a b 10482 1 NULL NULL 10493 2 3 0 10504 3 4 1 10516 5 6 4 10527 8 7 5 10538 7 NULL NULL 1054SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b); 1055a b a b 10562 1 NULL NULL 10573 2 3 0 10584 3 4 1 10596 5 6 4 10607 8 7 5 10618 7 NULL NULL 1062SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b); 1063a b a b 10643 2 3 0 10654 3 4 1 10666 5 6 4 1067SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b); 1068a b a b 10693 2 3 0 10704 3 4 1 10716 5 6 4 1072SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; 1073a b a b 10743 2 3 0 10754 3 4 1 10766 5 6 4 10777 8 7 5 1078SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); 1079a b a b 10803 2 3 0 10814 3 4 1 10826 5 6 4 10837 8 7 5 1084SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b)); 1085a b a b 10863 2 3 0 10874 3 4 1 10886 5 6 4 10897 8 7 5 1090SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b; 1091a b a b 10923 2 3 0 10934 3 4 1 10946 5 6 4 10957 8 7 5 1096SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b); 1097a b a b 10983 2 3 0 10994 3 4 1 11006 5 6 4 11017 8 7 5 1102SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b)); 1103a b a b 11043 2 3 0 11054 3 4 1 11066 5 6 4 11077 8 7 5 1108SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b); 1109a b a b 11103 2 3 0 11114 3 4 1 11126 5 6 4 11137 8 7 5 1114SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b)); 1115a b a b 11163 2 3 0 11174 3 4 1 11186 5 6 4 11197 8 7 5 1120SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b; 1121a b a b 11223 2 3 0 11234 3 4 1 11246 5 6 4 11257 8 7 5 1126SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b)); 1127a b a b 11283 2 3 0 11294 3 4 1 11306 5 6 4 11317 8 7 5 1132EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; 1133id select_type table type possible_keys key key_len ref rows Extra 11341 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 11351 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 1136EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); 1137id select_type table type possible_keys key key_len ref rows Extra 11381 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where 11391 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 1140EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1); 1141id select_type table type possible_keys key key_len ref rows Extra 11421 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where 11431 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 1144DROP TABLE t1,t2; 1145DROP VIEW IF EXISTS v1,v2; 1146DROP TABLE IF EXISTS t1,t2; 1147CREATE TABLE t1 (a int); 1148CREATE table t2 (b int); 1149INSERT INTO t1 VALUES (1), (2), (3), (4), (1), (1), (3); 1150INSERT INTO t2 VALUES (2), (3); 1151CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b; 1152CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a; 1153SELECT v1.a, v2. b 1154FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) 1155GROUP BY v1.a; 1156a b 11572 NULL 11583 3 1159SELECT v1.a, v2. b 1160FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) } 1161GROUP BY v1.a; 1162a b 11632 NULL 11643 3 1165DROP VIEW v1,v2; 1166DROP TABLE t1,t2; 1167CREATE TABLE t1 (a int); 1168CREATE TABLE t2 (b int); 1169INSERT INTO t1 VALUES (1), (2), (3), (4); 1170INSERT INTO t2 VALUES (2), (3); 1171SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1); 1172a b 11731 NULL 11742 2 11753 3 11764 NULL 1177SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1); 1178a b 11791 NULL 11802 2 11813 3 11824 NULL 1183SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1); 1184a b 11851 NULL 11862 2 11873 3 11884 NULL 1189SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); 1190a b 11911 NULL 11922 2 11933 3 11944 NULL 1195SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); 1196a b 11971 NULL 11982 2 11993 3 12004 NULL 1201DROP TABLE t1,t2; 1202CREATE TABLE t1 ( 1203f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, 1204f2 varchar(16) collate latin1_swedish_ci 1205); 1206CREATE TABLE t2 ( 1207f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, 1208f3 varchar(16) collate latin1_swedish_ci 1209); 1210INSERT INTO t1 VALUES ('bla','blah'); 1211INSERT INTO t2 VALUES ('bla','sheep'); 1212SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla'; 1213f1 f2 f3 1214bla blah sheep 1215SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; 1216f1 f2 f3 1217bla blah sheep 1218SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; 1219f1 f2 f3 1220bla blah sheep 1221DROP TABLE t1,t2; 1222CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); 1223CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id)); 1224INSERT INTO t1 VALUES 1225(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); 1226INSERT INTO t2 VALUES 1227(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40); 1228EXPLAIN 1229SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; 1230id select_type table type possible_keys key key_len ref rows Extra 12311 SIMPLE t1 ALL NULL NULL NULL NULL 5 12321 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists 1233flush status; 1234SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; 1235id a 12361 aaaaaaa 12374 ddddddd 1238show status like 'Handler_read%'; 1239Variable_name Value 1240Handler_read_first 0 1241Handler_read_key 5 1242Handler_read_last 0 1243Handler_read_next 0 1244Handler_read_prev 0 1245Handler_read_retry 0 1246Handler_read_rnd 0 1247Handler_read_rnd_deleted 0 1248Handler_read_rnd_next 6 1249DROP TABLE t1,t2; 1250CREATE TABLE t1 (c int PRIMARY KEY, e int NOT NULL); 1251INSERT INTO t1 VALUES (1,0), (2,1); 1252CREATE TABLE t2 (d int PRIMARY KEY); 1253INSERT INTO t2 VALUES (1), (2), (3); 1254EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; 1255id select_type table type possible_keys key key_len ref rows Extra 12561 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 12571 SIMPLE t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Not exists 1258SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL; 1259c e d 12601 0 NULL 1261SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL; 1262c e d 12631 0 NULL 1264DROP TABLE t1,t2; 1265# 1266# Bug#47650: using group by with rollup without indexes returns incorrect 1267# results with where 1268# 1269CREATE TABLE t1 ( a INT ); 1270INSERT INTO t1 VALUES (1); 1271CREATE TABLE t2 ( a INT, b INT ); 1272INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5); 1273EXPLAIN 1274SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) 1275FROM t1 LEFT JOIN t2 USING( a ) 1276GROUP BY t1.a WITH ROLLUP; 1277id select_type table type possible_keys key key_len ref rows Extra 12781 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary; Using filesort 12791 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where 1280SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) 1281FROM t1 LEFT JOIN t2 USING( a ) 1282GROUP BY t1.a WITH ROLLUP; 1283a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) 12841 3 6 3 1285NULL 3 6 3 1286EXPLAIN 1287SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) 1288FROM t1 JOIN t2 USING( a ) 1289GROUP BY t1.a WITH ROLLUP; 1290id select_type table type possible_keys key key_len ref rows Extra 12911 SIMPLE t1 system NULL NULL NULL NULL 1 12921 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using filesort 1293SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) 1294FROM t1 JOIN t2 USING( a ) 1295GROUP BY t1.a WITH ROLLUP; 1296a COUNT( t2.b ) SUM( t2.b ) MAX( t2.b ) 12971 3 6 3 1298NULL 3 6 3 1299DROP TABLE t1, t2; 1300# 1301# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison 1302# 1303CREATE TABLE t1(f1 INT, f2 INT, f3 INT); 1304INSERT INTO t1 VALUES (1, NULL, 3); 1305CREATE TABLE t2(f1 INT, f2 INT); 1306INSERT INTO t2 VALUES (2, 1); 1307EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 1308WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); 1309id select_type table type possible_keys key key_len ref rows filtered Extra 13101 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 13111 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1312Warnings: 1313Note 1003 select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where 1 1314SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2 1315WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2)); 1316f1 f2 f3 f1 f2 13171 NULL 3 NULL NULL 1318DROP TABLE t1, t2; 1319# 1320# Bug#52357: Assertion failed: join->best_read in greedy_search 1321# optimizer_search_depth=0 1322# 1323CREATE TABLE t1( a INT ); 1324INSERT INTO t1 VALUES (1),(2); 1325SET optimizer_search_depth = 0; 1326# Should not core dump on query preparation 1327EXPLAIN 1328SELECT 1 1329FROM t1 tt3 LEFT OUTER JOIN t1 tt4 ON 1 1330LEFT OUTER JOIN t1 tt5 ON 1 1331LEFT OUTER JOIN t1 tt6 ON 1 1332LEFT OUTER JOIN t1 tt7 ON 1 1333LEFT OUTER JOIN t1 tt8 ON 1 1334RIGHT OUTER JOIN t1 tt2 ON 1 1335RIGHT OUTER JOIN t1 tt1 ON 1 1336STRAIGHT_JOIN t1 tt9 ON 1; 1337id select_type table type possible_keys key key_len ref rows Extra 13381 SIMPLE tt1 ALL NULL NULL NULL NULL 2 13391 SIMPLE tt2 ALL NULL NULL NULL NULL 2 Using where 13401 SIMPLE tt3 ALL NULL NULL NULL NULL 2 Using where 13411 SIMPLE tt4 ALL NULL NULL NULL NULL 2 Using where 13421 SIMPLE tt5 ALL NULL NULL NULL NULL 2 Using where 13431 SIMPLE tt6 ALL NULL NULL NULL NULL 2 Using where 13441 SIMPLE tt7 ALL NULL NULL NULL NULL 2 Using where 13451 SIMPLE tt8 ALL NULL NULL NULL NULL 2 Using where 13461 SIMPLE tt9 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 1347SET optimizer_search_depth = DEFAULT; 1348DROP TABLE t1; 1349# 1350# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result 1351# 1352CREATE TABLE t1 (f1 INT NOT NULL); 1353INSERT INTO t1 VALUES (9),(0); 1354CREATE TABLE t2 (f1 INT NOT NULL); 1355INSERT INTO t2 VALUES 1356(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1); 1357SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 1358RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; 1359COUNT(*) 1360476 1361EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1 1362RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1; 1363id select_type table type possible_keys key key_len ref rows Extra 13641 SIMPLE TA2 ALL NULL NULL NULL NULL 20 Using where 13651 SIMPLE TA3 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 13661 SIMPLE TA1 ALL NULL NULL NULL NULL 2 Using where 1367DROP TABLE t1, t2; 1368# 1369# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990 1370# 1371CREATE TABLE t1(f1 INT, PRIMARY KEY (f1)); 1372INSERT INTO t1 VALUES (1),(2); 1373EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 1374LEFT JOIN t1 AS jt2 1375RIGHT JOIN t1 AS jt3 1376JOIN t1 AS jt4 ON 1 1377LEFT JOIN t1 AS jt5 ON 1 1378ON 1 1379RIGHT JOIN t1 AS jt6 ON jt6.f1 1380ON 1; 1381id select_type table type possible_keys key key_len ref rows filtered Extra 13821 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using index 13831 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 13841 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 13851 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index 13861 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 13871 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 1388Warnings: 1389Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(`test`.`jt6`.`f1` <> 0 and 1)) on(1) where 1 1390EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1 1391RIGHT JOIN t1 AS jt2 1392RIGHT JOIN t1 AS jt3 1393JOIN t1 AS jt4 ON 1 1394LEFT JOIN t1 AS jt5 ON 1 1395ON 1 1396RIGHT JOIN t1 AS jt6 ON jt6.f1 1397ON 1; 1398id select_type table type possible_keys key key_len ref rows filtered Extra 13991 SIMPLE jt6 index NULL PRIMARY 4 NULL 2 100.00 Using index 14001 SIMPLE jt3 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 14011 SIMPLE jt4 index NULL PRIMARY 4 NULL 2 100.00 Using index 14021 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 14031 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 14041 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index 1405Warnings: 1406Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(`test`.`jt6`.`f1` <> 0 and 1) left join `test`.`t1` `jt1` on(1) where 1 1407DROP TABLE t1; 1408# 1409# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field 1410# 1411CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1)); 1412CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2)); 1413INSERT INTO t1 VALUES (4); 1414INSERT INTO t2 VALUES (3, 3); 1415INSERT INTO t2 VALUES (7, 7); 1416EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1417WHERE t1.f1 = 4 1418GROUP BY t2.f1, t2.f2; 1419id select_type table type possible_keys key key_len ref rows Extra 14201 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort 14211 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index 1422SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1423WHERE t1.f1 = 4 1424GROUP BY t2.f1, t2.f2; 1425f1 f1 f2 14264 NULL NULL 1427EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1428WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1429GROUP BY t2.f1, t2.f2; 1430id select_type table type possible_keys key key_len ref rows Extra 14311 SIMPLE t1 system PRIMARY NULL NULL NULL 1 14321 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort 1433SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1434WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1435GROUP BY t2.f1, t2.f2; 1436f1 f1 f2 1437DROP TABLE t1,t2; 1438# 1439# Bug#57034 incorrect OUTER JOIN result when joined on unique key 1440# 1441CREATE TABLE t1 (pk INT PRIMARY KEY, 1442col_int INT, 1443col_int_unique INT UNIQUE KEY); 1444INSERT INTO t1 VALUES (1,NULL,2), (2,0,0); 1445CREATE TABLE t2 (pk INT PRIMARY KEY, 1446col_int INT, 1447col_int_unique INT UNIQUE KEY); 1448INSERT INTO t2 VALUES (1,0,1), (2,0,2); 1449EXPLAIN 1450SELECT * FROM t1 LEFT JOIN t2 1451ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 1452WHERE t1.pk=1; 1453id select_type table type possible_keys key key_len ref rows Extra 14541 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 14551 SIMPLE t2 const col_int_unique col_int_unique 5 const 1 1456SELECT * FROM t1 LEFT JOIN t2 1457ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 1458WHERE t1.pk=1; 1459pk col_int col_int_unique pk col_int col_int_unique 14601 NULL 2 NULL NULL NULL 1461DROP TABLE t1,t2; 1462# 1463# Bug#48046 Server incorrectly processing JOINs on NULL values 1464# 1465CREATE TABLE `BB` ( 1466`pk` int(11) NOT NULL AUTO_INCREMENT, 1467`time_key` time DEFAULT NULL, 1468`varchar_key` varchar(1) DEFAULT NULL, 1469`varchar_nokey` varchar(1) DEFAULT NULL, 1470PRIMARY KEY (`pk`), 1471KEY `time_key` (`time_key`), 1472KEY `varchar_key` (`varchar_key`) 1473) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; 1474INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL); 1475SELECT table1.time_key AS field1, table2.pk 1476FROM BB table1 LEFT JOIN BB table2 1477ON table2.varchar_nokey = table1.varchar_key 1478HAVING field1; 1479field1 pk 148018:27:58 NULL 1481DROP TABLE BB; 1482# 1483# Bug#49600 Server incorrectly processing RIGHT JOIN with 1484# constant WHERE clause and no index 1485# 1486CREATE TABLE `BB` ( 1487`col_datetime_key` datetime DEFAULT NULL, 1488`col_varchar_key` varchar(1) DEFAULT NULL, 1489`col_varchar_nokey` varchar(1) DEFAULT NULL, 1490KEY `col_datetime_key` (`col_datetime_key`), 1491KEY `col_varchar_key` (`col_varchar_key`) 1492) ENGINE=MyISAM DEFAULT CHARSET=latin1; 1493INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL); 1494SELECT table1.col_datetime_key 1495FROM BB table1 RIGHT JOIN BB table2 1496ON table2 .col_varchar_nokey = table1.col_varchar_key 1497WHERE 7; 1498col_datetime_key 1499NULL 1500ALTER TABLE BB DISABLE KEYS; 1501SELECT table1.col_datetime_key 1502FROM BB table1 RIGHT JOIN BB table2 1503ON table2 .col_varchar_nokey = table1.col_varchar_key 1504WHERE 7; 1505col_datetime_key 1506NULL 1507DROP TABLE BB; 1508# 1509# Bug#58490: Incorrect result in multi level OUTER JOIN 1510# in combination with IS NULL 1511# 1512CREATE TABLE t1 (i INT NOT NULL); 1513INSERT INTO t1 VALUES (0), (2),(3),(4); 1514CREATE TABLE t2 (i INT NOT NULL); 1515INSERT INTO t2 VALUES (0),(1), (3),(4); 1516CREATE TABLE t3 (i INT NOT NULL); 1517INSERT INTO t3 VALUES (0),(1),(2), (4); 1518CREATE TABLE t4 (i INT NOT NULL); 1519INSERT INTO t4 VALUES (0),(1),(2),(3) ; 1520SELECT * FROM 1521t1 LEFT JOIN 1522( t2 LEFT JOIN 1523( t3 LEFT JOIN 1524t4 1525ON t4.i = t3.i 1526) 1527ON t3.i = t2.i 1528) 1529ON t2.i = t1.i 1530; 1531i i i i 15320 0 0 0 15332 NULL NULL NULL 15343 3 NULL NULL 15354 4 4 NULL 1536SELECT * FROM 1537t1 LEFT JOIN 1538( t2 LEFT JOIN 1539( t3 LEFT JOIN 1540t4 1541ON t4.i = t3.i 1542) 1543ON t3.i = t2.i 1544) 1545ON t2.i = t1.i 1546WHERE t4.i IS NULL; 1547i i i i 15482 NULL NULL NULL 15493 3 NULL NULL 15504 4 4 NULL 1551SELECT * FROM 1552t1 LEFT JOIN 1553( ( t2 LEFT JOIN 1554t3 1555ON t3.i = t2.i 1556) 1557) 1558ON t2.i = t1.i 1559WHERE t3.i IS NULL; 1560i i i 15612 NULL NULL 15623 3 NULL 1563SELECT * FROM 1564t1 LEFT JOIN 1565( ( t2 LEFT JOIN 1566t3 1567ON t3.i = t2.i 1568) 1569JOIN t4 1570ON t4.i=t2.i 1571) 1572ON t2.i = t1.i 1573WHERE t3.i IS NULL; 1574i i i i 15752 NULL NULL NULL 15763 3 NULL 3 15774 NULL NULL NULL 1578SELECT * FROM 1579t1 LEFT JOIN 1580( ( t2 LEFT JOIN 1581t3 1582ON t3.i = t2.i 1583) 1584JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i) 1585ON t4a.i=t2.i 1586) 1587ON t2.i = t1.i 1588WHERE t3.i IS NULL; 1589i i i i i 15902 NULL NULL NULL NULL 15913 3 NULL 3 3 15924 NULL NULL NULL NULL 1593SELECT * FROM 1594t1 LEFT JOIN 1595( ( t2 LEFT JOIN 1596t3 1597ON t3.i = t2.i 1598) 1599JOIN (t4 AS t4a, t4 AS t4b) 1600ON t4a.i=t2.i 1601) 1602ON t2.i = t1.i 1603WHERE t3.i IS NULL; 1604i i i i i 16052 NULL NULL NULL NULL 16063 3 NULL 3 0 16073 3 NULL 3 1 16083 3 NULL 3 2 16093 3 NULL 3 3 16104 NULL NULL NULL NULL 1611DROP TABLE t1,t2,t3,t4; 1612# 1613# Bug#49322(Duplicate): Server is adding extra NULL row 1614# on processing a WHERE clause 1615# 1616CREATE TABLE h (pk INT NOT NULL, col_int_key INT); 1617INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8); 1618CREATE TABLE m (pk INT NOT NULL, col_int_key INT); 1619INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9); 1620CREATE TABLE k (pk INT NOT NULL, col_int_key INT); 1621INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5); 1622SELECT TABLE1.pk FROM k TABLE1 1623RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 1624RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key; 1625pk 16262 16272 16284 16294 1630NULL 1631NULL 1632NULL 1633NULL 1634NULL 1635NULL 1636NULL 1637SELECT TABLE1.pk FROM k TABLE1 1638RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key 1639RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key 1640WHERE TABLE1.pk IS NULL; 1641pk 1642NULL 1643NULL 1644NULL 1645NULL 1646NULL 1647NULL 1648NULL 1649DROP TABLE h,m,k; 1650 1651# BUG#12567331 - INFINITE LOOP WHEN RESOLVING AN ALIASED COLUMN 1652# USED IN GROUP BY 1653 1654CREATE TABLE t1 ( 1655col_varchar_1024_latin1_key varchar(1024), 1656col_varchar_10_latin1 varchar(10), 1657col_int int(11), 1658pk int(11) 1659); 1660CREATE TABLE t2 ( 1661col_int_key int(11), 1662col_int int(11), 1663pk int(11) 1664); 1665PREPARE prep_stmt_9846 FROM ' 1666SELECT alias1.pk AS field1 FROM 1667t1 AS alias1 1668LEFT JOIN 1669( 1670 t2 AS alias2 1671 RIGHT JOIN 1672 ( 1673 t2 AS alias3 1674 JOIN t1 AS alias4 1675 ON 1 1676 ) 1677 ON 1 1678) 1679ON 1 1680GROUP BY field1'; 1681execute prep_stmt_9846; 1682field1 1683execute prep_stmt_9846; 1684field1 1685drop table t1,t2; 1686# 1687# Bug #11765810 58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY 1688# IS EXECUTED TWICE FROM P 1689# 1690CREATE TABLE t1 ( a INT ) ENGINE = MYISAM; 1691INSERT INTO t1 VALUES (1); 1692PREPARE prep_stmt FROM ' 1693 SELECT 1 AS f FROM t1 1694 LEFT JOIN t1 t2 1695 RIGHT JOIN t1 t3 1696 JOIN t1 t4 1697 ON 1 1698 ON 1 1699 ON 1 1700 GROUP BY f'; 1701EXECUTE prep_stmt; 1702f 17031 1704EXECUTE prep_stmt; 1705f 17061 1707DROP TABLE t1; 1708# 1709# Bug#49600: outer join of two single-row tables with joining attributes 1710# evaluated to nulls 1711create table t1 (a int, b int); 1712create table t2 (a int, b int); 1713insert into t1 values (1, NULL); 1714insert into t2 values (2, NULL); 1715select * from t1 left join t2 on t1.b=t2.b; 1716a b a b 17171 NULL NULL NULL 1718select * from t1 left join t2 on t1.b=t2.b where 1=1; 1719a b a b 17201 NULL NULL NULL 1721drop table t1,t2; 1722# 1723# Bug#53161: outer join in the derived table is erroneously converted 1724# into an inner join for a query with a group by clause 1725# 1726create table t1 (pk int not null primary key, a int not null); 1727create table t2 like t1; 1728create table t3 like t1; 1729create table t4 (pk int not null primary key); 1730insert into t1 values (1000, 1), (1001, 1); 1731insert into t2 values (2000, 2), (2001, 2); 1732insert into t3 values (3000, 3), (3001, 2); 1733insert into t4 values (4000), (4001); 1734explain extended 1735select t2.pk, 1736(select t3.pk+if(isnull(t4.pk),0,t4.pk) 1737from t3 left join t4 on t4.pk=t3.pk 1738where t3.pk=t2.pk+1000 limit 1 ) as t 1739from t1,t2 1740where t2.pk=t1.pk+1000 and t1.pk>1000 1741group by t2.pk; 1742id select_type table type possible_keys key key_len ref rows filtered Extra 17431 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using index; Using temporary; Using filesort 17441 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 17452 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index 17462 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.pk 1 100.00 Using index 1747Warnings: 1748Note 1276 Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1 1749Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((/* select#2 */ select `test`.`t3`.`pk` + if(`test`.`t4`.`pk` is null,0,`test`.`t4`.`pk`) from `test`.`t3` left join `test`.`t4` on(`test`.`t4`.`pk` = `test`.`t3`.`pk`) where `test`.`t3`.`pk` = `test`.`t2`.`pk` + 1000 limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`pk` = `test`.`t1`.`pk` + 1000 and `test`.`t1`.`pk` > 1000 group by `test`.`t2`.`pk` 1750select t2.pk, 1751(select t3.pk+if(isnull(t4.pk),0,t4.pk) 1752from t3 left join t4 on t4.pk=t3.pk 1753where t3.pk=t2.pk+1000 limit 1 ) as t 1754from t1,t2 1755where t2.pk=t1.pk+1000 and t1.pk>1000 1756group by t2.pk; 1757pk t 17582001 3001 1759drop table t1,t2,t3,t4; 1760# 1761# Bug#57024: Poor performance when conjunctive condition over the outer 1762# table is used in the on condition of an outer join 1763# 1764create table t1 (a int); 1765insert into t1 values (NULL), (NULL), (NULL), (NULL); 1766insert into t1 select * from t1; 1767insert into t1 select * from t1; 1768insert into t1 select * from t1; 1769insert into t1 select * from t1; 1770insert into t1 select * from t1; 1771insert into t1 select * from t1; 1772insert into t1 select * from t1; 1773insert into t1 select * from t1; 1774insert into t1 select * from t1; 1775insert into t1 select * from t1; 1776insert into t1 select * from t1; 1777insert into t1 select * from t1; 1778insert into t1 select * from t1; 1779insert into t1 select * from t1; 1780insert into t1 select * from t1; 1781insert into t1 select * from t1; 1782insert into t1 select * from t1; 1783insert into t1 select * from t1; 1784insert into t1 values (4), (2), (1), (3); 1785create table t2 like t1; 1786insert into t2 select if(t1.a is null, 10, t1.a) from t1; 1787create table t3 (a int, b int, index idx(a)); 1788insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101); 1789insert into t3 values (11, 100), (33, 301), (44, 402), (11, 102), (11, 101); 1790insert into t3 values (22, 100), (53, 301), (64, 402), (22, 102), (22, 101); 1791analyze table t1,t2,t3; 1792Table Op Msg_type Msg_text 1793test.t1 analyze status Engine-independent statistics collected 1794test.t1 analyze status OK 1795test.t2 analyze status Engine-independent statistics collected 1796test.t2 analyze status OK 1797test.t3 analyze status Engine-independent statistics collected 1798test.t3 analyze status OK 1799flush status; 1800select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null; 1801sum(t3.b) 18021006 1803show status like "handler_read%"; 1804Variable_name Value 1805Handler_read_first 0 1806Handler_read_key 13 1807Handler_read_last 0 1808Handler_read_next 5 1809Handler_read_prev 0 1810Handler_read_retry 0 1811Handler_read_rnd 0 1812Handler_read_rnd_deleted 0 1813Handler_read_rnd_next 1048581 1814flush status; 1815select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10; 1816sum(t3.b) 18171006 1818show status like "handler_read%"; 1819Variable_name Value 1820Handler_read_first 0 1821Handler_read_key 7 1822Handler_read_last 0 1823Handler_read_next 5 1824Handler_read_prev 0 1825Handler_read_retry 0 1826Handler_read_rnd 0 1827Handler_read_rnd_deleted 0 1828Handler_read_rnd_next 1048581 1829drop table t1,t2,t3; 1830# 1831# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field 1832# 1833CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1)); 1834CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2)); 1835INSERT INTO t1 VALUES (4); 1836INSERT INTO t2 VALUES (3, 3); 1837INSERT INTO t2 VALUES (7, 7); 1838EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1839WHERE t1.f1 = 4 1840GROUP BY t2.f1, t2.f2; 1841id select_type table type possible_keys key key_len ref rows Extra 18421 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort 18431 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index 1844SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1845WHERE t1.f1 = 4 1846GROUP BY t2.f1, t2.f2; 1847f1 f1 f2 18484 NULL NULL 1849EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1850WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1851GROUP BY t2.f1, t2.f2; 1852id select_type table type possible_keys key key_len ref rows Extra 18531 SIMPLE t1 system PRIMARY NULL NULL NULL 1 18541 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort 1855SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1856WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1857GROUP BY t2.f1, t2.f2; 1858f1 f1 f2 1859DROP TABLE t1,t2; 1860# 1861# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS 1862# WRONG RESULT 1863# 1864CREATE TABLE t1 (i1 int); 1865INSERT INTO t1 VALUES (100), (101); 1866CREATE TABLE t2 (i2 int, i3 int); 1867INSERT INTO t2 VALUES (20,1),(10,2); 1868CREATE TABLE t3 (i4 int(11)); 1869INSERT INTO t3 VALUES (1),(2); 1870 1871SELECT ( 1872SELECT MAX( t2.i2 ) 1873FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 ) 1874WHERE t2.i3 <> t1.i1 1875) AS field1 1876FROM t1;; 1877field1 187820 187920 1880 1881SELECT ( 1882SELECT MAX( t2.i2 ) 1883FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 ) 1884WHERE t2.i3 <> t1.i1 1885) AS field1 1886FROM t1 GROUP BY field1;; 1887field1 188820 1889 1890drop table t1,t2,t3; 1891# End of test for Bug#13068506 1892End of 5.1 tests 1893# 1894# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery 1895# predicate in WHERE condition. 1896# 1897CREATE TABLE t1(a INT); 1898INSERT INTO t1 VALUES(9); 1899CREATE TABLE t2(b INT); 1900INSERT INTO t2 VALUES(8); 1901CREATE TABLE t3(c INT); 1902INSERT INTO t3 VALUES(3); 1903SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); 1904b c 1905NULL 3 1906SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); 1907c b 19083 NULL 1909SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); 1910b c 1911NULL 3 1912SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); 1913c b 19143 NULL 1915drop table t1,t2,t3; 1916End of 5.2 tests 1917# 1918# LP bug #813447: LEFT JOIN with single-row inner table and 1919# a subquery in ON expression 1920# 1921CREATE TABLE t1 (a int); 1922INSERT INTO t1 VALUES (0); 1923CREATE TABLE t2 (a int); 1924INSERT INTO t2 VALUES (0); 1925CREATE TABLE t3 (a int); 1926INSERT INTO t3 VALUES (0), (0); 1927SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); 1928a 1929NULL 1930EXPLAIN EXTENDED 1931SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); 1932id select_type table type possible_keys key key_len ref rows filtered Extra 19331 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 19341 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 19352 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1936Warnings: 1937Note 1003 /* select#1 */ select NULL AS `a` from `test`.`t2` where 1 1938DROP TABLE t1,t2,t3; 1939# 1940# LP bug #817384 Wrong result with outer join + subquery in ON 1941# clause +unique key 1942# 1943CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ; 1944INSERT INTO t1 VALUES (1,'b'); 1945CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ; 1946INSERT INTO t2 VALUES (1,'a'); 1947create table t3 (c1 char(1), c2 char(2)); 1948insert into t3 values ('c','d'); 1949insert into t3 values ('c','d'); 1950EXPLAIN SELECT t2.b 1951FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3); 1952id select_type table type possible_keys key key_len ref rows Extra 19531 PRIMARY t1 system NULL NULL NULL NULL 1 19541 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 const 1 Using where 19552 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where 1956SELECT t2.b 1957FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3); 1958b 1959NULL 1960EXPLAIN SELECT t2.b 1961FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; 1962id select_type table type possible_keys key key_len ref rows Extra 19631 PRIMARY t1 system NULL NULL NULL NULL 1 19641 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using where 19652 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where 1966SELECT t2.b 1967FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; 1968b 1969NULL 1970DROP TABLE t1,t2,t3; 1971# 1972# lp:825035 second execution of PS with outer join 1973# 1974CREATE TABLE t1 (a int); 1975INSERT INTO t1 VALUES (1),(2),(3),(4); 1976CREATE TABLE t2 (a int); 1977PREPARE stmt FROM 1978"SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a"; 1979EXECUTE stmt; 1980a a 19811 NULL 19822 NULL 19833 NULL 19844 NULL 1985EXECUTE stmt; 1986a a 19871 NULL 19882 NULL 19893 NULL 19904 NULL 1991DEALLOCATE PREPARE stmt; 1992DROP TABLE t1,t2; 1993# 1994# lp:838633 second execution of PS with outer join 1995# converted to inner join 1996# 1997CREATE TABLE t1 ( b int NOT NULL ) ; 1998INSERT INTO t1 VALUES (9),(10); 1999CREATE TABLE t2 ( b int NOT NULL, PRIMARY KEY (b)) ; 2000INSERT INTO t2 VALUES 2001(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89), 2002(10), (90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100); 2003CREATE TABLE t3 ( a int, b int NOT NULL , PRIMARY KEY (b)) ; 2004INSERT INTO t3 VALUES 2005(0,6),(0,7),(0,8),(2,9),(0,10),(2,21),(0,22),(2,23),(2,24),(2,25); 2006set @save_join_cache_level= @@join_cache_level; 2007SET SESSION join_cache_level=4; 2008EXPLAIN EXTENDED 2009SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; 2010id select_type table type possible_keys key key_len ref rows filtered Extra 20111 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 20121 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (flat, BNLH join) 20131 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (incremental, BNLH join) 2014Warnings: 2015Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t2` join `test`.`t1` join `test`.`t3` where `test`.`t3`.`b` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t1`.`b` 2016PREPARE stmt FROM 2017'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; 2018EXECUTE stmt; 2019b b a b 202010 10 0 10 2021EXECUTE stmt; 2022b b a b 202310 10 0 10 2024DEALLOCATE PREPARE stmt; 2025SET SESSION join_cache_level=@save_join_cache_level; 2026DROP TABLE t1,t2,t3; 2027# 2028# LP bug #943543: LEFT JOIN converted to JOIN with 2029# ORed IS NULL(primary key) in WHERE clause 2030# 2031CREATE TABLE t1 ( 2032a int, b int NOT NULL, pk int NOT NULL, 2033PRIMARY KEY (pk), INDEX idx(b) 2034); 2035INSERT INTO t1 VALUES 2036(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), 2037(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); 2038CREATE TABLE t2 (pk int PRIMARY KEY); 2039INSERT INTO t2 VALUES (3), (8), (5); 2040EXPLAIN EXTENDED 2041SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a 2042WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2043ORDER BY t1.pk; 2044id select_type table type possible_keys key key_len ref rows filtered Extra 20451 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 20461 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index 2047Warnings: 2048Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5 2049SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a 2050WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2051ORDER BY t1.pk; 2052pk 20535 2054EXPLAIN EXTENDED 2055SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a 2056WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2057ORDER BY t1.pk; 2058id select_type table type possible_keys key key_len ref rows filtered Extra 20591 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 20601 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index 2061Warnings: 2062Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5 2063SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a 2064WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2065ORDER BY t1.pk; 2066pk 20675 2068DROP TABLE t2; 2069CREATE TABLE t2 (c int, d int, KEY (c)); 2070INSERT INTO t2 VALUES 2071(3,30), (8,88), (5,50), (8,81), 2072(4,40), (9,90), (7,70), (9,90), 2073(13,130), (18,188), (15,150), (18,181), 2074(14,140), (19,190), (17,170), (19,190); 2075INSERT INTO t1 VALUES (8,5,9); 2076EXPLAIN EXTENDED 2077SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a 2078WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2079ORDER BY t1.b; 2080id select_type table type possible_keys key key_len ref rows filtered Extra 20811 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where 20821 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 2083Warnings: 2084Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`b` = 5 order by `test`.`t1`.`b` 2085SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a 2086WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2087ORDER BY t1.b; 2088b c d 20895 8 88 20905 8 81 20915 8 88 20925 8 81 2093EXPLAIN EXTENDED 2094SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a 2095WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2096ORDER BY t1.b; 2097id select_type table type possible_keys key key_len ref rows filtered Extra 20981 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where 20991 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 2100Warnings: 2101Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where `test`.`t2`.`c` = `test`.`t1`.`a` and `test`.`t1`.`b` = 5 order by `test`.`t1`.`b` 2102SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a 2103WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2104ORDER BY t1.b; 2105b c d 21065 8 88 21075 8 81 21085 8 88 21095 8 81 2110DROP TABLE t1,t2; 2111# 2112# Bug mdev-4336: LEFT JOIN with disjunctive 2113# <non-nullable datetime field> IS NULL in WHERE 2114# causes a hang and eventual crash 2115# 2116CREATE TABLE t1 ( 2117id int(11) NOT NULL, 2118modified datetime NOT NULL, 2119PRIMARY KEY (id) 2120); 2121SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id 2122WHERE a.modified > b.modified or b.modified IS NULL; 2123id modified 2124DROP TABLE t1; 2125# 2126# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL 2127# 2128create table t0 (a int not null); 2129insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2130alter table t0 add person_id varchar(255) not null; 2131create table t1 (pk int not null primary key); 2132insert into t1 select A.a + 10*B.a from t0 A, t0 B; 2133explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; 2134id select_type table type possible_keys key key_len ref rows Extra 21351 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21361 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2137explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; 2138id select_type table type possible_keys key key_len ref rows Extra 21391 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21401 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2141explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; 2142id select_type table type possible_keys key key_len ref rows Extra 21431 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21441 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2145drop table t0, t1; 2146# 2147# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) 2148# (this is a regression after fix for MDEV-4817) 2149# 2150CREATE TABLE t1 (id INT, d DATE NOT NULL); 2151INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); 2152CREATE TABLE t2 (i INT); 2153SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; 2154id d i 21551 0000-00-00 NULL 21562 0000-00-00 NULL 2157DROP TABLE t1,t2; 2158CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); 2159INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); 2160CREATE TABLE t2 (i2 INT, j2 INT); 2161INSERT INTO t2 VALUES (1,10),(2,20); 2162SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; 2163i1 d1 i2 j2 21642 0000-00-00 NULL NULL 2165DROP TABLE t1,t2; 2166# Another testcase 2167CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; 2168INSERT INTO t1 VALUES (NULL); 2169CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; 2170CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; 2171INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); 2172SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; 2173i1 i2 a b 2174SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; 2175i1 i2 a b 2176drop view v2; 2177drop table t1,t2; 2178# 2179# Bug mdev-4942: LEFT JOIN with conjunctive 2180# <non-nullable datetime field> IS NULL in WHERE 2181# causes an assert failure 2182# 2183CREATE TABLE t1 ( i1 int, d1 date ); 2184INSERT INTO t1 VALUES (1,'2001-06-26'), (2,'2000-11-16'); 2185CREATE TABLE t2 ( i2 int, d2 date NOT NULL ); 2186INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25'); 2187SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL; 2188i1 d1 i2 d2 2189DROP TABLE t1,t2; 2190# 2191# Bug mdev-4952: LEFT JOIN with disjunctive 2192# <non-nullable datetime field> IS NULL in WHERE 2193# causes an assert failure 2194# 2195CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM; 2196INSERT INTO t1 VALUES (1, 10), (2, 11); 2197CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM; 2198INSERT INTO t2 VALUES 2199('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200); 2200SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2 2201WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL; 2202a1 b1 dt a2 b2 2203DROP TABLE t1,t2; 2204# 2205# Bug mdev-4962: nested outer join with 2206# <non-nullable datetime field> IS NULL in WHERE 2207# causes an assert failure 2208# 2209CREATE TABLE t1 (i1 int) ENGINE=MyISAM; 2210INSERT INTO t1 VALUES (1),(2); 2211CREATE TABLE t2 (i2 int) ENGINE=MyISAM; 2212INSERT INTO t2 VALUES (10),(20); 2213CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM; 2214INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); 2215SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 2216WHERE d3 IS NULL; 2217i1 i2 i3 d3 22181 NULL NULL NULL 22192 NULL NULL NULL 2220EXPLAIN EXTENDED 2221SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 2222WHERE d3 IS NULL; 2223id select_type table type possible_keys key key_len ref rows filtered Extra 22241 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 22251 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 22261 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2227Warnings: 2228Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`i2` = `test`.`t1`.`i1` and `test`.`t3`.`i3` = `test`.`t1`.`i1`) where `test`.`t3`.`d3` = 0 or `test`.`t3`.`d3` is null 2229DROP TABLE t1,t2,t3; 2230# 2231# Bug mdev-6705: wrong on expression after constant row substitution 2232# that triggers a simplification of WHERE condition 2233# 2234CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; 2235INSERT INTO t1 VALUES (10,8); 2236CREATE TABLE t2 (c int) ENGINE=MyISAM; 2237INSERT INTO t2 VALUES (8),(9); 2238CREATE TABLE t3 (d int) ENGINE=MyISAM; 2239INSERT INTO t3 VALUES (3),(8); 2240EXPLAIN EXTENDED 2241SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a 2242WHERE b IN (1,2,3) OR b = d; 2243id select_type table type possible_keys key key_len ref rows filtered Extra 22441 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 22451 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 22461 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2247Warnings: 2248Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`d` = 10) where `test`.`t2`.`c` = 8 and `test`.`t3`.`d` = 8 2249SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a 2250WHERE b IN (1,2,3) OR b = d; 2251a b c d 2252DROP TABLE t1,t2,t3; 2253# 2254# MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate 2255# 2256create table t1(a int); 2257insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2258create table t2 (a int, b int, c int, key(b), key(c)); 2259insert into t2 select 2260@a:=A.a + 10*B.a+100*C.a, 2261IF(@a<900, NULL, @a), 2262IF(@a<500, NULL, @a) 2263from t1 A, t1 B, t1 C; 2264delete from t1 where a=0; 2265# Check that there are different #rows of NULLs for b and c, both !=10: 2266explain select * from t2 force index (b) where b is null; 2267id select_type table type possible_keys key key_len ref rows Extra 22681 SIMPLE t2 ref b b 5 const 780 Using index condition 2269explain select * from t2 force index (c) where c is null; 2270id select_type table type possible_keys key key_len ref rows Extra 22711 SIMPLE t2 ref c c 5 const 393 Using index condition 2272explain select * from t1 left join t2 on t2.b is null; 2273id select_type table type possible_keys key key_len ref rows Extra 22741 SIMPLE t1 ALL NULL NULL NULL NULL 9 22751 SIMPLE t2 ALL b NULL NULL NULL 1000 Using where 2276explain select * from t1 left join t2 on t2.c is null; 2277id select_type table type possible_keys key key_len ref rows Extra 22781 SIMPLE t1 ALL NULL NULL NULL NULL 9 22791 SIMPLE t2 ALL c NULL NULL NULL 1000 Using where 2280drop table t1,t2; 2281# 2282# MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause 2283# 2284CREATE TABLE t1(i1 int primary key, v1 int, key(v1)); 2285INSERT INTO t1 VALUES (1, 1); 2286INSERT INTO t1 VALUES (2, 2); 2287INSERT INTO t1 VALUES (3, 3); 2288INSERT INTO t1 VALUES (4, 4); 2289INSERT INTO t1 VALUES (5, 3); 2290INSERT INTO t1 VALUES (6, 6); 2291INSERT INTO t1 VALUES (7, 7); 2292INSERT INTO t1 VALUES (8, 8); 2293INSERT INTO t1 VALUES (9, 9); 2294CREATE TABLE t2(i2 int primary key, v2 int, key(v2)); 2295INSERT INTO t2 VALUES (1, 1); 2296INSERT INTO t2 VALUES (2, 2); 2297INSERT INTO t2 VALUES (3, 3); 2298INSERT INTO t2 VALUES (4, 4); 2299INSERT INTO t2 VALUES (5, 3); 2300INSERT INTO t2 VALUES (6, 6); 2301INSERT INTO t2 VALUES (7, 7); 2302INSERT INTO t2 VALUES (8, 8); 2303INSERT INTO t2 VALUES (9, 9); 2304CREATE TABLE t3(i3 int primary key, v3 int, key(v3)); 2305INSERT INTO t3 VALUES (2, 2); 2306INSERT INTO t3 VALUES (4, 4); 2307INSERT INTO t3 VALUES (6, 6); 2308INSERT INTO t3 VALUES (8, 8); 2309# This should have a join order of t3,t1,t2 (or t3,t2,t1, the idea is that t3 is the first one) 2310EXPLAIN EXTENDED 2311SELECT * FROM 2312(SELECT t1.i1 as i1, t1.v1 as v1, 2313t2.i2 as i2, t2.v2 as v2, 2314t3.i3 as i3, t3.v3 as v3 2315FROM t1 JOIN t2 on t1.i1 = t2.i2 2316LEFT JOIN t3 on t2.i2 = t3.i3 2317) as w1 2318WHERE v3 = 4; 2319id select_type table type possible_keys key key_len ref rows filtered Extra 23201 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00 23211 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 23221 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 2323Warnings: 2324Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`v3` = 4 and `test`.`t1`.`i1` = `test`.`t3`.`i3` and `test`.`t2`.`i2` = `test`.`t3`.`i3` 2325# This should have the same join order like the query above: 2326EXPLAIN EXTENDED 2327SELECT * FROM 2328(SELECT t1.i1 as i1, t1.v1 as v1, 2329t2.i2 as i2, t2.v2 as v2, 2330t3.i3 as i3, t3.v3 as v3 2331FROM t1 JOIN t2 on t1.i1 = t2.i2 2332LEFT JOIN t3 on t2.i2 = t3.i3 2333WHERE t1.i1 = t2.i2 2334AND 1 = 1 2335) as w2 2336WHERE v3 = 4; 2337id select_type table type possible_keys key key_len ref rows filtered Extra 23381 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00 23391 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 23401 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 2341Warnings: 2342Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`v3` = 4 and `test`.`t1`.`i1` = `test`.`t3`.`i3` and `test`.`t2`.`i2` = `test`.`t3`.`i3` 2343drop table t1,t2,t3; 2344# 2345# MDEV-11958: LEFT JOIN with stored routine produces incorrect result 2346# 2347CREATE TABLE t (x INT); 2348INSERT INTO t VALUES(1),(NULL); 2349CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); 2350SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2351FROM t t1 LEFT JOIN t t2 2352ON t1.x = t2.x 2353WHERE IFNULL(t2.x,0)=0; 2354x x IFNULL(t2.x,0) f(t2.x,0) 2355NULL NULL 0 0 2356explain extended 2357SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2358FROM t t1 LEFT JOIN t t2 2359ON t1.x = t2.x 2360WHERE IFNULL(t2.x,0)=0; 2361id select_type table type possible_keys key key_len ref rows filtered Extra 23621 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 23631 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2364Warnings: 2365Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where ifnull(`test`.`t2`.`x`,0) = 0 2366SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2367FROM t t1 LEFT JOIN t t2 2368ON t1.x = t2.x 2369WHERE f(t2.x,0)=0; 2370x x IFNULL(t2.x,0) f(t2.x,0) 2371NULL NULL 0 0 2372explain extended 2373SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2374FROM t t1 LEFT JOIN t t2 2375ON t1.x = t2.x 2376WHERE f(t2.x,0)=0; 2377id select_type table type possible_keys key key_len ref rows filtered Extra 23781 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 23791 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2380Warnings: 2381Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on(`test`.`t2`.`x` = `test`.`t1`.`x`) where `f`(`test`.`t2`.`x`,0) = 0 2382drop function f; 2383drop table t; 2384CREATE TABLE t1 ( 2385col1 DECIMAL(33,5) NULL DEFAULT NULL, 2386col2 DECIMAL(33,5) NULL DEFAULT NULL 2387); 2388CREATE TABLE t2 ( 2389col1 DECIMAL(33,5) NULL DEFAULT NULL, 2390col2 DECIMAL(33,5) NULL DEFAULT NULL, 2391col3 DECIMAL(33,5) NULL DEFAULT NULL 2392); 2393INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); 2394INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); 2395CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) 2396RETURNS decimal(33,5) 2397LANGUAGE SQL 2398DETERMINISTIC 2399CONTAINS SQL 2400SQL SECURITY INVOKER 2401BEGIN 2402IF p_num IS NULL THEN 2403RETURN p_return; 2404ELSE 2405RETURN p_num; 2406END IF; 2407END | 2408SELECT t1.col1, t2.col1, t2.col3 2409FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2410WHERE IFNULL(t2.col3,0) = 0; 2411col1 col1 col3 24122.00000 NULL NULL 24132.00000 NULL NULL 2414EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 2415FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2416WHERE IFNULL(t2.col3,0) = 0; 2417id select_type table type possible_keys key key_len ref rows filtered Extra 24181 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 24191 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2420Warnings: 2421Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where ifnull(`test`.`t2`.`col3`,0) = 0 2422SELECT t1.col1, t2.col1, t2.col3 2423FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2424WHERE f1(t2.col3,0) = 0; 2425col1 col1 col3 24262.00000 NULL NULL 24272.00000 NULL NULL 2428EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 2429FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2430WHERE f1(t2.col3,0) = 0; 2431id select_type table type possible_keys key key_len ref rows filtered Extra 24321 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 24331 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2434Warnings: 2435Note 1003 select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0 2436DROP FUNCTION f1; 2437DROP TABLE t1,t2; 2438# 2439# MDEV-10397: Server crashes in key_copy with join_cache_level > 2 and join on BIT fields 2440# 2441CREATE TABLE t1 (b1 BIT NOT NULL); 2442INSERT INTO t1 VALUES (0),(1); 2443CREATE TABLE t2 (b2 BIT NOT NULL); 2444INSERT INTO t2 VALUES (0),(1); 2445set @save_join_cache_level= @@join_cache_level; 2446SET @@join_cache_level = 3; 2447SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; 2448t1.b1+'0' t2.b2 + '0' 24490 0 24501 1 2451DROP TABLE t1, t2; 2452set @@join_cache_level= @save_join_cache_level; 2453# 2454# MDEV-14779: using left join causes incorrect results with materialization and derived tables 2455# 2456create table t1(id int); 2457insert into t1 values (1),(2); 2458create table t2(sid int, id int); 2459insert into t2 values (1,1),(2,2); 2460select * from t1 t 2461left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r 2462on t.id=r.id ; 2463id sid id 24641 NULL NULL 24652 NULL NULL 2466drop table t1, t2; 2467# 2468# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN 2469# converted to INNER JOIN with first constant inner table 2470# 2471CREATE TABLE t1 ( 2472pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) 2473) engine=MyISAM; 2474INSERT INTO t1 VALUES 2475(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), 2476(14,226,'m','m'),(15,133,'p','p'); 2477CREATE TABLE t2 ( 2478pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) 2479) engine=MyISAM; 2480INSERT INTO t2 VALUES (10,6,'p','p'); 2481EXPLAIN EXTENDED 2482SELECT STRAIGHT_JOIN t2.v2 2483FROM 2484(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2485RIGHT JOIN 2486(t2,t1) 2487ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2488WHERE tb1.pk = 40 2489ORDER BY tb1.i1; 2490id select_type table type possible_keys key key_len ref rows filtered Extra 24911 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2492Warnings: 2493Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL 2494EXPLAIN EXTENDED 2495SELECT STRAIGHT_JOIN t2.v2 2496FROM 2497(t2,t1) 2498LEFT JOIN 2499(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2500ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2501WHERE tb1.pk = 40 2502ORDER BY tb1.i1; 2503id select_type table type possible_keys key key_len ref rows filtered Extra 25041 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2505Warnings: 2506Note 1003 select straight_join 'p' AS `v2` from `test`.`t1` join `test`.`t1` `tb1` left join `test`.`t1` `tb2` on(multiple equal(NULL, NULL)) where 0 order by NULL 2507SELECT STRAIGHT_JOIN DISTINCT t2.v2 2508FROM 2509(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2510RIGHT JOIN 2511(t2,t1) 2512ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2513WHERE tb1.pk = 40 2514ORDER BY tb1.i1; 2515v2 2516DROP TABLE t1,t2; 2517# 2518# MDEV-19790 : IS NOT TRUE / IS NOT FALSE predicates over 2519# inner tables of outer joins 2520# 2521create table t1 (a int); 2522create table t2 (b int); 2523insert into t1 values (3), (7), (1); 2524insert into t2 values (7), (4), (3); 2525select * from t1 left join t2 on a=b; 2526a b 25273 3 25287 7 25291 NULL 2530select * from t1 left join t2 on a=b where (b > 3) is not true; 2531a b 25323 3 25331 NULL 2534explain extended select * from t1 left join t2 on a=b where (b > 3) is not true; 2535id select_type table type possible_keys key key_len ref rows filtered Extra 25361 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 25371 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2538Warnings: 2539Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a`) where `test`.`t2`.`b` > 3 is not true 2540select * from t1 left join t2 on a=b where (b > 3) is not false; 2541a b 25427 7 25431 NULL 2544explain extended select * from t1 left join t2 on a=b where (b > 3) is not false; 2545id select_type table type possible_keys key key_len ref rows filtered Extra 25461 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 25471 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2548Warnings: 2549Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`b` = `test`.`t1`.`a`) where `test`.`t2`.`b` > 3 is not false 2550drop table t1,t2; 2551# end of 5.5 tests 2552# 2553# MDEV-19258: chained right joins all converted to inner joins 2554# 2555CREATE TABLE t1 ( 2556id int NOT NULL AUTO_INCREMENT, 2557timestamp bigint NOT NULL, 2558modifiedBy varchar(255) DEFAULT NULL, 2559PRIMARY KEY (id) 2560); 2561CREATE TABLE t2 ( 2562id int NOT NULL, 2563REV int NOT NULL, 2564REVTYPE tinyint DEFAULT NULL, 2565profile_id int DEFAULT NULL, 2566PRIMARY KEY (id,REV) 2567); 2568CREATE TABLE t3 ( 2569id int NOT NULL, 2570REV int NOT NULL, 2571person_id int DEFAULT NULL, 2572PRIMARY KEY (id,REV) 2573); 2574CREATE TABLE t4 ( 2575id int NOT NULL, 2576REV int NOT NULL, 2577PRIMARY KEY (id,REV) 2578); 2579INSERT INTO t1 VALUES 2580(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), 2581(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), 2582(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), 2583(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), 2584(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), 2585(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), 2586(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), 2587(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), 2588(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), 2589(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), 2590(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), 2591(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), 2592(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), 2593(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), 2594(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), 2595(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), 2596(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), 2597(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), 2598(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), 2599(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), 2600(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), 2601(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), 2602(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), 2603(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), 2604(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), 2605(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), 2606(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), 2607(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), 2608(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), 2609(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); 2610INSERT INTO t2 VALUES 2611(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), 2612(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), 2613(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), 2614(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), 2615(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), 2616(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), 2617(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), 2618(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), 2619(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), 2620(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), 2621(12,605892,2,10219),(13,1,0,10220); 2622INSERT INTO t3 VALUES 2623(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), 2624(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), 2625(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), 2626(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), 2627(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), 2628(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), 2629(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), 2630(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); 2631INSERT INTO t4 VALUES 2632(300000,1),(300001,1),(300003,1),(300004,1), 2633(300005,1),(300005,688796),(300006,1),(300006,97697), 2634(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), 2635(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), 2636(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), 2637(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); 2638# This should have join order of t2,t3,t4,t1 2639EXPLAIN EXTENDED SELECT * 2640FROM t1 INNER JOIN t2 ON t2.REV=t1.id 2641INNER JOIN t3 ON t3.id=t2.profile_id 2642INNER JOIN t4 ON t4.id=t3.person_id 2643WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND 2644t2.REVTYPE=2; 2645id select_type table type possible_keys key key_len ref rows filtered Extra 26461 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where 26471 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 26481 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 26491 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 2650Warnings: 2651Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 2652SELECT * 2653FROM t1 INNER JOIN t2 ON t2.REV=t1.id 2654INNER JOIN t3 ON t3.id=t2.profile_id 2655INNER JOIN t4 ON t4.id=t3.person_id 2656WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND 2657t2.REVTYPE=2; 2658id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 265912 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 266012 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 266112 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 2662# This should have join order of t2,t3,t4,t1 with the same plan as above 2663# because all RIGHT JOIN operations are converted into INNER JOIN 2664EXPLAIN EXTENDED SELECT * 2665FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id 2666RIGHT JOIN t3 ON t3.id=t2.profile_id 2667RIGHT JOIN t4 ON t4.id=t3.person_id 2668WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 2669AND t2.REVTYPE=2; 2670id select_type table type possible_keys key key_len ref rows filtered Extra 26711 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where 26721 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 26731 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 26741 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 2675Warnings: 2676Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`timestamp` AS `timestamp`,`test`.`t1`.`modifiedBy` AS `modifiedBy`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`REV` AS `REV`,`test`.`t2`.`REVTYPE` AS `REVTYPE`,`test`.`t2`.`profile_id` AS `profile_id`,`test`.`t3`.`id` AS `id`,`test`.`t3`.`REV` AS `REV`,`test`.`t3`.`person_id` AS `person_id`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`REV` AS `REV` from `test`.`t4` join `test`.`t3` join `test`.`t2` join `test`.`t1` where `test`.`t2`.`REVTYPE` = 2 and `test`.`t1`.`id` = `test`.`t2`.`REV` and `test`.`t3`.`id` = `test`.`t2`.`profile_id` and `test`.`t4`.`id` = `test`.`t3`.`person_id` and `test`.`t1`.`timestamp` < 1294664900039 and `test`.`t1`.`timestamp` > 1294644616416 2677SELECT * 2678FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id 2679RIGHT JOIN t3 ON t3.id=t2.profile_id 2680RIGHT JOIN t4 ON t4.id=t3.person_id 2681WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 2682AND t2.REVTYPE=2; 2683id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 268412 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 268512 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 268612 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 2687DROP TABLE t1,t2,t3,t4; 2688# end of 10.1 tests 2689# 2690# MDEV-25362: name resolution for subqueries in ON expressions 2691# 2692create table t1 (a int, b int); 2693create table t2 (c int, d int); 2694create table t3 (e int, f int); 2695create table t4 (g int, h int); 2696explain 2697select * 2698from 2699t1 left join 2700(t2 2701join 2702t3 on 2703(t3.f=t1.a) 2704) on (t2.c=t1.a ); 2705ERROR 42S22: Unknown column 't1.a' in 'on clause' 2706explain 2707select * 2708from 2709t1 left join 2710(t2 2711join 2712t3 on 2713(t3.f=(select max(g) from t4 where t4.h=t1.a)) 2714) on (t2.c=t1.a ); 2715ERROR 42S22: Unknown column 't1.a' in 'where clause' 2716drop table t1,t2,t3,t4; 2717create table t1 (a int); 2718insert into t1 values (1),(2); 2719create table t2 (b int); 2720insert into t2 values (1),(2); 2721create table t3 (c int); 2722insert into t3 values (1),(2); 2723select * from ( select * from t1 left join t2 2724on b in (select x from t3 as sq1) 2725) as sq2; 2726ERROR 42S22: Unknown column 'x' in 'field list' 2727drop table t1,t2,t3; 2728# end of 10.2 tests 2729# 2730# MDEV-22866: Crash in join optimizer with constant outer join nest 2731# 2732CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2733INSERT INTO t1 VALUES (1),(2); 2734CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2735INSERT INTO t2 VALUES (3),(4); 2736CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; 2737CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; 2738INSERT INTO t4 VALUES (5),(6); 2739CREATE TABLE t5 (e INT) ENGINE=MyISAM; 2740INSERT INTO t5 VALUES (7),(8); 2741CREATE TABLE t6 (f INT) ENGINE=MyISAM; 2742INSERT INTO t6 VALUES (9),(10); 2743SELECT * 2744FROM 2745t1 2746LEFT JOIN ( 2747t2 LEFT JOIN ( 2748t3 JOIN 2749t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 2750) ON t2.b >= t4.d 2751) ON t1.a <= t2.b 2752LEFT JOIN t5 ON t2.b = t5.e 2753LEFT JOIN t6 ON t3.c = t6.f; 2754a b c d e f 27551 3 NULL NULL NULL NULL 27561 4 NULL NULL NULL NULL 27572 3 NULL NULL NULL NULL 27582 4 NULL NULL NULL NULL 2759drop table t1,t2,t3,t4,t5,t6; 2760# 2761# MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins 2762# 2763create table t1(a int); 2764insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2765create table t2(a int); 2766insert into t2 values (0),(1); 2767create table t3 (a int, b int, key(a)); 2768insert into t3 select A.a + B.a* 10 + C.a * 100, 12345 from t1 A, t1 B, t1 C; 2769# Uses range for table t3: 2770explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5; 2771id select_type table type possible_keys key key_len ref rows Extra 27721 SIMPLE t1 ALL NULL NULL NULL NULL 10 27731 SIMPLE t3 range a a 5 NULL 5 Using where 2774# This must use range for table t3, too: 2775explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; 2776id select_type table type possible_keys key key_len ref rows Extra 27771 SIMPLE t1 ALL NULL NULL NULL NULL 10 27781 SIMPLE t2 ALL NULL NULL NULL NULL 2 27791 SIMPLE t3 range a a 5 NULL 5 Using where 2780# 2781# .. part 2: make sure condition selectivity can use the condition too. 2782# 2783alter table t3 drop key a; 2784set @tmp1=@@optimizer_use_condition_selectivity; 2785set @tmp2=@@use_stat_tables; 2786set @tmp3=@@histogram_size; 2787set use_stat_tables=preferably; 2788set optimizer_use_condition_selectivity=4; 2789set histogram_size=100; 2790analyze table t3 persistent for all; 2791Table Op Msg_type Msg_text 2792test.t3 analyze status Engine-independent statistics collected 2793test.t3 analyze status OK 2794# t3.filtered is less than 100%: 2795explain extended select * from t1 left join t3 on t1.a=t3.b and t3.a<5; 2796id select_type table type possible_keys key key_len ref rows filtered Extra 27971 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 27981 SIMPLE t3 ALL NULL NULL NULL NULL 1000 1.96 Using where 2799Warnings: 2800Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t1` left join `test`.`t3` on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 2801# t3.filtered must less than 100%, too: 2802explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; 2803id select_type table type possible_keys key key_len ref rows filtered Extra 28041 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 28051 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 28061 SIMPLE t3 ALL NULL NULL NULL NULL 1000 1.96 Using where 2807Warnings: 2808Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join (`test`.`t3` join `test`.`t2`) on(`test`.`t3`.`b` = `test`.`t1`.`a` and `test`.`t3`.`a` < 5) where 1 2809drop table t1,t2,t3; 2810set optimizer_use_condition_selectivity= @tmp1; 2811set use_stat_tables= @tmp2; 2812set histogram_size= @tmp3; 2813# Another test 2814CREATE TABLE t1 (i1 int) ; 2815CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; 2816CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; 2817INSERT INTO t3 VALUES (2, NULL); 2818CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; 2819CREATE VIEW v4 AS SELECT * FROM t4; 2820SELECT 1 2821FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 2822LEFT JOIN v4 2823RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk 2824WHERE t3.pk IN (2); 28251 2826drop view v4; 2827drop table t1,t2,t3,t4; 2828# end of 10.3 tests 2829SET optimizer_switch=@org_optimizer_switch; 2830