DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
set @org_optimizer_switch=@@optimizer_switch;
set @save_join_cache_level=@@join_cache_level;
set @save_join_buffer_space_limit=@@join_buffer_space_limit;
set @save_join_buffer_size=@@join_buffer_size;
set @save_expensive_subquery_limit=@@expensive_subquery_limit;
set @@optimizer_switch='optimize_join_buffer_size=on';
set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='outer_join_with_cache=on';
set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
set @local_optimizer_switch=@@optimizer_switch;
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
set @innodb_stats_persistent_sample_pages_save=
@@innodb_stats_persistent_sample_pages;
set global innodb_stats_persistent= 1;
set global innodb_stats_persistent_sample_pages=100;
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
set join_cache_level=1;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
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; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=2;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	2
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; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (incremental, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
SELECT Country.Name, Country.Population, City.Name, City.Population 
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City 
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
China	1277558000	Zaozhuang	380846
China	1277558000	Zaoyang	162198
China	1277558000	Zalantun	130031
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
CREATE INDEX City_Population ON City(Population);
CREATE INDEX City_Name ON City(Name);
ANALYZE TABLE City;
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population 
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND 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	City	hash_range	City_Population	#hash#$hj:City_Population	3:4	world.Country.Code	24	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population 
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City 
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND 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	City	hash_index_merge	City_Population,City_Name	#hash#$hj:City_Population,City_Name	3:4,35	world.Country.Code	96	Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City 
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
China	1277558000	Zaozhuang	380846
China	1277558000	Zaoyang	162198
China	1277558000	Zalantun	130031
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
DROP INDEX City_Population ON City;
DROP INDEX City_Name ON City;
set join_cache_level=1;
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	1
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; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=2;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	2
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; Using join buffer (flat, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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; Using join buffer (flat, BNL join)
1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (incremental, BNL join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
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	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	City	hash_ALL	NULL	#hash#$hj	3	world.Country.Code	4079	Using where; Using join buffer (incremental, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
set join_cache_level=@save_join_cache_level;
set join_buffer_size=@save_join_buffer_size;
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
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
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; Rowid-ordered scan
1	SIMPLE	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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	hash_ALL	PRIMARY	#hash#PRIMARY	33	world.Country.Code,const	984	Using where; Using join buffer (flat, BNLH join)
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
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; Rowid-ordered scan
1	SIMPLE	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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	hash_ALL	PRIMARY	#hash#PRIMARY	33	world.Country.Code,const	984	Using where; Using join buffer (flat, BNLH join)
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population 
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	Name	Name	52	NULL	#	Using index condition; Using where; Rowid-ordered scan
1	SIMPLE	City	hash_range	Population,Country	#hash#Country:Population	3:4	world.Country.Code	#	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population 
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
CREATE INDEX City_Name ON City(Name);
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City 
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	Name	Name	52	NULL	17	Using index condition; Using where; Rowid-ordered scan
1	SIMPLE	City	hash_index_merge	Population,Country,City_Name	#hash#Country:Population,City_Name	3:4,35	world.Country.Code	96	Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City 
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
Name	Population	Name	Population
China	1277558000	Shanghai	9696300
China	1277558000	Peking	7472000
China	1277558000	Chongqing	6351600
China	1277558000	Tianjin	5286800
China	1277558000	Zaozhuang	380846
China	1277558000	Zaoyang	162198
China	1277558000	Zalantun	130031
Colombia	42321000	Santafé de Bogotá	6260862
Congo, The Democratic Republic of the	51654000	Kinshasa	5064000
Chile	15211000	NULL	NULL
Cambodia	11168000	NULL	NULL
Cameroon	15085000	NULL	NULL
Canada	31147000	NULL	NULL
Cuba	11201000	NULL	NULL
Côte d?Ivoire	14786000	NULL	NULL
Czech Republic	10278100	NULL	NULL
DROP INDEX City_Name ON City;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	262144
set join_cache_level=5;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	5
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	6
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=7;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	7
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_cache_level=8;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	8
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
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; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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)
Australia	81.2
United Kingdom	97.3
Canada	60.4
United States	86.2
Zimbabwe	2.2
Japan	0.1
South Africa	8.5
Malaysia	1.6
Afghanistan	NULL
Netherlands	NULL
Algeria	NULL
Angola	NULL
Argentina	NULL
Bangladesh	NULL
Belgium	NULL
Brazil	NULL
Burkina Faso	NULL
Chile	NULL
Ecuador	NULL
Egypt	NULL
Spain	NULL
Ethiopia	NULL
Philippines	NULL
Ghana	NULL
Guatemala	NULL
Indonesia	NULL
India	NULL
Iraq	NULL
Iran	NULL
Italy	NULL
Yemen	NULL
Yugoslavia	NULL
Cambodia	NULL
Cameroon	NULL
Kazakstan	NULL
Kenya	NULL
China	NULL
Colombia	NULL
Congo, The Democratic Republic of the	NULL
North Korea	NULL
South Korea	NULL
Greece	NULL
Cuba	NULL
Madagascar	NULL
Malawi	NULL
Mali	NULL
Morocco	NULL
Mexico	NULL
Mozambique	NULL
Myanmar	NULL
Nepal	NULL
Niger	NULL
Nigeria	NULL
Côte d?Ivoire	NULL
Pakistan	NULL
Peru	NULL
Poland	NULL
France	NULL
Romania	NULL
Germany	NULL
Saudi Arabia	NULL
Somalia	NULL
Sri Lanka	NULL
Sudan	NULL
Syria	NULL
Taiwan	NULL
Tanzania	NULL
Thailand	NULL
Czech Republic	NULL
Turkey	NULL
Uganda	NULL
Ukraine	NULL
Hungary	NULL
Uzbekistan	NULL
Belarus	NULL
Venezuela	NULL
Russian Federation	NULL
Vietnam	NULL
set join_buffer_size=256;
show variables like 'join_buffer_size';
Variable_name	Value
join_buffer_size	256
set join_cache_level=3;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	3
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; Rowid-ordered scan
1	SIMPLE	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=4;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	4
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; Rowid-ordered scan
1	SIMPLE	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	hash_ALL	Population,Country	#hash#Country	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=5;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	5
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	6
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=7;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	7
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=8;
show variables like 'join_cache_level';
Variable_name	Value
join_cache_level	8
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; Rowid-ordered scan
1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
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 AND
LENGTH(Language) < LENGTH(City.Name) - 2;
Name	Name	Language
La Matanza	Argentina	Spanish
Lagos de Moreno	Mexico	Spanish
Lalitapur	Nepal	Nepali
Las Margaritas	Mexico	Spanish
Las Palmas de Gran Canaria	Spain	Spanish
Lashio (Lasho)	Myanmar	Burmese
Lauro de Freitas	Brazil	Portuguese
Lengshuijiang	China	Chinese
Leninsk-Kuznetski	Russian Federation	Russian
Leverkusen	Germany	German
Lexington-Fayette	United States	English
Liangcheng	China	Chinese
Lianyungang	China	Chinese
Little Rock	United States	English
Liupanshui	China	Chinese
Lleida (Lérida)	Spain	Spanish
Lomas de Zamora	Argentina	Spanish
Long Beach	United States	English
Los Angeles	Chile	Spanish
Los Angeles	United States	English
Los Teques	Venezuela	Spanish
Louisville	United States	English
Lower Hutt	New Zealand	English
Luchou	Taiwan	Min
Ludwigshafen am Rhein	Germany	German
Lungtan	Taiwan	Min
L´Hospitalet de Llobregat	Spain	Spanish
Lázaro Cárdenas	Mexico	Spanish
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	PRIMARY	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Rowid-ordered scan
1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	17	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT Name FROM City
WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
City.Population > 100000;
Name
Vientiane
Riga
Daugavpils
Maseru
Beirut
Tripoli
Monrovia
Tripoli
Bengasi
Misrata
Vilnius
Kaunas
Klaipeda
?iauliai
Panevezys
set join_cache_level=@save_join_cache_level;
set join_buffer_size=@save_join_buffer_size;
set join_cache_level=1;
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_cache_level=8;
set join_buffer_size=384;
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; Rowid-ordered scan
1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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=@save_join_buffer_size;
set join_cache_level=6;
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_cache_level=@save_join_cache_level;
set join_buffer_size=@save_join_buffer_size;
#
# MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults
#
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
set @save_use_stat_tables=@@use_stat_tables;
set optimizer_use_condition_selectivity=4;
set use_stat_tables='preferably';
use world;
set join_cache_level=4;
CREATE INDEX City_Name ON City(Name);
ANALYZE TABLE City, Country;
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND City.Population > 5000000
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
1	SIMPLE	City	hash_range	Population,Country	#hash#Country:Population	3:4	world.Country.Code	24	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
EXPLAIN
SELECT Country.Name, Country.Population, City.Name, City.Population
FROM Country LEFT JOIN City
ON City.Country=Country.Code AND
(City.Population > 5000000 OR City.Name LIKE 'Za%')
WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Country	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
1	SIMPLE	City	hash_index_merge	Population,Country,City_Name	#hash#Country:Population,City_Name	3:4,35	world.Country.Code	96	Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set @@use_stat_tables=@save_use_stat_tables;
set @@join_cache_level=@save_join_cache_level;
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);
insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3);
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');
set join_cache_level=6;
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	
1	SIMPLE	t5	ref	PRIMARY,t5_formattypeid	t5_formattypeid	4	const	1	
1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
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	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaidformatid	4	test.t1.metaid	1	Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t4	eq_ref	PRIMARY,t4_formatclassid,t4_formats_idx	PRIMARY	4	test.t3.formatid	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t9	index	PRIMARY,t9_subgenreid,t9_metaid	PRIMARY	8	NULL	2	Using where; Using index; Using join buffer (incremental, BNL join)
1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
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)
) ;
Warnings:
Warning	1280	Name 'idx' ignored for PRIMARY key.
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);
set join_cache_level=1;
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	4	4	13	13	138		
0	4	4	18	18	188		
0	1	1	30	30	309		
0	1	1	32	32	329		
0	9	9	22	22	228		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	5	5	82	82	829		
0	5	5	87	87	878		
0	3	3	45	45	459		
0	3	3	45	45	458		
0	6	6	73	73	738		
0	6	6	74	74	749		
0	2	2	61	61	618		
set join_cache_level=5;
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; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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	4	4	18	18	188		
0	4	4	13	13	138		
0	1	1	30	30	309		
0	1	1	32	32	329		
0	9	9	22	22	228		
0	8	8	92	92	929		
0	8	8	99	99	998		
0	5	5	82	82	829		
0	3	3	45	45	459		
0	3	3	45	45	458		
0	5	5	87	87	878		
0	2	2	61	61	618		
0	6	6	73	73	738		
0	6	6	74	74	749		
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);
INSERT INTO t2 VALUES (17,10), (11,20), (12,20), (18,20), (18,10), (11,20);
INSERT INTO t2 VALUES (11,10), (14,20), (13,20), (17,20), (17,10), (11,20);
set join_buffer_size=32;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '32'
set join_cache_level=8;
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	3	Using index condition; Using where; Rowid-ordered scan
1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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 #40134: outer join with not exists optimization and join buffer
#
set join_cache_level=@save_join_cache_level;
set join_buffer_size=@save_join_buffer_size;
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);
INSERT INTO t2 VALUES (14,10), (12,10), (15,30), (12,20), (14,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	
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
SET join_cache_level=6;
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	
1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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;
set join_cache_level=@save_join_cache_level;
set join_buffer_size=@save_join_buffer_size;
#
# 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);
set @save_join_cache_level=@@join_cache_level;
set join_cache_level=6;
The following must not show "using join cache":
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	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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
set join_cache_level=@save_join_cache_level;
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);
set join_cache_level=6;
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
4	2	1	2	3	2
4	2	1	2	4	2
3	3	NULL	NULL	NULL	NULL
5	3	NULL	NULL	NULL	NULL
set join_cache_level=@save_join_cache_level;
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);
set join_cache_level=6;
select * from t1 left join t2 on t1.a=t2.a;
a	b	a	c
1	10	1	101
3	30	3	102
1	10	1	100
2	20	NULL	NULL
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	
1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.a	3	Using where; Using join buffer (flat, BNLH join)
select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
a	b	a	c
3	30	3	102
2	20	NULL	NULL
set join_cache_level=@save_join_cache_level;
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);
set join_cache_level=6;
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; Using join buffer (flat, BNL join)
select * from t1 left join t2 on (1=0) where a=40;
a	b
40	NULL
set join_cache_level=0;
explain select * from t1 left join t2 on (1=0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
set join_cache_level=@save_join_cache_level;
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_cache_level=6;
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	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
AVG(c)
5.0000
set join_buffer_size=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
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_cache_level=7;
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; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
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=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
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);
set join_cache_level=0;
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_cache_level=6;
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	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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;
set join_cache_level=2;
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; Using join buffer (flat, BNL join)
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=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
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');
flush status;
set join_cache_level=5;
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
2	2	qwerty
1	1	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(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
show status like "Handler_icp%";
Variable_name	Value
Handler_icp_attempts	20
Handler_icp_match	4
set join_cache_level=6;
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
2	2	qwerty
1	1	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(BKA); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
show status like "Handler_icp%";
Variable_name	Value
Handler_icp_attempts	40
Handler_icp_match	8
set join_cache_level=7;
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
2	2	qwerty
1	1	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(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
show status like "Handler_icp%";
Variable_name	Value
Handler_icp_attempts	60
Handler_icp_match	12
set join_cache_level=8;
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
2	2	qwerty
1	1	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(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
show status like "Handler_icp%";
Variable_name	Value
Handler_icp_attempts	80
Handler_icp_match	16
drop table t1,t2;
set join_cache_level=@save_join_cache_level;
#
# 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);
insert into t2 values 
(21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100),
(41, 200), (31, 300), (11, 400), (21, 200), (21, 300);
set join_cache_level=6;
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; Using temporary; Using filesort
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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; Using temporary; Using filesort
1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
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
set join_cache_level=@save_join_cache_level;
drop table t1,t2;
#
# Bug #44019: star-like multi-join query executed 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 t2 values
(130, 'bbb'), (110, 'b'), (170, 'bbbbbbb'), (160, 'bbbbbb'),
(131, 'bbb'), (111, 'b'), (171, 'bbbbbbb'), (161, 'bbbbbb'),
(132, 'bbb'), (112, 'b'), (172, 'bbbbbbb'), (162, '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 t3 values
(14000, 'dddd'), (13000, 'ddd'), (11000, 'd'), (18000, 'dddddddd'),
(14001, 'dddd'), (13001, 'ddd'), (11001, 'd'), (18001, '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');
insert into t4 values
(1200, 'cc'), (1600, 'cccccc'), (1300, 'ccc'), (1500, 'ccccc'),
(1201, 'cc'), (1601, 'cccccc'), (1301, 'ccc'), (1501, 'ccccc'),
(1202, 'cc'), (1602, 'cccccc'), (1302, 'ccc'), (1502, 'ccccc');
analyze table t2,t3,t4;
set join_cache_level=1;
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	
1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
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
set join_cache_level=6;
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	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
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
set join_cache_level=@save_join_cache_level;
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_cache_level=8;
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; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
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	14	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	134	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	15	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228172702	3	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
228808822	61	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	60	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	6	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	18	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	17	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	50	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
228808822	89	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	19	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	9	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	84	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	14	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	10	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	26	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	28	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
228808822	62	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
set join_buffer_size=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4,t5;
#
# 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 #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; Using join buffer (flat, BNL join)

DROP TABLE t1,t2;
SET join_cache_level=@save_join_cache_level;
#
# 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 DECIMAL value: 'x'
Warning	1292	Truncated incorrect DECIMAL 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; Using join buffer (flat, BNL join)

DROP TABLE t1,t2;
#
# Bug #45092: 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;
set join_cache_level=6;
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
set join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #51092: linked join buffer is used for a 3-way cross join query 
#             that selects only records of the first table
#
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);
set join_cache_level=1;
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	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
select t1.* from t1,t2,t3;
a	b
1	1
2	2
1	1
2	2
1	1
2	2
1	1
2	2
set join_cache_level=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	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (incremental, BNL join)
select t1.* from t1,t2,t3;
a	b
1	1
2	2
1	1
2	2
1	1
2	2
1	1
2	2
set join_cache_level=@save_join_cache_level;
drop table t1,t2,t3;
#
# Bug #52394:  using join buffer for 3 table join with ref access 
# LP #623209: and no references to the columns of the middle table
#
set join_cache_level=6;
CREATE TABLE t1 (a int(11), b varchar(1));
INSERT INTO t1 VALUES (6,'r'),(27,'o');
CREATE TABLE t2(a int);
INSERT INTO t2 VALUES(1),(2),(3),(4),(5);
CREATE TABLE t3 (a int(11) primary key, b varchar(1));
INSERT INTO t3 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,'o');
EXPLAIN
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (incremental, BNL join)
SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b;
a
27
27
27
27
27
DROP TABLE t1,t2,t3;
set join_cache_level=@save_join_cache_level;
#
# 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);
SET join_cache_level=8;
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	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	Using where
1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
SET join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
#
# Bug #52636: allowing JOINs on NULL values w/ 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),(50,150),(250,350),(180,210),(100,150),
(101,NULL),(151,200),(51,150),(251,350),(181,210),(101,150);
set join_cache_level = 5;
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	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level = 8;
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	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
delete from t1;
INSERT INTO t1 VALUES (NULL),(NULL);
set join_cache_level = 5;
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	
1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT t2.a FROM t1 LEFT JOIN t2 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");
INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar");
set join_cache_level = 5;
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	
1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level = 8;
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	
1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
a
NULL
NULL
set join_cache_level=@save_join_cache_level;
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 NOT NULL,
a int DEFAULT NULL,
b varchar(16) DEFAULT NULL,
c varchar(16) DEFAULT NULL,
INDEX idx (b,a))
;
INSERT INTO t1 VALUES (4,9,'k','k');
INSERT INTO t1 VALUES (12,5,'k','k');
set join_cache_level = 8;
EXPLAIN
SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
WHERE s.pk AND s.a  >= t.pk AND  s.b  = t.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	s	ref	idx	idx	19	test.t.c	1	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx)
WHERE s.pk AND s.a  >= t.pk AND  s.b  = t.c;
a
9
9
set join_cache_level=@save_join_cache_level;
DROP TABLE t1;
#
# Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs
#
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), (2), (null), (1);
set join_cache_level = 6;
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	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
SELECT t1.a 
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 
WHERE t1.a OR t3.a;
a
2
1
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	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
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
2
1
set join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
#
# Bug #663840: Memory overwrite causing crash with hash join
#
SET SESSION join_cache_level=3;
SET SESSION join_buffer_size=100;
Warnings:
Warning	1292	Truncated incorrect join_buffer_size value: '100'
CREATE TABLE t3 (
i int NOT NULL,
j int NOT NULL,
d date NOT NULL,
t time NOT NULL,
v varchar(1) NOT NULL,
u varchar(1) NOT NULL,
INDEX idx (v)
)  COLLATE=latin1_bin;
INSERT INTO t3 VALUES
(3,8,'2008-12-04','00:00:00','v','v'), (3,8,'2009-03-28','00:00:00','f','f'),
(3,5,'1900-01-01','00:55:47','v','v'), (2,8,'2009-10-02','00:00:00','s','s'),
(1,8,'1900-01-01','20:51:59','a','a'), (0,6,'2008-06-04','09:47:27','p','p'),
(8,7,'2009-01-13','21:58:29','z','z'), (5,2,'1900-01-01','22:45:53','a','a'),
(9,5,'2008-01-28','14:06:48','h','h'), (5,7,'2004-09-18','22:17:16','h','h'),
(4,2,'2006-10-14','14:59:37','v','v'), (2,9,'1900-01-01','23:37:40','v','v'),
(33,142,'2000-11-28','14:14:01','b','b'), (5,3,'2008-04-04','02:54:19','y','y'),
(1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'),
(1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'),
(8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a');
INSERT INTO t3 VALUES
(103,108,'2008-12-04','00:00:00','a','v'), (103,108,'2009-03-28','00:00:00','b','f'),
(103,105,'1900-01-01','00:55:47','c','v'), (102,108,'2009-10-02','00:00:00','d','s'),
(100,108,'1900-01-01','20:51:59','e','a'), (100,106,'2008-06-04','09:47:27','f','p'),
(108,107,'2009-01-13','21:58:29','g','z'), (105,102,'1900-01-01','22:45:53','h','a'),
(109,105,'2008-01-28','14:06:48','i','h'), (105,107,'2004-09-18','22:17:16','j','h'),
(104,102,'2006-10-14','14:59:37','k','v'), (102,109,'1900-01-01','23:37:40','l','v'),
(1033,1142,'2000-11-28','14:14:01','m','b'), (105,103,'2008-04-04','02:54:19','n','y'),
(100,100,'2002-07-13','06:34:26','o','v'), (109,103,'2003-01-03','18:07:38','p','m'),
(100,105,'2006-04-02','13:55:23','q','z'), (103,109,'2006-10-19','20:32:28','s','n'),
(108,100,'2005-06-08','11:57:44','t','d'), (1231,1107,'2006-12-26','03:10:35','v','a');
CREATE TABLE t1 SELECT * FROM t3;
DELETE FROM t1 WHERE i > 8;
CREATE TABLE t2 SELECT * FROM t3;
DELETE FROM t2 WHERE j > 10;
EXPLAIN
SELECT t1.i, t1.d,  t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3
WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	16	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	18	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash_ALL	idx	#hash#idx	3	test.t2.u	40	Using where; Using join buffer (flat, BNLH join)
SELECT t1.i, t1.d,  t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3
WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u;
i	d	v	i	d	t	v
0	2008-06-04	p	1	2002-07-13	06:34:26	v
0	2008-06-04	p	4	2006-10-14	14:59:37	v
0	2008-06-04	p	5	1900-01-01	22:45:53	a
0	2008-06-04	p	5	1900-01-01	22:45:53	a
0	2008-06-04	p	5	1900-01-01	22:45:53	a
1	1900-01-01	a	1	2002-07-13	06:34:26	v
1	1900-01-01	a	4	2006-10-14	14:59:37	v
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	1900-01-01	a	5	1900-01-01	22:45:53	a
1	2002-07-13	v	1	2002-07-13	06:34:26	v
1	2002-07-13	v	4	2006-10-14	14:59:37	v
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2002-07-13	v	5	1900-01-01	22:45:53	a
1	2006-04-02	z	1	2002-07-13	06:34:26	v
1	2006-04-02	z	4	2006-10-14	14:59:37	v
1	2006-04-02	z	5	1900-01-01	22:45:53	a
1	2006-04-02	z	5	1900-01-01	22:45:53	a
1	2006-04-02	z	5	1900-01-01	22:45:53	a
2	1900-01-01	v	1	2002-07-13	06:34:26	v
2	1900-01-01	v	4	2006-10-14	14:59:37	v
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	1900-01-01	v	5	1900-01-01	22:45:53	a
2	2009-10-02	s	1	2002-07-13	06:34:26	v
2	2009-10-02	s	4	2006-10-14	14:59:37	v
2	2009-10-02	s	5	1900-01-01	22:45:53	a
2	2009-10-02	s	5	1900-01-01	22:45:53	a
2	2009-10-02	s	5	1900-01-01	22:45:53	a
3	1900-01-01	v	1	2002-07-13	06:34:26	v
3	1900-01-01	v	4	2006-10-14	14:59:37	v
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	1900-01-01	v	5	1900-01-01	22:45:53	a
3	2006-10-19	n	1	2002-07-13	06:34:26	v
3	2006-10-19	n	4	2006-10-14	14:59:37	v
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2006-10-19	n	5	1900-01-01	22:45:53	a
3	2008-12-04	v	1	2002-07-13	06:34:26	v
3	2008-12-04	v	4	2006-10-14	14:59:37	v
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2008-12-04	v	5	1900-01-01	22:45:53	a
3	2009-03-28	f	1	2002-07-13	06:34:26	v
3	2009-03-28	f	4	2006-10-14	14:59:37	v
3	2009-03-28	f	5	1900-01-01	22:45:53	a
3	2009-03-28	f	5	1900-01-01	22:45:53	a
3	2009-03-28	f	5	1900-01-01	22:45:53	a
4	2006-10-14	v	1	2002-07-13	06:34:26	v
4	2006-10-14	v	4	2006-10-14	14:59:37	v
4	2006-10-14	v	5	1900-01-01	22:45:53	a
4	2006-10-14	v	5	1900-01-01	22:45:53	a
4	2006-10-14	v	5	1900-01-01	22:45:53	a
5	1900-01-01	a	1	2002-07-13	06:34:26	v
5	1900-01-01	a	4	2006-10-14	14:59:37	v
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	1900-01-01	a	5	1900-01-01	22:45:53	a
5	2004-09-18	h	1	2002-07-13	06:34:26	v
5	2004-09-18	h	4	2006-10-14	14:59:37	v
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2004-09-18	h	5	1900-01-01	22:45:53	a
5	2008-04-04	y	1	2002-07-13	06:34:26	v
5	2008-04-04	y	4	2006-10-14	14:59:37	v
5	2008-04-04	y	5	1900-01-01	22:45:53	a
5	2008-04-04	y	5	1900-01-01	22:45:53	a
5	2008-04-04	y	5	1900-01-01	22:45:53	a
8	2005-06-08	d	1	2002-07-13	06:34:26	v
8	2005-06-08	d	4	2006-10-14	14:59:37	v
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2005-06-08	d	5	1900-01-01	22:45:53	a
8	2009-01-13	z	1	2002-07-13	06:34:26	v
8	2009-01-13	z	4	2006-10-14	14:59:37	v
8	2009-01-13	z	5	1900-01-01	22:45:53	a
8	2009-01-13	z	5	1900-01-01	22:45:53	a
8	2009-01-13	z	5	1900-01-01	22:45:53	a
DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=@save_join_cache_level;
SET SESSION join_buffer_size=@save_join_buffer_size;
#
# Bug #664508: 'Simple' GROUP BY + ORDER BY 
#              when join buffers are used
#
CREATE TABLE t1 (
pk int NOT NULL, i int NOT NULL, v  varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2 (v,i)
) COLLATE latin1_bin;
INSERT INTO t1 VALUES
(10,8,'v'), (11,8,'f'), (13,8,'s'), (14,8,'a'),
(15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
(25,3,'m'), (26,5,'a'), (27,9,'n'), (28,1,'d'), (29,107,'a');
INSERT INTO t1 VALUES
(110,8,'x'), (111,8,'y'), (112,5,'v'), (113,8,'z'), (114,8,'i'),
(115,6,'j'), (116,7,'t'), (117,2,'b'), (118,5,'j'), (119,7,'w'),
(125,3,'q'), (126,5,'o'), (127,9,'n'), (128,1,'e'), (129,107,'c');
INSERT INTO t1 VALUES
(210,8,'b'), (211,8,'c'), (212,5,'d'), (213,8,'e'), (214,8,'g'),
(215,6,'f'), (216,7,'h'), (217,2,'i'), (218,5,'j'), (219,7,'k'),
(225,3,'l'), (226,5,'m'), (227,9,'n'), (228,1,'o'), (229,107,'p');
CREATE TABLE t2 (
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
) COLLATE latin1_bin;
INSERT INTO t2 VALUES
(10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'),
(15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'),
(20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'),
(25,3,'m'), (26,5,'b'), (27,9,'n'), (28,1,'d'), (29,107,'a');
CREATE TABLE t3 (
pk int NOT NULL, i int NOT NULL,  v varchar(1) NOT NULL,
PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i)
) COLLATE latin1_bin;
INSERT INTO t3 VALUES
(1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
(6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
(11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'),
(16,6,'e'), (17,3,'t'), (18,8,'j'), (19,5,'h'), (20,7,'w');
SET SESSION join_cache_level=1;
EXPLAIN
SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where
SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
EXPLAIN
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
SET SESSION join_cache_level=6;
EXPLAIN
SELECT t2.v FROM t1, t2, t3 
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t2.v FROM t1, t2, t3 
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
EXPLAIN
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	5	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
SET SESSION join_cache_level=4;
EXPLAIN
SELECT t2.v FROM t1, t2, t3 
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	hash_ALL	PRIMARY,idx2	#hash#PRIMARY	4	test.t2.i	20	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t1	hash_ALL	idx2	#hash#idx2	3	test.t3.v	44	Using where; Using join buffer (incremental, BNLH join)
SELECT t2.v FROM t1, t2, t3
WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
EXPLAIN
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	idx1	idx2	7	NULL	20	Using index; Using temporary; Using filesort
1	SIMPLE	t3	hash_ALL	PRIMARY,idx2	#hash#PRIMARY	4	test.t2.i	20	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t1	hash_ALL	idx2	#hash#idx2	3	test.t3.v	44	Using where; Using join buffer (incremental, BNLH join)
SELECT t2.v FROM t1, t2, t3
WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100
GROUP BY t2.v ORDER BY t1.pk,t2.v;
v
b
h
n
v
p
DROP TABLE t1,t2,t3;
SET SESSION join_cache_level=@save_join_cache_level;
#
# Bug #668290: hash join with non-binary collations 
#
CREATE TABLE t1 (
i int DEFAULT NULL,
cl varchar(10) CHARACTER SET latin1 DEFAULT NULL,
cu varchar(10) CHARACTER SET utf8 DEFAULT NULL,
INDEX cl (cl),
INDEX cu (cu)
);
INSERT INTO t1 VALUES
(650903552,'cmxffkpsel','z'), (535298048,'tvtjrcmxff','y'),
(1626865664,'when','for'), (39649280,'rcvljitvtj','ercvljitvt'),
(792068096,'ttercvljit','jttercvlji');
INSERT INTO t1 SELECT * FROM t1;
CREATE TABLE t2 (
cu varchar(10) CHARACTER SET utf8 DEFAULT NULL,
i int DEFAULT NULL,
cl varchar(10) CHARACTER SET latin1 DEFAULT NULL,
INDEX cu (cu),
INDEX cl (cl)
);
INSERT INTO t2 VALUES 
('g',7,'like'), ('fujttercvl',6,'y'),
('s',2,'e'), ('didn\'t',0,'v'),
  ('gvdrodpedk',8,'chogvdrodp'), ('jichogvdro',7,'will');
EXPLAIN
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
1	SIMPLE	t1	ref	cu	cu	33	func	2	Using where; Using index
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
i
6
6
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	
1	SIMPLE	t1	hash_index	cu	#hash#cu:cu	33:33	func	10	Using where; Using index; Using join buffer (flat, BNLH join)
SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ;
i
6
6
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #669382: hash join using a ref with constant key parts 
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES
(9), (11), (7), (8), (4), (1), (12), (3), (5);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
CREATE TABLE t2 (a int, b int, c int, INDEX idx (a,b));
INSERT INTO t2 VALUES
(8, 80, 800), (1, 10, 100), (1, 11, 101), (3, 30, 300),
(1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200),
(8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301),
(1, 15, 105), (8, 83, 803), (7, 71, 701);
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 256;
EXPLAIN
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	15	Using where
1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.a	36	Using where; Using join buffer (flat, BNLH join)
SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99;
a	c
SET SESSION join_cache_level=@save_join_cache_level;
SET SESSION join_buffer_size=@save_join_buffer_size;
DROP TABLE t1,t2;
#
# Bug #671901: hash join using a ref to a varchar field
#
CREATE TABLE t1 (
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
i int  DEFAULT NULL
);
INSERT INTO t1 VALUES
('k',8), ('abcdefjh',-575340544), ('f',77), ('because', 2), ('f',-517472256),
('abcdefjhj',5), ('z',7);
CREATE TABLE t2 (
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
i int DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t2 VALUES
('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), 
('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888),
('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464),
('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), 
('ff', 5), ('abcdefjhjk',-1074397184);
INSERT INTO t2 VALUES
('dig',5), ('were',-1631322112), ('is',3), ('abcdefjhjl',3), 
('abcdefjh',4), ('told',-824573952), ('tt',0),('vv',-1711013888),
('abcdefjhjj',1015414784), ('and',4), ('here',0), ('abcdefjhjm',-32702464),
('abcdefjhji',4), ('space',1078394880), ('fs',4), ('mn',-1845559296), 
('fq', 5), ('abcdefjhjp',-1074397184);
EXPLAIN 
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
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	13	test.t1.v	3	
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
v	i
abcdefjh	4
f	4
f	4
EXPLAIN
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
1	SIMPLE	t2	ref	idx	idx	13	func	3	Using index condition
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
v	i
f	5
f	5
SET SESSION join_cache_level = 4;
EXPLAIN 
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
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	hash_ALL	idx	#hash#idx	13	test.t1.v	36	Using join buffer (flat, BNLH join)
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
v	i
f	4
f	4
abcdefjh	4
EXPLAIN
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
1	SIMPLE	t2	hash_ALL	idx	#hash#idx	13	func	36	Using where; Using join buffer (flat, BNLH join)
SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v);
v	i
f	5
f	5
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
# Bug #672497: 3 way join with tiny incremental join buffer with
#              and a ref access from the first table 
#
CREATE TABLE t1 (
pk int PRIMARY KEY,
v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t1 VALUES
(1,'abcdefjhjk'), (2,'i'),(3,'abcdefjhjk'), (4,'well'), (5,'abcdefjhjk'),
(6,'abcdefjhjk'), (7,'that');
CREATE TABLE t2 (
pk int PRIMARY KEY,
i int DEFAULT NULL,
v varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (v)
);
INSERT INTO t2 VALUES 
(1,6,'yes'), (2,NULL,'will'), (3,NULL,'o'), (4,NULL,'k'), (5,NULL,'she'),
(6,-1450835968,'abcdefjhjkl'), (7,-975831040,'abcdefjhjkl'), (8,NULL,'z'),
(10,-343932928,'t'),
(11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'),
(16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'),
(19,-343932928,'t');
INSERT INTO t2 VALUES 
(101,6,'yes'), (102,NULL,'will'), (103,NULL,'o'), (104,NULL,'k'), (105,NULL,'she'),
(106,-1450835968,'abcdefjhjkl'), (107,-975831040,'abcdefjhjkl'), (108,NULL,'z'),
(100,-343932928,'t'),
(111,6,'yes'), (112,NULL,'will'), (113,NULL,'o'), (114,NULL,'k'), (115,NULL,'she'),
(116,-1450835968,'abcdefjhjkl'), (117,-975831040,'abcdefjhjkl'), (118,NULL,'z'),
(119,-343932928,'t');
CREATE TABLE t3 (
pk int NOT NULL PRIMARY KEY,
i int,
v varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
INDEX idx (v(333))
);
INSERT INTO t3 VALUES 
(1,7,'abcdefjhjkl'),(2,6,'y'), (3,NULL,'to'),(4,7,'n'),(5,7,'look'), (6,NULL,'all'),
(7,1443168256,'c'), (8,1427046400,'right'),
(11,7,'abcdefjhjkl'), (12,6,'y'), (13,NULL,'to'), (14,7,'n'), (15,7,'look'),
(16,NULL,'all'), (17,1443168256,'c'), (18,1427046400,'right'),
(21,7,'abcdefjhjkl'), (22,6,'y'), (23,NULL,'to'), (24,7,'n'), (25,7,'look'),
(26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'),
(31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'),
(36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right');
INSERT INTO t3 VALUES 
(101,7,'abcdefjhjkl'),(102,6,'y'), (103,NULL,'to'),(104,7,'n'),(105,7,'look'),
(106,NULL,'all'), (107,1443168256,'c'), (108,1427046400,'right'),
(111,7,'abcdefjhjkl'), (112,6,'y'), (113,NULL,'to'), (114,7,'n'), (115,7,'look'),
(116,NULL,'all'), (117,1443168256,'c'), (118,1427046400,'right'),
(121,7,'abcdefjhjkl'), (122,6,'y'), (123,NULL,'to'), (124,7,'n'), (125,7,'look'),
(126,NULL,'all'), (127,1443168256,'c'), (128,1427046400,'right'),
(131,7,'abcdefjhjkl'), (132,6,'y'), (133,NULL,'to'), (134,7,'n'), (135,7,'look'),
(136,NULL,'all'), (137,1443168256,'c'), (138,1427046400,'right');
SET SESSION join_buffer_size = 256;
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT t3.i FROM t1,t2,t3
WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	idx	idx	13	NULL	7	Using where; Using index
1	SIMPLE	t2	hash_ALL	idx	#hash#idx	1003	test.t1.v	36	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t3	hash_ALL	idx	#hash#idx	1002	func	64	Using where; Using join buffer (incremental, BNLH join)
SELECT t3.i FROM t1,t2,t3
WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;
i
SET SESSION join_cache_level=@save_join_cache_level;
SET SESSION join_buffer_size=@save_join_buffer_size;
DROP TABLE t1,t2,t3;
#
# Bug #672551: hash join over a long varchar field
#
CREATE TABLE t1 (
pk int PRIMARY KEY,
a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (a)
);
INSERT INTO t1 VALUES (2, 'aa'), (5, 'ccccccc'), (3, 'bb');
CREATE TABLE t2(
pk int PRIMARY KEY,
a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL,
INDEX idx (a)
);
INSERT INTO t2 VALUES 
(10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'),
(11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'),
(12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa');
INSERT INTO t2 VALUES 
(110, 'a'), (120, 'c'), (130, 'aa'), (14, 'bb'),
(111, 'a'), (121, 'c'), (131, 'aa'), (141, 'cc'),
(112, 'a'), (122, 'c'), (132, 'bb'), (142, 'aa');
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
pk	a	pk	a
2	aa	30	aa
2	aa	31	aa
2	aa	42	aa
2	aa	130	aa
2	aa	131	aa
2	aa	142	aa
3	bb	4	bb
3	bb	32	bb
3	bb	14	bb
3	bb	132	bb
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	Using where
1	SIMPLE	t2	hash_ALL	idx	#hash#idx	515	test.t1.a	24	Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t2.a=t1.a;
pk	a	pk	a
2	aa	30	aa
3	bb	4	bb
2	aa	31	aa
3	bb	32	bb
2	aa	42	aa
2	aa	130	aa
3	bb	14	bb
2	aa	131	aa
3	bb	132	bb
2	aa	142	aa
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #674431: nested outer join when join_cache_level is set to 7
#
CREATE TABLE t1 (a int, b varchar(32)) ;
INSERT INTO t1 VALUES (5,'h'), (NULL,'j');
CREATE TABLE t2 (a int, b varchar(32), c int) ;
INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200);
CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b));
INSERT INTO t3 VALUES (77,'h'), (88,'g');
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 7;
SELECT t3.a
FROM t1 LEFT JOIN
(t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b
WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c;
a
Warnings:
Warning	1292	Truncated incorrect INTEGER value: 'h'
Warning	1292	Truncated incorrect INTEGER value: 'j'
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3;
#
# Bug #52540: nested outer join when join_cache_level is set to 3
#
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));
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 3;
SELECT *
FROM t2 LEFT JOIN
((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1;
a	a	pk	a
f	2	2	NULL
x	2	2	NULL
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
#
# Bug #674423: outer join with ON expression over only outer tables 
#
CREATE TABLE t1 (a int) ;
INSERT INTO t1 VALUES ('9');
CREATE TABLE t2 (pk int, a int) ;
INSERT INTO t2 VALUES ('9',NULL), ('1',NULL);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9;
pk	a	a
1	NULL	NULL
SET SESSION join_cache_level = 1;
EXPLAIN
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
pk	a	a
1	NULL	NULL
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #675095: nested outer join using join buffer 
#
CREATE TABLE t1 (pk int, a1 int) ;
INSERT IGNORE INTO t1 VALUES (2,NULL), (8,0);
CREATE TABLE t2 (pk int, a2 int, c2 int, d2 int) ;
INSERT IGNORE INTO t2 VALUES  (9,0,0,2), (1,0,0,7);
CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ;
INSERT IGNORE INTO t3 VALUES  (9,0,0,2), (1,0,0,7);
CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ;
INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0);
INSERT IGNORE INTO t4 VALUES (12,10), (18,20);
INSERT IGNORE INTO t4 VALUES (22,11), (28,21);
INSERT IGNORE INTO t4 VALUES (32,12), (38,22);
CREATE TABLE t5 (pk int, a5 int) ;
INSERT IGNORE INTO t5 VALUES (2,0), (8,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION join_cache_level = 2;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION join_cache_level = 1;
EXPLAIN EXTENDED
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
1	SIMPLE	t4	ref	idx	idx	5	test.t1.a1	2	100.00	Using where
1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
Warnings:
Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a1` AS `a1`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`c2` AS `c2`,`test`.`t2`.`d2` AS `d2`,`test`.`t3`.`pk` AS `pk`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`c3` AS `c3`,`test`.`t3`.`d3` AS `d3`,`test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a4` AS `a4`,`test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a5` AS `a5` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`d2` = `test`.`t1`.`pk` and `test`.`t3`.`a3` = `test`.`t2`.`c2`) left join `test`.`t4` on(`test`.`t4`.`a4` = `test`.`t1`.`a1` and `test`.`t1`.`a1` is not null) left join `test`.`t5` on(`test`.`t5`.`a5` = `test`.`t3`.`a3`) where 1
SELECT *
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
pk	a1	pk	a2	c2	d2	pk	a3	c3	d3	pk	a4	pk	a5
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	2	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	2	0
2	NULL	9	0	0	2	9	0	0	2	NULL	NULL	8	0
2	NULL	9	0	0	2	1	0	0	7	NULL	NULL	8	0
8	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	8	0	NULL	NULL
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug #675516: nested outer join with 3 tables in the nest
#             using BNL + BNLH 
#
CREATE TABLE t1 (a1 int, b1 int, c1 int) ;
INSERT INTO t1 VALUES (7,8,0), (6,4,0);
CREATE TABLE t2 (a2 int) ;
INSERT INTO t2 VALUES (5);
CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ;
INSERT INTO t3 VALUES (2,5,0);
CREATE TABLE t4 (a4 int, b4 int, c4 int) ;
INSERT INTO t4 VALUES (7,8,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t3	hash_ALL	PRIMARY	#hash#$hj	5	test.t1.c1	1	Using where; Using join buffer (flat, BNLH join)
1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t3.b3	1	Using where; Using join buffer (incremental, BNLH join)
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM
t1 LEFT JOIN 
((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
WHERE t3.a3 IS NULL;
a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4;
#
# Bug #660963: nested outer join with join_cache_level set to 5
#
CREATE TABLE t1 (a1 int) ;
INSERT INTO t1 VALUES (0),(0);
CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ;
INSERT INTO t2 VALUES (2,1);
CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ;
INSERT INTO t3 VALUES (2,1);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 6;
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
a1	a2	b2	a3	b3
0	2	1	2	1
0	2	1	2	1
SET SESSION join_cache_level = 5;
EXPLAIN
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash_ALL	PRIMARY	#hash#PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
a1	a2	b2	a3	b3
0	2	1	2	1
0	2	1	2	1
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3;
#
# Bug #675922: incremental buffer for BKA with access from previous
#      buffers from non-nullable columns whose values may be null
#
CREATE TABLE t1 (a1 varchar(32)) ;
INSERT INTO t1 VALUES ('s'),('k');
CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
INSERT INTO t2 VALUES (7,'s');
CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
INSERT INTO t3 VALUES (7,'s');
CREATE TABLE t4 (a4 int) ;
INSERT INTO t4 VALUES (9);
CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
INSERT INTO t5 VALUES (7,0);
SET SESSION optimizer_switch = 'outer_join_with_cache=on';
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4	b5
9	0
9	NULL
SET SESSION join_cache_level = 6;
EXPLAIN
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
SELECT t4.a4, t5.b5 
FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
a4	b5
9	0
9	NULL
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2,t3,t4,t5;
#
# Bug #670380: hash join for non-binary collation
#
CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32));
CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a));
INSERT INTO t1 VALUES
(10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee');
INSERT INTO t2 VALUES
(1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'),
(6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'),
(11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'),
(101,'Bbbb'), (102,'BBB'), (103,'bbbb'), (104,'AaA'), (105,'CC'),
(106,'cC'), (107,'CCC'), (108,'AAA'), (109,'bBbB'), (110,'aaaa'),
(111,'a'), (112,'dd'), (113,'EE'), (114,'ee'), (115,'D');
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT * FROM t1,t2 WHERE t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
1	SIMPLE	t2	hash_ALL	idx	#hash#idx	35	test.t1.a	30	Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t1.a=t2.a;
pk	a	pk	a
20	BBBB	1	Bbbb
20	BBBB	3	bbbb
10	AAA	4	AaA
30	Cc	5	CC
30	Cc	6	cC
10	AAA	8	AAA
20	BBBB	9	bBbB
40	DD	12	dd
50	ee	13	EE
50	ee	14	ee
20	BBBB	101	Bbbb
20	BBBB	103	bbbb
10	AAA	104	AaA
30	Cc	105	CC
30	Cc	106	cC
10	AAA	108	AAA
20	BBBB	109	bBbB
40	DD	112	dd
50	ee	113	EE
50	ee	114	ee
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #694092: incorrect detection of index only pushdown conditions
#
CREATE TABLE t1 (
f1 varchar(10), f3 int(11), PRIMARY KEY (f3)
);
INSERT INTO t1 VALUES ('y',1),('or',5);
CREATE TABLE t2 (
f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3)
);
INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m');
SET SESSION join_cache_level = 1;
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Rowid-ordered scan
1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
f1	f3	f3	f2	f4
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Rowid-ordered scan
1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2
WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6);
f1	f3	f3	f2	f4
SET SESSION join_cache_level=@save_join_cache_level;
SET SESSION optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2;
CREATE TABLE t1 (f1 int, f2 varchar(10), KEY (f1), KEY (f2)) ;
INSERT INTO t1 VALUES
(4,'e'), (891879424,'l'), (-243400704,'ectlyqupbk'), (1851981824,'of'),
(-1495203840,'you'), (4,'no'), (-1436942336,'c'), (891420672,'DQQYO'),
(608698368,'qergldqmec'), (1,'x');
CREATE TABLE t2 (f3 varchar(64), KEY (f3));
INSERT INTO t2 VALUES 
('d'), ('UALLN'), ('d'), ('z'), ('r'), ('YVAKV'), ('d'), ('TNGZK'), ('e'),
('xucupaxdyythsgiw'), ('why'), ('ttugkxucupaxdyyt'), ('l'), ('LHTKN'),
('d'), ('o'), ('v'), ('KGLCJ'), ('your');
SET SESSION optimizer_switch='index_merge_sort_intersection=off';
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	f1,f2	f2	13	NULL	10	Using where
1	SIMPLE	t2	ref	f3	f3	67	test.t1.f2	2	Using where; Using index
SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
f1	f2	f3
SET SESSION optimizer_switch=@local_optimizer_switch;
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	f1,f2	f2	13	NULL	10	Using where
1	SIMPLE	t2	ref	f3	f3	67	test.t1.f2	2	Using where; Using index
SELECT * FROM t1,t2 
WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1;
f1	f2	f3
SET SESSION optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2;
#
# Bug #694443: hash join using IS NULL the an equi-join condition
#
CREATE TABLE t1 (a int PRIMARY KEY);
INSERT INTO t1 VALUES
(7), (4), (9), (1), (3), (8), (2);
CREATE TABLE t2 (a int, b int, INDEX idx (a));
INSERT INTO t2 VALUES
(NULL,10), (4,80), (7,70), (6,11), (7,90), (NULL,40),
(4,77), (4,50), (NULL,41), (7,99), (7,88), (8,12),
(1,21), (4,90), (7,91), (8,22), (6,92), (NULL,42),
(2,78), (2,51), (1,43), (5,97), (5,89);
SET SESSION join_cache_level = 1;
EXPLAIN 
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
1	SIMPLE	t2	ref	idx	idx	5	const	4	Using index condition
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
a	a	b
1	NULL	10
1	NULL	40
1	NULL	41
1	NULL	42
2	NULL	10
2	NULL	40
2	NULL	41
2	NULL	42
SET SESSION join_cache_level = 4;
EXPLAIN 
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
1	SIMPLE	t2	hash_range	idx	#hash#idx:idx	5:5	const	4	Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL;
a	a	b
1	NULL	10
2	NULL	10
1	NULL	40
2	NULL	40
1	NULL	41
2	NULL	41
1	NULL	42
2	NULL	42
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #697557: hash join on a varchar field 
#
CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1));
INSERT INTO t1 VALUES ('r',1), ('m',2);
CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1));
INSERT INTO t2 VALUES
('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88),
('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55),
('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77),
('jgtofu',3), ('JDO',33), ('mn',3), ('jggxgarrr',77),
('igtofu',3), ('IDO',33), ('ln',3), ('iggxgarrr',77);
SET SESSION join_cache_level=3;
EXPLAIN 
SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	f1	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	hash_ALL	f1	#hash#f1	13	test.t1.f1	20	Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1;
f1	f2	f1	f2
SET SESSION join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# Bug #707827: hash join on varchar column with NULLs 
#
CREATE TABLE t1 (v varchar(1));
INSERT INTO t1 VALUES ('o'), ('u');
CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ;
INSERT INTO t2 VALUES
(8,NULL), (10,'b'), (5,'k'), (4,NULL),
(1,NULL), (11,'u'), (7,NULL), (2,'d'), 
(18,'u'), (11,'b'), (15,'k'), (12,'d'), 
(18,'x'), (11,'y'), (15,'l'), (12,'e');
SET SESSION join_buffer_size = 256;
SET SESSION join_cache_level = 4;
EXPLAIN
SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	hash_ALL	idx	#hash#idx	4	test.t1.v	16	Using join buffer (flat, BNLH join)
SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
a
11
18
SET SESSION join_cache_level = 1;
EXPLAIN
SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	ref	idx	idx	4	test.t1.v	2	
SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
a
11
18
SET SESSION join_cache_level=@save_join_cache_level;
SET SESSION join_buffer_size=@save_join_buffer_size;
DROP TABLE t1,t2;
#
# Bug #802860: crash on join cache + derived + duplicate_weedout 
#
SET SESSION optimizer_switch=
'semijoin=on,materialization=off,firstmatch=off,loosescan=off,derived_with_keys=on';
CREATE TABLE t1 (a int) ;
INSERT IGNORE INTO t1 VALUES (0), (1), (0);
CREATE TABLE t2 (a int) ;
INSERT IGNORE INTO t2 VALUES (0), (3), (0), (2);
SET SESSION join_cache_level = 0;
EXPLAIN
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a IN (SELECT t2.a FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	End temporary
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a IN (SELECT t2.a FROM t2);
a
0
SET SESSION join_cache_level = 1;
EXPLAIN
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a  IN (SELECT t2.a FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.a	1	End temporary
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
SELECT * FROM (SELECT DISTINCT * FROM t1) t
WHERE t.a  IN (SELECT t2.a FROM t2);
a
0
DROP TABLE t1, t2;
SET SESSION join_cache_level=@save_join_cache_level;
#
# Bug #887479: join_cache_level=3 + semijoin=on 
#
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20);
CREATE TABLE t2 (c int, KEY (c));
INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2);
SET @tmp_optimizer_switch=@@optimizer_switch;
SET SESSION optimizer_switch='semijoin=on';
SET SESSION optimizer_switch='semijoin_with_cache=on';
SET SESSION join_cache_level=1;
EXPLAIN
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	PRIMARY	t2	ref	c	c	5	test.t1.b	2	Using index; Start temporary; End temporary
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
a	b
3914	17
3888	20
SET SESSION join_cache_level=3;
EXPLAIN
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
1	PRIMARY	t2	hash_index	c	#hash#c:c	5:5	test.t1.b	8	Using index; Start temporary; End temporary; Using join buffer (flat, BNLH join)
SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2);
a	b
3914	17
3888	20
SET SESSION join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@tmp_optimizer_switch;
DROP TABLE t1,t2;
#
# Bug #899777: join_cache_level=4 + semijoin=on 
#
CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a));
INSERT INTO t1 VALUES (1,8,6), (2,2,8);
CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a));
INSERT INTO t2 VALUES (1,8,6), (2,2,8);
CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a));
INSERT INTO t3 VALUES (1,8,6), (2,2,8);
CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a));
INSERT INTO t4 VALUES (1,8,6), (2,2,8);
SET @tmp_optimizer_switch=@@optimizer_switch;
SET SESSION optimizer_switch='semijoin=on';
SET SESSION optimizer_switch='semijoin_with_cache=on';
SET SESSION join_cache_level=1;
EXPLAIN 
SELECT t1.* FROM t1,t2
WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
AND t1.a = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	idx	idx	5	const	1	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; Using join buffer (flat, BNL join)
1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
SELECT t1.* FROM t1,t2
WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
AND t1.a = 1;
a	b	c
1	8	6
SET SESSION join_cache_level=4;
EXPLAIN 
SELECT t1.* FROM t1,t2
WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
AND t1.a = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	const	idx	idx	5	const	1	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	const	2	Using where; Start temporary; Using join buffer (flat, BNLH join)
1	PRIMARY	t4	hash_ALL	NULL	#hash#$hj	10	const,test.t2.b	2	Using where; End temporary; Using join buffer (incremental, BNLH join)
SELECT t1.* FROM t1,t2
WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
AND t1.a = 1;
a	b	c
1	8	6
SET SESSION join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3,t4;
#
# Bug #899509: an attempt to use hash join with join_cache_level=0 
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (8), (7);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (8), (7);
CREATE TABLE t3 (a int);
INSERT INTO t3 VALUES (8), (7);
set @@optimizer_switch='semijoin_with_cache=off';
set @@optimizer_switch='outer_join_with_cache=off';
set @@optimizer_switch='derived_merge=off,derived_with_keys=off';
SET join_cache_level=0;
EXPLAIN
SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
a
8
8
7
7
SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 );
a
8
8
7
7
SET SESSION join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3;
#
# Bug #900469: semijoin + BNLH + ORDER BY 
#
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (8,10);
CREATE TABLE t2 (c int, d int);
INSERT INTO t2 VALUES (8,10);
INSERT INTO t2 VALUES (9,11);
CREATE TABLE t3 (c int, d int);
INSERT INTO t3 VALUES (8,10);
INSERT INTO t3 VALUES (9,11);
set @@optimizer_switch='semijoin_with_cache=on';
set @@optimizer_switch='firstmatch=off';
SET join_cache_level=1;
EXPLAIN
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
a	b	c	d
8	10	8	10
8	10	9	11
SET join_cache_level=3;
EXPLAIN
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	const	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c);
a	b	c	d
8	10	8	10
8	10	9	11
SET join_cache_level=3;
EXPLAIN
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	const	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d;
a	b	c	d
8	10	8	10
8	10	9	11
SET SESSION join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3;
#
# Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 
#
CREATE TABLE t1 (a char(1));
INSERT INTO t1 VALUES ('x');
CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c));
INSERT INTO t2 VALUES
(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
CREATE TABLE t3 (a CHAR(1));
INSERT INTO t3 VALUES ('x');
CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c));
INSERT INTO t4 VALUES
(9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4);
INSERT INTO t4 VALUES
(19,11,10), (17,12,18), (12,13,15), (14,12,19),
(18,13,18), (13,14,11), (15,15,14);
SET @@optimizer_switch='semijoin=on';
SET @@optimizer_switch='firstmatch=off';
SET @@optimizer_switch='mrr=off';
SET @@optimizer_switch='semijoin_with_cache=off';
set join_cache_level=1;
EXPLAIN
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
1	PRIMARY	t2	range	a,c	a	5	NULL	2	Using index condition; Using where; Using filesort
1	PRIMARY	t4	ref	c	c	5	test.t2.c	2	Using where; Start temporary; End temporary
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
a	a	b	c
x	4	2	9
x	5	5	4
set join_cache_level=4;
EXPLAIN
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
1	PRIMARY	t2	range	a,c	a	5	NULL	2	Using index condition; Using where
1	PRIMARY	t4	ref	c	c	5	test.t2.c	2	Using where; Start temporary; End temporary
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
a	a	b	c
x	4	2	9
x	5	5	4
SET @@optimizer_switch='semijoin_with_cache=on';
set join_cache_level=6;
EXPLAIN
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
1	PRIMARY	t2	range	a,c	a	5	NULL	2	Using index condition; Using where
1	PRIMARY	t4	ref	c	c	5	test.t2.c	2	Using where; Start temporary; End temporary
SELECT * FROM t1,t2
WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND
t2.a BETWEEN 4 and 5
ORDER BY t2.b;
a	a	b	c
x	4	2	9
x	5	5	4
set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3,t4;
#
# Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8)
#
create table t1 (uid int, fid int, index(uid));
insert into t1 values
(1,1), (1,2), (1,3), (1,4),
(2,5), (2,6), (2,7), (2,8),
(3,1), (3,2), (3,9);
create table t2 (uid int primary key, name varchar(128), index(name));
insert into t2 values 
(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
(6, "F"), (7, "G"), (8, "H"), (9, "I");
create table t3 (uid int, fid int, index(uid));
insert into t3 values
(1,1), (1,2), (1,3),(1,4),
(2,5), (2,6), (2,7), (2,8),
(3,1), (3,2), (3,9);
set @@optimizer_switch='semijoin=on';
set @@optimizer_switch='materialization=off';
set @@optimizer_switch='loosescan=off,firstmatch=off';
set @@optimizer_switch='mrr_sort_keys=off';
set join_cache_level=7;
create table t4 (uid int primary key, name varchar(128), index(name));
insert into t4 values 
(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
(6, "F"), (7, "G"), (8, "H"), (9, "I");
explain select name from t2, t1 
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
and t2.uid=t1.fid;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
1	PRIMARY	t1	ALL	uid	NULL	NULL	NULL	11	Using where; End temporary; Using join buffer (flat, BNL join)
1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.fid	1	Using join buffer (flat, BKAH join); Rowid-ordered scan
select name from t2, t1 
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
and t2.uid=t1.fid;
name
A
A
B
B
C
D
E
F
G
H
I
set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
drop table t1,t2,t3,t4;
#
# Bug#50358 - semijoin execution of subquery with outerjoin
#             emplying join buffer
#
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 (6);
INSERT INTO t3 VALUES (1), (2);
set @@optimizer_switch='semijoin=on';
set @@optimizer_switch='materialization=on';
set join_cache_level=0;
EXPLAIN
SELECT * FROM t1 WHERE t1.i IN 
(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM t1 WHERE t1.i IN 
(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
i
1
2
set join_cache_level=2;
EXPLAIN
SELECT * FROM t1 WHERE t1.i IN 
(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
SELECT * FROM t1 WHERE t1.i IN 
(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
i
1
2
set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3;
#
# Bug #12546542: missing row with semijoin=off + join cache
# (LP bug #922971)
#
CREATE TABLE t1 (a varchar(1024));
INSERT INTO t1 VALUES ('v'), ('we');
CREATE TABLE t2 (
a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
);
INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
CREATE TABLE t3 (b int, c int);
INSERT INTO t3 VALUES (4,4);
set @@optimizer_switch='semijoin=off';
set @@optimizer_switch='materialization=off';
set join_cache_level=0;
EXPLAIN
SELECT * FROM t1
WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
WHERE t2.c < 10 OR t3.c > 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
SELECT * FROM t1
WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
WHERE t2.c < 10 OR t3.c > 1);
a
v
we
set join_cache_level=2;
EXPLAIN
SELECT * FROM t1
WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
WHERE t2.c < 10 OR t3.c > 1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
SELECT * FROM t1
WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
WHERE t2.c < 10 OR t3.c > 1);
a
v
we
set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3;
#
# lp:925985 LEFT JOIN with optimize_join_buffer_size=off +
#           join_buffer_size > join_buffer_space_limit
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (5), (3);
CREATE TABLE t2 (a int, b int);
INSERT INTO t2 VALUES
(3,30), (1,10), (7,70), (2,20),
(3,31), (1,11), (7,71), (2,21),
(3,32), (1,12), (7,72), (2,22);
CREATE TABLE t3 (b int, c int);
INSERT INTO t3 VALUES (32, 302), (42,400), (30,300);
set @@optimizer_switch='optimize_join_buffer_size=off';
set @@optimizer_switch='outer_join_with_cache=on';
set join_buffer_space_limit=4096;
set join_buffer_size=4096*2;
set join_cache_level=2;
EXPLAIN
SELECT * FROM t1, t2 LEFT JOIN t3 ON 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	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	12	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a;
a	a	b	b	c
3	3	30	30	300
3	3	31	NULL	NULL
3	3	32	32	302
set join_buffer_space_limit=@save_join_buffer_space_limit;
set join_buffer_size=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t2,t3;
#
# Bug #1058071: LEFT JOIN using blobs 
# (MDEV-564)    when join buffer size is small 
#
CREATE TABLE t1 (
col269 decimal(31,10) unsigned DEFAULT NULL,
col280 geometry DEFAULT NULL,
col281 tinyint(1) DEFAULT NULL,
col282 time NOT NULL,
col284 datetime DEFAULT NULL,
col286 date DEFAULT NULL,
col287 datetime DEFAULT NULL,
col288 decimal(30,29) DEFAULT NULL,
col291 time DEFAULT NULL,
col292 time DEFAULT NULL
) ENGINE=Aria;
INSERT INTO t1 VALUES
(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL),
(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'),
(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'),
(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'),
(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'),
(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'),
(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL),
(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36');
Warnings:
Note	1265	Data truncated for column 'col286' at row 7
CREATE TABLE t2 (b int) ENGINE=Aria;
INSERT INTO t2 VALUES (NULL);
CREATE TABLE t3 (c int) ENGINE=Aria;
INSERT INTO t3 VALUES (NULL);
set @@optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on';
set join_buffer_size=128;
EXPLAIN
SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
GROUP BY elt(t1.col282,1,t1.col280);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
GROUP BY elt(t1.col282,1,t1.col280);
c
1
DROP table t1,t2,t3;
set join_buffer_size=@save_join_buffer_size;
set @@optimizer_switch=@org_optimizer_switch,@local_optimizer_switch= @org_optimizer_switch;
#
# MDEV-5293: outer join, join buffering, and order by - invalid query plan
#
create table t0 (a int primary key) engine=myisam;
insert into t0 values (1);
create table t1(a int) engine=myisam;
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
alter table t1 add b int;
create table t2 like t1;
insert into t2 select * from t1;
#The following must use "Using temporary; Using filesort" and not just "Using filesort":
explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
drop table t0,t1,t2;
# MDEV-6292: huge performance degradation for a sequence
#            of LEFT JOIN operations when using join buffer
#
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
col1 varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id smallint(3) NOT NULL DEFAULT '0',
col2 varchar(25) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=INNODB;
set join_buffer_size=8192;
set join_cache_level=0;
set @init_time:=now();
SELECT t.* 
FROM 
t1 t 
LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
  LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
  LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
ORDER BY 
col1;
id	col1
select timestampdiff(second, @init_time, now()) <= 5;
timestampdiff(second, @init_time, now()) <= 5
1
set join_cache_level=2;
set @init_time:=now();
SELECT t.* 
FROM 
t1 t 
LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
  LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
  LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
ORDER BY 
col1;
id	col1
select timestampdiff(second, @init_time, now()) <= 5;
timestampdiff(second, @init_time, now()) <= 5
1
EXPLAIN
SELECT t.* 
FROM 
t1 t 
LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val"
  LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val"
  LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val"
  LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val"
  LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val"
  LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val"
  LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val"
  LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val"
  LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val"
  LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val"
  LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val"
  LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val"
  LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val"
  LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val"
  LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val"
  LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val"
  LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val"
  LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val"
  LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val"
  LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val"
  LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val"
  LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val"
  LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val"
  LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val"
  LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val"
  LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val"
  LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val"
ORDER BY 
col1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	SIMPLE	c1	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	c2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c5	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c6	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c7	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c8	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c9	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c10	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c11	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c12	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c13	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c14	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c15	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c16	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c17	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c18	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c19	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c20	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c21	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c22	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c23	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c24	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c25	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c26	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
1	SIMPLE	c27	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
set join_buffer_size=@save_join_buffer_size;
set join_cache_level=@save_join_cache_level;
DROP TABLE t1,t2;
#
# MDEV-14960: BNLH used for materialized semi-join
#
CREATE TABLE t1 (i1 int);
CREATE TABLE t2 (e1 int);
CREATE TABLE t4 (e1 int);
CREATE TABLE t5 (e1 int);
INSERT INTO t1 VALUES
(1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 SELECT i1+8 FROM t1;
INSERT INTO t1 SELECT i1+16 FROM t1;
INSERT INTO t1 SELECT i1+32 FROM t1;
INSERT INTO t1 SELECT i1+64 FROM t1;
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
INSERT INTO t5 SELECT * FROM t1;
SET join_cache_level = 6;
SET join_buffer_size=4096;
SET join_buffer_space_limit=4096;
set @@optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on';
EXPLAIN SELECT * FROM t1
WHERE
i1 < 10 AND
i1 IN
(SELECT i1 FROM
(SELECT  (t4.e1) i1  FROM   t4
LEFT JOIN t5  ON t4.e1 = t5.e1
LEFT JOIN  (SELECT e1  FROM t2 ) AS d ON  t4.e1 = d.e1) a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	128	Using where
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	128	
2	MATERIALIZED	t5	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; Using join buffer (flat, BNLH join)
2	MATERIALIZED	t2	hash_ALL	NULL	#hash#$hj	5	test.t4.e1	128	Using where; Using join buffer (incremental, BNLH join)
SELECT * FROM t1
WHERE
i1 < 10 AND
i1 IN
(SELECT i1 FROM
(SELECT  (t4.e1) i1  FROM   t4
LEFT JOIN t5  ON t4.e1 = t5.e1
LEFT JOIN  (SELECT e1  FROM t2 ) AS d ON  t4.e1 = d.e1) a);
i1
1
2
3
4
5
6
7
8
9
set join_cache_level=@save_join_cache_level;
SET join_buffer_size=@save_join_buffer_size;
SET join_buffer_space_limit=@save_join_buffer_space_limit;
set @@optimizer_switch=@local_optimizer_switch;
DROP TABLE t1,t4,t5,t2;
#
# MDEV-16603: BNLH for query with materialized semi-join
#
set join_cache_level=4;
CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (7,'x');
CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB;
INSERT INTO t2 VALUES
(NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'),
(228,'x'),(3,'y'),(1,'z'),(9,'z');
ANALYZE TABLE t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
CREATE TABLE temp
SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1));
SELECT * FROM temp
WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
f1	f2
7	x
7	x
7	x
7	x
7	x
7	x
7	x
EXPLAIN EXTENDED SELECT * FROM temp
WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	100.00	
1	PRIMARY	temp	hash_ALL	NULL	#hash#$hj	9	test.t1.i1,test.t1.v1	7	100.00	Using where; Using join buffer (flat, BNLH join)
2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
2	MATERIALIZED	t2	hash_index	v1	#hash#v1:v1	4:9	test.t1.v1	10	33.33	Using index; Using join buffer (flat, BNLH join)
Warnings:
Note	1003	select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where `test`.`temp`.`f1` = `test`.`t1`.`i1` and `test`.`t2`.`v1` = `test`.`t1`.`v1` and `test`.`temp`.`f2` = `test`.`t1`.`v1`
DROP TABLE t1,t2,temp;
set join_cache_level=@save_join_cache_level;
#
# MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins.
#
set expensive_subquery_limit=0;
create table t1 (c1 int);
create table t2 (c2 int);
create table t3 (c3 int);
insert into t1 values (1), (2);
insert into t2 values (1), (2);
insert into t3 values (2);
explain
select count(*) from t1 straight_join t2
where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
2	UNCACHEABLE SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
set @counter=0;
select count(*) from t1 straight_join t2
where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1);
count(*)
2
select @counter;
@counter
2
explain
select count(*) from t1 straight_join t2
where c1 = c2-0 and
c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
c2 / 2 = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
2	UNCACHEABLE SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
set @counter=0;
select count(*) from t1 straight_join t2
where c1 = c2-0 and
c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and
c2 / 2 = 1;
count(*)
1
select @counter;
@counter
2
drop table t1,t2,t3;
set expensive_subquery_limit=@save_expensive_subquery_limit;
# 
# MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down
# 
create table t1 (a int);
insert into t1 values
(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10);
explain select count(*) from t1, t1 t2 where t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	11	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
set join_buffer_space_limit=1024*8;
explain select count(*) from t1, t1 t2 where t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	11	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer (flat, BNL join)
set join_buffer_space_limit=@save_join_buffer_space_limit;
drop table t1;
#
# MDEV-6687: Assertion `0' failed in Protocol::end_statement on query
#
SET join_cache_level = 3;
# The following should have 
#  - table order PROFILING,user,  
#  - table db accessed with hash_ALL:
explain
SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.db WHERE Select_priv = PAGE_FAULTS_MINOR;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	PROFILING	ALL	NULL	NULL	NULL	NULL	NULL	Using where
1	SIMPLE	db	hash_ALL	NULL	#hash#$hj	1	information_schema.PROFILING.PAGE_FAULTS_MINOR	2	Using where; Using join buffer (flat, BNLH join)
set join_cache_level=@save_join_cache_level;
create table t1 (c1 date not null, key (c1)) engine=innodb;
insert t1 values ('2017-12-27');
create table t2 (pk int, f1 int, f2 int);
insert t2 values (4,1,1), (6,1,1);
set join_buffer_size = 222222208;
select f2 from t2,t1 where f2 = 0;
f2
drop table t1, t2;
set join_buffer_size=@save_join_buffer_size;
#
# MDEV-21104: BNLH used for multi-join query with embedded outer join
#             and possible 'not exists' optimization
#
set join_cache_level=4;
CREATE TABLE t1 (a int) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
INSERT INTO t2 VALUES  (1,2),(2,4);
CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
INSERT INTO t3 VALUES (1),(2);
CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
INSERT INTO t4 VALUES (1),(2);
ANALYZE TABLE t1,t2,t3,t4;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	OK
test.t4	analyze	status	Engine-independent statistics collected
test.t4	analyze	status	OK
SELECT * FROM t2 LEFT JOIN t3 ON c = d;
b	c	d
1	2	2
2	4	NULL
SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4;
b	c	d	e
1	2	2	1
2	4	NULL	1
1	2	2	2
2	4	NULL	2
EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash_index	d	#hash#d:d	5:5	test.t2.c	2	Using where; Using index; Using join buffer (incremental, BNLH join)
1	SIMPLE	t4	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t2.b	2	Using index; Using join buffer (incremental, BNLH join)
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
a	b	c	d	e
1	1	2	2	1
2	1	2	2	1
1	2	4	NULL	2
2	2	4	NULL	2
EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
WHERE e IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	hash_index	d	#hash#d:d	5:5	test.t2.c	2	Using where; Using index; Using join buffer (incremental, BNLH join)
1	SIMPLE	t4	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t2.b	2	Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
WHERE e IS NULL;
a	b	c	d	e
DROP TABLE t1,t2,t3,t4;
set join_cache_level=@save_join_cache_level;
#
# MDEV-24767: forced BNLH used for equi-join supported by compound index
#
create table t1 (a int, b int, c int ) engine=myisam ;
create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ;
insert into t1 values (3,4,2), (5,6,4);
insert into t2 values (3,4,2), (5,6,4);
select t1.a, t1.b, t1.c from t1,t2
where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
a	b	c
3	4	2
5	6	4
explain select t1.a, t1.b, t1.c from t1,t2
where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	12	test.t1.c,test.t1.a,test.t1.b	1	Using index
set join_cache_level=3;
select t1.a, t1.b, t1.c from t1,t2
where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
a	b	c
3	4	2
5	6	4
explain select t1.a, t1.b, t1.c from t1,t2
where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	12:12	test.t1.c,test.t1.a,test.t1.b	2	Using index; Using join buffer (flat, BNLH join)
drop table t1,t2;
set join_cache_level=@save_join_cache_level;
#
# MDEV-21243: Join buffer: condition is checked in wrong place for range access
#
create table t1(a int primary key);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int);
insert into t2 select A.a + 10*B.a from t1 A, t1 B;
create table t3 (
kp1 int,
kp2 int,
col1 int,
col2 int,
key (kp1, kp2)
);
insert into t3
select
A.a,
B.a,
A.a + 100*B.a,
A.a + 100*B.a
from
t2 A, t2 B;
analyze table t3;
Table	Op	Msg_type	Msg_text
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	Table is already up to date
# The following must have "B.col1 + 1 < 33333" attached to table B
# and not to the block-nl-join node:
explain format=json
select *
from t1 a, t3 b
where
b.kp1=a.a and
b.kp1 <= 10 and
b.kp2 <= 10 and
b.col1 +1 < 33333;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "a",
      "access_type": "range",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["a"],
      "rows": 10,
      "filtered": 100,
      "attached_condition": "a.a <= 10",
      "using_index": true
    },
    "block-nl-join": {
      "table": {
        "table_name": "b",
        "access_type": "range",
        "possible_keys": ["kp1"],
        "key": "kp1",
        "key_length": "10",
        "used_key_parts": ["kp1", "kp2"],
        "rows": 836,
        "filtered": 76.43428802,
        "index_condition": "b.kp2 <= 10",
        "attached_condition": "b.kp2 <= 10 and b.col1 + 1 < 33333"
      },
      "buffer_type": "flat",
      "buffer_size": "54",
      "join_type": "BNL",
      "attached_condition": "b.kp1 = a.a"
    }
  }
}
drop table t1,t2,t3;
# End of 10.3 tests
set @@optimizer_switch=@save_optimizer_switch;
set global innodb_stats_persistent= @innodb_stats_persistent_save;
set global innodb_stats_persistent_sample_pages=
@innodb_stats_persistent_sample_pages_save;