drop table if exists t0,t1,t2,t3,t4,t5;
CREATE TABLE t1 (
grp int(11) default NULL,
a bigint(20) unsigned default NULL,
c char(10) NOT NULL default ''
) ENGINE=MyISAM;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,'');
create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a));
insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7);
select t1.*,t2.* from t1 JOIN t2 where t1.a=t2.a;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
EXPLAIN FORMAT=tree select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
EXPLAIN
-> Sort: t1.grp, t1.a, t2.c
    -> Stream results  (cost=3.67 rows=7)
        -> Nested loop left join  (cost=3.67 rows=7)
            -> Table scan on t1  (cost=1.22 rows=7)
            -> Single-row index lookup on t2 using PRIMARY (a=t1.a)  (cost=0.264 rows=1)

select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) order by t1.grp,t1.a,t2.c;
grp	a	c	id	a	c	d
NULL	NULL		NULL	NULL	NULL	NULL
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from { oj t2 left outer join t1 on (t1.a=t2.a) };
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL	NULL	4	7	D	7
select t1.*,t2.* from t1 as t0,{ oj t2 left outer join t1 on (t1.a=t2.a) } WHERE t0.a=2;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL	NULL	4	7	D	7
select t1.*,t2.* from t1 left join t2 using (a);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from t1 left join t2 using (a,c);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	NULL	NULL	NULL	NULL
3	5	C	NULL	NULL	NULL	NULL
3	6	D	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 left join t2 using (c);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
1	1	a	3	4	A	4
2	2	b	3	5	B	5
2	3	c	3	6	C	6
3	4	E	NULL	NULL	NULL	NULL
3	5	C	3	6	C	6
3	6	D	4	7	D	7
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 natural left outer join t2;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
3	4	E	NULL	NULL	NULL	NULL
3	5	C	NULL	NULL	NULL	NULL
3	6	D	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id=3;
grp	a	c	id	a	c	d
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from t1 left join t2 on (t1.a=t2.a) where t2.id is null;
grp	a	c	id	a	c	d
2	2	b	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL
NULL	NULL		NULL	NULL	NULL	NULL
explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`grp` AS `grp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` join `test`.`t2` where false
explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	7	100.00	NULL
1	SIMPLE	t2	NULL	eq_ref	PRIMARY	PRIMARY	8	test.t1.a	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`grp` AS `grp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where ((`test`.`t2`.`a` is null) = 1)
select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
grp	a	c	id	a	c	d	a
1	1	a	1	1	a	1	1
2	2	b	NULL	NULL	NULL	NULL	NULL
2	3	c	NULL	NULL	NULL	NULL	NULL
3	4	E	3	4	A	4	4
3	5	C	3	5	B	5	5
3	6	D	3	6	C	6	6
NULL	NULL		NULL	NULL	NULL	NULL	NULL
explain 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);
ERROR 42S22: Unknown column 't3.a' in 'on clause'
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);
ERROR 42S22: Unknown column 't3.a' in 'on clause'
select t1.*,t2.*,t3.a from t1 left join t2 on (t3.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
ERROR 42S22: Unknown column 't3.a' in 'on clause'
select t1.*,t2.* from t1 inner join t2 using (a);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from t1 inner join t2 on (t1.a=t2.a);
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
3	4	E	3	4	A	4
3	5	C	3	5	B	5
3	6	D	3	6	C	6
select t1.*,t2.* from t1 natural join t2;
grp	a	c	id	a	c	d
1	1	a	1	1	a	1
drop table t1,t2;
CREATE TABLE t1 (
usr_id INT unsigned NOT NULL,
uniq_id INT unsigned NOT NULL AUTO_INCREMENT,
start_num INT unsigned NOT NULL DEFAULT 1,
increment INT unsigned NOT NULL DEFAULT 1,
PRIMARY KEY (uniq_id),
INDEX usr_uniq_idx (usr_id, uniq_id),
INDEX uniq_usr_idx (uniq_id, usr_id)
);
CREATE TABLE t2 (
id INT unsigned NOT NULL DEFAULT 0,
usr2_id INT unsigned NOT NULL DEFAULT 0,
max INT unsigned NOT NULL DEFAULT 0,
c_amount INT unsigned NOT NULL DEFAULT 0,
d_max INT unsigned NOT NULL DEFAULT 0,
d_num INT unsigned NOT NULL DEFAULT 0,
orig_time INT unsigned NOT NULL DEFAULT 0,
c_time INT unsigned NOT NULL DEFAULT 0,
active ENUM ("no","yes") NOT NULL,
PRIMARY KEY (id,usr2_id),
INDEX id_idx (id),
INDEX usr2_idx (usr2_id)
);
INSERT 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);
SELECT 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;
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
SELECT t1.usr_id,t1.uniq_id,t1.increment,
t2.usr2_id,t2.c_amount,t2.max
FROM t2
RIGHT JOIN t1 ON t2.id = t1.uniq_id
WHERE t1.uniq_id = 4
ORDER BY t2.c_amount;
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
INSERT INTO t2 VALUES (2,3,3000,6000,0,0,746584,837484,'yes');
ERROR 23000: Duplicate entry '2-3' for key 't2.PRIMARY'
INSERT INTO t2 VALUES (7,3,1000,2000,0,0,746294,937484,'yes');
SELECT 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;
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
SELECT 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;
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
SELECT 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;
usr_id	uniq_id	increment	usr2_id	c_amount	max
3	4	84676	NULL	NULL	NULL
drop table t1,t2;
CREATE TABLE t1 (
cod_asig int(11) DEFAULT '0' NOT NULL,
desc_larga_cat varchar(80) DEFAULT '' NOT NULL,
desc_larga_cas varchar(80) DEFAULT '' NOT NULL,
desc_corta_cat varchar(40) DEFAULT '' NOT NULL,
desc_corta_cas varchar(40) DEFAULT '' NOT NULL,
cred_total double(3,1) DEFAULT '0.0' NOT NULL,
pre_requisit int(11),
co_requisit int(11),
preco_requisit int(11),
PRIMARY KEY (cod_asig)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (10360,'asdfggfg','Introduccion a los  Ordenadores I','asdfggfg','Introduccio Ordinadors I',6.0,NULL,NULL,NULL);
INSERT 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);
INSERT INTO t1 VALUES (10362,'Laboratori d`Ordinadors','Laboratorio de Ordenadores','Laboratori d`Ordinadors','Laboratori Ordinadors',4.5,NULL,NULL,NULL);
INSERT 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);
INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL);
INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL);
CREATE TABLE t2 (
idAssignatura int(11) DEFAULT '0' NOT NULL,
Grup int(11) DEFAULT '0' NOT NULL,
Places smallint(6) DEFAULT '0' NOT NULL,
PlacesOcupades int(11) DEFAULT '0',
PRIMARY KEY (idAssignatura,Grup)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (10360,12,333,0);
INSERT INTO t2 VALUES (10361,30,2,0);
INSERT INTO t2 VALUES (10361,40,3,0);
INSERT INTO t2 VALUES (10360,45,10,0);
INSERT INTO t2 VALUES (10362,10,12,0);
INSERT INTO t2 VALUES (10360,55,2,0);
INSERT INTO t2 VALUES (10360,70,0,0);
INSERT INTO t2 VALUES (10360,565656,0,0);
INSERT INTO t2 VALUES (10360,32767,7,0);
INSERT INTO t2 VALUES (10360,33,8,0);
INSERT INTO t2 VALUES (10360,7887,85,0);
INSERT INTO t2 VALUES (11405,88,8,0);
INSERT INTO t2 VALUES (10360,0,55,0);
INSERT INTO t2 VALUES (10360,99,0,0);
INSERT INTO t2 VALUES (11411,30,10,0);
INSERT INTO t2 VALUES (11404,0,0,0);
INSERT INTO t2 VALUES (10362,11,111,0);
INSERT INTO t2 VALUES (10363,33,333,0);
INSERT INTO t2 VALUES (11412,55,0,0);
INSERT INTO t2 VALUES (50003,66,6,0);
INSERT INTO t2 VALUES (11403,5,0,0);
INSERT INTO t2 VALUES (11406,11,11,0);
INSERT INTO t2 VALUES (11410,11410,131,0);
INSERT INTO t2 VALUES (11416,11416,32767,0);
INSERT INTO t2 VALUES (11409,0,0,0);
CREATE TABLE t3 (
id int(11) NOT NULL auto_increment,
dni_pasaporte char(16) DEFAULT '' NOT NULL,
idPla int(11) DEFAULT '0' NOT NULL,
cod_asig int(11) DEFAULT '0' NOT NULL,
any smallint(6) DEFAULT '0' NOT NULL,
quatrimestre smallint(6) DEFAULT '0' NOT NULL,
estat char(1) DEFAULT 'M' NOT NULL,
PRIMARY KEY (id),
UNIQUE dni_pasaporte (dni_pasaporte,idPla),
UNIQUE dni_pasaporte_2 (dni_pasaporte,idPla,cod_asig,any,quatrimestre)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t3 VALUES (1,'11111111',1,10362,98,1,'M');
CREATE TABLE t4 (
id int(11) NOT NULL auto_increment,
papa int(11) DEFAULT '0' NOT NULL,
fill int(11) DEFAULT '0' NOT NULL,
idPla int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY papa (idPla,papa),
UNIQUE papa_2 (idPla,papa,fill)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t4 VALUES (1,-1,10360,1);
INSERT INTO t4 VALUES (2,-1,10361,1);
INSERT INTO t4 VALUES (3,-1,10362,1);
SELECT 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;
fill	desc_larga_cat	cred_total	Grup	Places	PlacesOcupades
10360	asdfggfg	6.0	0	55	0
10360	asdfggfg	6.0	12	333	0
10360	asdfggfg	6.0	33	8	0
10360	asdfggfg	6.0	45	10	0
10360	asdfggfg	6.0	55	2	0
10360	asdfggfg	6.0	7887	85	0
10360	asdfggfg	6.0	32767	7	0
10361	Components i Circuits Electronics I	6.0	30	2	0
10361	Components i Circuits Electronics I	6.0	40	3	0
10362	Laboratori d`Ordinadors	4.5	10	12	0
10362	Laboratori d`Ordinadors	4.5	11	111	0
SELECT 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 ;
fill	idPla
10360	NULL
10361	NULL
10362	NULL
INSERT INTO t3 VALUES (3,'1234',1,10360,98,1,'S');
SELECT 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 ;
fill	idPla
10360	1
10361	NULL
10362	NULL
drop table t1,t2,t3,test.t4;
CREATE TABLE t1 (
id smallint(5) unsigned NOT NULL auto_increment,
name char(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1,'Antonio Paz');
INSERT INTO t1 VALUES (2,'Lilliana Angelovska');
INSERT INTO t1 VALUES (3,'Thimble Smith');
CREATE TABLE t2 (
id smallint(5) unsigned NOT NULL auto_increment,
owner smallint(5) unsigned DEFAULT '0' NOT NULL,
name char(60),
PRIMARY KEY (id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (1,1,'El Gato');
INSERT INTO t2 VALUES (2,1,'Perrito');
INSERT INTO t2 VALUES (3,3,'Happy');
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner);
name	name	id
Antonio Paz	El Gato	1
Antonio Paz	Perrito	2
Lilliana Angelovska	NULL	NULL
Thimble Smith	Happy	3
select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
name	name	id
Lilliana Angelovska	NULL	NULL
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; Not exists; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`name` AS `name`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`id` AS `id` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`owner` = `test`.`t1`.`id`)) where (`test`.`t2`.`id` is null)
explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`name` AS `name`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`id` AS `id` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`owner` = `test`.`t1`.`id`)) where (`test`.`t2`.`name` is null)
select count(*) from t1 left join t2 on (t1.id = t2.owner);
count(*)
4
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner);
name	name	id
Antonio Paz	El Gato	1
Antonio Paz	Perrito	2
Lilliana Angelovska	NULL	NULL
Thimble Smith	Happy	3
select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
name	name	id
Lilliana Angelovska	NULL	NULL
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; Not exists; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`name` AS `name`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`id` AS `id` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`owner` = `test`.`t1`.`id`)) where (`test`.`t2`.`id` is null)
explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	3	33.33	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`name` AS `name`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`id` AS `id` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`owner` = `test`.`t1`.`id`)) where (`test`.`t2`.`name` is null)
select count(*) from t2 right join t1 on (t1.id = t2.owner);
count(*)
4
select 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;
name	name	id	id
Antonio Paz	El Gato	1	1
Antonio Paz	Perrito	2	1
Lilliana Angelovska	NULL	NULL	NULL
Thimble Smith	Happy	3	3
select 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;
name	name	id	id
Antonio Paz	El Gato	1	1
Antonio Paz	Perrito	2	1
NULL	NULL	NULL	2
Thimble Smith	Happy	3	3
explain format=tree select 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;
EXPLAIN
-> Nested loop left join  (cost=2.2 rows=9)
    -> Index scan on t3 using PRIMARY  (cost=0.55 rows=3)
    -> Nested loop inner join  (cost=0.834 rows=3)
        -> Single-row index lookup on t1 using PRIMARY (id=t3.id)  (cost=0.283 rows=1)
        -> Filter: (t2.`owner` = t3.id)  (cost=0.184 rows=3)
            -> Table scan on t2  (cost=0.184 rows=3)

select 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;
name	name	id	owner	id
Antonio Paz	El Gato	1	1	1
Antonio Paz	Perrito	2	1	1
NULL	NULL	NULL	NULL	2
Thimble Smith	Happy	3	3	3
drop table t1,t2;
create table t1 (id int not null, str char(10), index(str));
insert into t1 values (1, null), (2, null), (3, "foo"), (4, "bar");
select * from t1 where str is not null order by id;
id	str
3	foo
4	bar
select * from t1 where str is null;
id	str
1	NULL
2	NULL
explain select * from t1 where str is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	str	str	41	const	2	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1` where (`test`.`t1`.`str` is null)
explain format=tree select * from t1 where str is null;
EXPLAIN
-> Index lookup on t1 using str (str=NULL), with index condition: (t1.str is null)  (cost=0.7 rows=2)

drop table t1;
CREATE TABLE t1 (
t1_id bigint(21) NOT NULL auto_increment,
PRIMARY KEY (t1_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2 (
t2_id bigint(21) NOT NULL auto_increment,
PRIMARY KEY (t2_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t3 (
t3_id bigint(21) NOT NULL auto_increment,
PRIMARY KEY (t3_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t4 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
KEY seq_0_id (seq_0_id),
KEY seq_1_id (seq_1_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t5 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
KEY seq_1_id (seq_1_id),
KEY seq_0_id (seq_0_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 values (1);
insert into t2 values (1);
insert into t3 values (1);
insert into t4 values (1,1);
insert into t5 values (1,1);
explain 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;
ERROR 42S22: Unknown column 't2.t2_id' in 'on clause'
drop table t1,t2,t3,t4,t5;
create table t1 (n int, m int, o int, key(n));
create table t2 (n int not null, m int, o int, primary key(n));
insert into t1 values (1, 2, 11), (1, 2, 7), (2, 2, 8), (1,2,9),(1,3,9);
insert into t2 values (1, 2, 3),(2, 2, 8), (4,3,9),(3,2,10);
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
t1.m = t2.m where t1.n = 1;
n	m	o	n	m	o
1	2	11	1	2	3
1	2	7	1	2	3
1	2	9	1	2	3
1	3	9	NULL	NULL	NULL
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
t1.m = t2.m where t1.n = 1 order by t1.o,t1.m;
n	m	o	n	m	o
1	2	7	1	2	3
1	2	9	1	2	3
1	3	9	NULL	NULL	NULL
1	2	11	1	2	3
drop table t1,t2;
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
INSERT INTO t1 VALUES (1,'a',1);
INSERT INTO t1 VALUES (2,'b',1);
INSERT INTO t1 VALUES (3,'c',2);
CREATE TABLE t2 (id2 INT NOT NULL PRIMARY KEY, dat2 CHAR(1));
INSERT INTO t2 VALUES (1,'x');
INSERT INTO t2 VALUES (2,'y');
INSERT INTO t2 VALUES (3,'z');
SELECT t2.id2 FROM t2 LEFT OUTER JOIN t1 ON t1.id2 = t2.id2 WHERE id1 IS NULL;
id2
3
SELECT t2.id2 FROM t2 NATURAL LEFT OUTER JOIN t1 WHERE id1 IS NULL;
id2
3
drop table t1,t2;
create table t1 ( color varchar(20), name varchar(20) );
insert into t1 values ( 'red', 'apple' );
insert into t1 values ( 'yellow', 'banana' );
insert into t1 values ( 'green', 'lime' );
insert into t1 values ( 'black', 'grape' );
insert into t1 values ( 'blue', 'blueberry' );
create table t2 ( count int, color varchar(20) );
insert into t2 values (10, 'green');
insert into t2 values (5, 'black');
insert into t2 values (15, 'white');
insert into t2 values (7, 'green');
select * from t1;
color	name
red	apple
yellow	banana
green	lime
black	grape
blue	blueberry
select * from t2;
count	color
10	green
5	black
15	white
7	green
select * from t2 natural join t1;
color	count	name
black	5	grape
green	10	lime
green	7	lime
select t2.count, t1.name from t2 natural join t1;
count	name
10	lime
5	grape
7	lime
select t2.count, t1.name from t2 inner join t1 using (color);
count	name
10	lime
5	grape
7	lime
drop table t1;
drop table t2;
CREATE TABLE t1 (
pcode varchar(8) DEFAULT '' NOT NULL
);
INSERT 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');
CREATE TABLE t2 (
pcode varchar(8) DEFAULT '' NOT NULL,
KEY pcode (pcode)
);
INSERT 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');
SELECT t1.pcode, IF(ISNULL(t2.pcode), 0, COUNT(*)) AS count FROM t1
LEFT JOIN t2 ON t1.pcode = t2.pcode GROUP BY t1.pcode;
pcode	count
kld2000	1
klw1000	0
klw1020	0
klw1500	0
klw2000	0
klw2001	0
klw2002	0
klw2500	0
kmw1000	0
kmw1500	0
kmw2000	0
kmw2001	0
kmw2100	0
kmw3000	0
kmw3200	0
kvw2000	26
kvw2001	0
kvw3000	36
kvw3001	0
kvw3002	0
kvw3500	26
kvw3501	0
kvw3502	0
kvw3800	0
kvw3801	0
kvw3802	0
kvw3900	0
kvw3901	0
kvw3902	0
kvw4000	0
kvw4001	0
kvw4002	0
kvw4200	0
kvw4500	0
kvw5000	0
kvw5001	0
kvw5500	0
kvw5510	0
kvw5600	0
kvw5601	0
kvw6000	2
SELECT 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;
pcode	count
kld2000	1
klw1000	0
klw1020	0
klw1500	0
klw2000	0
klw2001	0
klw2002	0
klw2500	0
kmw1000	0
kmw1500	0
kmw2000	0
kmw2001	0
kmw2100	0
kmw3000	0
kmw3200	0
kvw2000	26
kvw2001	0
kvw3000	36
kvw3001	0
kvw3002	0
kvw3500	26
kvw3501	0
kvw3502	0
kvw3800	0
kvw3801	0
kvw3802	0
kvw3900	0
kvw3901	0
kvw3902	0
kvw4000	0
kvw4001	0
kvw4002	0
kvw4200	0
kvw4500	0
kvw5000	0
kvw5001	0
kvw5500	0
kvw5510	0
kvw5600	0
kvw5601	0
kvw6000	2
drop table t1,t2;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (
id int(11),
pid int(11),
rep_del tinyint(4),
KEY id (id),
KEY pid (pid)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1,NULL,NULL);
INSERT INTO t1 VALUES (2,1,NULL);
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
create index rep_del ON t1(rep_del);
select * from t1 LEFT JOIN t1 t2 ON (t1.id=t2.pid) AND t2.rep_del IS NULL;
id	pid	rep_del	id	pid	rep_del
1	NULL	NULL	2	1	NULL
2	1	NULL	NULL	NULL	NULL
drop table t1;
CREATE TABLE t1 (
id int(11) DEFAULT '0' NOT NULL,
name tinytext DEFAULT '' NOT NULL,
UNIQUE id (id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1101	BLOB, TEXT, GEOMETRY or JSON column 'name' can't have a default value
INSERT INTO t1 VALUES (1,'yes'),(2,'no');
CREATE TABLE t2 (
id int(11) DEFAULT '0' NOT NULL,
idx int(11) DEFAULT '0' NOT NULL,
UNIQUE id (id,idx)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (1,1);
explain SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ref	id	id	4	test.t1.id	1	100.00	Using where; Not exists; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`idx` AS `idx` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`id` = `test`.`t1`.`id`)) where (`test`.`t2`.`id` is null)
SELECT * from t1 left join t2 on t1.id=t2.id where t2.id IS NULL;
id	name	id	idx
2	no	NULL	NULL
drop table t1,t2;
SET sql_mode = default;
create table t1 (bug_id mediumint, reporter mediumint);
create table t2 (bug_id mediumint, who mediumint, index(who));
insert into t2 values (1,1),(1,2);
insert into t1 values (1,1),(2,1);
SELECT * FROM t1 LEFT JOIN t2 ON (t1.bug_id =  t2.bug_id AND  t2.who = 2) WHERE  (t1.reporter = 2 OR t2.who = 2);
bug_id	reporter	bug_id	who
1	1	1	2
drop table t1,t2;
create table t1 (fooID smallint unsigned auto_increment, primary key (fooID));
create table t2 (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
insert into t1 (fooID) values (10),(20),(30);
insert into t2 values (10,1),(20,2),(30,3);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
explain select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	PRIMARY	4	NULL	3	100.00	Using index
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	2	const	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`fooID` AS `fooID`,`test`.`t2`.`barID` AS `barID`,`test`.`t1`.`fooID` AS `fooID` from `test`.`t2` left join `test`.`t1` on(((`test`.`t2`.`fooID` = 30) and (`test`.`t1`.`fooID` = 30))) where true
select * from t2 left join t1 on t1.fooID = t2.fooID and t1.fooID = 30;
fooID	barID	fooID
10	1	NULL
20	2	NULL
30	3	30
select * from t2 left join t1 ignore index(primary) on t1.fooID = t2.fooID and t1.fooID = 30;
fooID	barID	fooID
10	1	NULL
20	2	NULL
30	3	30
drop table t1,t2;
create table t1 (i int);
create table t2 (i int);
create table t3 (i int);
insert into t1 values(1),(2);
insert into t2 values(2),(3);
insert into t3 values(2),(4);
select * from t1 natural left join t2 natural left join t3;
i
1
2
select * from t1 natural left join t2 where (t2.i is not null)=0;
i
1
select * from t1 natural left join t2 where (t2.i is not null) is not null;
i
1
2
select * from t1 natural left join t2 where (i is not null)=0;
i
select * from t1 natural left join t2 where (i is not null) is not null;
i
1
2
drop table t1,t2,t3;
create table t1 (f1 integer,f2 integer,f3 integer);
create table t2 (f2 integer,f4 integer);
create table t3 (f3 integer,f5 integer);
select * from t1
left outer join t2 using (f2)
left outer join t3 using (f3);
f3	f2	f1	f4	f5
drop table t1,t2,t3;
create table t1 (a1 int, a2 int);
create table t2 (b1 int not null, b2 int);
create table t3 (c1 int, c2 int);
insert into t1 values (1,2), (2,2), (3,2);
insert into t2 values (1,3), (2,3);
insert into t3 values (2,4),        (3,4);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
a1	a2	b1	b2	c1	c2
1	2	1	3	NULL	NULL
2	2	2	3	NULL	NULL
3	2	NULL	NULL	3	4
explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 is null;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	3	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`b1` = `test`.`t1`.`a1`)) left join `test`.`t3` on(((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` is null))) where true
drop table t1, t2, t3;
create table t1 (
a int(11),
b char(10),
key (a)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 (a) values (1),(2),(3),(4);
create table t2 (a int);
select * from t1 left join t2 on t1.a=t2.a where not (t2.a <=> t1.a);
a	b	a
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a);
a	b	a
1	NULL	NULL
2	NULL	NULL
3	NULL	NULL
4	NULL	NULL
drop table t1,t2;
create table t1 (
match_id tinyint(3) unsigned not null auto_increment,
home tinyint(3) unsigned default '0',
unique key match_id (match_id),
key match_id_2 (match_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 values("1", "2");
create table t2 (
player_id tinyint(3) unsigned default '0',
match_1_h tinyint(3) unsigned default '0',
key player_id (player_id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t2 values("1", "5");
insert into t2 values("2", "9");
insert into t2 values("3", "3");
insert into t2 values("4", "7");
insert into t2 values("5", "6");
insert into t2 values("6", "8");
insert into t2 values("7", "4");
insert into t2 values("8", "12");
insert into t2 values("9", "11");
insert into t2 values("10", "10");
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by m.match_id desc;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using temporary; Using filesort
1	SIMPLE	m	NULL	const	match_id,match_id_2	match_id	1	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`s`.`player_id` AS `player_id`,`test`.`s`.`match_1_h` AS `match_1_h`,'*' AS `*`,`test`.`m`.`match_id` AS `match_id`,`test`.`m`.`home` AS `home`,(`test`.`s`.`match_1_h` - `test`.`m`.`home`) AS `UUX` from `test`.`t2` `s` left join `test`.`t1` `m` on((`test`.`m`.`match_id` = 1)) where true order by `test`.`m`.`match_id` desc
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by UUX desc;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using temporary; Using filesort
1	SIMPLE	m	NULL	const	match_id,match_id_2	match_id	1	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`s`.`player_id` AS `player_id`,`test`.`s`.`match_1_h` AS `match_1_h`,'*' AS `*`,`test`.`m`.`match_id` AS `match_id`,`test`.`m`.`home` AS `home`,(`test`.`s`.`match_1_h` - `test`.`m`.`home`) AS `UUX` from `test`.`t2` `s` left join `test`.`t1` `m` on((`test`.`m`.`match_id` = 1)) where true order by `UUX` desc
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
(t2 s left join t1 m on m.match_id = 1) 
order by UUX desc;
player_id	match_1_h	*	match_id	home	UUX
8	12	*	1	2	10
9	11	*	1	2	9
10	10	*	1	2	8
2	9	*	1	2	7
6	8	*	1	2	6
4	7	*	1	2	5
5	6	*	1	2	4
1	5	*	1	2	3
7	4	*	1	2	2
3	3	*	1	2	1
explain select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
t2 s straight_join t1 m where m.match_id = 1 
order by UUX desc;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using temporary; Using filesort
1	SIMPLE	m	NULL	const	match_id,match_id_2	match_id	1	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`s`.`player_id` AS `player_id`,`test`.`s`.`match_1_h` AS `match_1_h`,'*' AS `*`,`test`.`m`.`match_id` AS `match_id`,`test`.`m`.`home` AS `home`,(`test`.`s`.`match_1_h` - `test`.`m`.`home`) AS `UUX` from `test`.`t2` `s` straight_join `test`.`t1` `m` where (`test`.`m`.`match_id` = 1) order by `UUX` desc
select s.*, '*', m.*, (s.match_1_h - m.home) UUX from 
t2 s straight_join t1 m where m.match_id = 1 
order by UUX desc;
player_id	match_1_h	*	match_id	home	UUX
8	12	*	1	2	10
9	11	*	1	2	9
10	10	*	1	2	8
2	9	*	1	2	7
6	8	*	1	2	6
4	7	*	1	2	5
5	6	*	1	2	4
1	5	*	1	2	3
7	4	*	1	2	2
3	3	*	1	2	1
drop table t1, t2;
create table t1 (a int, b int, unique index idx (a, b));
create table t2 (a int, b int, c int, unique index idx (a, b));
insert into t1 values (1, 10), (1,11), (2,10), (2,11);
insert into t2 values (1,10,3);
select t1.a, t1.b, t2.c from t1 left join t2
on t1.a=t2.a and t1.b=t2.b and t2.c=3
where t1.a=1 and t2.c is null;
a	b	c
1	11	NULL
drop table t1, t2;
CREATE TABLE t1 (
ts_id bigint(20) default NULL,
inst_id tinyint(4) default NULL,
flag_name varchar(64) default NULL,
flag_value text,
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
CREATE TABLE t2 (
ts_id bigint(20) default NULL,
inst_id tinyint(4) default NULL,
flag_name varchar(64) default NULL,
flag_value text,
UNIQUE KEY ts_id (ts_id,inst_id,flag_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
INSERT INTO t1 VALUES
(111056548820001, 0, 'flag1', NULL),
(111056548820001, 0, 'flag2', NULL),
(2, 0, 'other_flag', NULL);
INSERT INTO t2 VALUES
(111056548820001, 3, 'flag1', 'sss');
SELECT t1.flag_name,t2.flag_value 
FROM t1 LEFT JOIN t2 
ON (t1.ts_id = t2.ts_id AND t1.flag_name = t2.flag_name AND
t2.inst_id = 3) 
WHERE t1.inst_id = 0 AND t1.ts_id=111056548820001 AND
t2.flag_value IS  NULL;
flag_name	flag_value
flag2	NULL
DROP TABLE t1,t2;
CREATE TABLE t1 (
id int(11) unsigned NOT NULL auto_increment,
text_id int(10) unsigned default NULL,
PRIMARY KEY  (id)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES("1", "0");
INSERT INTO t1 VALUES("2", "10");
CREATE TABLE t2 (
text_id char(3) NOT NULL default '',
language_id char(3) NOT NULL default '',
text_data text,
PRIMARY KEY  (text_id,language_id)
);
INSERT INTO t2 VALUES("0", "EN", "0-EN");
INSERT INTO t2 VALUES("0", "SV", "0-SV");
INSERT INTO t2 VALUES("10", "EN", "10-EN");
INSERT INTO t2 VALUES("10", "SV", "10-SV");
SELECT t1.id, t1.text_id, t2.text_data
FROM t1 LEFT JOIN t2
ON t1.text_id = t2.text_id
AND t2.language_id = 'SV'
  WHERE (t1.id LIKE '%' OR t2.text_data LIKE '%');
id	text_id	text_data
1	0	0-SV
2	10	10-SV
DROP TABLE t1, t2;
CREATE TABLE t0 (a0 int PRIMARY KEY);
CREATE TABLE t1 (a1 int PRIMARY KEY);
CREATE TABLE t2 (a2 int);
CREATE TABLE t3 (a3 int);
INSERT INTO t0 VALUES (1);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
a1	a2
1	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	PRIMARY	4	NULL	1	100.00	Using index
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2` from `test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a1` = 0)) where true
SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
a1	a2	a3
1	NULL	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	PRIMARY	4	NULL	1	100.00	Using index
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t3`.`a3` AS `a3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on((`test`.`t1`.`a1` = 0)) where true
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
a0	a1	a2	a3
1	1	NULL	NULL
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	index	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t0.a0	1	100.00	Using index
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`a0` AS `a0`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t3`.`a3` AS `a3` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t0`.`a0` = 0) and (`test`.`t1`.`a1` = 0))) where (`test`.`t1`.`a1` = `test`.`t0`.`a0`)
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
a0	a1	a2	a3
1	1	NULL	NULL
EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=5 WHERE a0=a1 AND a0=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `a0`,'1' AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t3`.`a3` AS `a3` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(false) where true
drop table t1,t2;
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2),(3,3);
create table t2 (a int, b int);
insert into t2 values (1,1), (2,2);
select * from t2 right join t1 on t2.a=t1.a;
a	b	a	b
1	1	1	1
2	2	2	2
NULL	NULL	3	3
select straight_join * from t2 right join t1 on t2.a=t1.a;
a	b	a	b
1	1	1	1
2	2	2	2
NULL	NULL	3	3
DROP TABLE t0,t1,t2,t3;
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
INSERT INTO t2 VALUES (1,2), (2,2);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
4	2	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE not(0+(t1.a=30 and t2.b=1));
a	b	a	b
1	1	1	2
2	1	2	2
3	1	NULL	NULL
4	2	NULL	NULL
DROP TABLE t1,t2;
set group_concat_max_len=5;
create table t1 (a int, b varchar(20));
create table t2 (a int, c varchar(20));
insert into t1 values (1,"aaaaaaaaaa"),(2,"bbbbbbbbbb");
insert into t2 values (1,"cccccccccc"),(2,"dddddddddd");
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by t1.a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
select group_concat(t1.b,t2.c) from t1 left join t2 using(a) group by a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by a;
group_concat(t1.b,t2.c)
aaaaa
bbbbb
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
drop table t1, t2;
set group_concat_max_len=default;
create 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));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
create 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));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
create 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));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert 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');
explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
and t1.gid =1 and t2.gid =1 and t3.set_id =1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	10	const,const,const	1	100.00	NULL
1	SIMPLE	t2	NULL	const	PRIMARY,id	PRIMARY	14	const,const,const,const	1	100.00	Using index
1	SIMPLE	t3	NULL	const	PRIMARY	PRIMARY	3	const,const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select 'd' AS `name` from `test`.`t1` join `test`.`t2` join `test`.`t3` where true
drop tables t1,t2,t3;
CREATE TABLE t1 (EMPNUM INT, GRP INT);
INSERT INTO t1 VALUES (0, 10);
INSERT INTO t1 VALUES (2, 30);
CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
INSERT INTO t2 VALUES (0, 'KERI');
INSERT INTO t2 VALUES (9, 'BARRY');
CREATE VIEW v1 AS
SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
SELECT * FROM v1;
EMPNUM	NAME	GRP
0	KERI	10
9	BARRY	NULL
SELECT * FROM v1 WHERE EMPNUM < 10;
EMPNUM	NAME	GRP
0	KERI	10
9	BARRY	NULL
DROP VIEW v1;
DROP TABLE t1,t2;
CREATE TABLE t1 (c11 int);
CREATE TABLE t2 (c21 int);
INSERT INTO t1 VALUES (30), (40), (50);
INSERT INTO t2 VALUES (300), (400), (500);
SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40;
c11	c21
40	NULL
DROP TABLE t1, t2;
CREATE TABLE t1 (a int PRIMARY KEY, b int);
CREATE TABLE t2 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10);
INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b;
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b;
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b);
a	b	a	b
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
8	7	NULL	NULL
SELECT * 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;
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
8	7	NULL	NULL
SELECT * 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);
a	b	a	b
2	1	NULL	NULL
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
8	7	NULL	NULL
SELECT * 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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
SELECT * 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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * 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);
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * 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;
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b));
a	b	a	b
3	2	3	0
4	3	4	1
6	5	6	4
7	8	7	5
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	100.00	Using where
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	100.00	Using where
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` in (`test`.`t2`.`a`,`test`.`t2`.`b`)))
EXPLAIN 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);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	PRIMARY	NULL	NULL	NULL	4	100.00	Using where
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t2`.`a` > if((`test`.`t2`.`a` = (`test`.`t2`.`b` - 2)),`test`.`t2`.`b`,(`test`.`t2`.`b` - 1))))
DROP TABLE t1,t2;
DROP VIEW IF EXISTS v1,v2;
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a int);
CREATE table t2 (b int);
INSERT INTO t1 VALUES (1), (2), (3), (4), (1), (1), (3);
INSERT INTO t2 VALUES (2), (3);
CREATE VIEW v1 AS SELECT a FROM t1 JOIN t2 ON t1.a=t2.b;
CREATE VIEW v2 AS SELECT b FROM t2 JOIN t1 ON t2.b=t1.a;
SELECT v1.a, v2. b 
FROM v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3)
GROUP BY v1.a;
a	b
2	NULL
3	3
SELECT v1.a, v2. b 
FROM { OJ v1 LEFT OUTER JOIN v2 ON (v1.a=v2.b) AND (v1.a >= 3) }
GROUP BY v1.a;
a	b
2	NULL
3	3
DROP VIEW v1,v2;
DROP TABLE t1,t2;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (2), (3);
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
a	b
1	NULL
2	2
3	3
4	NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
a	b
1	NULL
2	2
3	3
4	NULL
DROP TABLE t1,t2;
CREATE TABLE t1 (
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
f2 varchar(16) collate latin1_swedish_ci
);
CREATE TABLE t2 (
f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY,
f3 varchar(16) collate latin1_swedish_ci
);
INSERT INTO t1 VALUES ('bla','blah');
INSERT INTO t2 VALUES ('bla','sheep');
SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla';
f1	f2	f3
bla	blah	sheep
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
f1	f2	f3
bla	blah	sheep
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
f1	f2	f3
bla	blah	sheep
DROP TABLE t1,t2;
CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
INSERT INTO t1 VALUES
(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
INSERT INTO t2 VALUES
(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
1	SIMPLE	t2	NULL	ALL	idx	NULL	NULL	NULL	9	11.11	Using where; Not exists; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`id` = `test`.`t1`.`id`)) where (`test`.`t2`.`b` is null)
flush status;
SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
id	a
1	aaaaaaa
4	ddddddd
show status like 'Handler_read%';
Variable_name	Value
Handler_read_first	2
Handler_read_key	2
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	16
DROP TABLE t1,t2;
CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
INSERT INTO t1 VALUES (1,0), (2,1);
CREATE TABLE t2 (d int PRIMARY KEY);
INSERT INTO t2 VALUES (1), (2), (3);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL
1	SIMPLE	t2	NULL	index	NULL	PRIMARY	4	NULL	3	33.33	Using where; Not exists; Using index
Warnings:
Note	1003	/* select#1 */ select '1' AS `c`,'0' AS `e`,`test`.`t2`.`d` AS `d` from `test`.`t1` left join `test`.`t2` on(('0' <> 0)) where ((`test`.`t2`.`d` is null))
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
c	e	d
1	0	NULL
SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
c	e	d
1	0	NULL
DROP TABLE t1,t2;
#
# Bug#47650: using group by with rollup without indexes returns incorrect 
# results with where
#
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( a INT, b INT );
INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,rollup_sum_switcher(count(`test`.`t2`.`b`)) AS `COUNT( t2.b )`,rollup_sum_switcher(sum(`test`.`t2`.`b`)) AS `SUM( t2.b )`,rollup_sum_switcher(max(`test`.`t2`.`b`)) AS `MAX( t2.b )` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where true group by `test`.`t1`.`a` with rollup
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 LEFT JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
1	3	6	3
NULL	3	6	3
EXPLAIN
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,rollup_sum_switcher(count(`test`.`t2`.`b`)) AS `COUNT( t2.b )`,rollup_sum_switcher(sum(`test`.`t2`.`b`)) AS `SUM( t2.b )`,rollup_sum_switcher(max(`test`.`t2`.`b`)) AS `MAX( t2.b )` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`a`) group by `test`.`t1`.`a` with rollup
SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
FROM t1 JOIN t2 USING( a )
GROUP BY t1.a WITH ROLLUP;
a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
1	3	6	3
NULL	3	6	3
DROP TABLE t1, t2;
#
# Bug#51598 Inconsistent behaviour with a COALESCE statement inside an IN comparison
#
CREATE TABLE t1(f1 INT, f2 INT, f3 INT);
INSERT INTO t1 VALUES (1, NULL, 3);
CREATE TABLE t2(f1 INT, f2 INT);
INSERT INTO t2 VALUES (2, 1);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`f2` = `test`.`t1`.`f2`)) where ((coalesce(`test`.`t1`.`f1`,`test`.`t2`.`f1`),`test`.`t1`.`f3`) in ((1,3),(2,2)))
SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
f1	f2	f3	f1	f2
1	NULL	3	NULL	NULL
DROP TABLE t1, t2;
#
# Bug#52357: Assertion failed: join->best_read in greedy_search 
# optimizer_search_depth=0
#
CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1),(2);
SET optimizer_search_depth = 0;
# Should not core dump on query preparation
EXPLAIN
SELECT 1
FROM t1 tt3 LEFT  OUTER JOIN t1 tt4 ON 1
LEFT  OUTER JOIN t1 tt5 ON 1
LEFT  OUTER JOIN t1 tt6 ON 1
LEFT  OUTER JOIN t1 tt7 ON 1
LEFT  OUTER JOIN t1 tt8 ON 1
RIGHT OUTER JOIN t1 tt2 ON 1
RIGHT OUTER JOIN t1 tt1 ON 1
STRAIGHT_JOIN    t1 tt9 ON 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tt1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	tt2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt4	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt5	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt6	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt7	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt8	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	tt9	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select 1 AS `1` from `test`.`t1` `tt1` left join (`test`.`t1` `tt2` left join (`test`.`t1` `tt3` left join `test`.`t1` `tt4` on(true) left join `test`.`t1` `tt5` on(true) left join `test`.`t1` `tt6` on(true) left join `test`.`t1` `tt7` on(true) left join `test`.`t1` `tt8` on(true)) on(true)) on(true) straight_join `test`.`t1` `tt9` where true
SET optimizer_search_depth = DEFAULT;
DROP TABLE t1;
#
# Bug#46091 STRAIGHT_JOIN + RIGHT JOIN returns different result
#
CREATE TABLE t1 (f1 INT NOT NULL);
INSERT INTO t1 VALUES (9),(0);
CREATE TABLE t2 (f1 INT NOT NULL);
INSERT INTO t2 VALUES
(5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SELECT STRAIGHT_JOIN COUNT(*) FROM t1 ta1
RIGHT JOIN t2 ta2 JOIN t2 ta3 ON ta2.f1 ON ta3.f1;
COUNT(*)
476
EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 ta1
RIGHT JOIN t2 ta2 JOIN t2 ta3 ON ta2.f1 ON ta3.f1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ta2	NULL	ALL	NULL	NULL	NULL	NULL	20	90.00	Using where
1	SIMPLE	ta3	NULL	ALL	NULL	NULL	NULL	NULL	20	100.00	Using join buffer (hash join)
1	SIMPLE	ta1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select straight_join count(0) AS `COUNT(*)` from `test`.`t2` `ta2` join `test`.`t2` `ta3` left join `test`.`t1` `ta1` on((0 <> `test`.`ta3`.`f1`)) where (0 <> `test`.`ta2`.`f1`)
DROP TABLE t1, t2;
#
# Bug#48971 Segfault in add_found_match_trig_cond () at sql_select.cc:5990
#
CREATE TABLE t1(f1 INT, PRIMARY KEY (f1));
INSERT INTO t1 VALUES (1),(2);
EXPLAIN SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
LEFT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
JOIN t1 AS jt4 ON 1
LEFT JOIN t1 AS jt5 ON 1
ON 1
RIGHT JOIN t1 AS jt6 ON jt6.f1
ON 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	jt1	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt6	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt3	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt4	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using index; Using join buffer (hash join)
1	SIMPLE	jt5	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt2	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ 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(true) left join `test`.`t1` `jt2` on(true)) on(((0 <> `test`.`jt6`.`f1`)))) on(true) where true
EXPLAIN SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
RIGHT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
JOIN t1 AS jt4 ON 1
LEFT JOIN t1 AS jt5 ON 1
ON 1
RIGHT JOIN t1 AS jt6 ON jt6.f1
ON 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	jt6	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using index
1	SIMPLE	jt3	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt4	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using index; Using join buffer (hash join)
1	SIMPLE	jt5	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt2	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
1	SIMPLE	jt1	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ 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(true) left join `test`.`t1` `jt2` on(true)) on(((0 <> `test`.`jt6`.`f1`))) left join `test`.`t1` `jt1` on(true) where true
DROP TABLE t1;
#
# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
#
CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1));
CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2));
INSERT INTO t1 VALUES (4);
INSERT INTO t2 VALUES (3, 3);
INSERT INTO t2 VALUES (7, 7);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4
GROUP BY t2.f1, t2.f2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index; Using temporary
1	SIMPLE	t2	NULL	ref	PRIMARY	PRIMARY	4	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '4' AS `f1`,`test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`f1` = 4)) where true group by `test`.`t2`.`f1`,`test`.`t2`.`f2`
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4
GROUP BY t2.f1, t2.f2;
f1	f1	f2
4	NULL	NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
GROUP BY t2.f1, t2.f2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	t2	NULL	ref	PRIMARY	PRIMARY	4	const	1	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select '4' AS `f1`,`test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f1` = 4) and (`test`.`t2`.`f2` is not null)) group by `test`.`t2`.`f1`,`test`.`t2`.`f2`
SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
GROUP BY t2.f1, t2.f2;
f1	f1	f2
DROP TABLE t1,t2;
#
# Bug#57034 incorrect OUTER JOIN result when joined on unique key
#
CREATE TABLE t1 (pk INT PRIMARY KEY, 
col_int INT, 
col_int_unique INT UNIQUE KEY);
INSERT INTO t1 VALUES (1,NULL,2), (2,0,0);
CREATE TABLE t2 (pk INT PRIMARY KEY,
col_int INT,
col_int_unique INT UNIQUE KEY);
INSERT INTO t2 VALUES (1,0,1), (2,0,2);
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2
ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
WHERE t1.pk=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL
1	SIMPLE	t2	NULL	const	col_int_unique	col_int_unique	5	const	1	100.00	Impossible ON condition
Warnings:
Note	1003	/* select#1 */ select '1' AS `pk`,NULL AS `col_int`,'2' AS `col_int_unique`,NULL AS `pk`,NULL AS `col_int`,NULL AS `col_int_unique` from `test`.`t1` left join `test`.`t2` on((multiple equal('2', NULL) and multiple equal(NULL, NULL))) where true
SELECT * FROM t1 LEFT JOIN t2
ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
WHERE t1.pk=1;
pk	col_int	col_int_unique	pk	col_int	col_int_unique
1	NULL	2	NULL	NULL	NULL
DROP TABLE t1,t2;
#
# Bug#48046 Server incorrectly processing JOINs on NULL values
#
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`time_key` time DEFAULT NULL,
`varchar_key` varchar(1) DEFAULT NULL,
`varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `time_key` (`time_key`),
KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL);
SELECT table1.time_key AS field1, table2.pk 
FROM BB table1  LEFT JOIN BB table2 
ON table2.varchar_nokey = table1.varchar_key
HAVING field1;
field1	pk
18:27:58	NULL
DROP TABLE BB;
#
# Bug#49600 Server incorrectly processing RIGHT JOIN with 
#           constant WHERE clause and no index
#
CREATE TABLE `BB` (
`col_datetime_key` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL);
SELECT table1.col_datetime_key  
FROM BB table1 RIGHT JOIN BB table2 
ON table2 .col_varchar_nokey = table1.col_varchar_key
WHERE 7;
col_datetime_key
NULL
ALTER TABLE BB DISABLE KEYS;
SELECT table1.col_datetime_key  
FROM BB table1 RIGHT JOIN BB table2
ON table2 .col_varchar_nokey = table1.col_varchar_key
WHERE 7;
col_datetime_key
NULL
DROP TABLE BB;
#
# Bug#58490: Incorrect result in multi level OUTER JOIN
# in combination with IS NULL
#
CREATE TABLE t1 (i INT NOT NULL);
INSERT INTO t1 VALUES (0),    (2),(3),(4);
CREATE TABLE t2 (i INT NOT NULL);
INSERT INTO t2 VALUES (0),(1),    (3),(4);
CREATE TABLE t3 (i INT NOT NULL);
INSERT INTO t3 VALUES (0),(1),(2),    (4);
CREATE TABLE t4 (i INT NOT NULL);
INSERT INTO t4 VALUES (0),(1),(2),(3)   ;
SELECT * FROM
t1 LEFT JOIN
( t2 LEFT JOIN
( t3 LEFT JOIN
t4
ON t4.i = t3.i
)
ON t3.i = t2.i
)
ON t2.i = t1.i
;
i	i	i	i
0	0	0	0
2	NULL	NULL	NULL
3	3	NULL	NULL
4	4	4	NULL
SELECT * FROM
t1 LEFT JOIN
( t2 LEFT JOIN
( t3 LEFT JOIN
t4
ON t4.i = t3.i
)
ON t3.i = t2.i
)
ON t2.i = t1.i
WHERE t4.i IS NULL;
i	i	i	i
2	NULL	NULL	NULL
3	3	NULL	NULL
4	4	4	NULL
SELECT * FROM
t1 LEFT JOIN
( ( t2 LEFT JOIN
t3
ON t3.i = t2.i
)
)
ON t2.i = t1.i
WHERE t3.i IS NULL;
i	i	i
2	NULL	NULL
3	3	NULL
SELECT * FROM
t1 LEFT JOIN
( ( t2 LEFT JOIN
t3
ON t3.i = t2.i
)
JOIN t4
ON t4.i=t2.i
)
ON t2.i = t1.i
WHERE t3.i IS NULL;
i	i	i	i
2	NULL	NULL	NULL
3	3	NULL	3
4	NULL	NULL	NULL
SELECT * FROM
t1 LEFT JOIN
( ( t2 LEFT JOIN
t3
ON t3.i = t2.i
)
JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i)
ON t4a.i=t2.i
)
ON t2.i = t1.i
WHERE t3.i IS NULL;
i	i	i	i	i
2	NULL	NULL	NULL	NULL
3	3	NULL	3	3
4	NULL	NULL	NULL	NULL
SELECT * FROM
t1 LEFT JOIN
( ( t2 LEFT JOIN
t3
ON t3.i = t2.i
)
JOIN (t4 AS t4a, t4 AS t4b)
ON t4a.i=t2.i
)
ON t2.i = t1.i
WHERE t3.i IS NULL;
i	i	i	i	i
2	NULL	NULL	NULL	NULL
3	3	NULL	3	0
3	3	NULL	3	1
3	3	NULL	3	2
3	3	NULL	3	3
4	NULL	NULL	NULL	NULL
DROP TABLE t1,t2,t3,t4;
#
# Bug#49322(Duplicate): Server is adding extra NULL row
# on processing a WHERE clause
#
CREATE TABLE h (pk INT NOT NULL, col_int_key INT);
INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8);
CREATE TABLE m (pk INT NOT NULL, col_int_key INT);
INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9);
CREATE TABLE k (pk INT NOT NULL, col_int_key INT);
INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5);
SELECT TABLE1.pk FROM k TABLE1
RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key;
pk
2
2
4
4
NULL
NULL
NULL
NULL
NULL
NULL
NULL
SELECT TABLE1.pk FROM k TABLE1
RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key
RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key
WHERE TABLE1.pk IS NULL;
pk
NULL
NULL
NULL
NULL
NULL
NULL
NULL
DROP TABLE h,m,k;
#
# Bug #11765810	58813: SERVER THREAD HANGS WHEN JOIN + WHERE + GROUP BY
# IS EXECUTED TWICE FROM P
#
CREATE TABLE t1 ( a INT ) ENGINE = MYISAM;
INSERT INTO t1 VALUES (1);
PREPARE prep_stmt FROM '
 SELECT 1 AS f FROM t1
 LEFT JOIN t1 t2
  RIGHT JOIN t1 t3
    JOIN t1 t4
   ON 1
  ON 1
 ON 1
 GROUP BY f';
EXECUTE prep_stmt;
f
1
EXECUTE prep_stmt;
f
1
DROP TABLE t1;
End of 5.1 tests
#
# Bug#54235 Extra rows with join_cache_level=4,6,8 and two LEFT JOIN
#
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);
INSERT INTO t1 VALUES (null),(null);
explain SELECT t1.a FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a)
ON 0 WHERE t1.a OR t3.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on((0 <> `test`.`t2`.`a`))) on(false) where ((0 <> `test`.`t1`.`a`) or (0 <> `test`.`t3`.`a`))
SELECT t1.a FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a)
ON 0 WHERE t1.a OR t3.a;
a
explain SELECT t1.a FROM t1 LEFT JOIN
(t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a)
ON 0 WHERE t1.a OR t4.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` left join `test`.`t4` on(true)) on((0 <> `test`.`t2`.`a`))) on(false) where ((0 <> `test`.`t1`.`a`) or (0 <> `test`.`t4`.`a`))
SELECT t1.a FROM t1 LEFT JOIN
(t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a)
ON 0 WHERE t1.a OR t4.a;
a
DROP TABLE t1,t2,t3,t4;
#
# Bug#56254 Assertion tab->ref.use_count fails in
# join_read_key_unlock_row() on 4-way JOIN
#
CREATE TABLE t1 (
pk INT NOT NULL,
col_int_key INT,
col_int INT,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
);
INSERT INTO t1 VALUES (6, -448724992, NULL);
CREATE TABLE t2 (
col_int INT,
col_varchar_10 VARCHAR(10)
);
INSERT INTO t2 VALUES (6,'afasdkiyum');
CREATE TABLE t3 (
col_varchar_10 VARCHAR(10),
col_int INT
);
CREATE TABLE t4 (
pk INT NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t4 VALUES (1);
INSERT INTO t4 VALUES (2);
SELECT t1.col_int
FROM t1
LEFT JOIN t2
LEFT JOIN t3
JOIN t4
ON t3.col_int  = t4.pk
ON t2.col_varchar_10 = t3.col_varchar_10
ON t2.col_int = t1.pk
WHERE   t1.col_int_key IS NULL OR t4.pk < t3.col_int;
col_int
EXPLAIN FORMAT=tree SELECT t1.col_int
FROM t1
LEFT JOIN t2
LEFT JOIN t3
JOIN t4
ON t3.col_int  = t4.pk
ON t2.col_varchar_10 = t3.col_varchar_10
ON t2.col_int = t1.pk
WHERE   t1.col_int_key IS NULL OR t4.pk < t3.col_int;
EXPLAIN
-> Filter: <if>(found_match(t3..t4), ((t1.col_int_key is null) or (t4.pk < t3.col_int)), true)  (cost=0.7 rows=1)
    -> Nested loop left join  (cost=0.7 rows=1)
        -> Table scan on t1  (cost=0.35 rows=1)
        -> Nested loop left join  (cost=0.7 rows=1)
            -> Filter: (t2.col_int = t1.pk)  (cost=0.35 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
            -> Nested loop inner join  (cost=0.7 rows=1)
                -> Filter: (t3.col_varchar_10 = t2.col_varchar_10)  (cost=0.35 rows=1)
                    -> Table scan on t3  (cost=0.35 rows=1)
                -> Single-row covering index lookup on t4 using PRIMARY (pk=t3.col_int)  (cost=0.35 rows=1)

DROP TABLE t1,t2,t3,t4;

# BUG#12567331 - INFINITE LOOP WHEN RESOLVING AN ALIASED COLUMN
# USED IN GROUP BY

CREATE TABLE t1 (pk int(11));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
PREPARE prep_stmt_9846 FROM '
SELECT alias1.pk AS field1 FROM
t1 AS alias1
LEFT JOIN
( 
  t1 AS alias2
  RIGHT  JOIN
  ( 
    t1 AS alias3
    JOIN t1 AS alias4
    ON 1
  )
  ON 1
)
ON 1
GROUP BY field1';
execute prep_stmt_9846;
field1
execute prep_stmt_9846;
field1
deallocate prepare prep_stmt_9846;
drop table t1;
#
# Bug#13040136 - ASSERT IN PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG()
#
CREATE TABLE t1 (
col_varchar_10 VARCHAR(10),
col_int_key INTEGER,
col_varchar_10_key VARCHAR(10),
pk INTEGER NOT NULL,
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_10_key)
);
INSERT INTO t1 VALUES ('q',NULL,'o',1);
CREATE TABLE t2 (
pk INTEGER NOT NULL AUTO_INCREMENT,
col_varchar_10_key VARCHAR(10),
col_int_key INTEGER,
col_varchar_10 VARCHAR(10),
PRIMARY KEY (pk),
KEY (col_varchar_10_key),
KEY col_int_key (col_int_key)
);
INSERT INTO t2 VALUES
(1,'r',NULL,'would'),(2,'tell',-655032320,'t'),
(3,'d',9,'a'),(4,'gvafasdkiy',6,'ugvafasdki'),
(5,'that\'s',NULL,'she'),(6,'bwftwugvaf',7,'cbwftwugva'),
(7,'f',-700055552,'mkacbwftwu'),(8,'a',9,'be'),
(9,'d',NULL,'u'),(10,'ckiixcsxmk',NULL,'o');
SELECT DISTINCT t2.col_int_key 
FROM
t1
LEFT JOIN t2
ON t1.col_varchar_10 = t2.col_varchar_10_key 
WHERE t2.pk
ORDER BY t2.col_int_key;
col_int_key
DROP TABLE t1,t2;
#
# Bug#13068506 - QUERY WITH GROUP BY ON NON-AGGR COLUMN RETURNS WRONG RESULT
#
CREATE TABLE t1 (i1 int);
INSERT INTO t1 VALUES (100), (101);
CREATE TABLE t2 (i2 int, i3 int);
INSERT INTO t2 VALUES (20,1),(10,2);
CREATE TABLE t3 (i4 int(11));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t3 VALUES (1),(2);

SELECT (
SELECT MAX( t2.i2 )
FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
WHERE t2.i3 <> t1.i1
) AS field1
FROM t1;;
field1
20
20

SELECT (
SELECT MAX( t2.i2 )
FROM t3 RIGHT JOIN t2 ON ( t2.i3 = 2 )
WHERE t2.i3 <> t1.i1
) AS field1
FROM t1 GROUP BY field1;;
field1
20

drop table t1,t2,t3;
# Bug#11766384 - 59487: WRONG RESULT WITH STRAIGHT_JOIN AND RIGHT JOIN
CREATE TABLE t1 (
pk int(11) NOT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1,'1');
CREATE TABLE t2 (
pk int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t2 VALUES (1);
CREATE TABLE t3 (
pk int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t3 VALUES (1);
CREATE TABLE t4 (
pk int(11) NOT NULL,
col_int int(11) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t4 VALUES (1,1,1,'1');
CREATE TABLE t5 (
col_int int(11) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
INSERT INTO t5 VALUES (1,'1');
CREATE TABLE t6 (
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
pk int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t6 VALUES (1,'1',1);
SELECT STRAIGHT_JOIN t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN
(
t2
RIGHT JOIN
(
(
t1
LEFT JOIN
(
t4
JOIN
t3
ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(
t5
JOIN
t6 AS t6b
ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
AND t5.col_varchar_10_utf8_key = 0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
pk	pk
1	NULL
EXPLAIN SELECT STRAIGHT_JOIN t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN
(
t2
RIGHT JOIN
(
(
t1
LEFT JOIN
(
t4
JOIN
t3
ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(
t5
JOIN
t6 AS t6b
ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
AND t5.col_varchar_10_utf8_key = 0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t6a	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
1	SIMPLE	t5	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t6b	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select straight_join `test`.`t6a`.`pk` AS `pk`,`test`.`t2`.`pk` AS `pk` from `test`.`t6` `t6a` join `test`.`t1` left join (`test`.`t4` join `test`.`t3`) on(((`test`.`t4`.`col_int_key` = `test`.`t1`.`pk`) and (0 <> `test`.`t4`.`col_int`))) join `test`.`t5` join `test`.`t6` `t6b` left join `test`.`t2` on(((`test`.`t4`.`col_varchar_10_latin1_key` = `test`.`t1`.`col_varchar_10_latin1_key`) and (`test`.`t5`.`col_varchar_10_utf8_key` = 0))) where ((`test`.`t5`.`col_int` = `test`.`t1`.`pk`) and ((0 <> `test`.`t6b`.`col_int_key`) is true) and (`test`.`t5`.`col_varchar_10_utf8_key` = `test`.`t6b`.`col_varchar_10_latin1_key`) and ((0 <> `test`.`t6a`.`pk`) is true))
SELECT t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN
(
t2
RIGHT JOIN
(
(
t1
LEFT JOIN
(
t4
JOIN
t3
ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(
t5
JOIN
t6 AS t6b
ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
AND t5.col_varchar_10_utf8_key = 0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
pk	pk
1	NULL
EXPLAIN SELECT t6a.pk, t2.pk
FROM
t6 AS t6a
LEFT JOIN
(
t2
RIGHT JOIN
(
(
t1
LEFT JOIN
(
t4
JOIN
t3
ON t4.col_int
)
ON t4.col_int_key = t1.pk
)
LEFT JOIN
(
t5
JOIN
t6 AS t6b
ON t5.col_varchar_10_utf8_key = t6b.col_varchar_10_latin1_key
)
ON t1.pk = t5.col_int
)
ON t4.col_varchar_10_latin1_key = t1.col_varchar_10_latin1_key
AND t5.col_varchar_10_utf8_key = 0
)
ON t6a.pk IS TRUE
WHERE t6b.col_int_key IS TRUE
;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t6a	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (hash join)
1	SIMPLE	t5	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t6b	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t6a`.`pk` AS `pk`,`test`.`t2`.`pk` AS `pk` from `test`.`t6` `t6a` join `test`.`t1` left join (`test`.`t4` join `test`.`t3`) on(((`test`.`t4`.`col_int_key` = `test`.`t1`.`pk`) and (0 <> `test`.`t4`.`col_int`))) join `test`.`t5` join `test`.`t6` `t6b` left join `test`.`t2` on(((`test`.`t4`.`col_varchar_10_latin1_key` = `test`.`t1`.`col_varchar_10_latin1_key`) and (`test`.`t5`.`col_varchar_10_utf8_key` = 0))) where ((`test`.`t5`.`col_int` = `test`.`t1`.`pk`) and ((0 <> `test`.`t6b`.`col_int_key`) is true) and (`test`.`t5`.`col_varchar_10_utf8_key` = `test`.`t6b`.`col_varchar_10_latin1_key`) and ((0 <> `test`.`t6a`.`pk`) is true))
EXPLAIN SELECT * FROM t5 LEFT JOIN t6 ON t5.col_int=1000
WHERE t6.col_int_key IS TRUE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t5	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
1	SIMPLE	t6	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t5`.`col_int` AS `col_int`,`test`.`t5`.`col_varchar_10_utf8_key` AS `col_varchar_10_utf8_key`,`test`.`t6`.`col_int_key` AS `col_int_key`,`test`.`t6`.`col_varchar_10_latin1_key` AS `col_varchar_10_latin1_key`,`test`.`t6`.`pk` AS `pk` from `test`.`t5` join `test`.`t6` where ((`test`.`t5`.`col_int` = 1000) and ((0 <> `test`.`t6`.`col_int_key`) is true))
EXPLAIN SELECT * FROM t5 LEFT JOIN t6 ON t5.col_int=1000
WHERE t6.col_int_key IS NOT TRUE;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t5	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t6	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t5`.`col_int` AS `col_int`,`test`.`t5`.`col_varchar_10_utf8_key` AS `col_varchar_10_utf8_key`,`test`.`t6`.`col_int_key` AS `col_int_key`,`test`.`t6`.`col_varchar_10_latin1_key` AS `col_varchar_10_latin1_key`,`test`.`t6`.`pk` AS `pk` from `test`.`t5` left join `test`.`t6` on((`test`.`t5`.`col_int` = 1000)) where ((0 <> `test`.`t6`.`col_int_key`) is not true)
drop table t1,t2,t3,t4,t5,t6;
#
# Verify that the "not exists" optimization works.
#
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT NOT NULL);
INSERT INTO t1 VALUES(1),(2);
INSERT INTO t2 VALUES(1),(2);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.a IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; Not exists; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where (`test`.`t2`.`a` is null)
FLUSH STATUS;
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.a IS NULL;
a	a
SHOW STATUS LIKE 'HANDLER_READ%';
Variable_name	Value
Handler_read_first	2
Handler_read_key	2
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	6
DROP TABLE t1,t2;
#
# Bug#13464334 SAME QUERY PRODUCES DIFFERENT RESULTS WHEN USED WITH AND
# WITHOUT UNION ALL
#
CREATE TABLE t1 (p1 INT PRIMARY KEY, a CHAR(1));
CREATE TABLE t2 (p2 INT PRIMARY KEY, b CHAR(1));
INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO t2 VALUES (1,'h'),(2,'i'),(3,'j'),(4,'k');
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
(SELECT p1 FROM v2 LEFT JOIN v1 ON b = a WHERE p2 = 1 GROUP BY p1 ORDER BY p1)
UNION (SELECT NULL LIMIT 0);
p1
NULL
DROP VIEW v1, v2;
DROP TABLE t1, t2;
#
# Bug#13980954 Missing data on left join + null value + where..in
#
CREATE TABLE t1 (ik INT, vc varchar(1)) charset utf8mb4 ENGINE=Innodb;
explain format=json SELECT straight_join t1.vc, t1.ik
FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.05"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.35",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "ik",
            "vc"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "0.70",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "vc"
          ],
          "attached_condition": "(`test`.`t2`.`vc` = `test`.`t1`.`vc`)"
        }
      },
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "using_join_buffer": "hash join",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.10",
            "prefix_cost": "1.05",
            "data_read_per_join": "16"
          },
          "used_columns": [
            "vc"
          ],
          "attached_condition": "<if>(is_not_null_compl(t3), (`test`.`t3`.`vc` = `test`.`t1`.`vc`), true)"
        }
      }
    ]
  }
}
Warnings:
Note	1003	/* select#1 */ select straight_join `test`.`t1`.`vc` AS `vc`,`test`.`t1`.`ik` AS `ik` from `test`.`t1` join `test`.`t1` `t2` left join `test`.`t1` `t3` on((`test`.`t3`.`vc` = `test`.`t1`.`vc`)) where (`test`.`t2`.`vc` = `test`.`t1`.`vc`)
SELECT straight_join t1.vc, t1.ik
FROM t1 JOIN t1 AS t2 ON t1.vc=t2.vc LEFT JOIN t1 AS t3 ON t1.vc=t3.vc;
vc	ik
DROP TABLE t1;
#
# Bug #18345786 CRASH AROUND ST_JOIN_TABLE::AND_WITH_CONDITION
#
CREATE TABLE t1(a INT) ENGINE=INNODB;
SET @a:=(SELECT ROW(1, 2)=
ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1), 1));
DROP TABLE t1;
#
# Coverage for "unique row not found"
#
create table t1(a int, unique key(a)) engine=innodb;
insert into t1 values(1);
explain select * from t1 left join t1 as t2
on t2.a=12
where t1.a=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	a	a	5	const	1	100.00	Using index
1	SIMPLE	t2	NULL	const	a	a	5	const	0	0.00	unique row not found
Warnings:
Note	1003	/* select#1 */ select '1' AS `a`,NULL AS `a` from `test`.`t1` left join `test`.`t1` `t2` on(multiple equal(12, NULL)) where true
select * from t1 left join t1 as t2
on t2.a=12
where t1.a=1;
a	a
1	NULL
drop table t1;
#
# Bug#18717059 MISSING ROWS ON NESTED JOIN WITH SUBQUERY  
#              WITH MYISAM OR MEMORY    
#
CREATE TABLE t1 (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key, col_int_key)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (23,4,'d');
INSERT INTO t1 VALUES (24,8,'g');
INSERT INTO t1 VALUES (25,NULL,'x');
INSERT INTO t1 VALUES (26,NULL,'f');
INSERT INTO t1 VALUES (27,0,'p');
INSERT INTO t1 VALUES (28,NULL,'j');
INSERT INTO t1 VALUES (29,8,'c');
CREATE TABLE t2 (
pk INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
PRIMARY KEY (pk)
) ENGINE=MyISAM;
SELECT 9
FROM t1 AS table1
RIGHT JOIN t1 AS table2
ON table2.col_int_key = table1.col_int_key
AND table1.col_varchar_key = (
SELECT subquery2_t2.col_varchar_key
FROM t2
STRAIGHT_JOIN ( t2 AS subquery2_t2
JOIN t1 AS subquery2_t3
) ON ( subquery2_t3.col_int_key = subquery2_t2.pk )
);
9
9
9
9
9
9
9
9
CREATE TABLE where_subselect_table AS
SELECT 9
FROM t1 AS table1
RIGHT JOIN t1 AS table2
ON table2.col_int_key = table1.col_int_key
AND table1.col_varchar_key = (
SELECT subquery2_t2.col_varchar_key
FROM t2
STRAIGHT_JOIN ( t2 AS subquery2_t2
JOIN t1 AS subquery2_t3
) ON ( subquery2_t3.col_int_key = subquery2_t2.pk )
);
set optimizer_switch='condition_fanout_filter=on';
SELECT *
FROM where_subselect_table
WHERE (9) IN ( SELECT 9
FROM t1 AS table1
RIGHT JOIN t1 AS table2
ON table2.col_int_key = table1.col_int_key
AND table1.col_varchar_key = (
SELECT subquery2_t2.col_varchar_key
FROM t2
STRAIGHT_JOIN ( t2 AS subquery2_t2
JOIN t1 AS subquery2_t3
) ON ( subquery2_t3.col_int_key = subquery2_t2.pk )
) )
/* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
9
9
9
9
9
9
9
9
set optimizer_switch='condition_fanout_filter=off';
SELECT *
FROM where_subselect_table
WHERE (9) IN ( SELECT 9
FROM t1 AS table1
RIGHT JOIN t1 AS table2
ON table2.col_int_key = table1.col_int_key
AND table1.col_varchar_key = (
SELECT subquery2_t2.col_varchar_key
FROM t2
STRAIGHT_JOIN ( t2 AS subquery2_t2
JOIN t1 AS subquery2_t3
) ON ( subquery2_t3.col_int_key = subquery2_t2.pk )
) )
/* TRANSFORM_OUTCOME_UNORDERED_MATCH */;
9
9
9
9
9
9
9
9
DROP TABLE t1, t2, where_subselect_table;
# Bug#14358878 Wrong results on table left join view
CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE ALGORITHM=MERGE VIEW vmerge AS SELECT 1 AS id, id AS b_id FROM b;
CREATE ALGORITHM=TEMPTABLE VIEW vmat AS SELECT 1 AS id, id AS b_id FROM b;
INSERT INTO a(id) VALUES (1);
SELECT *
FROM a LEFT JOIN vmerge AS v ON a.id = v.id;
id	id	b_id
1	NULL	NULL
SELECT *
FROM a LEFT JOIN vmat AS v ON a.id = v.id;
id	id	b_id
1	NULL	NULL
SELECT *
FROM a LEFT JOIN (SELECT 1 AS one, id FROM b) AS v ON a.id = v.id;
id	one	id
1	NULL	NULL
SELECT *
FROM a LEFT JOIN (SELECT DISTINCT 1 AS one, id FROM b) AS v ON a.id = v.id;
id	one	id
1	NULL	NULL
SELECT *
FROM a LEFT JOIN vmerge AS v ON a.id = v.id
UNION DISTINCT
SELECT *
FROM a LEFT JOIN vmerge AS v ON a.id = v.id;
id	id	b_id
1	NULL	NULL
SELECT *
FROM a LEFT JOIN vmerge AS v ON a.id = v.id
UNION ALL
SELECT *
FROM a LEFT JOIN vmerge AS v ON a.id = v.id;
id	id	b_id
1	NULL	NULL
1	NULL	NULL
DROP VIEW vmerge, vmat;
DROP TABLE a, b;
# Bug#15936817 Table left join view, unmatched rows problem where
#              view contains an IF
CREATE TABLE small (
id INTEGER not null,
PRIMARY KEY (id)
);
CREATE TABLE big (
id INTEGER not null,
PRIMARY KEY (id)
);
INSERT INTO small VALUES (1), (2);
INSERT INTO big VALUES (1), (2), (3), (4);
CREATE VIEW small_view AS
SELECT *, IF (id % 2 = 1, 1, 0) AS is_odd
FROM small;
CREATE VIEW big_view AS
SELECT big.*, small_view.id AS small_id, small_view.is_odd
FROM big LEFT JOIN small_view ON small_view.id = big.id;
SELECT * FROM big_view;
id	small_id	is_odd
1	1	1
2	2	0
3	NULL	NULL
4	NULL	NULL
SELECT big.*, small.id AS small_id, small.is_odd
FROM big LEFT JOIN
(SELECT id, IF (id % 2 = 1, 1, 0) AS is_odd FROM small) AS small
ON big.id = small.id;
id	small_id	is_odd
1	1	1
2	2	0
3	NULL	NULL
4	NULL	NULL
# Check the IS NULL and thruth predicates
SELECT big.*, dt.*
FROM big LEFT JOIN (SELECT id as dt_id,
id IS NULL AS nul,
id IS NOT NULL AS nnul,
id IS TRUE AS t,
id IS NOT TRUE AS nt,
id IS FALSE AS f,
id IS NOT FALSE AS nf,
id IS UNKNOWN AS u,
id IS NOT UNKNOWN AS nu
FROM small) AS dt
ON big.id=dt.dt_id;
id	dt_id	nul	nnul	t	nt	f	nf	u	nu
1	1	0	1	1	0	0	1	0	1
2	2	0	1	1	0	0	1	0	1
3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# Check comparison predicates
SELECT big.*, dt.*
FROM big LEFT JOIN (SELECT id as dt_id,
id = 1 AS eq,
id <> 1 AS ne,
id > 1 AS gt,
id >= 1 AS ge,
id < 1 AS lt,
id <= 1 AS le,
id <=> 1 AS equal
FROM small) AS dt
ON big.id=dt.dt_id;
id	dt_id	eq	ne	gt	ge	lt	le	equal
1	1	1	0	0	1	0	1	1
2	2	0	1	1	1	0	0	0
3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
# Check CASE, NULLIF and COALESCE
SELECT big.*, dt.*
FROM big LEFT JOIN (SELECT id as dt_id,
CASE id WHEN 0 THEN 0 ELSE 1 END AS simple,
CASE WHEN id=0 THEN NULL ELSE 1 END AS cond,
NULLIF(1, NULL) AS nullif,
IFNULL(1, NULL) AS ifnull,
COALESCE(id) AS coal,
INTERVAL(NULL, 1, 2, 3) as intv,
IF (id % 2 = 1, NULL, 1) AS iff
FROM small) AS dt
ON big.id=dt.dt_id;
id	dt_id	simple	cond	nullif	ifnull	coal	intv	iff
1	1	1	1	1	1	1	-1	NULL
2	2	1	1	1	1	2	-1	1
3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
DROP VIEW small_view, big_view;
DROP TABLE small, big;
#
# Bug#16893426 OPTIMIZER FAILS TO OPTIMIZE EXPRESSION OF THE
#              FORM 'FOO' IS NULL
#
CREATE TABLE t1 (
id int(11) NOT NULL PRIMARY KEY,
name varchar(20),
INDEX (name)
) ENGINE=InnoDB;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2 (
id int(11) NOT NULL PRIMARY KEY,
f_id int(11),
FOREIGN KEY (f_id) REFERENCES t2(id)
) ENGINE=InnoDB;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
ANALYZE TABLE t1;
ANALYZE TABLE t2;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,name	name	83	NULL	2	100.00	Using where; Using index
1	SIMPLE	t2	NULL	ref	f_id	f_id	5	test.t1.id	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`f_id` = `test`.`t1`.`id`) and (`test`.`t1`.`name` like 'A%'))
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%';
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%'";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%' OR 'ASDFGH' IS NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,name	name	83	NULL	2	100.00	Using where; Using index
1	SIMPLE	t2	NULL	ref	f_id	f_id	5	test.t1.id	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`f_id` = `test`.`t1`.`id`) and (`test`.`t1`.`name` like 'A%'))
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%' OR 'ASDFGH' IS NULL;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE t1.name LIKE 'A%' OR 'ASDFGH' IS NULL";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 0 OR 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on(multiple equal(`test`.`t2`.`f_id`, `test`.`t1`.`id`)) where false
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 0 OR 0;
id	f_id	id	name
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 0 OR 0";
EXECUTE stmt;
id	f_id	id	name
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 OR 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 OR 0;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 OR 0";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 1;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 1";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 0 = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on(multiple equal(`test`.`t2`.`f_id`, `test`.`t1`.`id`)) where false
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 0 = 1;
id	f_id	id	name
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE NOT(NOT (0)) OR 0 = 1";
EXECUTE stmt;
id	f_id	id	name
DEALLOCATE PREPARE stmt;
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT TRUE AND 0 IS TRUE ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on(multiple equal(`test`.`t2`.`f_id`, `test`.`t1`.`id`)) where false
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT TRUE AND 0 IS TRUE ;
id	f_id	id	name
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT TRUE AND 0 IS TRUE ";
EXECUTE stmt;
id	f_id	id	name
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 'a' LIKE 1 OR 2 LIKE '2%' ;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 'a' LIKE 1 OR 2 LIKE '2%' ;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 'a' LIKE 1 OR 2 LIKE '2%' ";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT NULL;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE 1 IS NOT NULL";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<2 AND 2<3) OR (1>1 AND 2<3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<2 AND 2<3) OR (1>1 AND 2<3);
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<2 AND 2<3) OR (1>1 AND 2<3)";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1>=1 AND 2<=2) OR (1=1 AND 2<3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1>=1 AND 2<=2) OR (1=1 AND 2<3);
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1>=1 AND 2<=2) OR (1=1 AND 2<3)";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
EXPLAIN SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<>1 AND 2=2) OR (1 BETWEEN 0 AND 2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	f_id	5	NULL	5	100.00	Using index
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.t2.f_id	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f_id` AS `f_id`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`f_id`)) where true
SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<>1 AND 2=2) OR (1 BETWEEN 0 AND 2);
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
PREPARE stmt FROM "SELECT * FROM t2 LEFT JOIN t1 ON t2.f_id = t1.id
WHERE (1<>1 AND 2=2) OR (1 BETWEEN 0 AND 2)";
EXECUTE stmt;
id	f_id	id	name
1	1	1	A1
2	2	2	A2
3	2	2	A2
4	3	3	B
5	3	3	B
DROP TABLE t1,t2;
#
# Bug#22103398 BUG#16893426:SIG11 IN
# ST_SELECT_LEX::FLATTEN_SUBQUERIES | SQL_RESOLVER.CC
#
CREATE TABLE t1(c1 INT,	c2 INT,	c3 CHAR(1), KEY(c3))ENGINE=InnoDB;
CREATE TABLE t2(c1 INT,	c2 INT,	c3 CHAR(1), KEY(c3))ENGINE=InnoDB;
SELECT b.c2 AS f1 FROM (t2 AS a JOIN
((t2 AS b JOIN t2 AS c ON (c.c3=b.c3)))
ON (c.c1=b.c2))
WHERE (c.c3 IN (SELECT subquery1_b.c3 AS subquery1_f1
FROM (t1 AS subquery1_a JOIN t2 AS subquery1_b ON
(subquery1_b.c1=subquery1_a.c1)))) AND
(a.c1=a.c1 AND (SELECT''FROM DUAL) IS NULL);
f1
DROP TABLE t1, t2;
# End of Bug#16893426
# Bug#22561937 Wrong result on outer join with multiple join conditions
#              and derived table
CREATE TABLE t1 (
col_int INT,
pk INT NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES
(2,1), (2,2), (6,3), (4,4), (7,5),
(188,6), (0,7), (6,8), (0,9), (9,10);
CREATE TABLE t2 (
pk INT NOT NULL,
col_int INT,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES
(1,0), (2,0), (3,2), (4,NULL), (5,2),
(6,3), (7,3), (8,100), (9,3), (10,6);
explain SELECT table2.pk, table1.col_int
FROM t2 AS table1
LEFT JOIN t1 AS table2
ON table2.pk < table1.col_int AND
table2.pk = table1.col_int;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	table1	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	NULL
1	SIMPLE	table2	NULL	eq_ref	PRIMARY	PRIMARY	4	test.table1.col_int	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`table2`.`pk` AS `pk`,`test`.`table1`.`col_int` AS `col_int` from `test`.`t2` `table1` left join `test`.`t1` `table2` on(((`test`.`table2`.`pk` = `test`.`table1`.`col_int`) and (`test`.`table1`.`col_int` < `test`.`table1`.`col_int`))) where true
SELECT table2.pk, table1.col_int
FROM t2 AS table1
LEFT JOIN t1 AS table2
ON table2.pk < table1.col_int AND
table2.pk = table1.col_int;
pk	col_int
NULL	0
NULL	0
NULL	2
NULL	NULL
NULL	2
NULL	3
NULL	3
NULL	100
NULL	3
NULL	6
explain SELECT table2.pk, table1.col_int
FROM t2 AS table1
LEFT JOIN (SELECT * FROM t1) AS table2
ON table2.pk < table1.col_int AND
table2.pk = table1.col_int;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	table1	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	NULL
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	4	test.table1.col_int	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`table1`.`col_int` AS `col_int` from `test`.`t2` `table1` left join (`test`.`t1`) on(((`test`.`t1`.`pk` = `test`.`table1`.`col_int`) and (`test`.`t1`.`pk` < `test`.`table1`.`col_int`))) where true
SELECT table2.pk, table1.col_int
FROM t2 AS table1
LEFT JOIN (SELECT * FROM t1) AS table2
ON table2.pk < table1.col_int AND
table2.pk = table1.col_int;
pk	col_int
NULL	0
NULL	0
NULL	2
NULL	NULL
NULL	2
NULL	3
NULL	3
NULL	100
NULL	3
NULL	6
DROP TABLE t1, t2;
# Bug#22671557: Wrong results on JOIN when composite index is present
CREATE TABLE t1 (
col_int INT DEFAULT NULL,
col_int_key INT DEFAULT NULL,
pk INT NOT NULL,
PRIMARY KEY (pk),
KEY test_idx (col_int_key,col_int)
);
INSERT INTO t1 VALUES (0, -7, 1), (9, NULL, 15), (182, NULL, 25);
CREATE TABLE t2 (
col_int INT DEFAULT NULL,
pk INT NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (NULL, 4), (-208, 5), (5, 6), (NULL, 75);
CREATE TABLE t3 (
col_datetime_key DATETIME DEFAULT NULL,
pk INT NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t3 VALUES ('1970-01-01 00:00:00', 5);
CREATE TABLE t4 (
col_int INT DEFAULT NULL,
pk INT NOT NULL,
col_int_key INT DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
);
INSERT INTO t4 VALUES (0, 15, 6), (9, 16, 6);
SELECT alias2.col_datetime_key
FROM
t1 AS alias1
LEFT JOIN t3 AS alias2
LEFT JOIN t2 AS alias3
LEFT JOIN t4 AS alias4
ON alias3.pk = alias4.col_int_key
ON alias2.pk = alias3.col_int
ON alias1.col_int = alias4.col_int
;
col_datetime_key
1970-01-01 00:00:00
1970-01-01 00:00:00
NULL
DROP TABLE t1, t2, t3, t4;
# Bug#22833364: Left join returns incorrect results on the outer side
CREATE TABLE ta (
a1 varchar(1024) NOT NULL,
a2 int NOT NULL,
KEY user_id(a2)
);
INSERT INTO ta (a1, a2) VALUES ('row1', 4), ('row2', 4);
CREATE TABLE tb (
b1 int NOT NULL,
b2 varchar(1024) NOT NULL,
b3 int NOT NULL,
PRIMARY KEY (b1)
);
INSERT INTO tb (b1, b2, b3) VALUES
(1, 'text1', 0), (2, 'text2', 0), (3, 'text3', 1), (4, 'text4', 1);
explain SELECT ta.a1, tb.b1, tb.b2
FROM ta LEFT OUTER JOIN tb
ON ta.a2 = tb.b1 AND tb.b3 = 0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ta	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	tb	NULL	eq_ref	PRIMARY	PRIMARY	4	test.ta.a2	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`ta`.`a1` AS `a1`,`test`.`tb`.`b1` AS `b1`,`test`.`tb`.`b2` AS `b2` from `test`.`ta` left join `test`.`tb` on(((`test`.`tb`.`b3` = 0) and (`test`.`tb`.`b1` = `test`.`ta`.`a2`))) where true
SELECT ta.a1, tb.b1, tb.b2
FROM ta LEFT OUTER JOIN tb
ON ta.a2 = tb.b1 AND tb.b3 = 0;
a1	b1	b2
row1	NULL	NULL
row2	NULL	NULL
DROP TABLE ta, tb;
# Bug#23079533: Left join on PK + extra condition doesn't return match
CREATE TABLE m (
machineid VARCHAR(32) NOT NULL,
orderid bigint unsigned DEFAULT NULL,
extra bigint unsigned DEFAULT NULL,
PRIMARY KEY (machineid)
);
INSERT INTO m (machineid, orderid)
VALUES ('m1', NULL), ('m2', 2), ('m3', NULL), ('m4', NULL);
CREATE TABLE o (
orderid bigint unsigned NOT NULL,
machineid VARCHAR(32) DEFAULT NULL,
PRIMARY KEY (orderid)
);
INSERT INTO o (orderid, machineid)
VALUES (1, 'm2'), (2, 'm2');
SELECT o.*,'|' as sep, m.*
FROM o LEFT JOIN m
ON m.machineid = o.machineid AND
m.orderid = o.orderid;
orderid	machineid	sep	machineid	orderid	extra
1	m2	|	NULL	NULL	NULL
2	m2	|	m2	2	NULL
DROP TABLE m, o;
# Bug#23086825: Incorrect query results using left join against derived
CREATE TABLE t1 (
adslot varchar(5) NOT NULL
);
INSERT INTO t1(adslot) VALUES ('1'), ('2'), ('3');
CREATE TABLE t2 (
ionumber varchar(20) NOT NULL,
adslot varchar(5) NOT NULL
);
INSERT INTO t2 (ionumber, adslot) VALUES ('01602', 1), ('01602', 3);
CREATE TABLE t3 (
ionumber varchar(20) NOT NULL,
ioattribute varchar(5) NOT NULL,
PRIMARY KEY (ionumber)
);
INSERT INTO t3 VALUES ('01602', 'BOB'), ('01603', 'SALLY');
SELECT s.adslot, lid.ionumber1, lid.ionumber2, lid.ioattribute
FROM t1 s LEFT JOIN
(SELECT lid.adslot,
i.ionumber as ionumber1,
lid.ionumber as ionumber2,
i.ioattribute
FROM t2 lid JOIN t3 i
USING (ionumber)
) AS lid
USING (adslot);
adslot	ionumber1	ionumber2	ioattribute
1	01602	01602	BOB
2	NULL	NULL	NULL
3	01602	01602	BOB
DROP TABLE t1, t2, t3;
#
# Bug #26432173: INCORRECT SUBQUERY OPTIMIZATION WITH
#                LEFT JOIN(SUBQUERY) AND ORDER BY
#
CREATE TABLE t1 (a INT);
INSERT t1 values (1),(2),(15),(24),(5);
CREATE TABLE t2 (t1_a INT, b VARCHAR(10));
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN SELECT t1.a, subq.st_value
FROM t1
LEFT JOIN (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
ON subq.t1_a = t1.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,'red' AS `st_value` from `test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`t1_a` = `test`.`t1`.`a`)) where true
EXPLAIN SELECT t1.a, subq.st_value
FROM t1
LEFT JOIN (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
ON subq.t1_a = t1.a
ORDER BY t1.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,'red' AS `st_value` from `test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`t1_a` = `test`.`t1`.`a`)) where true order by `test`.`t1`.`a`
EXPLAIN SELECT t1.a, subq.st_value
FROM (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
LEFT JOIN t1
ON subq.t1_a = t1.a
ORDER BY t1.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,'red' AS `st_value` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`t1_a`)) where true order by `test`.`t1`.`a`
SELECT t1.a, subq.st_value
FROM t1
LEFT JOIN (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
ON subq.t1_a = t1.a;
a	st_value
1	NULL
2	NULL
15	NULL
24	NULL
5	NULL
SELECT t1.a, subq.st_value
FROM t1
LEFT JOIN (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
ON subq.t1_a = t1.a
ORDER BY t1.a;
a	st_value
1	NULL
2	NULL
5	NULL
15	NULL
24	NULL
SELECT t1.a, subq.st_value
FROM (SELECT t2.t1_a, 'red' AS st_value
FROM t2) AS subq
LEFT JOIN t1
ON subq.t1_a = t1.a
ORDER BY t1.a;
a	st_value
DROP TABLE t1, t2;
# Bug #18898433: EXTREMELY SLOW PERFORMANCE WITH OUTER JOINS AND JOIN
#                BUFFER.
#
CREATE TABLE t1 (i INT NOT NULL);
INSERT INTO t1 VALUES (0),(2),(3),(4);
CREATE TABLE t2 (i INT NOT NULL);
INSERT INTO t2 VALUES (0),(1),(3),(4);
CREATE TABLE t3 (i INT NOT NULL);
INSERT INTO t3 VALUES (0),(1),(2),(4);
CREATE TABLE t4 (i INT NOT NULL);
INSERT INTO t4 VALUES (0),(1),(2),(3);
ANALYZE TABLE t1, t2, t3, t4;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
test.t4	analyze	status	OK
EXPLAIN SELECT *
FROM t1 LEFT JOIN
(
(t2 LEFT JOIN t3 ON t3.i= t2.i)
LEFT JOIN t4 ON t3.i= t4.i
)ON t2.i= t1.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i`,`test`.`t3`.`i` AS `i`,`test`.`t4`.`i` AS `i` from `test`.`t1` left join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`i` = `test`.`t2`.`i`)) left join `test`.`t4` on((`test`.`t4`.`i` = `test`.`t3`.`i`))) on((`test`.`t2`.`i` = `test`.`t1`.`i`)) where true
EXPLAIN SELECT *
FROM t1 LEFT JOIN
(
(t2 INNER JOIN t3 ON t3.i= t2.i)
LEFT JOIN t4 ON t3.i= t4.i
)ON t2.i= t1.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i`,`test`.`t3`.`i` AS `i`,`test`.`t4`.`i` AS `i` from `test`.`t1` left join (`test`.`t2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`i` = `test`.`t2`.`i`))) on(((`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t3`.`i` = `test`.`t1`.`i`))) where true
EXPLAIN SELECT *
FROM t1 LEFT JOIN t2 ON t2.i= t1.i
LEFT JOIN t3 ON t3.i= t2.i
LEFT JOIN t4 ON t3.i= t4.i;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
1	SIMPLE	t4	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`i` AS `i`,`test`.`t3`.`i` AS `i`,`test`.`t4`.`i` AS `i` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i` = `test`.`t1`.`i`)) left join `test`.`t3` on((`test`.`t3`.`i` = `test`.`t2`.`i`)) left join `test`.`t4` on((`test`.`t4`.`i` = `test`.`t3`.`i`)) where true
flush status;
SELECT *
FROM t1 LEFT JOIN
(
(t2 LEFT JOIN t3 ON t3.i= t2.i)
LEFT JOIN t4 ON t3.i= t4.i
)ON t2.i= t1.i;
i	i	i	i
0	0	0	0
2	NULL	NULL	NULL
3	3	NULL	NULL
4	4	4	NULL
SHOW STATUS LIKE 'HANDLER_%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	8
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	4
Handler_read_key	4
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	20
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	0
flush status;
SELECT *
FROM t1 LEFT JOIN
(
(t2 INNER JOIN t3 ON t3.i= t2.i)
LEFT JOIN t4 ON t3.i= t4.i
)ON t2.i= t1.i;
i	i	i	i
0	0	0	0
2	NULL	NULL	NULL
3	NULL	NULL	NULL
4	4	4	NULL
SHOW STATUS LIKE 'HANDLER_%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	8
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	4
Handler_read_key	4
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	20
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	0
flush status;
SELECT *
FROM t1 LEFT JOIN t2 ON t2.i= t1.i
LEFT JOIN t3 ON t3.i= t2.i
LEFT JOIN t4 ON t3.i= t4.i;
i	i	i	i
0	0	0	0
2	NULL	NULL	NULL
3	3	NULL	NULL
4	4	4	NULL
SHOW STATUS LIKE 'HANDLER_%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	8
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	4
Handler_read_key	4
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	20
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	0
DROP TABLE t1, t2, t3, t4;
#
# Bug #26627181: WRONG RESULT WITH LEFT JOIN + DERIVED TABLES
#
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
EXPLAIN SELECT *
FROM (SELECT id
FROM t1) AS a
LEFT JOIN
(SELECT id, 2 AS tall
FROM t2) AS b
ON a.id = b.id
WHERE b.tall IS NOT NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,2 AS `tall` from `test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`id` = `test`.`t1`.`id`)) where (2 is not null)
SELECT *
FROM (SELECT id
FROM t1) AS a
LEFT JOIN
(SELECT id, 2 AS tall
FROM t2) AS b
ON a.id = b.id
WHERE b.tall IS NOT NULL;
id	id	tall
1	1	2
DROP TABLE t1, t2;
#
# Bug #23169204: Left join + merged derived table + group by = bad result
#
CREATE TABLE t1(doc text);
CREATE TABLE t2(a INTEGER DEFAULT NULL);
INSERT INTO t2 VALUES(1);
SELECT je
FROM t2 LEFT JOIN (SELECT 1 AS je FROM t1 LIMIT 1) AS dt ON FALSE;
je
NULL
SELECT je
FROM t2 LEFT JOIN (SELECT 1 AS je FROM t1 LIMIT 1) AS dt ON FALSE
GROUP BY je;
je
NULL
SELECT je
FROM t2 LEFT JOIN (SELECT 1 AS je FROM t1) AS dt ON FALSE;
je
NULL
SELECT je
FROM t2 LEFT JOIN (SELECT 1 AS je FROM t1) AS dt ON FALSE
GROUP BY je;
je
NULL
DROP TABLE t1, t2;
#
# Bug#22489105 WL#9571 : SIG11 IN ITEM_SUBSELECT::EXEC | SQL/ITEM_SUBSELECT.CC
#
SELECT (SELECT * FROM (SELECT 'a') t) AS f1 HAVING (f1 = 'a' OR TRUE);
f1
a
SELECT (SELECT * FROM (SELECT 'a') t) + 1 AS f1 HAVING (f1 = 'a' OR TRUE);
f1
1
SELECT 1 + (SELECT * FROM (SELECT 'a') t) AS f1 HAVING (f1 = 'a' OR TRUE);
f1
1
#
# Bug#28237111: WL#9571: TABLE FULL ERROR
#
CREATE TABLE t1 (pk INTEGER, f1 INTEGER, primary key(pk));
CREATE TABLE t2 (pk INTEGER, f1 INTEGER, primary key(pk));
CREATE TABLE t3 (pk INTEGER);
INSERT INTO t1 VALUES (1,1),(2,1),(3,1);
INSERT INTO t2 VALUES (1,1),(2,1),(3,1);
INSERT INTO t3 VALUES (1);
SELECT  * FROM (t1 RIGHT JOIN
(SELECT * FROM t3 WHERE (DAYNAME('1995')))  AS table2 ON
(( t1.f1 ,t1.pk) IN (SELECT 7,4 UNION SELECT 9,2))) WHERE
(NOT EXISTS (SELECT t1.f1 FROM (t1 INNER JOIN t2 ON (t1.pk=t2.f1))
WHERE 0 IS NOT NULL)) AND t1.f1 >  50;
pk	f1	pk
Warnings:
Warning	1292	Incorrect datetime value: '1995'
DROP TABLE t3,t1,t2;
#
# Bug#28341790: WL#9571: SIG11 IN ITEM_COND::FIX_FIELDS() AT SQL/ITEM_CMPFUNC.CC
#
CREATE TABLE t1 (col_varchar varchar(1) DEFAULT NULL);
INSERT INTO t1 VALUES ('Z') ;
CREATE TABLE t2 (col_varchar varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('Z') ;
PREPARE prep_stmt FROM " SELECT 1 FROM ( ( SELECT * FROM t1 WHERE col_varchar
>= 1 )  AS table1 RIGHT JOIN t2 ON ( ( NULL < NULL ) IS NULL OR 1 = 0 ) ) " ;
EXECUTE prep_stmt ;
1
1
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'Z'
EXECUTE prep_stmt ;
1
1
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'Z'
DROP TABLE t1,t2;
#
# More coverage for IS TRUE in LEFT JOIN conditions
#
CREATE TABLE t1(c1 INT);
INSERT INTO t1 VALUES(1),(2);
CREATE TABLE t2(c2 INT);
INSERT INTO t2 VALUES(1);
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2;
c1	c2
1	1
2	NULL
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE c2 IS NULL;
c1	c2
2	NULL
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE c2 IS NOT NULL;
c1	c2
1	1
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE (c2 IS NOT NULL) = 1;
c1	c2
1	1
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE (c2 IS NOT NULL) IS TRUE;
c1	c2
1	1
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE (c2 IS NOT NULL) = 0;
c1	c2
2	NULL
SELECT * FROM t1 LEFT JOIN t2 ON c1=c2 WHERE (c2 IS NOT NULL) IS FALSE;
c1	c2
2	NULL
DROP TABLE t1,t2;
#
# Bug#29402481: INCORRECT RESULT FROM OUTER JOIN QUERIES
#
CREATE TABLE t1 (
pk int primary key auto_increment,
col_int_unique int unique
) ENGINE=InnoDB;
INSERT INTO t1(col_int_unique) values (6),(7);
CREATE TABLE t2 (
pk int primary key auto_increment,
col_int_key int(11) DEFAULT NULL,
col_int_unique int(11) DEFAULT NULL,
UNIQUE KEY `ix2` (col_int_key,col_int_unique),
KEY col_int_key (col_int_key)
) ENGINE=InnoDB;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t3 (
pk int NOT NULL
) ENGINE=InnoDB;
INSERT INTO t3(pk) values (6),(7);
SELECT STRAIGHT_JOIN t1.col_int_unique, t2.col_int_key, t3.pk
FROM
(t1 LEFT JOIN t2 ON t1.col_int_unique = t2.col_int_key)
LEFT JOIN t3 ON t3.pk = t1.col_int_unique AND
t1.col_int_unique = t2.col_int_key;
col_int_unique	col_int_key	pk
6	NULL	NULL
7	NULL	NULL
DROP TABLE t1,t2,t3;
#
# Bug#29493830 CONST'IFIED OUTER JOIN RETURN INCORRECT RESULTS
#
CREATE TABLE t1 (
col_int_unique INT DEFAULT NULL,
col_int_key INT DEFAULT NULL,
UNIQUE KEY col_int_unique (col_int_unique)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (5,0);
CREATE TABLE t2 (
col_char_16_unique char(16) DEFAULT NULL,
col_int_key INT DEFAULT NULL,
col_int_unique INT DEFAULT NULL,
UNIQUE KEY col_int_unique (col_int_unique)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES ("just",21,5);
CREATE TABLE t3 (
col_int INT DEFAULT NULL,
col_char_16_unique CHAR(16) DEFAULT NULL,
UNIQUE KEY col_char_16_unique (col_char_16_unique)
) ENGINE=InnoDB;
INSERT INTO t3 VALUES (9,"foo");
CREATE TABLE t4 (
col_int INT DEFAULT NULL,
col_int_unique INT DEFAULT NULL,
UNIQUE KEY col_int_unique (col_int_unique)
) ENGINE=InnoDB;
INSERT INTO t4 VALUES (9,5);
explain SELECT STRAIGHT_JOIN
t3.col_int, t4.col_int,
t3.col_int = t4.col_int or t4.col_int IS NULL
FROM (t1
LEFT JOIN t2
ON t1.col_int_key = t2.col_int_key AND
t1.col_int_unique = t2.col_int_unique
LEFT JOIN t3
ON t3.col_char_16_unique = t2.col_char_16_unique
LEFT JOIN t4
ON t4.col_int = t3.col_int AND    #Note, this pred term
t4.col_int_unique = t1.col_int_unique
) WHERE t1.col_int_unique = 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	col_int_unique	col_int_unique	5	const	1	100.00	NULL
1	SIMPLE	t2	NULL	const	col_int_unique	col_int_unique	5	const	1	100.00	Impossible ON condition
1	SIMPLE	t3	NULL	const	col_char_16_unique	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t4	NULL	const	col_int_unique	col_int_unique	5	const	1	100.00	Impossible ON condition
Warnings:
Note	1003	/* select#1 */ select straight_join NULL AS `col_int`,NULL AS `col_int`,((NULL = NULL) or (NULL is null)) AS `t3.col_int = t4.col_int or t4.col_int IS NULL` from `test`.`t1` left join `test`.`t2` on((multiple equal('0', NULL) and multiple equal(5, NULL))) left join `test`.`t3` on(multiple equal(NULL, NULL)) left join `test`.`t4` on((multiple equal(NULL, NULL) and multiple equal(5, NULL))) where true
SELECT STRAIGHT_JOIN
t3.col_int, t4.col_int,
t3.col_int = t4.col_int or t4.col_int IS NULL
FROM (t1
LEFT JOIN t2
ON t1.col_int_key = t2.col_int_key AND
t1.col_int_unique = t2.col_int_unique
LEFT JOIN t3
ON t3.col_char_16_unique = t2.col_char_16_unique
LEFT JOIN t4
ON t4.col_int = t3.col_int AND    #Note, this pred term
t4.col_int_unique = t1.col_int_unique
) WHERE t1.col_int_unique = 5;
col_int	col_int	t3.col_int = t4.col_int or t4.col_int IS NULL
NULL	NULL	1
DROP TABLE t1, t2, t3, t4;
#
# Bug #30659532: WL#13476: DIFFERENT NUMBER OF ROWS WITH NESTED LOOP JOINS
#
CREATE TABLE t1 (
pk INTEGER NOT NULL
);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (
pk INTEGER NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t2 VALUES (3);
CREATE TABLE t3 (
f1 INTEGER,
f2 INTEGER,
KEY k2 (f2)
);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (NULL,295010100);
INSERT INTO t3 VALUES (NULL,NULL);
INSERT INTO t3 VALUES (NULL,-1762438755);
INSERT INTO t3 VALUES (NULL,4);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.pk = t2.pk LEFT JOIN t3 ON t2.pk = t3.f2 WHERE t2.pk < 5;
EXPLAIN
-> Nested loop left join  (cost=2.02 rows=2.5)
    -> Nested loop inner join  (cost=1.15 rows=2)
        -> Filter: (t1.pk < 5)  (cost=0.45 rows=2)
            -> Table scan on t1  (cost=0.45 rows=2)
        -> Single-row covering index lookup on t2 using PRIMARY (pk=t1.pk)  (cost=0.3 rows=1)
    -> Index lookup on t3 using k2 (f2=t1.pk)  (cost=0.375 rows=1.25)

SELECT * FROM t1 LEFT JOIN t2 ON t1.pk = t2.pk LEFT JOIN t3 ON t2.pk = t3.f2 WHERE t2.pk < 5;
pk	pk	f1	f2
1	1	NULL	NULL
2	2	NULL	NULL
DROP TABLE t1, t2, t3;
#
# Bug #30654713: WL#13476: DIFFERENT ROWS WITH BATCHED KEY ACCESS LEFT JOIN
#
CREATE TABLE t1 (
col_int INTEGER,
a INTEGER,
b varchar(10),
KEY key_a (a)
);
INSERT INTO t1 VALUES (5,NULL,'p');
INSERT INTO t1 VALUES (6,NULL,'');
INSERT INTO t1 VALUES (7,NULL,'');
INSERT INTO t1 VALUES (8,NULL,'Z');
INSERT INTO t1 VALUES (9,4,'g');
INSERT INTO t1 VALUES (10,NULL,'if');
INSERT INTO t1 VALUES (11,NULL,'j');
INSERT INTO t1 VALUES (12,9,'');
CREATE TABLE t2 (
a INTEGER,
b varchar(10),
KEY key_b (b)
);
INSERT INTO t2 VALUES (1,'j');
INSERT INTO t2 VALUES (2,'o');
INSERT INTO t2 VALUES (3,'z');
INSERT INTO t2 VALUES (4,'really');
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN format=tree SELECT t1.col_int as t1_ci, t1.a as t1_a, t1.b as t1_b, t2.a as t2_a, t2.b as t2_b, t3.col_int as t3_ci, t3.a as t3_a, t3.b as t3_b
FROM t1
LEFT JOIN ( t2 LEFT JOIN t1 AS t3 ON t2.a=t3.a )
ON t1.b = t2.b;
EXPLAIN
-> Left hash join (t2.b = t1.b)  (cost=23.2 rows=144)
    -> Table scan on t1  (cost=1.05 rows=8)
    -> Hash
        -> Left hash join (t3.a = t2.a)  (cost=3.7 rows=24)
            -> Table scan on t2  (cost=0.0813 rows=4)
            -> Hash
                -> Table scan on t3  (cost=0.0439 rows=8)

SELECT t1.col_int as t1_ci, t1.a as t1_a, t1.b as t1_b, t2.a as t2_a, t2.b as t2_b, t3.col_int as t3_ci, t3.a as t3_a, t3.b as t3_b
FROM t1
LEFT JOIN ( t2 LEFT JOIN t1 AS t3 ON t2.a=t3.a )
ON t1.b = t2.b;
t1_ci	t1_a	t1_b	t2_a	t2_b	t3_ci	t3_a	t3_b
10	NULL	if	NULL	NULL	NULL	NULL	NULL
11	NULL	j	1	j	NULL	NULL	NULL
12	9		NULL	NULL	NULL	NULL	NULL
5	NULL	p	NULL	NULL	NULL	NULL	NULL
6	NULL		NULL	NULL	NULL	NULL	NULL
7	NULL		NULL	NULL	NULL	NULL	NULL
8	NULL	Z	3	z	NULL	NULL	NULL
9	4	g	NULL	NULL	NULL	NULL	NULL
DROP TABLE t1, t2;
#
# Bug#30520749 - REGRESSION: LEFT JOIN WITH IMPOSSIBLE ON CONDITION PERFORMS SLOWLY
#
set optimizer_switch='block_nested_loop=off';
CREATE TABLE t1 ( f1 INTEGER );
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
CREATE TABLE t2 AS SELECT * FROM t1;
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON 1=2;
EXPLAIN
-> Table scan on t1  (cost=0.75 rows=5)

DROP TABLE t1, t2;
set optimizer_switch='block_nested_loop=on';
#
# Bug #31252625: DATA IN WRONG ROW ON LEFT JOIN
#
CREATE TABLE t1 (
id INTEGER NOT NULL,
b INTEGER,
PRIMARY KEY (id)
);
INSERT INTO t1 VALUES (17,NULL);
INSERT INTO t1 VALUES (136,564);
INSERT INTO t1 VALUES (137,NULL);
CREATE TABLE t2 (
id INTEGER NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t2 VALUES (564);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
SELECT * FROM t1 LEFT JOIN t2 ON t1.b = t2.id GROUP BY t1.id;
id	b	id
17	NULL	NULL
136	564	564
137	NULL	NULL
DROP TABLE t1, t2;
#
# Bug#33639644: sig11 in CreateIteratorFromAccessPath
#
CREATE TABLE t1 (x INTEGER);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (x INTEGER);
INSERT INTO t2 VALUES (1), (2), (3), (4);
WITH subq(x) AS (SELECT 1 AS x FROM t1 AS t1a, t1 AS t1b, t2 WHERE FALSE)
SELECT 1 FROM t1 LEFT JOIN subq ON t1.x = subq.x ORDER BY subq.x;
1
1
DROP TABLE t1, t2;
#
# Bug#34668756: impossible on not optimized in 8.x like 5.6
#
CREATE TABLE t1 (f1 INT, f2 INT);
CREATE TABLE t2 (f1 INT, f2 INT);
CREATE TABLE t3 (f1 INT, f2 INT);
INSERT INTO t1 VALUES(1, 10), (2, 20), (3, 30), (4, 40);
INSERT INTO t2 VALUES(1, 10), (2, 20), (5, 30), (6, 40);
INSERT INTO t3 VALUES(1, 20), (3, 30), (7, 40), (8, 50);
ANALYZE TABLE t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
EXPLAIN
SELECT t1.f1
FROM t1 JOIN t2 ON t1.f1 = t2.f2 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` where false
EXPLAIN
SELECT t1.f1
FROM t1 JOIN t2 ON t1.f1 = t2.f2
WHERE 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` where false
EXPLAIN
SELECT t1.f1
FROM t1 JOIN t2 ON t1.f1 = t2.f2 AND 1=2 JOIN t3 ON t2.f1=t3.f1 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` join `test`.`t3` where false
EXPLAIN
SELECT t1.f1
FROM t1 JOIN t2 ON t1.f1 = t2.f2 JOIN t3 ON t2.f1=t3.f1
WHERE 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` join `test`.`t3` where false
EXPLAIN
SELECT t1.f1,t2.f1
FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1 AND t1.f2=t2.f2 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on(false) where true
EXPLAIN
SELECT t1.f1,t2.f1
FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1 AND t1.f2=t2.f2
WHERE 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on((multiple equal(`test`.`t1`.`f1`, `test`.`t2`.`f1`) and multiple equal(`test`.`t1`.`f2`, `test`.`t2`.`f2`))) where false
EXPLAIN
SELECT t1.f1,t2.f1
FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1 AND 1=2 LEFT JOIN t3 ON t2.f1=t3.f1 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t3	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on(false) left join `test`.`t3` on(false) where true
EXPLAIN
SELECT t1.f1,t2.f1
FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1 AND 1=2 LEFT JOIN t3 ON t2.f1=t3.f1
WHERE 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t2`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on(false) left join `test`.`t3` on(multiple equal(`test`.`t2`.`f1`, `test`.`t3`.`f1`)) where false
EXPLAIN
SELECT f1,f2
FROM t1 WHERE EXISTS (SELECT t2.f1,t2.f2 FROM t2 WHERE 1=2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where false
EXPLAIN
SELECT f1,f2
FROM t1 WHERE NOT EXISTS (SELECT t2.f1,t2.f2 FROM t2 WHERE 1=2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where true
EXPLAIN
SELECT t2.f1,t2.f2
FROM t1 RIGHT JOIN t2 ON t1.f1=t2.f1 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` left join `test`.`t1` on(false) where true
EXPLAIN
SELECT t2.f1,t2.f2
FROM t1 RIGHT JOIN t2 ON t1.f1=t2.f1 AND 1=2
WHERE t1.f1>NULL;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2` from `test`.`t2` join `test`.`t1` where false
EXPLAIN
SELECT t3.f1
FROM t1 JOIN t2 ON t1.f1=t2.f1 AND 1=2 LEFT JOIN t3 ON t1.f1=t3.f1 AND 1=2
WHERE 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(false) where false
EXPLAIN
SELECT t3.f1
FROM t1 JOIN t2 ON t1.f1=t2.f1 LEFT JOIN t3 ON t1.f1=t3.f1 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (hash join)
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(false) where (`test`.`t2`.`f1` = `test`.`t1`.`f1`)
EXPLAIN
SELECT t2.f1
FROM t1 RIGHT JOIN t2 ON t1.f1=t2.f1 AND 1=2 LEFT JOIN t3 ON t1.f1=t3.f1 AND 1=2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t3	NULL	const	NULL	NULL	NULL	NULL	1	100.00	Impossible ON condition
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`f1` AS `f1` from `test`.`t2` left join `test`.`t1` on(false) left join `test`.`t3` on(false) where true
DROP TABLES t1,t2,t3;
#
# Bug#36418426: Even though there is an index, the index is not being used enough when LEFT JOIN.
#
CREATE TABLE t1 (
id int NOT NULL,
f1 int NOT NULL,
f2 int NOT NULL,
f3 int NOT NULL,
f4 int NOT NULL,
f5 int NOT NULL,
PRIMARY KEY (id),
KEY idx1 (f2,f3,f4,f5)
);
INSERT INTO t1 VALUES (16, 25, 35, 45, 55, 65);
INSERT INTO t1 VALUES (17, 29, 39, 49, 59, 69);
INSERT INTO t1 VALUES (18, 23, 33, 43, 53, 63);
INSERT INTO t1 VALUES (19, 27, 37, 47, 57, 67);
CREATE TABLE t2 (
id int NOT NULL,
f1 int NOT NULL,
f2 int NOT NULL,
PRIMARY KEY (id),
KEY idx2 (f1,f2)
);
INSERT INTO t2 VALUES (36, 55, 65);
INSERT INTO t2 VALUES (37, 59, 69);
INSERT INTO t2 VALUES (38, 53, 63);
INSERT INTO t2 VALUES (39, 57, 67);
ANALYZE TABLE t1, t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN
SELECT * FROM t2 USE INDEX(idx2) LEFT JOIN t1 USE INDEX(idx1) ON t1.f2=t2.f2 AND t1.f3 = 20 and t1.f4 > 10 and t1.f5>20;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	index	NULL	idx2	8	NULL	4	100.00	Using index
1	SIMPLE	t1	NULL	ref	idx1	idx1	8	test.t2.f2,const	1	100.00	Using index condition
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`f1` AS `f1`,`test`.`t2`.`f2` AS `f2`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t1`.`f4` AS `f4`,`test`.`t1`.`f5` AS `f5` from `test`.`t2` USE INDEX (`idx2`) left join `test`.`t1` USE INDEX (`idx1`) on(((`test`.`t1`.`f3` = 20) and (`test`.`t1`.`f2` = `test`.`t2`.`f2`) and (`test`.`t1`.`f4` > 10) and (`test`.`t1`.`f5` > 20))) where true
DROP TABLES t1,t2;