set optimizer_switch='block_nested_loop=off,batched_key_access=off';
set optimizer_switch='mrr_cost_based=off';
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
set names utf8;
CREATE DATABASE world;
use world;
CREATE TABLE Country (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
Population int(11) NOT NULL default '0',
Capital int(11) default NULL
);
CREATE TABLE City (
ID int(11) NOT NULL,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0'
);
CREATE TABLE CountryLanguage (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0'
);
SELECT COUNT(*) FROM Country;
COUNT(*)
239
SELECT COUNT(*) FROM City;
COUNT(*)
4079
SELECT COUNT(*) FROM CountryLanguage;
COUNT(*)
984
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
La Ceiba	Honduras	Spanish
La Habana	Cuba	Spanish
La Matanza	Argentina	Spanish
La Paz	Bolivia	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
La Plata	Argentina	Spanish
La Rioja	Argentina	Spanish
La Romana	Dominican Republic	Spanish
La Serena	Chile	Spanish
La Spezia	Italy	Italian
Lafayette	United States	English
Lages	Brazil	Portuguese
Lagos de Moreno	Mexico	Spanish
Lahti	Finland	Finnish
Laiwu	China	Chinese
Laiyang	China	Chinese
Laizhou	China	Chinese
Lakewood	United States	English
Lalitapur	Nepal	Nepali
Lambaré	Paraguay	Spanish
Lancaster	United States	English
Langfang	China	Chinese
Lansing	United States	English
Lanzhou	China	Chinese
Lanús	Argentina	Spanish
Laohekou	China	Chinese
Laredo	United States	English
Larisa	Greece	Greek
Las Heras	Argentina	Spanish
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Las Vegas	United States	English
Lashio (Lasho)	Myanmar	Burmese
Latakia	Syria	Arabic
Latina	Italy	Italian
Lauro de Freitas	Brazil	Portuguese
Lausanne	Switzerland	German
Laval	Canada	English
Le Havre	France	French
Le Mans	France	French
Le-Cap-Haïtien	Haiti	Haiti Creole
Lecce	Italy	Italian
Leeds	United Kingdom	English
Leganés	Spain	Spanish
Legnica	Poland	Polish
Leicester	United Kingdom	English
Leiden	Netherlands	Dutch
Leipzig	Germany	German
Leiyang	China	Chinese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Lerdo	Mexico	Spanish
Lerma	Mexico	Spanish
Leshan	China	Chinese
Leverkusen	Germany	German
Lexington-Fayette	United States	English
León	Mexico	Spanish
León	Nicaragua	Spanish
León	Spain	Spanish
Lhasa	China	Chinese
Liangcheng	China	Chinese
Lianyuan	China	Chinese
Lianyungang	China	Chinese
Liaocheng	China	Chinese
Liaoyang	China	Chinese
Liaoyuan	China	Chinese
Liberec	Czech Republic	Czech
Lida	Belarus	Belorussian
Liling	China	Chinese
Lille	France	French
Lilongwe	Malawi	Chichewa
Lima	Peru	Spanish
Limeira	Brazil	Portuguese
Limoges	France	French
Linchuan	China	Chinese
Lincoln	United States	English
Linfen	China	Chinese
Linhai	China	Chinese
Linhares	Brazil	Portuguese
Linhe	China	Chinese
Linköping	Sweden	Swedish
Linqing	China	Chinese
Linyi	China	Chinese
Linz	Austria	German
Lipetsk	Russian Federation	Russian
Lisboa	Portugal	Portuguese
Little Rock	United States	English
Liupanshui	China	Chinese
Liuzhou	China	Chinese
Liu´an	China	Chinese
Liverpool	United Kingdom	English
Livonia	United States	English
Livorno	Italy	Italian
Liyang	China	Chinese
Liège	Belgium	Dutch
Ljubertsy	Russian Federation	Russian
Lleida (Lérida)	Spain	Spanish
Logroño	Spain	Spanish
Loja	Ecuador	Spanish
Lomas de Zamora	Argentina	Spanish
London	Canada	English
London	United Kingdom	English
Londrina	Brazil	Portuguese
Long Beach	United States	English
Long Xuyen	Vietnam	Vietnamese
Longjing	China	Chinese
Longkou	China	Chinese
Longueuil	Canada	English
Longyan	China	Chinese
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Cabos	Mexico	Spanish
Los Teques	Venezuela	Spanish
Loudi	China	Chinese
Louisville	United States	English
Lowell	United States	English
Lower Hutt	New Zealand	English
Lubbock	United States	English
Lublin	Poland	Polish
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lugansk	Ukraine	Ukrainian
Lund	Sweden	Swedish
Lungtan	Taiwan	Min
Luohe	China	Chinese
Luoyang	China	Chinese
Luton	United Kingdom	English
Lutsk	Ukraine	Ukrainian
Luxor	Egypt	Arabic
Luzhou	China	Chinese
Luziânia	Brazil	Portuguese
Lviv	Ukraine	Ukrainian
Lyon	France	French
Lysyt?ansk	Ukraine	Ukrainian
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lódz	Poland	Polish
Lübeck	Germany	German
Lünen	Germany	German
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
La Ceiba	Honduras	Spanish
La Habana	Cuba	Spanish
La Matanza	Argentina	Spanish
La Paz	Bolivia	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
La Plata	Argentina	Spanish
La Rioja	Argentina	Spanish
La Romana	Dominican Republic	Spanish
La Serena	Chile	Spanish
La Spezia	Italy	Italian
Lafayette	United States	English
Lages	Brazil	Portuguese
Lagos de Moreno	Mexico	Spanish
Lahti	Finland	Finnish
Laiwu	China	Chinese
Laiyang	China	Chinese
Laizhou	China	Chinese
Lakewood	United States	English
Lalitapur	Nepal	Nepali
Lambaré	Paraguay	Spanish
Lancaster	United States	English
Langfang	China	Chinese
Lansing	United States	English
Lanzhou	China	Chinese
Lanús	Argentina	Spanish
Laohekou	China	Chinese
Laredo	United States	English
Larisa	Greece	Greek
Las Heras	Argentina	Spanish
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Las Vegas	United States	English
Lashio (Lasho)	Myanmar	Burmese
Latakia	Syria	Arabic
Latina	Italy	Italian
Lauro de Freitas	Brazil	Portuguese
Lausanne	Switzerland	German
Laval	Canada	English
Le Havre	France	French
Le Mans	France	French
Le-Cap-Haïtien	Haiti	Haiti Creole
Lecce	Italy	Italian
Leeds	United Kingdom	English
Leganés	Spain	Spanish
Legnica	Poland	Polish
Leicester	United Kingdom	English
Leiden	Netherlands	Dutch
Leipzig	Germany	German
Leiyang	China	Chinese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Lerdo	Mexico	Spanish
Lerma	Mexico	Spanish
Leshan	China	Chinese
Leverkusen	Germany	German
Lexington-Fayette	United States	English
León	Mexico	Spanish
León	Nicaragua	Spanish
León	Spain	Spanish
Lhasa	China	Chinese
Liangcheng	China	Chinese
Lianyuan	China	Chinese
Lianyungang	China	Chinese
Liaocheng	China	Chinese
Liaoyang	China	Chinese
Liaoyuan	China	Chinese
Liberec	Czech Republic	Czech
Lida	Belarus	Belorussian
Liling	China	Chinese
Lille	France	French
Lilongwe	Malawi	Chichewa
Lima	Peru	Spanish
Limeira	Brazil	Portuguese
Limoges	France	French
Linchuan	China	Chinese
Lincoln	United States	English
Linfen	China	Chinese
Linhai	China	Chinese
Linhares	Brazil	Portuguese
Linhe	China	Chinese
Linköping	Sweden	Swedish
Linqing	China	Chinese
Linyi	China	Chinese
Linz	Austria	German
Lipetsk	Russian Federation	Russian
Lisboa	Portugal	Portuguese
Little Rock	United States	English
Liupanshui	China	Chinese
Liuzhou	China	Chinese
Liu´an	China	Chinese
Liverpool	United Kingdom	English
Livonia	United States	English
Livorno	Italy	Italian
Liyang	China	Chinese
Liège	Belgium	Dutch
Ljubertsy	Russian Federation	Russian
Lleida (Lérida)	Spain	Spanish
Logroño	Spain	Spanish
Loja	Ecuador	Spanish
Lomas de Zamora	Argentina	Spanish
London	Canada	English
London	United Kingdom	English
Londrina	Brazil	Portuguese
Long Beach	United States	English
Long Xuyen	Vietnam	Vietnamese
Longjing	China	Chinese
Longkou	China	Chinese
Longueuil	Canada	English
Longyan	China	Chinese
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Cabos	Mexico	Spanish
Los Teques	Venezuela	Spanish
Loudi	China	Chinese
Louisville	United States	English
Lowell	United States	English
Lower Hutt	New Zealand	English
Lubbock	United States	English
Lublin	Poland	Polish
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lugansk	Ukraine	Ukrainian
Lund	Sweden	Swedish
Lungtan	Taiwan	Min
Luohe	China	Chinese
Luoyang	China	Chinese
Luton	United Kingdom	English
Lutsk	Ukraine	Ukrainian
Luxor	Egypt	Arabic
Luzhou	China	Chinese
Luziânia	Brazil	Portuguese
Lviv	Ukraine	Ukrainian
Lyon	France	French
Lysyt?ansk	Ukraine	Ukrainian
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lódz	Poland	Polish
Lübeck	Germany	German
Lünen	Germany	German
set join_buffer_size=default;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
DROP DATABASE world;
CREATE DATABASE world;
use world;
CREATE TABLE Country (
Code char(3) NOT NULL default '',
Name char(52) NOT NULL default '',
SurfaceArea float(10,2) NOT NULL default '0.00',
Population int(11) NOT NULL default '0',
Capital int(11) default NULL,
PRIMARY KEY  (Code),
UNIQUE INDEX (Name)
);
CREATE TABLE City (
ID int(11) NOT NULL auto_increment,
Name char(35) NOT NULL default '',
Country char(3) NOT NULL default '',
Population int(11) NOT NULL default '0',
PRIMARY KEY  (ID),
INDEX (Population),
INDEX (Country) 
);
CREATE TABLE CountryLanguage (
Country char(3) NOT NULL default '',
Language char(30) NOT NULL default '',
Percentage float(3,1) NOT NULL default '0.0',
PRIMARY KEY  (Country, Language),
INDEX (Percentage)
);
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
La Ceiba	Honduras	Spanish
La Habana	Cuba	Spanish
La Matanza	Argentina	Spanish
La Paz	Bolivia	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
La Plata	Argentina	Spanish
La Rioja	Argentina	Spanish
La Romana	Dominican Republic	Spanish
La Serena	Chile	Spanish
La Spezia	Italy	Italian
Lafayette	United States	English
Lages	Brazil	Portuguese
Lagos de Moreno	Mexico	Spanish
Lahti	Finland	Finnish
Laiwu	China	Chinese
Laiyang	China	Chinese
Laizhou	China	Chinese
Lakewood	United States	English
Lalitapur	Nepal	Nepali
Lambaré	Paraguay	Spanish
Lancaster	United States	English
Langfang	China	Chinese
Lansing	United States	English
Lanzhou	China	Chinese
Lanús	Argentina	Spanish
Laohekou	China	Chinese
Laredo	United States	English
Larisa	Greece	Greek
Las Heras	Argentina	Spanish
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Las Vegas	United States	English
Lashio (Lasho)	Myanmar	Burmese
Latakia	Syria	Arabic
Latina	Italy	Italian
Lauro de Freitas	Brazil	Portuguese
Lausanne	Switzerland	German
Laval	Canada	English
Le Havre	France	French
Le Mans	France	French
Le-Cap-Haïtien	Haiti	Haiti Creole
Lecce	Italy	Italian
Leeds	United Kingdom	English
Leganés	Spain	Spanish
Legnica	Poland	Polish
Leicester	United Kingdom	English
Leiden	Netherlands	Dutch
Leipzig	Germany	German
Leiyang	China	Chinese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Lerdo	Mexico	Spanish
Lerma	Mexico	Spanish
Leshan	China	Chinese
Leverkusen	Germany	German
Lexington-Fayette	United States	English
León	Mexico	Spanish
León	Nicaragua	Spanish
León	Spain	Spanish
Lhasa	China	Chinese
Liangcheng	China	Chinese
Lianyuan	China	Chinese
Lianyungang	China	Chinese
Liaocheng	China	Chinese
Liaoyang	China	Chinese
Liaoyuan	China	Chinese
Liberec	Czech Republic	Czech
Lida	Belarus	Belorussian
Liling	China	Chinese
Lille	France	French
Lilongwe	Malawi	Chichewa
Lima	Peru	Spanish
Limeira	Brazil	Portuguese
Limoges	France	French
Linchuan	China	Chinese
Lincoln	United States	English
Linfen	China	Chinese
Linhai	China	Chinese
Linhares	Brazil	Portuguese
Linhe	China	Chinese
Linköping	Sweden	Swedish
Linqing	China	Chinese
Linyi	China	Chinese
Linz	Austria	German
Lipetsk	Russian Federation	Russian
Lisboa	Portugal	Portuguese
Little Rock	United States	English
Liupanshui	China	Chinese
Liuzhou	China	Chinese
Liu´an	China	Chinese
Liverpool	United Kingdom	English
Livonia	United States	English
Livorno	Italy	Italian
Liyang	China	Chinese
Liège	Belgium	Dutch
Ljubertsy	Russian Federation	Russian
Lleida (Lérida)	Spain	Spanish
Logroño	Spain	Spanish
Loja	Ecuador	Spanish
Lomas de Zamora	Argentina	Spanish
London	Canada	English
London	United Kingdom	English
Londrina	Brazil	Portuguese
Long Beach	United States	English
Long Xuyen	Vietnam	Vietnamese
Longjing	China	Chinese
Longkou	China	Chinese
Longueuil	Canada	English
Longyan	China	Chinese
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Cabos	Mexico	Spanish
Los Teques	Venezuela	Spanish
Loudi	China	Chinese
Louisville	United States	English
Lowell	United States	English
Lower Hutt	New Zealand	English
Lubbock	United States	English
Lublin	Poland	Polish
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lugansk	Ukraine	Ukrainian
Lund	Sweden	Swedish
Lungtan	Taiwan	Min
Luohe	China	Chinese
Luoyang	China	Chinese
Luton	United Kingdom	English
Lutsk	Ukraine	Ukrainian
Luxor	Egypt	Arabic
Luzhou	China	Chinese
Luziânia	Brazil	Portuguese
Lviv	Ukraine	Ukrainian
Lyon	France	French
Lysyt?ansk	Ukraine	Ukrainian
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lódz	Poland	Polish
Lübeck	Germany	German
Lünen	Germany	German
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
?iauliai
Beirut
Bengasi
Daugavpils
Kaunas
Klaipeda
Maseru
Misrata
Monrovia
Panevezys
Riga
Tripoli
Tripoli
Vientiane
Vilnius
EXPLAIN
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where
SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
FROM Country LEFT JOIN CountryLanguage ON
(CountryLanguage.Country=Country.Code AND Language='English')
WHERE 
Country.Population > 10000000;
Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
Afghanistan	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Australia	81.2
Bangladesh	NULL
Belarus	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Cambodia	NULL
Cameroon	NULL
Canada	60.4
Chile	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
Cuba	NULL
Czech Republic	NULL
Côte d?Ivoire	NULL
Ecuador	NULL
Egypt	NULL
Ethiopia	NULL
France	NULL
Germany	NULL
Ghana	NULL
Greece	NULL
Guatemala	NULL
Hungary	NULL
India	NULL
Indonesia	NULL
Iran	NULL
Iraq	NULL
Italy	NULL
Japan	0.1
Kazakstan	NULL
Kenya	NULL
Madagascar	NULL
Malawi	NULL
Malaysia	1.6
Mali	NULL
Mexico	NULL
Morocco	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Netherlands	NULL
Niger	NULL
Nigeria	NULL
North Korea	NULL
Pakistan	NULL
Peru	NULL
Philippines	NULL
Poland	NULL
Romania	NULL
Russian Federation	NULL
Saudi Arabia	NULL
Somalia	NULL
South Africa	8.5
South Korea	NULL
Spain	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
United Kingdom	97.3
United States	86.2
Uzbekistan	NULL
Venezuela	NULL
Vietnam	NULL
Yemen	NULL
Yugoslavia	NULL
Zimbabwe	2.2
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
EXPLAIN
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	CountryLanguage	range	PRIMARY,Percentage	Percentage	4	NULL	#	Using index condition; Using MRR
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
CountryLanguage.Country=Country.Code AND
City.Name LIKE 'L%' AND Country.Population > 3000000 AND
CountryLanguage.Percentage > 50;
Name	Name	Language
La Ceiba	Honduras	Spanish
La Habana	Cuba	Spanish
La Matanza	Argentina	Spanish
La Paz	Bolivia	Spanish
La Paz	Mexico	Spanish
La Paz	Mexico	Spanish
La Plata	Argentina	Spanish
La Rioja	Argentina	Spanish
La Romana	Dominican Republic	Spanish
La Serena	Chile	Spanish
La Spezia	Italy	Italian
Lafayette	United States	English
Lages	Brazil	Portuguese
Lagos de Moreno	Mexico	Spanish
Lahti	Finland	Finnish
Laiwu	China	Chinese
Laiyang	China	Chinese
Laizhou	China	Chinese
Lakewood	United States	English
Lalitapur	Nepal	Nepali
Lambaré	Paraguay	Spanish
Lancaster	United States	English
Langfang	China	Chinese
Lansing	United States	English
Lanzhou	China	Chinese
Lanús	Argentina	Spanish
Laohekou	China	Chinese
Laredo	United States	English
Larisa	Greece	Greek
Las Heras	Argentina	Spanish
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Las Vegas	United States	English
Lashio (Lasho)	Myanmar	Burmese
Latakia	Syria	Arabic
Latina	Italy	Italian
Lauro de Freitas	Brazil	Portuguese
Lausanne	Switzerland	German
Laval	Canada	English
Le Havre	France	French
Le Mans	France	French
Le-Cap-Haïtien	Haiti	Haiti Creole
Lecce	Italy	Italian
Leeds	United Kingdom	English
Leganés	Spain	Spanish
Legnica	Poland	Polish
Leicester	United Kingdom	English
Leiden	Netherlands	Dutch
Leipzig	Germany	German
Leiyang	China	Chinese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Lerdo	Mexico	Spanish
Lerma	Mexico	Spanish
Leshan	China	Chinese
Leverkusen	Germany	German
Lexington-Fayette	United States	English
León	Mexico	Spanish
León	Nicaragua	Spanish
León	Spain	Spanish
Lhasa	China	Chinese
Liangcheng	China	Chinese
Lianyuan	China	Chinese
Lianyungang	China	Chinese
Liaocheng	China	Chinese
Liaoyang	China	Chinese
Liaoyuan	China	Chinese
Liberec	Czech Republic	Czech
Lida	Belarus	Belorussian
Liling	China	Chinese
Lille	France	French
Lilongwe	Malawi	Chichewa
Lima	Peru	Spanish
Limeira	Brazil	Portuguese
Limoges	France	French
Linchuan	China	Chinese
Lincoln	United States	English
Linfen	China	Chinese
Linhai	China	Chinese
Linhares	Brazil	Portuguese
Linhe	China	Chinese
Linköping	Sweden	Swedish
Linqing	China	Chinese
Linyi	China	Chinese
Linz	Austria	German
Lipetsk	Russian Federation	Russian
Lisboa	Portugal	Portuguese
Little Rock	United States	English
Liupanshui	China	Chinese
Liuzhou	China	Chinese
Liu´an	China	Chinese
Liverpool	United Kingdom	English
Livonia	United States	English
Livorno	Italy	Italian
Liyang	China	Chinese
Liège	Belgium	Dutch
Ljubertsy	Russian Federation	Russian
Lleida (Lérida)	Spain	Spanish
Logroño	Spain	Spanish
Loja	Ecuador	Spanish
Lomas de Zamora	Argentina	Spanish
London	Canada	English
London	United Kingdom	English
Londrina	Brazil	Portuguese
Long Beach	United States	English
Long Xuyen	Vietnam	Vietnamese
Longjing	China	Chinese
Longkou	China	Chinese
Longueuil	Canada	English
Longyan	China	Chinese
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Cabos	Mexico	Spanish
Los Teques	Venezuela	Spanish
Loudi	China	Chinese
Louisville	United States	English
Lowell	United States	English
Lower Hutt	New Zealand	English
Lubbock	United States	English
Lublin	Poland	Polish
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lugansk	Ukraine	Ukrainian
Lund	Sweden	Swedish
Lungtan	Taiwan	Min
Luohe	China	Chinese
Luoyang	China	Chinese
Luton	United Kingdom	English
Lutsk	Ukraine	Ukrainian
Luxor	Egypt	Arabic
Luzhou	China	Chinese
Luziânia	Brazil	Portuguese
Lviv	Ukraine	Ukrainian
Lyon	France	French
Lysyt?ansk	Ukraine	Ukrainian
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
Lódz	Poland	Polish
Lübeck	Germany	German
Lünen	Germany	German
EXPLAIN
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
?iauliai
Beirut
Bengasi
Daugavpils
Kaunas
Klaipeda
Maseru
Misrata
Monrovia
Panevezys
Riga
Tripoli
Tripoli
Vientiane
Vilnius
set join_buffer_size=default;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
Name	Name
Alexandria	Egypt
Ankara	Turkey
Baghdad	Iraq
Bangkok	Thailand
Berlin	Germany
Cairo	Egypt
Calcutta [Kolkata]	India
Chengdu	China
Chennai (Madras)	India
Chongqing	China
Ciudad de México	Mexico
Delhi	India
Dhaka	Bangladesh
Harbin	China
Ho Chi Minh City	Vietnam
Istanbul	Turkey
Jakarta	Indonesia
Jokohama [Yokohama]	Japan
Kanton [Guangzhou]	China
Karachi	Pakistan
Kinshasa	Congo, The Democratic Republic of the
Lahore	Pakistan
Lima	Peru
London	United Kingdom
Los Angeles	United States
Moscow	Russian Federation
Mumbai (Bombay)	India
New York	United States
Peking	China
Pusan	South Korea
Rangoon (Yangon)	Myanmar
Rio de Janeiro	Brazil
Riyadh	Saudi Arabia
Santafé de Bogotá	Colombia
Santiago de Chile	Chile
Seoul	South Korea
Shanghai	China
Shenyang	China
Singapore	Singapore
St Petersburg	Russian Federation
Sydney	Australia
São Paulo	Brazil
Teheran	Iran
Tianjin	China
Tokyo	Japan
Wuhan	China
set join_buffer_size=256;
EXPLAIN
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	range	Population,Country	Population	4	NULL	#	Using index condition; Using MRR
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	NULL
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND City.Population > 3000000;
Name	Name
Alexandria	Egypt
Ankara	Turkey
Baghdad	Iraq
Bangkok	Thailand
Berlin	Germany
Cairo	Egypt
Calcutta [Kolkata]	India
Chengdu	China
Chennai (Madras)	India
Chongqing	China
Ciudad de México	Mexico
Delhi	India
Dhaka	Bangladesh
Harbin	China
Ho Chi Minh City	Vietnam
Istanbul	Turkey
Jakarta	Indonesia
Jokohama [Yokohama]	Japan
Kanton [Guangzhou]	China
Karachi	Pakistan
Kinshasa	Congo, The Democratic Republic of the
Lahore	Pakistan
Lima	Peru
London	United Kingdom
Los Angeles	United States
Moscow	Russian Federation
Mumbai (Bombay)	India
New York	United States
Peking	China
Pusan	South Korea
Rangoon (Yangon)	Myanmar
Rio de Janeiro	Brazil
Riyadh	Saudi Arabia
Santafé de Bogotá	Colombia
Santiago de Chile	Chile
Seoul	South Korea
Shanghai	China
Shenyang	China
Singapore	Singapore
St Petersburg	Russian Federation
Sydney	Australia
São Paulo	Brazil
Teheran	Iran
Tianjin	China
Tokyo	Japan
Wuhan	China
set join_buffer_size=default;
ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default '';
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default '';
SELECT City.Name, Country.Name FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name
?iauliai	Lithuania
Beirut	Lebanon
Bengasi	Libyan Arab Jamahiriya
Daugavpils	Latvia
Kaunas	Lithuania
Klaipeda	Lithuania
Maseru	Lesotho
Misrata	Libyan Arab Jamahiriya
Monrovia	Liberia
Panevezys	Lithuania
Riga	Latvia
Tripoli	Lebanon
Tripoli	Libyan Arab Jamahiriya
Vientiane	Laos
Vilnius	Lithuania
ALTER TABLE Country ADD COLUMN PopulationBar text;
UPDATE Country 
SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int));
SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name	PopulationBar
?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
set join_buffer_size=256;
SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
WHERE City.Country=Country.Code AND 
Country.Name LIKE 'L%' AND City.Population > 100000;
Name	Name	PopulationBar
?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
set join_buffer_size=default;
DROP DATABASE world;
use test;
CREATE TABLE t1(
affiliatetometaid int  NOT NULL default '0',
uniquekey int NOT NULL default '0',
metaid int  NOT NULL default '0',
affiliateid int  NOT NULL default '0',
xml text,
isactive char(1) NOT NULL default 'Y',
PRIMARY KEY  (affiliatetometaid)
);
CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey);
CREATE INDEX t1_affiliateid ON t1(affiliateid);
CREATE INDEX t1_metaid on t1 (metaid);
INSERT INTO t1 VALUES
(1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y');
CREATE TABLE t2(
metaid int  NOT NULL default '0',
name varchar(80) NOT NULL default '',
dateadded timestamp NOT NULL ,
xml text,
status int default NULL,
origin int default NULL,
gid int NOT NULL default '1',
formattypeid int  default NULL,
PRIMARY KEY  (metaid)
);
CREATE INDEX t2_status ON t2(status);
CREATE INDEX t2_gid ON t2(gid);
CREATE INDEX t2_formattypeid ON t2(formattypeid);
INSERT INTO t2 VALUES
(1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL),
(1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL);
CREATE TABLE t3(
mediaid int  NOT NULL ,
metaid int  NOT NULL default '0',
formatid int  NOT NULL default '0',
status int default NULL,
path varchar(100) NOT NULL default '',
datemodified timestamp NOT NULL ,
resourcetype int  NOT NULL default '1',
parameters text,
signature int  default NULL,
quality int  NOT NULL default '255',
PRIMARY KEY  (mediaid)
);
CREATE INDEX t3_metaid ON t3(metaid);
CREATE INDEX t3_formatid ON t3(formatid);
CREATE INDEX t3_status ON t3(status);
CREATE INDEX t3_metaidformatid ON t3(metaid,formatid);
CREATE INDEX t3_signature ON t3(signature);
CREATE INDEX t3_quality ON t3(quality);
INSERT INTO t3 VALUES
(6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255),
(3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255);
CREATE TABLE t4(
formatid int  NOT NULL ,
name varchar(60) NOT NULL default '',
formatclassid int  NOT NULL default '0',
mime varchar(60) default NULL,
extension varchar(10) default NULL,
priority int NOT NULL default '0',
canaddtocapability char(1) NOT NULL default 'Y',
PRIMARY KEY  (formatid)
);
CREATE INDEX t4_formatclassid ON t4(formatclassid);
CREATE INDEX t4_formats_idx ON t4(canaddtocapability);
INSERT INTO t4 VALUES
(19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'),
(54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y');
CREATE TABLE t5(
formatclassid int  NOT NULL ,
name varchar(60) NOT NULL default '',
priority int NOT NULL default '0',
formattypeid int  NOT NULL default '0',
PRIMARY KEY  (formatclassid)
);
CREATE INDEX t5_formattypeid on t5(formattypeid);
INSERT INTO t5 VALUES
(11, "Info", 0, 4), (13, "Digital Audio", 0, 2);
CREATE TABLE t6(
formattypeid int  NOT NULL ,
name varchar(60) NOT NULL default '',
priority int default NULL,
PRIMARY KEY  (formattypeid)
);
INSERT INTO t6 VALUES
(2, "Ringtones", 0);
CREATE TABLE t7(
metaid int  NOT NULL default '0',
artistid int  NOT NULL default '0',
PRIMARY KEY  (metaid,artistid)
);
INSERT INTO t7 VALUES
(4, 5), (3, 4);
CREATE TABLE t8(
artistid int  NOT NULL ,
name varchar(80) NOT NULL default '',
PRIMARY KEY  (artistid)
);
INSERT INTO t8 VALUES
(5, "Anastacia"), (4, "John Mayer");
CREATE TABLE t9(
subgenreid int  NOT NULL default '0',
metaid int  NOT NULL default '0',
PRIMARY KEY  (subgenreid,metaid)
) ;
CREATE INDEX t9_subgenreid ON t9(subgenreid);
CREATE INDEX t9_metaid ON t9(metaid);
INSERT INTO t9 VALUES 
(138, 4), (31, 3);
CREATE TABLE t10(
subgenreid int  NOT NULL ,
genreid int  NOT NULL default '0',
name varchar(80) NOT NULL default '',
PRIMARY KEY  (subgenreid)
) ;
CREATE INDEX t10_genreid ON t10(genreid);
INSERT INTO t10 VALUES 
(138, 19, ''), (31, 3, '');
CREATE TABLE t11(
genreid int  NOT NULL default '0',
name char(80) NOT NULL default '',
priority int NOT NULL default '0',
masterclip char(1) default NULL,
PRIMARY KEY  (genreid)
) ;
CREATE INDEX t11_masterclip ON t11( masterclip);
INSERT INTO t11 VALUES
(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y');
EXPLAIN
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid, 
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
t10.subgenreid, t10.name AS subgenreName,
t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
t4.priority + t5.priority + t6.priority AS overallPriority,
t3.path AS path, t3.mediaid, 
t4.formatid, t4.name AS formatName, 
t5.formatclassid, t5.name AS formatclassName, 
t6.formattypeid, t6.name AS formattypeName 
FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
t1.metaid = t2.metaid AND t1.affiliateid = '2';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t6	system	PRIMARY	NULL	NULL	NULL	1	NULL
1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	NULL
1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	NULL
1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	NULL
1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	NULL
1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	NULL
1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	NULL
1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
SELECT t1.uniquekey, t1.xml AS affiliateXml,
t8.name AS artistName, t8.artistid, 
t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
t10.subgenreid, t10.name AS subgenreName,
t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
t4.priority + t5.priority + t6.priority AS overallPriority,
t3.path AS path, t3.mediaid, 
t4.formatid, t4.name AS formatName, 
t5.formatclassid, t5.name AS formatclassName, 
t6.formattypeid, t6.name AS formattypeName 
FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
t1.metaid = t2.metaid AND t1.affiliateid = '2';
uniquekey	affiliateXml	artistName	artistid	genreName	genreid	genrePriority	subgenreid	subgenreName	metaName	metaid	metaXml	overallPriority	path	mediaid	formatid	formatName	formatclassid	formatclassName	formattypeid	formattypeName
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' );
CREATE TABLE t2 (
a2 int, b2 int, filler2 char(64) default ' ', 
PRIMARY KEY idx(a2,b2,filler2)
) ;
CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3));
INSERT INTO t1(a1) VALUES 
(4), (7), (1), (9), (8), (5), (3), (6), (2);
INSERT INTO t2(a2,b2) VALUES
(1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56),
(4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81),
(5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51),
(9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79),
(3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11);
INSERT INTO t3 VALUES
(30,302), (92,923), (18,187), (45,459), (30,309), 
(39,393), (68,685), (45,458), (21,210), (81,817),
(40,405), (61,618), (73,738), (92,929), (27,275),
(18,188), (84,846), (56,564), (14,144), (76,763), 
(98,982), (55,551), (17,174), (99,998), (51,513),
(28,282), (52,527), (33,336), (13,138), (87,878), 
(43,431), (91,916), (62,624), (79,797), (49,494),
(93,933), (34,347), (82,829), (78,780), (63,634), 
(32,329), (22,228), (11,114), (74,749), (23,236);
EXPLAIN
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
a1<>a2	a1	a2	b2	b3	c3	s1	s2
0	1	1	30	30	309		
0	1	1	32	32	329		
0	2	2	61	61	618		
0	3	3	45	45	458		
0	3	3	45	45	459		
0	4	4	13	13	138		
0	4	4	18	18	188		
0	5	5	82	82	829		
0	5	5	87	87	878		
0	6	6	73	73	738		
0	6	6	74	74	749		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	9	9	22	22	228		
set join_buffer_size=512;
EXPLAIN
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where
SELECT a1<>a2, a1, a2, b2, b3, c3,
SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
a1<>a2	a1	a2	b2	b3	c3	s1	s2
0	1	1	30	30	309		
0	1	1	32	32	329		
0	2	2	61	61	618		
0	3	3	45	45	458		
0	3	3	45	45	459		
0	4	4	13	13	138		
0	4	4	18	18	188		
0	5	5	82	82	829		
0	5	5	87	87	878		
0	6	6	73	73	738		
0	6	6	74	74	749		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	9	9	22	22	228		
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a int, b int, INDEX idx(b));
CREATE TABLE t2 (a int, b int, INDEX idx(a));
INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20);
INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20);
INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20);
set join_buffer_size=32;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '32'
EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx	idx	5	NULL	4	Using index condition; Using where; Using MRR
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	NULL
SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
a	b	a	b
7	40	7	10
7	40	7	10
7	40	7	20
8	30	8	10
8	30	8	20
DROP TABLE t1,t2;

BUG#40136: Group by is ignored when join buffer is used for an outer join

create table t1(a int PRIMARY KEY, b int);
insert into t1 values
(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
create table t2 (p int, a int, INDEX i_a(a));
insert into t2 values
(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	8	Using index
1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
a	count
1	1
2	0
3	2
5	0
6	0
7	2
8	0
9	0
drop table t1, t2;
#
# Bug #40134: outer join with not exists optimization and join buffer
#
set join_buffer_size=default;
CREATE TABLE t1 (a int NOT NULL);
INSERT INTO t1 VALUES (2), (4), (3), (5), (1);
CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a));
INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20);
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists
SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
a	a	b
3	NULL	NULL
5	NULL	NULL
1	NULL	NULL
DROP TABLE t1, t2;
#
# BUG#40268: Nested outer join with not null-rejecting where condition
#            over an inner table which is not the last in the nest
#
CREATE TABLE t2 (a int, b int, c int);
CREATE TABLE t3 (a int, b int, c int);
CREATE TABLE t4 (a int, b int, c int);
INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
INSERT INTO t3 VALUES (1,2,0), (2,2,0);
INSERT INTO t4 VALUES (3,2,0), (4,2,0);
SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b
WHERE t3.a+2<t2.a OR t3.c IS NULL;
a	b	a	b	a	b
3	3	NULL	NULL	NULL	NULL
4	2	1	2	3	2
4	2	1	2	4	2
5	3	NULL	NULL	NULL	NULL
DROP TABLE t2, t3, t4;
#
# Bug #40192: outer join with where clause when using BNL 
#
create table t1 (a int, b int);
insert into t1 values (2, 20), (3, 30), (1, 10);
create table t2 (a int, c int);
insert into t2 values (1, 101), (3, 102), (1, 100);
select * from t1 left join t2 on t1.a=t2.a;
a	b	a	c
1	10	1	100
1	10	1	101
2	20	NULL	NULL
3	30	3	102
explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
a	b	a	c
2	20	NULL	NULL
3	30	3	102
drop table t1, t2;
#
# Bug #40317: outer join with with constant on expression equal to FALSE
#
create table t1 (a int);
insert into t1 values (30), (40), (20);
create table t2 (b int);
insert into t2 values (200), (100);
select * from t1 left join t2 on (1=0);
a	b
30	NULL
40	NULL
20	NULL
explain select * from t1 left join t2 on (1=0) where a=40;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
select * from t1 left join t2 on (1=0) where a=40;
a	b
40	NULL
drop table t1, t2;
#
# Bug #41204: small buffer with big rec_per_key for ref access
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (0);
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1(a) SELECT a FROM t1;
INSERT INTO t1 VALUES (20000), (10000);
CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b));
INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5);
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
INSERT INTO t2(b,c) SELECT b,c FROM t2;
ANALYZE TABLE t1,t2;
set join_buffer_size=1024;
EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2050	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	640	NULL
SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
AVG(c)
5.0000
set join_buffer_size=default;
DROP TABLE t1, t2;
#
# Bug #41894: big join buffer of level 7 used to join records
#              with null values in place of varchar strings
#
CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY,
b varchar(127) DEFAULT NULL);
INSERT INTO t1(a) VALUES (1);
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
INSERT INTO t1(b) SELECT b FROM t1;
CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
INSERT INTO t2 SELECT * FROM t1;
CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
INSERT INTO t3 SELECT * FROM t1;
set join_buffer_size=1024*1024;
EXPLAIN
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
SELECT COUNT(*) FROM t1,t2,t3
WHERE t1.a=t2.a AND t2.a=t3.a AND
t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
COUNT(*)
16384
set join_buffer_size=default;
DROP TABLE t1,t2,t3;
#
# Bug #42020: join buffer is used  for outer join with fields of 
#             several outer tables in join buffer
#
CREATE TABLE t1 (
a bigint NOT NULL,
PRIMARY KEY (a) 
);
INSERT INTO t1 VALUES
(2), (1);
CREATE TABLE t2 (
a bigint NOT NULL,
b bigint NOT NULL,
PRIMARY KEY (a,b)
);
INSERT INTO t2 VALUES
(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
(1,10), (1, 20), (1,30), (1,40), (1,50);
CREATE TABLE t3 (
pk bigint NOT NULL AUTO_INCREMENT,
a bigint NOT NULL,
b bigint NOT NULL,
val bigint DEFAULT '0',
PRIMARY KEY (pk),
KEY idx (a,b)
);
INSERT INTO t3(a,b) VALUES
(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
(4,30), (4,40), (4,50), (4,60), (4,70), (4,80),
(5,30), (5,40), (5,50), (5,60), (5,70), (5,80),
(7,30), (7,40), (7,50), (7,60), (7,70), (7,80);
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
set join_buffer_size=256;
EXPLAIN
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
1	SIMPLE	t3	ref	idx	idx	16	test.t1.a,test.t2.b	2	NULL
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
DROP INDEX idx ON t3;
EXPLAIN
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	24	Using where
SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
WHERE t1.a=t2.a;
a	a	a	b	b	val
1	1	NULL	10	NULL	NULL
1	1	NULL	20	NULL	NULL
1	1	NULL	30	NULL	NULL
1	1	NULL	40	NULL	NULL
1	1	NULL	50	NULL	NULL
2	2	2	30	30	0
2	2	2	40	40	0
2	2	2	50	50	0
2	2	2	60	60	0
2	2	2	70	70	0
2	2	2	80	80	0
set join_buffer_size=default;
DROP TABLE t1,t2,t3;
create table t1(f1 int, f2 int);
insert into t1 values (1,1),(2,2),(3,3);
create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2));
insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
(2,4, 'qwerty'),(2,5, 'qwerty');
insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
(4,4, 'qwerty');
insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
(2,4, 'qwerty'),(2,5, 'qwerty');
insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
(4,4, 'qwerty');
select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
f1	f2	f3
1	1	qwerty
1	1	qwerty
2	2	qwerty
2	2	qwerty
explain select t2.f1, t2.f2, t2.f3 from t1,t2
where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition
drop table t1,t2;
#
# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 
#             
create table t1 (d int, id1 int, index idx1 (d, id1));
insert into t1 values
(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30);
create table t2 (id1 int, id2 int, index idx2 (id1));
insert into t2 values 
(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
(40, 200), (30, 300), (10, 400), (20, 200), (20, 300);
explain
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 group by t1.id1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	NULL
select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 group by t1.id1;
id1	sum(t2.id2)
10	900
20	2000
30	900
explain
select t1.id1  from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where
select t1.id1  from t1 join t2 on t1.id1=t2.id1 
where t1.d=3 and t2.id2 > 200 order by t1.id1;
id1
10
10
20
20
20
20
30
30
drop table t1,t2;
#
# Bug #44019: star-like multi-join query executed optimizer_join_cache_level=6 
#             
create table t1 (a int, b int, c int, d int);
create table t2 (b int, e varchar(16), index idx(b));
create table t3 (d int, f varchar(16), index idx(d));
create table t4 (c int, g varchar(16), index idx(c));
insert into t1 values
(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000),
(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800),
(7, 70, 700, 7000);
insert into t2 values
(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
insert into t3 values
(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
insert into t4 values
(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
analyze table t2,t3,t4;
explain 
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	NULL
1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	NULL
1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	NULL
select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
a	b	c	d	e	f	g
3	30	300	3000	bbb	ddd	ccc
drop table t1,t2,t3,t4;
#
# Bug #44250: Corruption of linked join buffers when using BKA 
#             
CREATE TABLE t1 (
id1 bigint(20) DEFAULT NULL,
id2 bigint(20) DEFAULT NULL,
id3 bigint(20) DEFAULT NULL,
num1 bigint(20) DEFAULT NULL,
num2 int(11) DEFAULT NULL,
num3 bigint(20) DEFAULT NULL
);
CREATE TABLE t2 (
id3 bigint(20) NOT NULL DEFAULT '0',
id4 bigint(20) DEFAULT NULL,
enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL,
PRIMARY KEY (id3)
);
CREATE TABLE t3 (
id4 bigint(20) NOT NULL DEFAULT '0',
text1 text,
PRIMARY KEY (id4)
);
CREATE TABLE t4 (
id2 bigint(20) NOT NULL DEFAULT '0',
dummy int(11) DEFAULT '0',
PRIMARY KEY (id2)
);
CREATE TABLE t5 (
id1 bigint(20) NOT NULL DEFAULT '0',
id2 bigint(20) NOT NULL DEFAULT '0',
enum2 enum('Active','Deleted','Paused') DEFAULT NULL,
PRIMARY KEY (id1,id2)
);
set join_buffer_size=2048;
EXPLAIN
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	NULL
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
id1	num3	text1	id4	id3	dummy
228172702	134	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	14	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	15	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	3	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	10	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	14	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	17	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	18	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	19	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	26	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	28	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	50	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	6	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	60	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	61	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	62	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	84	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	89	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	9	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
set join_buffer_size=default;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug #46328: Use of aggregate function without GROUP BY clause 
#             returns many rows (vs. one )
#             
CREATE TABLE t1 (
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO t1 VALUES
(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9);
CREATE TABLE t2 (
int_key int(11) NOT NULL,
KEY int_key (int_key)
);
INSERT INTO t2 VALUES (2),(3);

# The query shall return 1 record with a max value 9 and one of the 
# int_key values inserted above (undefined which one). A changed 
# execution plan may change the value in the second column
SELECT  MAX(t1.int_key), t1.int_key
FROM t1 STRAIGHT_JOIN t2  
ORDER BY t1.int_key;
MAX(t1.int_key)	int_key
9	0

explain 
SELECT  MAX(t1.int_key), t1.int_key
FROM t1 STRAIGHT_JOIN t2  
ORDER BY t1.int_key;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	int_key	4	NULL	14	Using index
1	SIMPLE	t2	index	NULL	int_key	4	NULL	2	Using index

DROP TABLE t1,t2;
#
# Bug #45019: join buffer contains two blob columns one of which is
#             used in the key employed to access the joined table
#
CREATE TABLE t1 (c1 int, c2 int, key (c2));
INSERT INTO t1 VALUES (1,1);
INSERT INTO t1 VALUES (2,2);
CREATE TABLE t2 (c1 text, c2 text);
INSERT INTO t2 VALUES('tt', 'uu');
INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
ANALYZE TABLE t1,t2;
SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
2	2	tt	uu	2	2
DROP TABLE t1,t2;
#
# Regression test for
# Bug#46733 - NULL value not returned for aggregate on empty result 
#             set w/ semijoin on
CREATE TABLE t1 (
i int(11) NOT NULL,
v varchar(1) DEFAULT NULL,
PRIMARY KEY (i)
);
INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d');
CREATE TABLE t2 (
i int(11) NOT NULL,
v varchar(1) DEFAULT NULL,
PRIMARY KEY (i)
);
INSERT INTO t2 VALUES (1,'x'),(2,'y');

SELECT MAX(t1.i) 
FROM t1 JOIN t2 ON t2.v
ORDER BY t2.v;
MAX(t1.i)
NULL
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'x'
Warning	1292	Truncated incorrect INTEGER value: 'y'

EXPLAIN
SELECT MAX(t1.i) 
FROM t1 JOIN t2 ON t2.v
ORDER BY t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index

DROP TABLE t1,t2;
#
# Bug#51092: Linked join buffer gives wrong result 
#            for 3-way cross join
#
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES (1,1),(2,2);
CREATE TABLE t3 (a INT, b INT);
INSERT INTO t3 VALUES (1,1),(2,2);
EXPLAIN SELECT t1.* FROM t1,t2,t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	NULL
SELECT t1.* FROM t1,t2,t3;
a	b
1	1
1	1
1	1
1	1
2	2
2	2
2	2
2	2
DROP TABLE t1,t2,t3;
#
# BUG#52394 Segfault in JOIN_CACHE::get_offset () at sql_select.h:445
#
CREATE TABLE C(a int);
INSERT INTO C VALUES(1),(2),(3),(4),(5);
CREATE TABLE D (a int(11), b varchar(1));
INSERT INTO D VALUES (6,'r'),(27,'o');
CREATE TABLE E (a int(11) primary key, b varchar(1));
INSERT INTO E VALUES
(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'c');
SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b;
1
DROP TABLE C,D,E;
#
# BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (a varchar(10));
INSERT INTO t2 VALUES ('f'),('x');
CREATE TABLE t3 (pk int(11) PRIMARY KEY);
INSERT INTO t3 VALUES (2);
CREATE TABLE t4 (a varchar(10));
EXPLAIN SELECT 1
FROM t2 LEFT JOIN
((t1 JOIN t3 ON t1.a = t3.pk)
LEFT JOIN t4 ON 1 )
ON 1 ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	Using where
SELECT 1
FROM t2 LEFT JOIN
((t1 JOIN t3 ON t1.a = t3.pk)
LEFT JOIN t4 ON 1 )
ON 1 ;
1
1
1
DROP TABLE t1,t2,t3,t4;
#
# Bug#51084: Batched key access crashes for SELECT with
#            derived table and LEFT JOIN 
#
CREATE TABLE t1 (
carrier int,
id int PRIMARY KEY
);
INSERT INTO t1 VALUES (1,11),(1,12),(2,13);
CREATE TABLE t2 (
scan_date int,
package_id int
);
INSERT INTO t2 VALUES (2008,21),(2008,22);
CREATE TABLE t3 (
carrier int PRIMARY KEY,
id int
);
INSERT INTO t3 VALUES (1,31);
CREATE TABLE t4 (
carrier_id int,
INDEX carrier_id(carrier_id)
);
INSERT INTO t4 VALUES (31),(32);

SELECT COUNT(*)
FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
ON t3.carrier = t1.carrier;
COUNT(*)
6

EXPLAIN
SELECT COUNT(*)
FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
ON t3.carrier = t1.carrier;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	NULL
1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index

DROP TABLE t1,t2,t3,t4;
#
# Bug#45267: Incomplete check caused wrong result.
#
CREATE TABLE t1 (
`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE t3 (
`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO t3 VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20);
CREATE TABLE t2 (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`time_key` time NOT NULL,
PRIMARY KEY (`pk`),
KEY `time_key` (`time_key`)
);
INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46');
SELECT DISTINCT t1.`pk`
FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey`  ON t2.`time_key`
GROUP BY 1;
pk
NULL
DROP TABLE IF EXISTS t1, t2, t3;
#
# BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
#
CREATE TABLE t1 (b int);
INSERT INTO t1 VALUES (NULL),(3);
CREATE TABLE t2 (a int, b int, KEY (b));
INSERT INTO t2 VALUES (100,NULL),(150,200);
EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	NULL
SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
a
NULL
NULL
delete from t1;
INSERT INTO t1 VALUES (NULL),(NULL);
EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	NULL
SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
a
NULL
NULL
DROP TABLE t1,t2;
CREATE TABLE t1 (b varchar(100));
INSERT INTO t1 VALUES (NULL),("some varchar");
CREATE TABLE t2 (a int, b varchar(100), KEY (b));
INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	NULL
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
DROP TABLE t1,t2;
#
# BUG#54359 "Extra rows with join_cache_level=7,8 and two joins
# --and multi-column index"
#
CREATE TABLE t1 (
`pk` int(11) NOT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`))
;
INSERT INTO t1 VALUES (4,9,'k','k');
INSERT INTO t1 VALUES (12,5,'k','k');
explain SELECT table2 .`col_int_key` FROM t1 table2,
t1 table3 force index (`col_varchar_key`)
where table3 .`pk` and table3 .`col_int_key`  >= table2 .`pk`  
and table3 .`col_varchar_key`  = table2 .`col_varchar_nokey`;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table2	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	table3	ref	col_varchar_key	col_varchar_key	4	test.table2.col_varchar_nokey	1	Using index condition; Using where
SELECT table2 .`col_int_key` FROM t1 table2,
t1 table3 force index (`col_varchar_key`)
where table3 .`pk` and table3 .`col_int_key`  >= table2 .`pk`  
and table3 .`col_varchar_key`  = table2 .`col_varchar_nokey`;
col_int_key
9
9
drop table t1;
#
# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
# and join_cache_level=5-8"
#
CREATE TABLE t1 (
`col_int_key` int,
`col_datetime` datetime,
KEY `col_int_key` (`col_int_key`)
);
INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
CREATE TABLE t2 (
`col_int` int,
`col_int_key` int,
KEY `col_int_key` (`col_int_key`)
);
INSERT INTO t2 VALUES (14,1);
INSERT INTO t2 VALUES (98,1);
explain SELECT t1.col_int_key, t1.col_datetime 
FROM t1,t2
WHERE t2.col_int_key = 1 AND t2.col_int >= 3
GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	col_int_key	NULL	NULL	NULL	3	Using temporary; Using filesort
1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
SELECT t1.col_int_key, t1.col_datetime 
FROM t1,t2
WHERE t2.col_int_key = 1 AND t2.col_int >= 3
GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
col_int_key	col_datetime
0	2000-09-26 07:45:57
2	2003-02-11 21:19:41
explain SELECT t1.col_int_key, t1.col_datetime 
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
WHERE t2.col_int_key = 1 AND t2.col_int >= 3
GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	col_int_key	col_int_key	5	NULL	3	Using temporary; Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT t1.col_int_key, t1.col_datetime 
FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
WHERE t2.col_int_key = 1 AND t2.col_int >= 3
GROUP BY t1.col_int_key
ORDER BY t1.col_int_key, t1.col_datetime
LIMIT 2;
col_int_key	col_datetime
0	2000-09-26 07:45:57
2	2003-02-11 21:19:41
drop table t1,t2;

# Bug#11766522 "59651: ASSERTION `TABLE_REF->HAS_RECORD' FAILED
# WITH JOIN_CACHE_LEVEL=3"

CREATE TABLE t1 (
b varchar(20)
)  ;
INSERT INTO t1 VALUES ('1'),('1');
CREATE TABLE t4 (
col253 text
)  ;
INSERT INTO t4 VALUES (''),('pf');
CREATE TABLE t6 (
col282 timestamp
)  ;
INSERT INTO t6 VALUES ('2010-11-07 01:04:45'),('2010-12-13 01:36:32');
CREATE TABLE t7 (
col319 timestamp NOT NULL,
UNIQUE KEY idx263 (col319)
)  ;
insert into t7 values("2000-01-01"),("2000-01-02");
CREATE TABLE t3 (
col582 char(230) CHARACTER SET utf8 DEFAULT NULL
)  ;
INSERT INTO t3 VALUES ('cymej'),('spb');
CREATE TABLE t5 (
col712 time
)  ;
insert into t5 values(0),(0);
CREATE TABLE t8 (
col804 char(169),
col805 varchar(51)
)  ;
INSERT INTO t8 VALUES ('tmqcb','pwk');
CREATE TABLE t2 (
col841 varchar(10)
)  ;
INSERT INTO t2 VALUES (''),('');
set join_buffer_size=1;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '1'
select @@join_buffer_size;
@@join_buffer_size
128
select count(*) from
(t1 join t2 join t3)
left join t4 on 1
left join t5 on 1 like t4.col253
left join t6 on t5.col712 is null
left join t7 on t1.b <=>t7.col319
left join t8 on t3.col582 <=  1;
count(*)
32
drop table t1,t2,t3,t4,t5,t6,t7,t8;
#
# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
#                RETURNED WHEN JCL>=7
#
CREATE TABLE t1 (t1a int, t1b int);
INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);

# t2b is NULL-able

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	NULL
SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
t1a	t1b	t2a	t2b
99	NULL	NULL	NULL
99	3	NULL	NULL
99	0	100	0
99	0	999	0

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
t1a	t1b	t2a	t2b
99	NULL	999	NULL
99	3	NULL	NULL
99	0	100	0
99	0	999	0

DROP TABLE t2;
CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);

# t2b is NOT NULL

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	NULL
SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
t1a	t1b	t2a	t2b
99	NULL	NULL	NULL
99	3	NULL	NULL
99	0	100	0
99	0	999	0

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
t1a	t1b	t2a	t2b
99	NULL	NULL	NULL
99	3	NULL	NULL
99	0	100	0
99	0	999	0

DROP TABLE t1,t2;
#
# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
#
CREATE TABLE t1 (
c1 INTEGER NOT NULL,
c2_key INTEGER NOT NULL,
KEY col_int_key (c2_key)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (24,204);
CREATE TABLE t2 (  
pk INTEGER NOT NULL,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (10);
CREATE TABLE t3 (
c1 INTEGER,
KEY k1 (c1)
) ENGINE=InnoDB;
INSERT INTO t3 VALUES (NULL), (NULL);
set @old_opt_switch=@@optimizer_switch;

explain SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Full scan on NULL key
2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Full scan on NULL key
2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Full scan on NULL key
2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	index	NULL	k1	5	NULL	2	Using where; Using index
2	DEPENDENT SUBQUERY	t1	ref	col_int_key	col_int_key	4	func	1	Using where; Full scan on NULL key
2	DEPENDENT SUBQUERY	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
explain SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	col_int_key	NULL	NULL	NULL	1	Start temporary
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Range checked for each record (index map: 0x1)
1	SIMPLE	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
SELECT t3.c1 FROM t3
WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
c1
SELECT t3.c1 FROM t3
WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
c1
SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
XOR TRUE;
c1
SELECT t3.c1 FROM t3
WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
c1
SELECT t3.c1 FROM t3
WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
c1

set @@optimizer_switch=@old_opt_switch;
DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;

# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
# JCL>=5 AND MRR ENABLED"

CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,    
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=innodb;
INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
CREATE TABLE t2 (
col_datetime_key datetime NOT NULL,
col_varchar_key varchar(1) NOT NULL,
KEY col_varchar_key (col_varchar_key)
) ENGINE=innodb;
INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
explain SELECT MIN(t2.col_datetime_key) AS field1,
t1.col_int_key AS field2  
FROM t1
LEFT JOIN t2 force index (col_varchar_key)
ON t1.col_varchar_key = t2.col_varchar_key  
GROUP BY field2
ORDER BY field1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	col_int_key,col_varchar_key	col_int_key	4	NULL	3	Using temporary; Using filesort
1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	NULL
SELECT MIN(t2.col_datetime_key) AS field1,
t1.col_int_key AS field2  
FROM t1
LEFT JOIN t2 force index (col_varchar_key)
ON t1.col_varchar_key = t2.col_varchar_key  
GROUP BY field2
ORDER BY field1;
field1	field2
NULL	0
2003-08-21 00:00:00	4
DROP TABLE t1,t2;

# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"

CREATE TABLE t1 (
col_int_key int(11) NOT NULL,
col_datetime_key datetime NOT NULL,
col_varchar_nokey varchar(1) NOT NULL,
KEY col_int_key (col_int_key),
KEY col_datetime_key (col_datetime_key)
);
INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
CREATE TABLE t2 (
pk int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES
(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
FROM t1
RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
GROUP BY field1 , field4 
ORDER BY t1.col_datetime_key ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	2	NULL
SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
FROM t1
RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
GROUP BY field1 , field4 
ORDER BY t1.col_datetime_key ;
field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
b	0	13
c	4	3
d	0	19
d	1	6
d	2	9
d	3	5
e	0	20
g	0	15
j	2	1
m	0	12
m	1	4
p	0	16
q	0	17
r	0	11
s	0	10
t	0	8
v	1	2
w	0	18
x	0	14
y	2	7
DROP TABLE t1,t2;

# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"

CREATE TABLE t1 (col_varchar_key varchar(1));
CREATE TABLE t2 (
pk int(11) NOT NULL,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key)
);
INSERT INTO t2 VALUES (5,3,9);
INSERT INTO t2 VALUES (6,246,24);
INSERT INTO t2 VALUES (7,2,6);
INSERT INTO t2 VALUES (8,9,1);
INSERT INTO t2 VALUES (9,3,6);
INSERT INTO t2 VALUES (10,8,2);
INSERT INTO t2 VALUES (11,1,4);
INSERT INTO t2 VALUES (12,8,8);
INSERT INTO t2 VALUES (13,8,4);
INSERT INTO t2 VALUES (14,5,4);
INSERT INTO t2 VALUES (15,7,7);
INSERT INTO t2 VALUES (16,5,4);
INSERT INTO t2 VALUES (17,1,1);
INSERT INTO t2 VALUES (18,6,9);
INSERT INTO t2 VALUES (19,2,4);
INSERT INTO t2 VALUES (20,9,8);
explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
alias2.col_int_nokey 
left join t1 
ON alias3.col_int_nokey 
GROUP BY field1, field4
LIMIT 15;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	NULL
1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	NULL
SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
alias2.col_int_nokey 
left join t1 
ON alias3.col_int_nokey 
GROUP BY field1, field4
LIMIT 15;
field1	field4
NULL	1
NULL	2
NULL	4
NULL	7
NULL	8
NULL	9
DROP TABLE t1,t2;

# BUG#12722133 - JCL: JOIN QUERY GIVES DIFFERENT RESULTS AT
# JCL=6 ONLY [NULL VERSUS NULL+#INTS]

CREATE TABLE t1 (pk INTEGER PRIMARY KEY, k INTEGER, i INTEGER, KEY k(k));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
CREATE TABLE t4 LIKE t1;
INSERT INTO t1 VALUES (6,NULL,6),(0,1,11);
INSERT INTO t2 VALUES (1,NULL,NULL),(4,7,NULL);
INSERT INTO t3 VALUES (2,3,0),(3,4,4);
INSERT INTO t4 VALUES (1,9,-1),(4,7,NULL);
EXPLAIN SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i
as t4_i FROM t1
LEFT JOIN t2 ON t1.k = t2.pk
LEFT JOIN t3 ON t3.i
LEFT JOIN t4 ON t4.pk = t2.pk;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	k	5	NULL	2	Using index
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.k	1	Using index
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t2.pk	1	NULL
SELECT t2.pk as t2_pk, t4.pk as t4_pk, t4.k as t4_k, t4.i
as t4_i FROM t1
LEFT JOIN t2 ON t1.k = t2.pk
LEFT JOIN t3 ON t3.i
LEFT JOIN t4 ON t4.pk = t2.pk;
t2_pk	t4_pk	t4_k	t4_i
NULL	NULL	NULL	NULL
1	1	9	-1
DROP TABLE t1, t2, t3, t4;

# BUG#12827509 - BNL/BKA: SELECT LEFT/RIGHT JOIN QUERY GIVES 
#                DIFFERENT OUTPUT ON BNL=OFF+BKA=ON
# (Duplicate of BUG#12722133)

CREATE TABLE t1 (
col_int INTEGER
);
INSERT INTO t1 VALUES (3), (7), (2), (8), (6);
CREATE TABLE t2 (
pk INTEGER, 
col_int INTEGER,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (1,5), (2,8), (6,3), (8,7), (9,9);
CREATE TABLE t3 (
pk INTEGER, 
col_int INTEGER, 
PRIMARY KEY (pk)
);
INSERT INTO t3 VALUES (3,2), (4,3), (8,2);
CREATE TABLE t4 (
pk INTEGER,
col_int INTEGER,
PRIMARY KEY (pk)
);
INSERT INTO t4 VALUES (2,3), (6,1), (8,2);
EXPLAIN SELECT t4.col_int
FROM t1 
LEFT JOIN t2 ON t1.col_int = t2.col_int
LEFT JOIN t3 ON t2.pk = t3.pk 
LEFT JOIN t4 ON t4.pk = t2.pk
WHERE t1.col_int OR t3.col_int;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.pk	1	Using where
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	4	test.t2.pk	1	NULL
SELECT t4.col_int
FROM t1 
LEFT JOIN t2 ON t1.col_int = t2.col_int
LEFT JOIN t3 ON t2.pk = t3.pk 
LEFT JOIN t4 ON t4.pk = t2.pk
WHERE t1.col_int OR t3.col_int;
col_int
1
2
NULL
3
NULL
DROP TABLE t1, t2, t3, t4;
#
# Bug#12997905: VALGRIND: SYSCALL PARAM PWRITE64(BUF) 
#               POINTS TO UNINITIALISED BYTE(S)
#
CREATE TABLE t1 (
col1 varchar(10),
col2 varchar(1024)
) ENGINE=innodb;
INSERT INTO t1 VALUES ('a','a');
CREATE TABLE t2 (i varchar(10)) ENGINE=innodb;
INSERT INTO t2 VALUES ('a');
SELECT t1.col1
FROM t1 JOIN t2 ON t1.col1 = t2.i 
GROUP BY t1.col2;
col1
a
DROP TABLE t1,t2;
# End of Bug#12997905
#
# Bug 13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE + IS
# NULL WHEN SEMIJOIN + BNL IS ON
#
CREATE TABLE t1 (  
col_int_nokey int
);
INSERT INTO t1 VALUES(-1),(-1);
CREATE TABLE t2 (
col_int_nokey int,
col_datetime_nokey datetime NOT NULL,
col_varchar_key varchar(1),
KEY col_varchar_key (col_varchar_key)
);
INSERT INTO t2 VALUES (9, '2002-08-25 20:35:06', 'e'),
(9, '2002-08-25 20:35:06', 'e');
set @optimizer_switch_saved=@@session.optimizer_switch;
set @@session.optimizer_switch='semijoin=off';
EXPLAIN SELECT PARENT1.col_varchar_key
FROM t2 AS PARENT1 LEFT JOIN t1 USING (col_int_nokey)
WHERE PARENT1.col_varchar_key IN
( SELECT col_varchar_key FROM t2 AS CHILD1
WHERE PARENT1.col_datetime_nokey IS NULL
AND t1.col_int_nokey IS NULL )
;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	PARENT1	ALL	NULL	NULL	NULL	NULL	2	NULL
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	CHILD1	index_subquery	col_varchar_key	col_varchar_key	4	func	2	Using index; Using where
SELECT PARENT1.col_varchar_key
FROM t2 AS PARENT1 LEFT JOIN t1 USING (col_int_nokey)
WHERE PARENT1.col_varchar_key IN
( SELECT col_varchar_key FROM t2 AS CHILD1
WHERE PARENT1.col_datetime_nokey IS NULL
AND t1.col_int_nokey IS NULL )
;
col_varchar_key
set @@session.optimizer_switch=@optimizer_switch_saved;
DROP TABLE t1,t2;
set optimizer_switch = default;