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 index PRIMARY PRIMARY 4 NULL 2 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 OK 1794test.t2 analyze status OK 1795test.t3 analyze status OK 1796flush status; 1797select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null; 1798sum(t3.b) 17991006 1800show status like "handler_read%"; 1801Variable_name Value 1802Handler_read_first 0 1803Handler_read_key 4 1804Handler_read_last 0 1805Handler_read_next 5 1806Handler_read_prev 0 1807Handler_read_retry 0 1808Handler_read_rnd 0 1809Handler_read_rnd_deleted 0 1810Handler_read_rnd_next 1048581 1811flush status; 1812select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10; 1813sum(t3.b) 18141006 1815show status like "handler_read%"; 1816Variable_name Value 1817Handler_read_first 0 1818Handler_read_key 4 1819Handler_read_last 0 1820Handler_read_next 5 1821Handler_read_prev 0 1822Handler_read_retry 0 1823Handler_read_rnd 0 1824Handler_read_rnd_deleted 0 1825Handler_read_rnd_next 1048581 1826drop table t1,t2,t3; 1827# 1828# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field 1829# 1830CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1)); 1831CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2)); 1832INSERT INTO t1 VALUES (4); 1833INSERT INTO t2 VALUES (3, 3); 1834INSERT INTO t2 VALUES (7, 7); 1835EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1836WHERE t1.f1 = 4 1837GROUP BY t2.f1, t2.f2; 1838id select_type table type possible_keys key key_len ref rows Extra 18391 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using temporary; Using filesort 18401 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index 1841SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1842WHERE t1.f1 = 4 1843GROUP BY t2.f1, t2.f2; 1844f1 f1 f2 18454 NULL NULL 1846EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1847WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1848GROUP BY t2.f1, t2.f2; 1849id select_type table type possible_keys key key_len ref rows Extra 18501 SIMPLE t1 system PRIMARY NULL NULL NULL 1 18511 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort 1852SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 1853WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL 1854GROUP BY t2.f1, t2.f2; 1855f1 f1 f2 1856DROP TABLE t1,t2; 1857# 1858# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS 1859# WRONG RESULT 1860# 1861CREATE TABLE t1 (i1 int); 1862INSERT INTO t1 VALUES (100), (101); 1863CREATE TABLE t2 (i2 int, i3 int); 1864INSERT INTO t2 VALUES (20,1),(10,2); 1865CREATE TABLE t3 (i4 int(11)); 1866INSERT INTO t3 VALUES (1),(2); 1867 1868SELECT ( 1869SELECT MAX( t2.i2 ) 1870FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 ) 1871WHERE t2.i3 <> t1.i1 1872) AS field1 1873FROM t1;; 1874field1 187520 187620 1877 1878SELECT ( 1879SELECT MAX( t2.i2 ) 1880FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 ) 1881WHERE t2.i3 <> t1.i1 1882) AS field1 1883FROM t1 GROUP BY field1;; 1884field1 188520 1886 1887drop table t1,t2,t3; 1888# End of test for Bug#13068506 1889End of 5.1 tests 1890# 1891# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery 1892# predicate in WHERE condition. 1893# 1894CREATE TABLE t1(a INT); 1895INSERT INTO t1 VALUES(9); 1896CREATE TABLE t2(b INT); 1897INSERT INTO t2 VALUES(8); 1898CREATE TABLE t3(c INT); 1899INSERT INTO t3 VALUES(3); 1900SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); 1901b c 1902NULL 3 1903SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); 1904c b 19053 NULL 1906SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); 1907b c 1908NULL 3 1909SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); 1910c b 19113 NULL 1912drop table t1,t2,t3; 1913End of 5.2 tests 1914# 1915# LP bug #813447: LEFT JOIN with single-row inner table and 1916# a subquery in ON expression 1917# 1918CREATE TABLE t1 (a int); 1919INSERT INTO t1 VALUES (0); 1920CREATE TABLE t2 (a int); 1921INSERT INTO t2 VALUES (0); 1922CREATE TABLE t3 (a int); 1923INSERT INTO t3 VALUES (0), (0); 1924SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); 1925a 1926NULL 1927EXPLAIN EXTENDED 1928SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3); 1929id select_type table type possible_keys key key_len ref rows filtered Extra 19301 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 19311 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 19322 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1933Warnings: 1934Note 1003 /* select#1 */ select NULL AS `a` from `test`.`t2` where 1 1935DROP TABLE t1,t2,t3; 1936# 1937# LP bug #817384 Wrong result with outer join + subquery in ON 1938# clause +unique key 1939# 1940CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ; 1941INSERT INTO t1 VALUES (1,'b'); 1942CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ; 1943INSERT INTO t2 VALUES (1,'a'); 1944create table t3 (c1 char(1), c2 char(2)); 1945insert into t3 values ('c','d'); 1946insert into t3 values ('c','d'); 1947EXPLAIN SELECT t2.b 1948FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3); 1949id select_type table type possible_keys key key_len ref rows Extra 19501 PRIMARY t1 system NULL NULL NULL NULL 1 19511 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 const 1 Using where 19522 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where 1953SELECT t2.b 1954FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3); 1955b 1956NULL 1957EXPLAIN SELECT t2.b 1958FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; 1959id select_type table type possible_keys key key_len ref rows Extra 19601 PRIMARY t1 system NULL NULL NULL NULL 1 19611 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using where 19622 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where 1963SELECT t2.b 1964FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1; 1965b 1966NULL 1967DROP TABLE t1,t2,t3; 1968# 1969# lp:825035 second execution of PS with outer join 1970# 1971CREATE TABLE t1 (a int); 1972INSERT INTO t1 VALUES (1),(2),(3),(4); 1973CREATE TABLE t2 (a int); 1974PREPARE stmt FROM 1975"SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a"; 1976EXECUTE stmt; 1977a a 19781 NULL 19792 NULL 19803 NULL 19814 NULL 1982EXECUTE stmt; 1983a a 19841 NULL 19852 NULL 19863 NULL 19874 NULL 1988DEALLOCATE PREPARE stmt; 1989DROP TABLE t1,t2; 1990# 1991# lp:838633 second execution of PS with outer join 1992# converted to inner join 1993# 1994CREATE TABLE t1 ( b int NOT NULL ) ; 1995INSERT INTO t1 VALUES (9),(10); 1996CREATE TABLE t2 ( b int NOT NULL, PRIMARY KEY (b)) ; 1997INSERT INTO t2 VALUES 1998(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89), 1999(10), (90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100); 2000CREATE TABLE t3 ( a int, b int NOT NULL , PRIMARY KEY (b)) ; 2001INSERT INTO t3 VALUES 2002(0,6),(0,7),(0,8),(2,9),(0,10),(2,21),(0,22),(2,23),(2,24),(2,25); 2003set @save_join_cache_level= @@join_cache_level; 2004SET SESSION join_cache_level=4; 2005EXPLAIN EXTENDED 2006SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b; 2007id select_type table type possible_keys key key_len ref rows filtered Extra 20081 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 20091 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t1.b 10 10.00 Using join buffer (flat, BNLH join) 20101 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.b 27 3.70 Using index; Using join buffer (incremental, BNLH join) 2011Warnings: 2012Note 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` 2013PREPARE stmt FROM 2014'SELECT * FROM (t2 LEFT JOIN t1 ON t1.b = t2.b) JOIN t3 ON t1.b = t3.b'; 2015EXECUTE stmt; 2016b b a b 201710 10 0 10 2018EXECUTE stmt; 2019b b a b 202010 10 0 10 2021DEALLOCATE PREPARE stmt; 2022SET SESSION join_cache_level=@save_join_cache_level; 2023DROP TABLE t1,t2,t3; 2024# 2025# LP bug #943543: LEFT JOIN converted to JOIN with 2026# ORed IS NULL(primary key) in WHERE clause 2027# 2028CREATE TABLE t1 ( 2029a int, b int NOT NULL, pk int NOT NULL, 2030PRIMARY KEY (pk), INDEX idx(b) 2031); 2032INSERT INTO t1 VALUES 2033(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4), 2034(1,9,6), (8,5,7), (NULL,8,8), (8,1,5); 2035CREATE TABLE t2 (pk int PRIMARY KEY); 2036INSERT INTO t2 VALUES (3), (8), (5); 2037EXPLAIN EXTENDED 2038SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a 2039WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2040ORDER BY t1.pk; 2041id select_type table type possible_keys key key_len ref rows filtered Extra 20421 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 20431 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index 2044Warnings: 2045Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5 2046SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a 2047WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2048ORDER BY t1.pk; 2049pk 20505 2051EXPLAIN EXTENDED 2052SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a 2053WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2054ORDER BY t1.pk; 2055id select_type table type possible_keys key key_len ref rows filtered Extra 20561 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 20571 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index 2058Warnings: 2059Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where 1 order by 5 2060SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a 2061WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 2062ORDER BY t1.pk; 2063pk 20645 2065DROP TABLE t2; 2066CREATE TABLE t2 (c int, d int, KEY (c)); 2067INSERT INTO t2 VALUES 2068(3,30), (8,88), (5,50), (8,81), 2069(4,40), (9,90), (7,70), (9,90), 2070(13,130), (18,188), (15,150), (18,181), 2071(14,140), (19,190), (17,170), (19,190); 2072INSERT INTO t1 VALUES (8,5,9); 2073EXPLAIN EXTENDED 2074SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a 2075WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2076ORDER BY t1.b; 2077id select_type table type possible_keys key key_len ref rows filtered Extra 20781 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where 20791 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 2080Warnings: 2081Note 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` 2082SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a 2083WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2084ORDER BY t1.b; 2085b c d 20865 8 88 20875 8 81 20885 8 88 20895 8 81 2090EXPLAIN EXTENDED 2091SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a 2092WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2093ORDER BY t1.b; 2094id select_type table type possible_keys key key_len ref rows filtered Extra 20951 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where 20961 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 2097Warnings: 2098Note 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` 2099SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a 2100WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 2101ORDER BY t1.b; 2102b c d 21035 8 88 21045 8 81 21055 8 88 21065 8 81 2107DROP TABLE t1,t2; 2108# 2109# Bug mdev-4336: LEFT JOIN with disjunctive 2110# <non-nullable datetime field> IS NULL in WHERE 2111# causes a hang and eventual crash 2112# 2113CREATE TABLE t1 ( 2114id int(11) NOT NULL, 2115modified datetime NOT NULL, 2116PRIMARY KEY (id) 2117); 2118SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id 2119WHERE a.modified > b.modified or b.modified IS NULL; 2120id modified 2121DROP TABLE t1; 2122# 2123# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL 2124# 2125create table t0 (a int not null); 2126insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2127alter table t0 add person_id varchar(255) not null; 2128create table t1 (pk int not null primary key); 2129insert into t1 select A.a + 10*B.a from t0 A, t0 B; 2130explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; 2131id select_type table type possible_keys key key_len ref rows Extra 21321 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21331 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2134explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; 2135id select_type table type possible_keys key key_len ref rows Extra 21361 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21371 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2138explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; 2139id select_type table type possible_keys key key_len ref rows Extra 21401 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where 21411 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using index 2142drop table t0, t1; 2143# 2144# MDEV-4836: Wrong result on <not null date column> IS NULL (old documented hack stopped working) 2145# (this is a regression after fix for MDEV-4817) 2146# 2147CREATE TABLE t1 (id INT, d DATE NOT NULL); 2148INSERT INTO t1 VALUES (1,'0000-00-00'),(2,'0000-00-00'); 2149CREATE TABLE t2 (i INT); 2150SELECT * FROM t1 LEFT JOIN t2 ON (id=i) WHERE NULL OR d IS NULL; 2151id d i 21521 0000-00-00 NULL 21532 0000-00-00 NULL 2154DROP TABLE t1,t2; 2155CREATE TABLE t1 (i1 INT, d1 DATE NOT NULL); 2156INSERT INTO t1 VALUES (1,'2012-12-21'),(2,'0000-00-00'); 2157CREATE TABLE t2 (i2 INT, j2 INT); 2158INSERT INTO t2 VALUES (1,10),(2,20); 2159SELECT * FROM t1 LEFT JOIN t2 ON i1 = j2 WHERE d1 IS NULL AND 1 OR i1 = i2; 2160i1 d1 i2 j2 21612 0000-00-00 NULL NULL 2162DROP TABLE t1,t2; 2163# Another testcase 2164CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; 2165INSERT INTO t1 VALUES (NULL); 2166CREATE TABLE t2 (i2 INT, a INT, b INT) ENGINE=MyISAM; 2167CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; 2168INSERT INTO t2 VALUES (NULL,1,2),(NULL,2,3); 2169SELECT * FROM t1 LEFT JOIN v2 ON i1 = i2 WHERE a < b; 2170i1 i2 a b 2171SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE a < b; 2172i1 i2 a b 2173drop view v2; 2174drop table t1,t2; 2175# 2176# Bug mdev-4942: LEFT JOIN with conjunctive 2177# <non-nullable datetime field> IS NULL in WHERE 2178# causes an assert failure 2179# 2180CREATE TABLE t1 ( i1 int, d1 date ); 2181INSERT INTO t1 VALUES (1,'2001-06-26'), (2,'2000-11-16'); 2182CREATE TABLE t2 ( i2 int, d2 date NOT NULL ); 2183INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25'); 2184SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL; 2185i1 d1 i2 d2 2186DROP TABLE t1,t2; 2187# 2188# Bug mdev-4952: LEFT JOIN with disjunctive 2189# <non-nullable datetime field> IS NULL in WHERE 2190# causes an assert failure 2191# 2192CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM; 2193INSERT INTO t1 VALUES (1, 10), (2, 11); 2194CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM; 2195INSERT INTO t2 VALUES 2196('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200); 2197SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2 2198WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL; 2199a1 b1 dt a2 b2 2200DROP TABLE t1,t2; 2201# 2202# Bug mdev-4962: nested outer join with 2203# <non-nullable datetime field> IS NULL in WHERE 2204# causes an assert failure 2205# 2206CREATE TABLE t1 (i1 int) ENGINE=MyISAM; 2207INSERT INTO t1 VALUES (1),(2); 2208CREATE TABLE t2 (i2 int) ENGINE=MyISAM; 2209INSERT INTO t2 VALUES (10),(20); 2210CREATE TABLE t3 (i3 int, d3 datetime NOT NULL) ENGINE=MyISAM; 2211INSERT INTO t3 VALUES (8,'2008-12-04 17:53:42'),(9,'2012-12-21 12:12:12'); 2212SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 2213WHERE d3 IS NULL; 2214i1 i2 i3 d3 22151 NULL NULL NULL 22162 NULL NULL NULL 2217EXPLAIN EXTENDED 2218SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 2219WHERE d3 IS NULL; 2220id select_type table type possible_keys key key_len ref rows filtered Extra 22211 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 22221 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 22231 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2224Warnings: 2225Note 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 2226DROP TABLE t1,t2,t3; 2227# 2228# Bug mdev-6705: wrong on expression after constant row substitution 2229# that triggers a simplification of WHERE condition 2230# 2231CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; 2232INSERT INTO t1 VALUES (10,8); 2233CREATE TABLE t2 (c int) ENGINE=MyISAM; 2234INSERT INTO t2 VALUES (8),(9); 2235CREATE TABLE t3 (d int) ENGINE=MyISAM; 2236INSERT INTO t3 VALUES (3),(8); 2237EXPLAIN EXTENDED 2238SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a 2239WHERE b IN (1,2,3) OR b = d; 2240id select_type table type possible_keys key key_len ref rows filtered Extra 22411 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 22421 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 22431 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2244Warnings: 2245Note 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 2246SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a 2247WHERE b IN (1,2,3) OR b = d; 2248a b c d 2249DROP TABLE t1,t2,t3; 2250# 2251# MDEV-6634: Wrong estimates for ref(const) and key IS NULL predicate 2252# 2253create table t1(a int); 2254insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2255create table t2 (a int, b int, c int, key(b), key(c)); 2256insert into t2 select 2257@a:=A.a + 10*B.a+100*C.a, 2258IF(@a<900, NULL, @a), 2259IF(@a<500, NULL, @a) 2260from t1 A, t1 B, t1 C; 2261delete from t1 where a=0; 2262# Check that there are different #rows of NULLs for b and c, both !=10: 2263explain select * from t2 force index (b) where b is null; 2264id select_type table type possible_keys key key_len ref rows Extra 22651 SIMPLE t2 ref b b 5 const 780 Using index condition 2266explain select * from t2 force index (c) where c is null; 2267id select_type table type possible_keys key key_len ref rows Extra 22681 SIMPLE t2 ref c c 5 const 393 Using index condition 2269explain select * from t1 left join t2 on t2.b is null; 2270id select_type table type possible_keys key key_len ref rows Extra 22711 SIMPLE t1 ALL NULL NULL NULL NULL 9 22721 SIMPLE t2 ref b b 5 const 780 Using where 2273explain select * from t1 left join t2 on t2.c is null; 2274id select_type table type possible_keys key key_len ref rows Extra 22751 SIMPLE t1 ALL NULL NULL NULL NULL 9 22761 SIMPLE t2 ref c c 5 const 393 Using where 2277drop table t1,t2; 2278# 2279# MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause 2280# 2281CREATE TABLE t1(i1 int primary key, v1 int, key(v1)); 2282INSERT INTO t1 VALUES (1, 1); 2283INSERT INTO t1 VALUES (2, 2); 2284INSERT INTO t1 VALUES (3, 3); 2285INSERT INTO t1 VALUES (4, 4); 2286INSERT INTO t1 VALUES (5, 3); 2287INSERT INTO t1 VALUES (6, 6); 2288INSERT INTO t1 VALUES (7, 7); 2289INSERT INTO t1 VALUES (8, 8); 2290INSERT INTO t1 VALUES (9, 9); 2291CREATE TABLE t2(i2 int primary key, v2 int, key(v2)); 2292INSERT INTO t2 VALUES (1, 1); 2293INSERT INTO t2 VALUES (2, 2); 2294INSERT INTO t2 VALUES (3, 3); 2295INSERT INTO t2 VALUES (4, 4); 2296INSERT INTO t2 VALUES (5, 3); 2297INSERT INTO t2 VALUES (6, 6); 2298INSERT INTO t2 VALUES (7, 7); 2299INSERT INTO t2 VALUES (8, 8); 2300INSERT INTO t2 VALUES (9, 9); 2301CREATE TABLE t3(i3 int primary key, v3 int, key(v3)); 2302INSERT INTO t3 VALUES (2, 2); 2303INSERT INTO t3 VALUES (4, 4); 2304INSERT INTO t3 VALUES (6, 6); 2305INSERT INTO t3 VALUES (8, 8); 2306# This should have a join order of t3,t1,t2 (or t3,t2,t1, the idea is that t3 is the first one) 2307EXPLAIN EXTENDED 2308SELECT * FROM 2309(SELECT t1.i1 as i1, t1.v1 as v1, 2310t2.i2 as i2, t2.v2 as v2, 2311t3.i3 as i3, t3.v3 as v3 2312FROM t1 JOIN t2 on t1.i1 = t2.i2 2313LEFT JOIN t3 on t2.i2 = t3.i3 2314) as w1 2315WHERE v3 = 4; 2316id select_type table type possible_keys key key_len ref rows filtered Extra 23171 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00 23181 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 23191 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 2320Warnings: 2321Note 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` 2322# This should have the same join order like the query above: 2323EXPLAIN EXTENDED 2324SELECT * FROM 2325(SELECT t1.i1 as i1, t1.v1 as v1, 2326t2.i2 as i2, t2.v2 as v2, 2327t3.i3 as i3, t3.v3 as v3 2328FROM t1 JOIN t2 on t1.i1 = t2.i2 2329LEFT JOIN t3 on t2.i2 = t3.i3 2330WHERE t1.i1 = t2.i2 2331AND 1 = 1 2332) as w2 2333WHERE v3 = 4; 2334id select_type table type possible_keys key key_len ref rows filtered Extra 23351 SIMPLE t3 ref PRIMARY,v3 v3 5 const 1 100.00 23361 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 23371 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t3.i3 1 100.00 2338Warnings: 2339Note 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` 2340drop table t1,t2,t3; 2341# 2342# MDEV-11958: LEFT JOIN with stored routine produces incorrect result 2343# 2344CREATE TABLE t (x INT); 2345INSERT INTO t VALUES(1),(NULL); 2346CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret); 2347SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2348FROM t t1 LEFT JOIN t t2 2349ON t1.x = t2.x 2350WHERE IFNULL(t2.x,0)=0; 2351x x IFNULL(t2.x,0) f(t2.x,0) 2352NULL NULL 0 0 2353explain extended 2354SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2355FROM t t1 LEFT JOIN t t2 2356ON t1.x = t2.x 2357WHERE IFNULL(t2.x,0)=0; 2358id select_type table type possible_keys key key_len ref rows filtered Extra 23591 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 23601 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2361Warnings: 2362Note 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 2363SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2364FROM t t1 LEFT JOIN t t2 2365ON t1.x = t2.x 2366WHERE f(t2.x,0)=0; 2367x x IFNULL(t2.x,0) f(t2.x,0) 2368NULL NULL 0 0 2369explain extended 2370SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0) 2371FROM t t1 LEFT JOIN t t2 2372ON t1.x = t2.x 2373WHERE f(t2.x,0)=0; 2374id select_type table type possible_keys key key_len ref rows filtered Extra 23751 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 23761 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2377Warnings: 2378Note 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 2379drop function f; 2380drop table t; 2381CREATE TABLE t1 ( 2382col1 DECIMAL(33,5) NULL DEFAULT NULL, 2383col2 DECIMAL(33,5) NULL DEFAULT NULL 2384); 2385CREATE TABLE t2 ( 2386col1 DECIMAL(33,5) NULL DEFAULT NULL, 2387col2 DECIMAL(33,5) NULL DEFAULT NULL, 2388col3 DECIMAL(33,5) NULL DEFAULT NULL 2389); 2390INSERT INTO t1 VALUES (2, 1.1), (2, 2.1); 2391INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL); 2392CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15)) 2393RETURNS decimal(33,5) 2394LANGUAGE SQL 2395DETERMINISTIC 2396CONTAINS SQL 2397SQL SECURITY INVOKER 2398BEGIN 2399IF p_num IS NULL THEN 2400RETURN p_return; 2401ELSE 2402RETURN p_num; 2403END IF; 2404END | 2405SELECT t1.col1, t2.col1, t2.col3 2406FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2407WHERE IFNULL(t2.col3,0) = 0; 2408col1 col1 col3 24092.00000 NULL NULL 24102.00000 NULL NULL 2411EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 2412FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2413WHERE IFNULL(t2.col3,0) = 0; 2414id select_type table type possible_keys key key_len ref rows filtered Extra 24151 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 24161 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2417Warnings: 2418Note 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 2419SELECT t1.col1, t2.col1, t2.col3 2420FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2421WHERE f1(t2.col3,0) = 0; 2422col1 col1 col3 24232.00000 NULL NULL 24242.00000 NULL NULL 2425EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3 2426FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2 2427WHERE f1(t2.col3,0) = 0; 2428id select_type table type possible_keys key key_len ref rows filtered Extra 24291 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 24301 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2431Warnings: 2432Note 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 2433DROP FUNCTION f1; 2434DROP TABLE t1,t2; 2435# 2436# MDEV-10397: Server crashes in key_copy with join_cache_level > 2 and join on BIT fields 2437# 2438CREATE TABLE t1 (b1 BIT NOT NULL); 2439INSERT INTO t1 VALUES (0),(1); 2440CREATE TABLE t2 (b2 BIT NOT NULL); 2441INSERT INTO t2 VALUES (0),(1); 2442set @save_join_cache_level= @@join_cache_level; 2443SET @@join_cache_level = 3; 2444SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2; 2445t1.b1+'0' t2.b2 + '0' 24460 0 24471 1 2448DROP TABLE t1, t2; 2449set @@join_cache_level= @save_join_cache_level; 2450# 2451# MDEV-14779: using left join causes incorrect results with materialization and derived tables 2452# 2453create table t1(id int); 2454insert into t1 values (1),(2); 2455create table t2(sid int, id int); 2456insert into t2 values (1,1),(2,2); 2457select * from t1 t 2458left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r 2459on t.id=r.id ; 2460id sid id 24611 NULL NULL 24622 NULL NULL 2463drop table t1, t2; 2464# 2465# MDEV-16726: SELECT with STRAGHT JOIN containing NESTED RIGHT JOIN 2466# converted to INNER JOIN with first constant inner table 2467# 2468CREATE TABLE t1 ( 2469pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1), KEY v1 (v1,i1) 2470) engine=MyISAM; 2471INSERT INTO t1 VALUES 2472(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'), 2473(14,226,'m','m'),(15,133,'p','p'); 2474CREATE TABLE t2 ( 2475pk int PRIMARY KEY, i1 int, v1 varchar(1), v2 varchar(1) 2476) engine=MyISAM; 2477INSERT INTO t2 VALUES (10,6,'p','p'); 2478EXPLAIN EXTENDED 2479SELECT STRAIGHT_JOIN t2.v2 2480FROM 2481(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2482RIGHT JOIN 2483(t2,t1) 2484ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2485WHERE tb1.pk = 40 2486ORDER BY tb1.i1; 2487id select_type table type possible_keys key key_len ref rows filtered Extra 24881 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2489Warnings: 2490Note 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 2491EXPLAIN EXTENDED 2492SELECT STRAIGHT_JOIN t2.v2 2493FROM 2494(t2,t1) 2495LEFT JOIN 2496(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2497ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2498WHERE tb1.pk = 40 2499ORDER BY tb1.i1; 2500id select_type table type possible_keys key key_len ref rows filtered Extra 25011 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2502Warnings: 2503Note 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 2504SELECT STRAIGHT_JOIN DISTINCT t2.v2 2505FROM 2506(t1 as tb1 LEFT JOIN t1 AS tb2 ON tb2.v1 = tb1.v2) 2507RIGHT JOIN 2508(t2,t1) 2509ON t1.pk = t2.pk AND t2.v2 = tb1.v1 2510WHERE tb1.pk = 40 2511ORDER BY tb1.i1; 2512v2 2513DROP TABLE t1,t2; 2514# 2515# MDEV-19790 : IS NOT TRUE / IS NOT FALSE predicates over 2516# inner tables of outer joins 2517# 2518create table t1 (a int); 2519create table t2 (b int); 2520insert into t1 values (3), (7), (1); 2521insert into t2 values (7), (4), (3); 2522select * from t1 left join t2 on a=b; 2523a b 25243 3 25257 7 25261 NULL 2527select * from t1 left join t2 on a=b where (b > 3) is not true; 2528a b 25293 3 25301 NULL 2531explain extended select * from t1 left join t2 on a=b where (b > 3) is not true; 2532id select_type table type possible_keys key key_len ref rows filtered Extra 25331 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 25341 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2535Warnings: 2536Note 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 2537select * from t1 left join t2 on a=b where (b > 3) is not false; 2538a b 25397 7 25401 NULL 2541explain extended select * from t1 left join t2 on a=b where (b > 3) is not false; 2542id select_type table type possible_keys key key_len ref rows filtered Extra 25431 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 25441 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 2545Warnings: 2546Note 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 2547drop table t1,t2; 2548# end of 5.5 tests 2549# 2550# MDEV-19258: chained right joins all converted to inner joins 2551# 2552CREATE TABLE t1 ( 2553id int NOT NULL AUTO_INCREMENT, 2554timestamp bigint NOT NULL, 2555modifiedBy varchar(255) DEFAULT NULL, 2556PRIMARY KEY (id) 2557); 2558CREATE TABLE t2 ( 2559id int NOT NULL, 2560REV int NOT NULL, 2561REVTYPE tinyint DEFAULT NULL, 2562profile_id int DEFAULT NULL, 2563PRIMARY KEY (id,REV) 2564); 2565CREATE TABLE t3 ( 2566id int NOT NULL, 2567REV int NOT NULL, 2568person_id int DEFAULT NULL, 2569PRIMARY KEY (id,REV) 2570); 2571CREATE TABLE t4 ( 2572id int NOT NULL, 2573REV int NOT NULL, 2574PRIMARY KEY (id,REV) 2575); 2576INSERT INTO t1 VALUES 2577(1,1294391193890,'Cxqy$*9.kKeE'),(2,1294643906883,'rE4wqGV0gif@'), 2578(3,1294643927456,'L?3yt(%dY$Br'),(4,1294644343525,'WH&ObiZ$#2S4'), 2579(5,1294644616416,'YXnCbt?olUZ0'),(6,1294644954537,'8Npe4!(#lU@k'), 2580(7,1294645046659,'knc0GhXB1#ib'),(8,1294645183829,'w*oPpVfuS8^m'), 2581(9,1294645386701,'hwXR@3qVzrbU'),(10,1294645525982,'BeLW*Y9ndP0l'), 2582(11,1294645627723,'nTegib^)qZ$I'),(12,1294650860266,'u62C^Kzx3wH8'), 2583(13,1294657613745,'4&BkFjGa!qLg'),(14,1294660627161,')anpt312SCoh'), 2584(15,1294661023336,'LtJ2PX?*kTmx'),(16,1294662838066,'POGRr@?#ofpl'), 2585(17,1294663020989,'o.)1EOT2jnF7'),(18,1294663308065,'&TZ0F0LHE6.h'), 2586(19,1294664900039,'j)kSC%^In$9d'),(20,1294668904556,'97glN50)cAo.'), 2587(21,1294728056853,'lrKZxmw?I.Ek'),(22,1294728157174,'@P*SRg!pT.q?'), 2588(23,1294728327099,'W9gPrptF.)8n'),(24,1294728418481,'$q*c^sM&URd#'), 2589(25,1294728729620,'9*f4&bTPRtHo'),(26,1294728906014,')4VtTEnS7$oI'), 2590(27,1294732190003,'8dkNSPq2u3AQ'),(28,1294733205065,'SV2N6IoEf438'), 2591(29,1294741984927,'rBKj.0S^Ey%*'),(30,1294751748352,'j$2DvlBqk)Fw'), 2592(31,1294753902212,'C$N6OrEw8elz'),(32,1294758120598,'DCSVZw!rnxXq'), 2593(33,1294761769556,'OTS@QU8a6s5c'),(34,1294816845305,'IUE2stG0D3L5'), 2594(35,1294816966909,'Xd16yka.9nHe'),(36,1294817116302,'lOQHZpm%!8qb'), 2595(37,1294817374775,'^&pE3IhNf7ey'),(38,1294817538907,'oEn4#7C0Vhfp'), 2596(39,1294818482950,'bx54J*O0Va&?'),(40,1294819047024,'J%@a&1.qgdb?'), 2597(41,1294821826077,'C9kojr$L3Phz'),(42,1294825454458,'gG#BOnM80ZPi'), 2598(43,1294904129918,'F^!TrjM#zdvc'),(44,1294904254166,'Va&Tb)k0RvlM'), 2599(45,1294904414964,'dJjq0M6HvhR#'),(46,1294904505784,'nJmxg)ELqY(b'), 2600(47,1294904602835,'dhF#or$Vge!7'),(48,1294904684728,'?bIh5E3l!0em'), 2601(49,1294904877898,'Y*WflOdcxnk.'),(50,1294905002390,'*?H!lUgez5A.'), 2602(51,1294905096043,'wlEIY3n9uz!p'),(52,1294905404621,'T?qv3H6&hlQD'), 2603(53,1294905603922,'S@Bhys^Ti7bt'),(54,1294905788416,'KR?a5NVukz#l'), 2604(55,1294905993190,'A*&q4kWhED!o'),(56,1294906205254,'fT0%7z0DF6h*'), 2605(57,1294906319680,'LhzdW4?ivjR0'),(58,1294906424296,'h0KDlns%U*6T'), 2606(59,1294906623844,'b$CfB1noI6Ax'),(60,1294911258896,'#T1*LP!3$Oys'); 2607INSERT INTO t2 VALUES 2608(1,1,0,10209),(1,42480,1,10209),(1,61612,1,10209),(1,257545,1,10209), 2609(1,385332,1,10209),(1,1687999,1,10209),(3,1,0,10210),(3,617411,2,10210), 2610(4,11,0,14),(4,95149,1,10211),(4,607890,2,10211),(5,1,0,10212), 2611(6,1,0,10213),(6,93344,1,10213),(6,295578,1,10213),(6,295579,1,10213), 2612(6,295644,1,10213),(7,1,0,10214),(7,12,1,7),(7,688796,1,10214), 2613(7,1140433,1,10214),(7,1715227,1,10214),(8,1,0,10215),(8,74253,1,10215), 2614(8,93345,1,10215),(8,12,2,2),(9,1,0,10216),(9,93342,1,10216), 2615(9,122354,1,10216),(9,301499,2,10216),(10,11,0,5),(10,93343,1,10217), 2616(10,122355,1,10217),(10,123050,1,10217),(10,301500,2,10217),(11,1,0,10218), 2617(11,87852,1,10218),(11,605499,2,10218),(12,1,0,10219),(12,88024,1,10219), 2618(12,605892,2,10219),(13,1,0,10220); 2619INSERT INTO t3 VALUES 2620(1,1,300003),(1,117548,NULL),(2,1,300003),(2,117548,300006), 2621(3,1,300153),(3,117548,NULL),(4,1,300153),(4,117548,NULL), 2622(5,1,300153),(5,117548,NULL),(6,1,300182),(6,117548,NULL), 2623(7,1,300205),(7,117548,NULL),(8,1,300217),(8,117548,NULL), 2624(9,1,300290),(9,117548,NULL),(10,1,300290),(10,117548,NULL), 2625(11,1,300405),(11,117548,NULL),(12,1,300670),(12,117548,NULL), 2626(13,1,300670),(13,117548,NULL),(14,1,300006),(14,117548,NULL), 2627(15,1,300671),(15,117548,NULL),(16,1,300732),(16,117548,NULL); 2628INSERT INTO t4 VALUES 2629(300000,1),(300001,1),(300003,1),(300004,1), 2630(300005,1),(300005,688796),(300006,1),(300006,97697), 2631(300009,1),(300010,1),(300011,1),(300012,1),(300013,1), 2632(300014,1),(300015,1),(300016,1),(300017,1),(300018,1), 2633(300019,1),(300020,1),(300021,1),(300022,1),(300023,1), 2634(300024,1),(300025,1),(300026,1),(300027,1),(300028,1); 2635# This should have join order of t2,t3,t4,t1 2636EXPLAIN EXTENDED SELECT * 2637FROM t1 INNER JOIN t2 ON t2.REV=t1.id 2638INNER JOIN t3 ON t3.id=t2.profile_id 2639INNER JOIN t4 ON t4.id=t3.person_id 2640WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND 2641t2.REVTYPE=2; 2642id select_type table type possible_keys key key_len ref rows filtered Extra 26431 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where 26441 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 26451 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 26461 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 2647Warnings: 2648Note 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 2649SELECT * 2650FROM t1 INNER JOIN t2 ON t2.REV=t1.id 2651INNER JOIN t3 ON t3.id=t2.profile_id 2652INNER JOIN t4 ON t4.id=t3.person_id 2653WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 AND 2654t2.REVTYPE=2; 2655id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 265612 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 265712 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 265812 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 2659# This should have join order of t2,t3,t4,t1 with the same plan as above 2660# because all RIGHT JOIN operations are converted into INNER JOIN 2661EXPLAIN EXTENDED SELECT * 2662FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id 2663RIGHT JOIN t3 ON t3.id=t2.profile_id 2664RIGHT JOIN t4 ON t4.id=t3.person_id 2665WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 2666AND t2.REVTYPE=2; 2667id select_type table type possible_keys key key_len ref rows filtered Extra 26681 SIMPLE t2 ALL NULL NULL NULL NULL 42 100.00 Using where 26691 SIMPLE t3 ref PRIMARY PRIMARY 4 test.t2.profile_id 1 100.00 Using where 26701 SIMPLE t4 ref PRIMARY PRIMARY 4 test.t3.person_id 1 100.00 Using index 26711 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.REV 1 100.00 Using where 2672Warnings: 2673Note 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 2674SELECT * 2675FROM t1 RIGHT JOIN t2 ON t2.REV=t1.id 2676RIGHT JOIN t3 ON t3.id=t2.profile_id 2677RIGHT JOIN t4 ON t4.id=t3.person_id 2678WHERE t1.timestamp < 1294664900039 AND t1.timestamp > 1294644616416 2679AND t2.REVTYPE=2; 2680id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 268112 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 1 300003 300003 1 268212 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 1 268312 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 2684DROP TABLE t1,t2,t3,t4; 2685# end of 10.1 tests 2686# 2687# MDEV-25362: name resolution for subqueries in ON expressions 2688# 2689create table t1 (a int, b int); 2690create table t2 (c int, d int); 2691create table t3 (e int, f int); 2692create table t4 (g int, h int); 2693explain 2694select * 2695from 2696t1 left join 2697(t2 2698join 2699t3 on 2700(t3.f=t1.a) 2701) on (t2.c=t1.a ); 2702ERROR 42S22: Unknown column 't1.a' in 'on clause' 2703explain 2704select * 2705from 2706t1 left join 2707(t2 2708join 2709t3 on 2710(t3.f=(select max(g) from t4 where t4.h=t1.a)) 2711) on (t2.c=t1.a ); 2712ERROR 42S22: Unknown column 't1.a' in 'where clause' 2713drop table t1,t2,t3,t4; 2714create table t1 (a int); 2715insert into t1 values (1),(2); 2716create table t2 (b int); 2717insert into t2 values (1),(2); 2718create table t3 (c int); 2719insert into t3 values (1),(2); 2720select * from ( select * from t1 left join t2 2721on b in (select x from t3 as sq1) 2722) as sq2; 2723ERROR 42S22: Unknown column 'x' in 'field list' 2724drop table t1,t2,t3; 2725# end of 10.2 tests 2726# 2727# MDEV-22866: Crash in join optimizer with constant outer join nest 2728# 2729CREATE TABLE t1 (a INT) ENGINE=MyISAM; 2730INSERT INTO t1 VALUES (1),(2); 2731CREATE TABLE t2 (b INT) ENGINE=MyISAM; 2732INSERT INTO t2 VALUES (3),(4); 2733CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; 2734CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; 2735INSERT INTO t4 VALUES (5),(6); 2736CREATE TABLE t5 (e INT) ENGINE=MyISAM; 2737INSERT INTO t5 VALUES (7),(8); 2738CREATE TABLE t6 (f INT) ENGINE=MyISAM; 2739INSERT INTO t6 VALUES (9),(10); 2740SELECT * 2741FROM 2742t1 2743LEFT JOIN ( 2744t2 LEFT JOIN ( 2745t3 JOIN 2746t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 2747) ON t2.b >= t4.d 2748) ON t1.a <= t2.b 2749LEFT JOIN t5 ON t2.b = t5.e 2750LEFT JOIN t6 ON t3.c = t6.f; 2751a b c d e f 27521 3 NULL NULL NULL NULL 27531 4 NULL NULL NULL NULL 27542 3 NULL NULL NULL NULL 27552 4 NULL NULL NULL NULL 2756drop table t1,t2,t3,t4,t5,t6; 2757# 2758# MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins 2759# 2760create table t1(a int); 2761insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2762create table t2(a int); 2763insert into t2 values (0),(1); 2764create table t3 (a int, b int, key(a)); 2765insert into t3 select A.a + B.a* 10 + C.a * 100, 12345 from t1 A, t1 B, t1 C; 2766# Uses range for table t3: 2767explain select * from t1 left join t3 on t1.a=t3.b and t3.a<5; 2768id select_type table type possible_keys key key_len ref rows Extra 27691 SIMPLE t1 ALL NULL NULL NULL NULL 10 27701 SIMPLE t3 range a a 5 NULL 5 Using where 2771# This must use range for table t3, too: 2772explain select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; 2773id select_type table type possible_keys key key_len ref rows Extra 27741 SIMPLE t1 ALL NULL NULL NULL NULL 10 27751 SIMPLE t2 ALL NULL NULL NULL NULL 2 27761 SIMPLE t3 range a a 5 NULL 5 Using where 2777# 2778# .. part 2: make sure condition selectivity can use the condition too. 2779# 2780alter table t3 drop key a; 2781set @tmp1=@@optimizer_use_condition_selectivity; 2782set @tmp2=@@use_stat_tables; 2783set @tmp3=@@histogram_size; 2784set use_stat_tables=preferably; 2785set optimizer_use_condition_selectivity=4; 2786set histogram_size=100; 2787analyze table t3 persistent for all; 2788Table Op Msg_type Msg_text 2789test.t3 analyze status Engine-independent statistics collected 2790test.t3 analyze status OK 2791# t3.filtered is less than 100%: 2792explain extended select * from t1 left join t3 on t1.a=t3.b and t3.a<5; 2793id select_type table type possible_keys key key_len ref rows filtered Extra 27941 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 27951 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where 2796Warnings: 2797Note 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 2798# t3.filtered must less than 100%, too: 2799explain extended select * from t1 left join (t3 join t2) on t1.a=t3.b and t3.a<5; 2800id select_type table type possible_keys key key_len ref rows filtered Extra 28011 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 28021 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 28031 SIMPLE t3 ALL NULL NULL NULL NULL 1000 0.99 Using where 2804Warnings: 2805Note 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 2806drop table t1,t2,t3; 2807set optimizer_use_condition_selectivity= @tmp1; 2808set use_stat_tables= @tmp2; 2809set histogram_size= @tmp3; 2810# Another test 2811CREATE TABLE t1 (i1 int) ; 2812CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; 2813CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; 2814INSERT INTO t3 VALUES (2, NULL); 2815CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; 2816CREATE VIEW v4 AS SELECT * FROM t4; 2817SELECT 1 2818FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 2819LEFT JOIN v4 2820RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk 2821WHERE t3.pk IN (2); 28221 2823drop view v4; 2824drop table t1,t2,t3,t4; 2825# end of 10.3 tests 2826SET optimizer_switch=@org_optimizer_switch; 2827