[Commits] Rev 2897: Merge in file:///home/igor/maria/maria-5.3-mwl128-merge/

Igor Babaev igor at askmonty.org
Sat Jan 29 04:54:32 EET 2011


At file:///home/igor/maria/maria-5.3-mwl128-merge/

------------------------------------------------------------
revno: 2897 [merge]
revision-id: igor at askmonty.org-20110129025430-221yzgrb7yap99jt
parent: igor at askmonty.org-20110128052302-09617460yxj1kck7
parent: igor at askmonty.org-20110124225450-6rq15gqjcnon3cwr
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-merge
timestamp: Fri 2011-01-28 18:54:30 -0800
message:
  Merge
modified:
  mysql-test/r/join_cache.result join_cache.result-20091221012827-jfu65h0x5bmixhh3-1
  mysql-test/r/join_nested_jcl6.result join_nested_jcl6.res-20091221012846-avi031x14p6hixd0-1
  mysql-test/r/join_outer_jcl6.result join_outer_jcl6.resu-20091221012858-uiftww98yhc31z02-1
  mysql-test/r/select_jcl6.result select_jcl6.result-20091221012908-0kl039gl68crw8rz-1
  mysql-test/r/subselect3_jcl6.result subselect3_jcl6.resu-20100117143923-cf6j4mu5zzng00u7-1
  mysql-test/r/subselect_sj2_jcl6.result subselect_sj2_jcl6.r-20100117143927-r3uxj2zuyjtrnokh-1
  mysql-test/r/subselect_sj_jcl6.result subselect_sj_jcl6.re-20100117143928-7vzk51yaf29cdavp-1
  mysql-test/suite/innodb/r/innodb_mysql.result sp1f-innodb_mysql.result-20060426055153-bychbbfnqtvmvrwccwhn24i6yi46uqjv
  mysql-test/t/join_cache.test   join_cache.test-20091221012705-n3szmbc9blgmmu84-1
  mysql-test/t/subselect_sj_jcl6.test subselect_sj_jcl6.te-20100117144012-tmbazng78xjyw6m1-1
  sql/opt_index_cond_pushdown.cc opt_index_cond_pushd-20091222114938-i4xsno315subf13e-1
  sql/opt_subselect.cc           opt_subselect.cc-20100215190428-nekkl8wisp0k6nlk-1
  sql/sql_join_cache.cc          sql_join_cache.cc-20091221012625-ipp8zu28iijhjmq2-1
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/sql_select.h               sp1f-sql_select.h-19700101030959-oqegfxr76xlgmrzd6qlevonoibfnwzoz
  sql/table.cc                   sp1f-table.cc-19700101030959-nsxtem2adyqzwe6nz4cgrpcmts3o54v7
  sql/table.h                    sp1f-table.h-19700101030959-dv72bajftxj5fbdjuajquappanuv2ija
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2011-01-28 05:23:02 +0000
+++ b/mysql-test/r/join_cache.result	2011-01-29 02:54:30 +0000
@@ -71,7 +71,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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)
@@ -81,148 +82,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 set join_cache_level=2;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -259,7 +149,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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)
@@ -269,148 +160,193 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
-Name	Name	Language
-Leiden	Netherlands	Dutch
-La Matanza	Argentina	Spanish
-Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
-Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
-Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
-Las Palmas de Gran Canaria	Spain	Spanish
-L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
-Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
-Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
-Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
-Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
-Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
-L??zaro C??rdenas	Mexico	Spanish
-Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
-Las Margaritas	Mexico	Spanish
-Lashio (Lasho)	Myanmar	Burmese
-Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
-Ludwigshafen am Rhein	Germany	German
-Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
-Luchou	Taiwan	Min
-Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
-Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
-Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
-Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
-Los Angeles	United States	English
-Las Vegas	United States	English
-Long Beach	United States	English
-Lexington-Fayette	United States	English
-Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
-Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
+Name	Name	Language
+La Matanza	Argentina	Spanish
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
+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	NULL	hj_key	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
+Vientiane	Laos
+Riga	Latvia
+Daugavpils	Latvia
+Maseru	Lesotho
+Beirut	Lebanon
+Tripoli	Lebanon
+Monrovia	Liberia
+Tripoli	Libyan Arab Jamahiriya
+Bengasi	Libyan Arab Jamahiriya
+Misrata	Libyan Arab Jamahiriya
+Vilnius	Lithuania
+Kaunas	Lithuania
+Klaipeda	Lithuania
+?iauliai	Lithuania
+Panevezys	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	NULL	hj_key	3	world.Country.Code	984	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	NULL	hj_key	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
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
+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	NULL	hj_key	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
+Vientiane	Laos
+Riga	Latvia
+Daugavpils	Latvia
+Maseru	Lesotho
+Beirut	Lebanon
+Tripoli	Lebanon
+Monrovia	Liberia
+Tripoli	Libyan Arab Jamahiriya
+Bengasi	Libyan Arab Jamahiriya
+Misrata	Libyan Arab Jamahiriya
+Vilnius	Lithuania
+Kaunas	Lithuania
+Klaipeda	Lithuania
+?iauliai	Lithuania
+Panevezys	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	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.Country.Code	984	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
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
 set join_cache_level=default;
 set join_buffer_size=256;
 show variables like 'join_buffer_size';
@@ -451,158 +387,48 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
 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;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 set join_cache_level=2;
 show variables like 'join_cache_level';
 Variable_name	Value
@@ -639,158 +465,204 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
-1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where; 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;
-Name	Name	Language
-Leiden	Netherlands	Dutch
-La Matanza	Argentina	Spanish
-Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
-Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
-Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
-Las Palmas de Gran Canaria	Spain	Spanish
-L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
-Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
-Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
-Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
-Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
-Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
-L??zaro C??rdenas	Mexico	Spanish
-Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
-Las Margaritas	Mexico	Spanish
-Lashio (Lasho)	Myanmar	Burmese
-Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
-Ludwigshafen am Rhein	Germany	German
-Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
-Luchou	Taiwan	Min
-Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
-Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
-Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
-Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
-Los Angeles	United States	English
-Las Vegas	United States	English
-Long Beach	United States	English
-Lexington-Fayette	United States	English
-Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
-Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
+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	City	ALL	NULL	NULL	NULL	NULL	4079	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	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
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
+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	NULL	hj_key	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
+Vientiane	Laos
+Riga	Latvia
+Daugavpils	Latvia
+Maseru	Lesotho
+Beirut	Lebanon
+Tripoli	Lebanon
+Monrovia	Liberia
+Tripoli	Libyan Arab Jamahiriya
+Bengasi	Libyan Arab Jamahiriya
+Misrata	Libyan Arab Jamahiriya
+Vilnius	Lithuania
+Kaunas	Lithuania
+Klaipeda	Lithuania
+?iauliai	Lithuania
+Panevezys	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	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.Country.Code	984	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
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
+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	NULL	hj_key	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
+Vientiane	Laos
+Riga	Latvia
+Daugavpils	Latvia
+Maseru	Lesotho
+Beirut	Lebanon
+Tripoli	Lebanon
+Monrovia	Liberia
+Tripoli	Libyan Arab Jamahiriya
+Bengasi	Libyan Arab Jamahiriya
+Misrata	Libyan Arab Jamahiriya
+Vilnius	Lithuania
+Kaunas	Lithuania
+Klaipeda	Lithuania
+?iauliai	Lithuania
+Panevezys	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	City	hash	NULL	hj_key	3	world.Country.Code	4079	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	CountryLanguage	hash	NULL	hj_key	3	world.Country.Code	984	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
+Lomas de Zamora	Argentina	Spanish
+Lauro de Freitas	Brazil	Portuguese
+Los Angeles	Chile	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+L??Hospitalet de Llobregat	Spain	Spanish
+Lleida (L??rida)	Spain	Spanish
+Liupanshui	China	Chinese
+Lianyungang	China	Chinese
+Liangcheng	China	Chinese
+Lengshuijiang	China	Chinese
+L??zaro C??rdenas	Mexico	Spanish
+Lagos de Moreno	Mexico	Spanish
+Las Margaritas	Mexico	Spanish
+Lashio (Lasho)	Myanmar	Burmese
+Lalitapur	Nepal	Nepali
+Ludwigshafen am Rhein	Germany	German
+Leverkusen	Germany	German
+Luchou	Taiwan	Min
+Lungtan	Taiwan	Min
+Lower Hutt	New Zealand	English
+Los Teques	Venezuela	Spanish
+Leninsk-Kuznetski	Russian Federation	Russian
+Los Angeles	United States	English
+Long Beach	United States	English
+Lexington-Fayette	United States	English
+Louisville	United States	English
+Little Rock	United States	English
 set join_cache_level=default;
 set join_buffer_size=default;
 show variables like 'join_buffer_size';
@@ -840,7 +712,7 @@
 Country.Name LIKE 'L%' AND City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
-1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Population,Country	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;
@@ -866,165 +738,55 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
+1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Country	Country	3	world.CountryLanguage.Country	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;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 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; Using MRR
-1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	City	hash	Population,Country	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;
@@ -1052,7 +814,7 @@
 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, BNLH join)
+1	SIMPLE	CountryLanguage	hash	PRIMARY	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')
@@ -1150,7 +912,7 @@
 Country.Name LIKE 'L%' AND City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
-1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Population,Country	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;
@@ -1176,165 +938,55 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Country	Country	3	world.CountryLanguage.Country	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;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 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; Using MRR
-1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	City	hash	Population,Country	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;
@@ -1362,7 +1014,7 @@
 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, BNLH join)
+1	SIMPLE	CountryLanguage	hash	PRIMARY	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')
@@ -1486,7 +1138,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
@@ -1496,148 +1149,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -1793,7 +1335,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
@@ -1803,148 +1346,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -2100,7 +1532,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
@@ -2110,148 +1543,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -2407,7 +1729,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
@@ -2417,148 +1740,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -2692,7 +1904,7 @@
 Country.Name LIKE 'L%' AND City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
-1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Population,Country	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;
@@ -2718,165 +1930,55 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
+1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Country	Country	3	world.CountryLanguage.Country	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;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 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; Using MRR
-1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	City	hash	Population,Country	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;
@@ -2906,7 +2008,7 @@
 Country.Name LIKE 'L%' AND City.Population > 100000;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
-1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Population,Country	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;
@@ -2932,165 +2034,55 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
-1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	Country	hash	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	239	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	City	hash	Country	Country	3	world.CountryLanguage.Country	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;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 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; Using MRR
-1	PRIMARY	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	City	hash	Population,Country	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;
@@ -3146,7 +2138,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
@@ -3156,148 +2149,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -3360,7 +2242,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
@@ -3370,148 +2253,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -3574,7 +2346,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
@@ -3584,148 +2357,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -3788,7 +2450,8 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+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	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
@@ -3798,148 +2461,37 @@
 WHERE City.Country=Country.Code AND
 CountryLanguage.Country=Country.Code AND
 City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-CountryLanguage.Percentage > 50;
+CountryLanguage.Percentage > 50 AND
+LENGTH(Language) < LENGTH(City.Name) - 2;
 Name	Name	Language
-Leiden	Netherlands	Dutch
 La Matanza	Argentina	Spanish
 Lomas de Zamora	Argentina	Spanish
-La Plata	Argentina	Spanish
-Lan??s	Argentina	Spanish
-Las Heras	Argentina	Spanish
-La Rioja	Argentina	Spanish
-Li??ge	Belgium	Dutch
-La Paz	Bolivia	Spanish
-Londrina	Brazil	Portuguese
-Limeira	Brazil	Portuguese
-Lages	Brazil	Portuguese
-Luzi??nia	Brazil	Portuguese
 Lauro de Freitas	Brazil	Portuguese
-Linhares	Brazil	Portuguese
-London	United Kingdom	English
-Liverpool	United Kingdom	English
-Leeds	United Kingdom	English
-Leicester	United Kingdom	English
-Luton	United Kingdom	English
 Los Angeles	Chile	Spanish
-La Serena	Chile	Spanish
-La Romana	Dominican Republic	Spanish
-Loja	Ecuador	Spanish
-Luxor	Egypt	Arabic
 Las Palmas de Gran Canaria	Spain	Spanish
 L??Hospitalet de Llobregat	Spain	Spanish
-Legan??s	Spain	Spanish
-Le??n	Spain	Spanish
-Logro??o	Spain	Spanish
 Lleida (L??rida)	Spain	Spanish
-Le-Cap-Ha??tien	Haiti	Haiti Creole
-La Ceiba	Honduras	Spanish
-Livorno	Italy	Italian
-Latina	Italy	Italian
-Lecce	Italy	Italian
-La Spezia	Italy	Italian
-Linz	Austria	German
-London	Canada	English
-Laval	Canada	English
-Longueuil	Canada	English
-Lanzhou	China	Chinese
-Luoyang	China	Chinese
-Liuzhou	China	Chinese
-Liaoyang	China	Chinese
 Liupanshui	China	Chinese
-Liaoyuan	China	Chinese
 Lianyungang	China	Chinese
-Leshan	China	Chinese
-Linyi	China	Chinese
-Luzhou	China	Chinese
-Laiwu	China	Chinese
-Liaocheng	China	Chinese
-Laizhou	China	Chinese
-Linfen	China	Chinese
 Liangcheng	China	Chinese
-Longkou	China	Chinese
-Langfang	China	Chinese
-Liu??an	China	Chinese
-Longjing	China	Chinese
 Lengshuijiang	China	Chinese
-Laiyang	China	Chinese
-Longyan	China	Chinese
-Linhe	China	Chinese
-Leiyang	China	Chinese
-Loudi	China	Chinese
-Luohe	China	Chinese
-Linqing	China	Chinese
-Laohekou	China	Chinese
-Linchuan	China	Chinese
-Lhasa	China	Chinese
-Lianyuan	China	Chinese
-Liyang	China	Chinese
-Liling	China	Chinese
-Linhai	China	Chinese
-Larisa	Greece	Greek
-La Habana	Cuba	Spanish
-Lilongwe	Malawi	Chichewa
-Le??n	Mexico	Spanish
-La Paz	Mexico	Spanish
-La Paz	Mexico	Spanish
 L??zaro C??rdenas	Mexico	Spanish
 Lagos de Moreno	Mexico	Spanish
-Lerdo	Mexico	Spanish
-Los Cabos	Mexico	Spanish
-Lerma	Mexico	Spanish
 Las Margaritas	Mexico	Spanish
 Lashio (Lasho)	Myanmar	Burmese
 Lalitapur	Nepal	Nepali
-Le??n	Nicaragua	Spanish
-Lambar??	Paraguay	Spanish
-Lima	Peru	Spanish
-Lisboa	Portugal	Portuguese
-L??dz	Poland	Polish
-Lublin	Poland	Polish
-Legnica	Poland	Polish
-Lyon	France	French
-Le Havre	France	French
-Lille	France	French
-Le Mans	France	French
-Limoges	France	French
-Link??ping	Sweden	Swedish
-Lund	Sweden	Swedish
-Leipzig	Germany	German
-L??beck	Germany	German
 Ludwigshafen am Rhein	Germany	German
 Leverkusen	Germany	German
-L??nen	Germany	German
-Lahti	Finland	Finnish
-Lausanne	Switzerland	German
-Latakia	Syria	Arabic
 Luchou	Taiwan	Min
 Lungtan	Taiwan	Min
-Liberec	Czech Republic	Czech
-Lviv	Ukraine	Ukrainian
-Lugansk	Ukraine	Ukrainian
-Lutsk	Ukraine	Ukrainian
-Lysyt?ansk	Ukraine	Ukrainian
 Lower Hutt	New Zealand	English
-Lida	Belarus	Belorussian
 Los Teques	Venezuela	Spanish
-Lipetsk	Russian Federation	Russian
-Ljubertsy	Russian Federation	Russian
 Leninsk-Kuznetski	Russian Federation	Russian
-Long Xuyen	Vietnam	Vietnamese
 Los Angeles	United States	English
-Las Vegas	United States	English
 Long Beach	United States	English
 Lexington-Fayette	United States	English
 Louisville	United States	English
-Lincoln	United States	English
-Lubbock	United States	English
 Little Rock	United States	English
-Laredo	United States	English
-Lakewood	United States	English
-Lansing	United States	English
-Lancaster	United States	English
-Lafayette	United States	English
-Lowell	United States	English
-Livonia	United States	English
 EXPLAIN
 SELECT Name FROM City
 WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
@@ -4553,7 +3105,7 @@
 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	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	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
@@ -5407,7 +3959,7 @@
 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	ref	idx	idx	3	test.t2.u	2	Using where
+1	SIMPLE	t3	hash	idx	idx	3	test.t2.u	20	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
@@ -5530,8 +4082,8 @@
 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	ALL	idx1	NULL	NULL	NULL	20	Using temporary; Using filesort
-1	SIMPLE	t3	eq_ref	PRIMARY,idx2	PRIMARY	4	test.t2.i	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t1	ref	idx2	idx2	3	test.t3.v	2	Using join buffer (incremental, BNLH join)
+1	SIMPLE	t3	hash	PRIMARY,idx2	PRIMARY	4	test.t2.i	20	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash	idx2	idx2	3	test.t3.v	15	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 
 GROUP BY t2.v ORDER BY t1.pk,t2.v;
 v
@@ -5582,7 +4134,7 @@
 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; Using join buffer (flat, BNLH join)
+1	SIMPLE	t1	hash	cu	cu	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
@@ -5609,7 +4161,7 @@
 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	t1	ALL	NULL	NULL	NULL	NULL	36	Using where
-1	SIMPLE	t2	ref	idx	idx	10	test.t1.a,const	2	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	10	test.t1.a,const	15	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 = DEFAULT;
@@ -5659,7 +4211,7 @@
 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	2	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	13	test.t1.v	18	Using join buffer (flat, BNLH join)
 SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v;
 v	i
 f	4
@@ -5668,7 +4220,7 @@
 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	2	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	13	func	18	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
@@ -5721,8 +4273,8 @@
 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	ref	idx	idx	1003	test.t1.v	2	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t3	ref	idx	idx	1002	func	3	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	1003	test.t1.v	18	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	hash	idx	idx	1002	func	32	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
@@ -5759,7 +4311,7 @@
 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	ref	idx	idx	515	test.t1.a	2	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	515	test.t1.a	12	Using join buffer (flat, BNLH join)
 SELECT * FROM t1,t2 WHERE t2.a=t1.a;
 pk	a	pk	a
 2	aa	30	aa
@@ -5942,8 +4494,8 @@
 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; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t3	hash	PRIMARY	hj_key	5	test.t1.c1	1	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	NULL	hj_key	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 
@@ -5981,23 +4533,23 @@
 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 ;
+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)
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+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 ;
+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, BNLH join)
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+1	SIMPLE	t3	hash	PRIMARY	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
@@ -6072,7 +4624,7 @@
 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	ref	idx	idx	35	test.t1.a	2	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	35	test.t1.a	15	Using join buffer (flat, BNLH join)
 SELECT * FROM t1,t2 WHERE t1.a=t2.a;
 pk	a	pk	a
 20	BBBB	1	Bbbb
@@ -6187,7 +4739,7 @@
 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	3	Using where; Using index
-1	SIMPLE	t2	ref	idx	idx	5	const	4	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	5	const	23	Using where; 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
@@ -6215,7 +4767,7 @@
 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	ref	f1	f1	13	test.t1.f1	2	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	f1	f1	13	test.t1.f1	12	Using join buffer (flat, BNLH join)
 SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1;
 f1	f2	f1	f2
 SET SESSION join_cache_level = DEFAULT;
@@ -6235,7 +4787,7 @@
 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	Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	idx	idx	4	test.t1.v	8	Using join buffer (flat, BNLH join)
 SELECT a FROM t1,t2 WHERE t2.v = t1.v ;
 a
 11

=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result	2011-01-15 19:14:36 +0000
+++ b/mysql-test/r/join_nested_jcl6.result	2011-01-22 06:48:28 +0000
@@ -81,9 +81,9 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`))
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`))
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM t2
 LEFT JOIN              
@@ -157,10 +157,10 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`))
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`))
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
 FROM t2
 LEFT JOIN              
@@ -187,10 +187,10 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`)))
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`)))
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,t5.a,t5.b
 FROM t2
 LEFT JOIN              
@@ -237,9 +237,9 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t8	hash	NULL	hj_key	5	test.t7.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
-Note	1003	select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`b` = `test`.`t7`.`b`))) where 1
+Note	1003	select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and (`test`.`t8`.`b` = `test`.`t7`.`b`)) and (`test`.`t7`.`b` is not null))) where 1
 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM (t6, t7)
 LEFT JOIN 
@@ -551,16 +551,16 @@
 (t2.a >= 4 OR t2.c IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t8	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)))
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t0,t1
@@ -646,17 +646,17 @@
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t8	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 SELECT t9.a,t9.b
 FROM t9;
 a	b
@@ -845,9 +845,9 @@
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	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	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) where (`test`.`t1`.`a` <= 2)
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) where (`test`.`t1`.`a` <= 2)
 CREATE INDEX idx_b ON t2(b);
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -913,17 +913,17 @@
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash	NULL	hj_key	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t8	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t4(b);
 CREATE INDEX idx_b ON t5(b);
 EXPLAIN EXTENDED
@@ -963,17 +963,17 @@
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, 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_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t8	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t8(b);
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -1012,17 +1012,17 @@
 (t9.a=1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, 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_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 CREATE INDEX idx_b ON t1(b);
 CREATE INDEX idx_a ON t0(a);
 EXPLAIN EXTENDED
@@ -1067,12 +1067,12 @@
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash	NULL	hj_key	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t7.b	2	100.00	Using where; Using join buffer (incremental, BKA join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`)) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on((((`test`.`t6`.`b` < 10) and ((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`))) and (`test`.`t7`.`b` is not null)))) on((((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`)) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
 FROM t0,t1
@@ -1324,7 +1324,7 @@
 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
 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	0	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	5	test.t1.c11	0	Using where; Using join buffer (flat, BNLH join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2010-12-13 10:42:40 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2011-01-05 05:59:41 +0000
@@ -319,11 +319,11 @@
 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null;
 id	select_type	table	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	3	Using where; Not exists; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	2	test.t1.id	3	Using where; Not exists; Using join buffer (flat, BNLH join)
 explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null;
 id	select_type	table	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	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	2	test.t1.id	3	Using where; Using join buffer (flat, BNLH join)
 select count(*) from t1 left join t2 on (t1.id = t2.owner);
 count(*)
 4
@@ -339,11 +339,11 @@
 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null;
 id	select_type	table	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	3	Using where; Not exists; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	2	test.t1.id	3	Using where; Not exists; Using join buffer (flat, BNLH join)
 explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null;
 id	select_type	table	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	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	2	test.t1.id	3	Using where; Using join buffer (flat, BNLH join)
 select count(*) from t2 right join t1 on (t1.id = t2.owner);
 count(*)
 4
@@ -695,8 +695,8 @@
 explain select * from t1 left join t2  on  b1 = a1 left join t3  on  c1 = a1  and  b1 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	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	4	test.t1.a1	2	Using where; Using join buffer (flat, BNLH join)
+1	SIMPLE	t3	hash	NULL	hj_key	5	test.t1.a1	2	Using where; Using join buffer (incremental, BNLH join)
 drop table t1, t2, t3;
 create table t1 (
 a int(11),

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2011-01-26 19:30:29 +0000
+++ b/mysql-test/r/select_jcl6.result	2011-01-29 02:54:30 +0000
@@ -1370,7 +1370,7 @@
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1200	Using where; Not exists; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1200	Using where; Not exists; Using join buffer (flat, BNLH join)
 select companynr,companyname from t2 left join t4 using (companynr) where companynr is null;
 companynr	companyname
 select count(*) from t2 left join t4 using (companynr) where companynr is not null;
@@ -1398,39 +1398,39 @@
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	test.t4.companynr	1199	Using where; Using join buffer (flat, BNLH join)
 select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 companynr	companynr
 37	36
@@ -1438,7 +1438,7 @@
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	1	func	1199	Using where; Using join buffer (flat, BNLH join)
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period
 038008	37	reporters	1008
@@ -2337,7 +2337,7 @@
 1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	const row not found
 1	SIMPLE	t4	const	id4	NULL	NULL	NULL	1	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	hash	NULL	hj_key	4	test.t1.id1	1	Using where; Using join buffer (flat, BNLH join)
 select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id1	id2	id3	id4	id44
@@ -2916,12 +2916,12 @@
 4
 EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON 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	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t2	hash	NULL	hj_key	5	test.t1.a	3	Using where; Using join buffer (flat, BNLH join)
 EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	hash	NULL	hj_key	5	test.t2.a	5	Using where; Using join buffer (flat, BNLH join)
 DROP TABLE t1,t2;
 select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
 x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2010-11-15 01:45:49 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2011-01-22 06:48:28 +0000
@@ -1031,10 +1031,10 @@
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	8	Using temporary; Using filesort
-1	PRIMARY	t21	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	t22	ALL	NULL	NULL	NULL	NULL	26	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	t21	hash	NULL	hj_key	4	test.t11.a	26	Using where; Using join buffer (flat, BNLH join)
+1	PRIMARY	t22	hash	NULL	hj_key	4	test.t11.a	26	Using where; Using join buffer (incremental, BNLH join)
 2	SUBQUERY	t11	ALL	NULL	NULL	NULL	NULL	8	Using where
-2	SUBQUERY	t12	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+2	SUBQUERY	t12	hash	NULL	hj_key	4	test.t11.a	8	Using where; Using join buffer (flat, BNLH join)
 select t21.* from t21,t22 where t21.a = t22.a and 
 t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
 a	b	c
@@ -1151,11 +1151,11 @@
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (incremental, BNL join)
 1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (incremental, BNL join)
-1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	D	hash	NULL	hj_key	5	test.E.a	10	Using where; End temporary; Using join buffer (incremental, BNLH join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
 count(*)
@@ -1192,12 +1192,12 @@
 insert into t3 values (1),(2);
 explain select * from t2 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	hash	NULL	hj_key	4	test.t2.a	4	Using where; End temporary; Using join buffer (flat, BNLH join)
 explain select * from t2 where a in (select a from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	hash	NULL	hj_key	5	test.t2.a	2	Using where; End temporary; Using join buffer (flat, BNLH join)
 explain select * from t2 where a in (select a from t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start temporary
@@ -1211,8 +1211,8 @@
 insert into t1 values (1),(2);
 explain select * from t1 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	hash	NULL	hj_key	6	test.t1.a	2	Using where; End temporary; Using join buffer (flat, BNLH join)
 drop table t1;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int);

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2011-01-15 19:14:36 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2011-01-22 06:48:28 +0000
@@ -138,8 +138,8 @@
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	32	Using where; Using join buffer (flat, BNL join)
-2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
+1	PRIMARY	ot	hash	NULL	hj_key	5	test.it.a	32	Using where; Using join buffer (flat, BNLH join)
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	Using where
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -206,8 +206,8 @@
 from t1 ot where a in (select a from t2 it);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	subselect2	ALL	unique_key	NULL	NULL	NULL	22	
-1	PRIMARY	ot	ALL	NULL	NULL	NULL	NULL	52	Using where; Using join buffer (flat, BNL join)
-2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	
+1	PRIMARY	ot	hash	NULL	hj_key	5	test.it.a	52	Using where; Using join buffer (flat, BNLH join)
+2	SUBQUERY	it	ALL	NULL	NULL	NULL	NULL	22	Using where
 select 
 a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z 
 from t1 ot where a in (select a from t2 it);
@@ -588,7 +588,7 @@
 select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	hash	NULL	hj_key	5	test.t1.a	3	Using where; Using join buffer (flat, BNLH join)
 2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2011-01-17 21:26:04 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2011-01-22 06:48:28 +0000
@@ -1,6 +1,7 @@
 set @save_optimizer_switch_jcl6=@@optimizer_switch;
 set @@optimizer_switch='semijoin_with_cache=on';
 set @@optimizer_switch='outer_join_with_cache=on';
+set @@optimizer_switch='join_cache_hashed=off';
 set join_cache_level=6;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2010-12-27 22:22:05 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2011-01-05 05:59:41 +0000
@@ -2656,7 +2656,7 @@
 WHERE t1.pk = t2.pk AND t2.pk <> 8;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	11	Using where; Using index
-1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index; Using join buffer (flat, BNLH join)
+1	SIMPLE	t2	hash	PRIMARY	PRIMARY	4	test.t1.pk	13	Using index; Using join buffer (flat, BNLH join)
 SELECT t1.pk FROM t1,t2 
 WHERE t1.pk = t2.pk AND t2.pk <> 8;
 pk

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2011-01-28 05:23:02 +0000
+++ b/mysql-test/t/join_cache.test	2011-01-29 02:54:30 +0000
@@ -46,14 +46,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 set join_cache_level=2;
 show variables like 'join_cache_level';
@@ -73,14 +75,76 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
-
-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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
+
+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;
+
+set join_cache_level=3;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+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;
+
+
+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;
+
+
+set join_cache_level=4;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+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;
+
+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;
 
 set join_cache_level=default;
 
@@ -104,14 +168,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 set join_cache_level=2;
 show variables like 'join_cache_level';
@@ -131,14 +197,74 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
-
-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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
+
+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;
+
+set join_cache_level=3;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+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;
+
+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;
+
+set join_cache_level=4;
+show variables like 'join_cache_level';
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country
+  WHERE City.Country=Country.Code AND 
+        Country.Name LIKE 'L%' AND City.Population > 100000;
+
+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;
+
+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;
 
 set join_cache_level=default;
 set join_buffer_size=default;
@@ -182,14 +308,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -232,14 +360,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -283,14 +413,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -332,14 +464,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -381,14 +515,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -430,14 +566,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -482,14 +620,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -518,14 +658,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -554,14 +696,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -590,14 +734,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -626,14 +772,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -662,14 +810,16 @@
   WHERE City.Country=Country.Code AND
         CountryLanguage.Country=Country.Code AND
         City.Name LIKE 'L%' AND Country.Population > 3000000 AND
-        CountryLanguage.Percentage > 50;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 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;
+        CountryLanguage.Percentage > 50 AND
+        LENGTH(Language) < LENGTH(City.Name) - 2;
 
 EXPLAIN
 SELECT Name FROM City
@@ -2642,13 +2792,13 @@
 
 SET SESSION join_cache_level = 6;
 EXPLAIN
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
 
 SET SESSION join_cache_level = 5;
 EXPLAIN
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
-SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
+SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0;
 
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;

=== modified file 'mysql-test/t/subselect_sj_jcl6.test'
--- a/mysql-test/t/subselect_sj_jcl6.test	2010-10-27 23:37:33 +0000
+++ b/mysql-test/t/subselect_sj_jcl6.test	2011-01-05 05:59:41 +0000
@@ -5,6 +5,7 @@
 set @save_optimizer_switch_jcl6=@@optimizer_switch;
 set @@optimizer_switch='semijoin_with_cache=on';
 set @@optimizer_switch='outer_join_with_cache=on';
+set @@optimizer_switch='join_cache_hashed=off';
 
 set join_cache_level=6;
 show variables like 'join_cache_level';

=== modified file 'sql/opt_index_cond_pushdown.cc'
--- a/sql/opt_index_cond_pushdown.cc	2010-12-24 22:36:35 +0000
+++ b/sql/opt_index_cond_pushdown.cc	2011-01-22 02:21:55 +0000
@@ -26,7 +26,7 @@
     FALSE  No
 */
 
-bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno, 
+bool uses_index_fields_only(Item *item, TABLE *tbl, uint keyno,
                             bool other_tbls_ok)
 {
   if (item->const_item())
@@ -279,15 +279,12 @@
       tab            A join tab that has tab->table->file and its condition
                      in tab->select_cond
       keyno          Index for which extract and push the condition
-      other_tbls_ok  TRUE <=> Fields of other non-const tables are allowed
-      factor_out     TRUE <=> Factor out the extracted condition
 
   DESCRIPTION
     Try to extract and push the index condition down to table handler
 */
 
-void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok,
-                     bool factor_out)
+void push_index_cond(JOIN_TAB *tab, uint keyno)
 {
   DBUG_ENTER("push_index_cond");
   Item *idx_cond;
@@ -320,7 +317,7 @@
                  print_where(tab->select_cond, "full cond", QT_ORDINARY););
 
     idx_cond= make_cond_for_index(tab->select_cond, tab->table, keyno,
-                                  other_tbls_ok);
+                                  tab->icp_other_tables_ok);
 
     DBUG_EXECUTE("where",
                  print_where(idx_cond, "idx cond", QT_ORDINARY););
@@ -339,10 +336,8 @@
           /*
             if cache is used then the value is TRUE only 
             for BKA[_UNIQUE] cache (see check_join_cache_usage func).
-            In this case other_tbls_ok is an equivalent of
-            cache->is_key_access().
           */
-          other_tbls_ok &&
+          tab->icp_other_tables_ok &&
           (idx_cond->used_tables() &
            ~(tab->table->map | tab->join->const_table_map)))
         tab->cache_idx_cond= idx_cond;
@@ -360,8 +355,9 @@
       if (idx_remainder_cond != idx_cond)
         tab->ref.disable_cache= TRUE;
 
-      Item *row_cond= factor_out ?  make_cond_remainder(tab->select_cond, TRUE) :
-	                            tab->pre_idx_push_select_cond;
+      Item *row_cond= tab->idx_cond_fact_out ? 
+                        make_cond_remainder(tab->select_cond, TRUE) :
+	                tab->pre_idx_push_select_cond;
 
       DBUG_EXECUTE("where",
                    print_where(row_cond, "remainder cond", QT_ORDINARY););

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2011-01-14 11:03:41 +0000
+++ b/sql/opt_subselect.cc	2011-01-22 06:48:28 +0000
@@ -1326,16 +1326,21 @@
 bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables)
 {
   KEYUSE *keyuse= table->reginfo.join_tab->keyuse;
-  uint key;
 
   if (keyuse)
   {
-    while (1) /* For each key */
+    do
     {
-      key= keyuse->key;
-      KEY *keyinfo= table->key_info + key;
+      uint key= keyuse->key;
+      KEY *keyinfo;
       key_part_map bound_parts= 0;
-      if (keyinfo->flags & HA_NOSAME)
+      bool is_excluded_key= keyuse->is_for_hash_join(); 
+      if (!is_excluded_key)
+      {
+        keyinfo= table->key_info + key;
+        is_excluded_key= !test(keyinfo->flags & HA_NOSAME);
+      }
+      if (!is_excluded_key)
       {
         do  /* For all equalities on all key parts */
         {
@@ -1350,20 +1355,15 @@
 
         if (bound_parts == PREV_BITS(uint, keyinfo->key_parts))
           return TRUE;
-        if (keyuse->table != table)
-          return FALSE;
       }
       else
       {
         do
         {
           keyuse++;
-          if (keyuse->table != table)
-            return FALSE;
-        }
-        while (keyuse->key == key);
+        } while (keyuse->key == key && keyuse->table == table);
       }
-    }
+    } while (keyuse->table == table);
   }
   return FALSE;
 }

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2011-01-15 07:53:27 +0000
+++ b/sql/sql_join_cache.cc	2011-01-22 06:48:28 +0000
@@ -1060,9 +1060,8 @@
   CACHE_FIELD *copy;
   CACHE_FIELD *copy_end;
   uint len= 0;
-  TABLE *table= join_tab->table;
   TABLE_REF *ref= &join_tab->ref;
-  KEY *keyinfo= table->key_info+ref->key;
+  KEY *keyinfo= join_tab->get_keyinfo_by_key_no(ref->key);
 
   /* 
     If some of the key arguments are not from the local cache the key
@@ -2528,7 +2527,7 @@
   pack_length+= get_size_of_rec_offset(); 
   pack_length_with_blob_ptrs+= get_size_of_rec_offset();
 
-  ref_key_info= join_tab->table->key_info+join_tab->ref.key;
+  ref_key_info= join_tab->get_keyinfo_by_key_no(join_tab->ref.key);
   ref_used_key_parts= join_tab->ref.key_parts;
 
   hash_func= &JOIN_CACHE_HASHED::get_hash_idx_simple;
@@ -3437,7 +3436,7 @@
   uchar *key_ref_ptr;
   TABLE *table= join_tab->table;
   TABLE_REF *ref= &join_tab->ref;
-  KEY *keyinfo= table->key_info+ref->key;
+  KEY *keyinfo= join_tab->get_keyinfo_by_key_no(ref->key);
   /* Build the join key value out of the record in the record buffer */
   key_copy(key_buff, table->record[0], keyinfo, key_length, TRUE);
   /* Look for this key in the join buffer */

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-27 04:45:23 +0000
+++ b/sql/sql_select.cc	2011-01-29 02:54:30 +0000
@@ -42,7 +42,7 @@
 const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
 			      "MAYBE_REF","ALL","range","index","fulltext",
 			      "ref_or_null","unique_subquery","index_subquery",
-                              "index_merge"
+                              "index_merge","hash"
 };
 
 const char *copy_to_tmp_table= "Copying to tmp table";
@@ -749,6 +749,8 @@
 
   thd_proc_info(thd, "optimizing");
 
+  set_allowed_join_cache_types();
+
   /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */
   if (convert_join_subqueries_to_semijoins(this))
     DBUG_RETURN(1); /* purecov: inspected */
@@ -3016,7 +3018,8 @@
 	*/              
         while (keyuse->table == table)
         {
-          if (!(keyuse->val->used_tables() & ~join->const_table_map) &&
+          if (!keyuse->is_for_hash_join() && 
+              !(keyuse->val->used_tables() & ~join->const_table_map) &&
               keyuse->val->is_null() && keyuse->null_rejecting)
           {
             s->type= JT_CONST;
@@ -3059,6 +3062,11 @@
 	s->type= JT_REF;
 	while (keyuse->table == table)
 	{
+          if (keyuse->is_for_hash_join())
+	  {
+            keyuse++;
+            continue;
+          }
 	  start_keyuse=keyuse;
 	  key=keyuse->key;
 	  s->keys.set_bit(key);               // TODO: remove this ?
@@ -3522,21 +3530,27 @@
 */
 
 static void
-add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
+add_key_field(JOIN *join,
+              KEY_FIELD **key_fields,uint and_level, Item_func *cond,
               Field *field, bool eq_func, Item **value, uint num_values,
               table_map usable_tables, SARGABLE_PARAM **sargables)
 {
-  uint exists_optimize= 0;
-  if (!(field->flags & PART_KEY_FLAG))
+  uint optimize= 0;  
+  if (eq_func && join->is_allowed_hash_join_access() &&
+      field->hash_join_is_possible())
+  {
+    optimize= KEY_OPTIMIZE_EQ;
+  }   
+  else if (!(field->flags & PART_KEY_FLAG))
   {
     // Don't remove column IS NULL on a LEFT JOIN table
     if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
         !field->table->maybe_null || field->null_ptr)
       return;					// Not a key. Skip it
-    exists_optimize= KEY_OPTIMIZE_EXISTS;
+    optimize= KEY_OPTIMIZE_EXISTS;
     DBUG_ASSERT(num_values == 1);
   }
-  else
+  if (optimize != KEY_OPTIMIZE_EXISTS)
   {
     table_map used_tables=0;
     bool optimizable=0;
@@ -3553,7 +3567,7 @@
       if (!eq_func || (*value)->type() != Item::NULL_ITEM ||
           !field->table->maybe_null || field->null_ptr)
 	return;					// Can't use left join optimize
-      exists_optimize= KEY_OPTIMIZE_EXISTS;
+      optimize= KEY_OPTIMIZE_EXISTS;
     }
     else
     {
@@ -3573,7 +3587,8 @@
          Field BETWEEN ...
          Field IN ...
       */
-      stat[0].key_dependent|=used_tables;
+      if (field->flags & PART_KEY_FLAG)
+        stat[0].key_dependent|=used_tables;
 
       bool is_const=1;
       for (uint i=0; i<num_values; i++)
@@ -3651,8 +3666,8 @@
   (*key_fields)->field=		field;
   (*key_fields)->eq_func=	eq_func;
   (*key_fields)->val=		*value;
-  (*key_fields)->level=		and_level;
-  (*key_fields)->optimize=	exists_optimize;
+  (*key_fields)->level=         and_level;
+  (*key_fields)->optimize=      optimize;
   /*
     If the condition has form "tbl.keypart = othertbl.field" and 
     othertbl.field can be NULL, there will be no matches if othertbl.field 
@@ -3699,14 +3714,14 @@
 */
 
 static void
-add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
+add_key_equal_fields(JOIN *join, KEY_FIELD **key_fields, uint and_level,
                      Item_func *cond, Item_field *field_item,
                      bool eq_func, Item **val,
                      uint num_values, table_map usable_tables,
                      SARGABLE_PARAM **sargables)
 {
   Field *field= field_item->field;
-  add_key_field(key_fields, and_level, cond, field,
+  add_key_field(join, key_fields, and_level, cond, field,
                 eq_func, val, num_values, usable_tables, sargables);
   Item_equal *item_equal= field_item->item_equal;
   if (item_equal)
@@ -3721,7 +3736,7 @@
     {
       if (!field->eq(item->field))
       {
-        add_key_field(key_fields, and_level, cond, item->field,
+        add_key_field(join, key_fields, and_level, cond, item->field,
                       eq_func, val, num_values, usable_tables,
                       sargables);
       }
@@ -3854,7 +3869,7 @@
         values--;
       DBUG_ASSERT(cond_func->functype() != Item_func::IN_FUNC ||
                   cond_func->argument_count() != 2);
-      add_key_equal_fields(key_fields, *and_level, cond_func,
+      add_key_equal_fields(join, key_fields, *and_level, cond_func,
                            (Item_field*) (cond_func->key_item()->real_item()),
                            0, values, 
                            cond_func->argument_count()-1,
@@ -3869,7 +3884,7 @@
         if (is_local_field (cond_func->arguments()[i]))
         {
           field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
-          add_key_equal_fields(key_fields, *and_level, cond_func,
+          add_key_equal_fields(join, key_fields, *and_level, cond_func,
                                field_item, 0, values, 1, usable_tables, 
                                sargables);
         }
@@ -3884,7 +3899,7 @@
 
     if (is_local_field (cond_func->arguments()[0]))
     {
-      add_key_equal_fields(key_fields, *and_level, cond_func,
+      add_key_equal_fields(join, key_fields, *and_level, cond_func,
 	                (Item_field*) (cond_func->arguments()[0])->real_item(),
 		           equal_func,
                            cond_func->arguments()+1, 1, usable_tables,
@@ -3893,7 +3908,7 @@
     if (is_local_field (cond_func->arguments()[1]) &&
 	cond_func->functype() != Item_func::LIKE_FUNC)
     {
-      add_key_equal_fields(key_fields, *and_level, cond_func, 
+      add_key_equal_fields(join, key_fields, *and_level, cond_func, 
                        (Item_field*) (cond_func->arguments()[1])->real_item(),
 		           equal_func,
                            cond_func->arguments(),1,usable_tables,
@@ -3909,7 +3924,7 @@
       Item *tmp=new Item_null;
       if (unlikely(!tmp))                       // Should never be true
 	return;
-      add_key_equal_fields(key_fields, *and_level, cond_func,
+      add_key_equal_fields(join, key_fields, *and_level, cond_func,
 		    (Item_field*) (cond_func->arguments()[0])->real_item(),
 		    cond_func->functype() == Item_func::ISNULL_FUNC,
 			   &tmp, 1, usable_tables, sargables);
@@ -3929,7 +3944,7 @@
       */   
       while ((item= it++))
       {
-        add_key_field(key_fields, *and_level, cond_func, item->field,
+        add_key_field(join, key_fields, *and_level, cond_func, item->field,
                       TRUE, &const_item, 1, usable_tables, sargables);
       }
     }
@@ -3950,7 +3965,7 @@
           if (!field->eq(item->field))
           {
             Item *tmp_item= item;
-            add_key_field(key_fields, *and_level, cond_func, field,
+            add_key_field(join, key_fields, *and_level, cond_func, field,
                           TRUE, &tmp_item, 1, usable_tables,
                           sargables);
           }
@@ -3971,6 +3986,58 @@
   return found;
 }
 
+
+/**
+  Add a new keuse to the specified array of KEYUSE objects
+
+  @param[in,out]  keyuse_array  array of keyuses to be extended 
+  @param[in]      key_field     info on the key use occurrence
+  @param[in]      key           key number for the keyse to be added
+  @param[in]      part          key part for the keyuse to be added
+
+  @note
+  The function builds a new KEYUSE object for a key use utilizing the info
+  on the left and right parts of the given key use  extracted from the 
+  structure key_field, the key number and key part for this key use. 
+  The built object is added to the dynamic array keyuse_array.
+
+  @retval         0             the built object is succesfully added 
+  @retval         1             otherwise
+*/
+
+static bool
+add_keyuse(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field,
+          uint key, uint part)
+{
+  KEYUSE keyuse;
+  Field *field= key_field->field;
+
+  keyuse.table= field->table;
+  keyuse.val= key_field->val;
+  keyuse.key= key;
+  if (!is_hash_join_key_no(key))
+  {
+    keyuse.keypart=part;
+    keyuse.keypart_map= (key_part_map) 1 << part;
+  }
+  else
+  {
+    /* 
+      If this is a key use for hash join then keypart of
+      the added element actually contains the field number.
+    */
+    keyuse.keypart= field->field_index;
+    keyuse.keypart_map= (key_part_map) 0;
+  }
+  keyuse.used_tables= key_field->val->used_tables();
+  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
+  keyuse.null_rejecting= key_field->null_rejecting;
+  keyuse.cond_guard= key_field->cond_guard;
+  keyuse.sj_pred_no= key_field->sj_pred_no;
+  return (insert_dynamic(keyuse_array,(uchar*) &keyuse));
+}
+
+
 /*
   Add all keys with uses 'field' for some keypart
   If field->and_level != and_level then only mark key_part as const_part
@@ -3981,11 +4048,10 @@
 */
 
 static bool
-add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
+add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field)
 {
   Field *field=key_field->field;
   TABLE *form= field->table;
-  KEYUSE keyuse;
 
   if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
   {
@@ -4001,22 +4067,24 @@
       {
 	if (field->eq(form->key_info[key].key_part[part].field))
 	{
-	  keyuse.table= field->table;
-	  keyuse.val =  key_field->val;
-	  keyuse.key =  key;
-	  keyuse.keypart=part;
-	  keyuse.keypart_map= (key_part_map) 1 << part;
-	  keyuse.used_tables=key_field->val->used_tables();
-	  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
-          keyuse.ref_table_rows= 0;
-          keyuse.null_rejecting= key_field->null_rejecting;
-          keyuse.cond_guard= key_field->cond_guard;
-          keyuse.sj_pred_no= key_field->sj_pred_no;
-	  if (insert_dynamic(keyuse_array,(uchar*) &keyuse))
+          if (add_keyuse(keyuse_array, key_field, key, part))
             return TRUE;
 	}
       }
     }
+    if (field->hash_join_is_possible() &&
+        (key_field->optimize & KEY_OPTIMIZE_EQ) &&
+        key_field->val->used_tables())
+    {
+      /* 
+        If a key use is extracted from an equi-join predicate then it is
+        added not only as a key use for every index whose component can
+        be evalusted utilizing this key use, but also as a key use for
+        hash join. Such key uses are marked with a special key number. 
+      */    
+      if (add_keyuse(keyuse_array, key_field, get_hash_join_key_no(), 0))
+        return TRUE;
+    }
   }
   return FALSE;
 }
@@ -4329,31 +4397,34 @@
     found_eq_constant=0;
     for (i=0 ; i < keyuse->elements-1 ; i++,use++)
     {
-      if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL)
-	use->table->const_key_parts[use->key]|= use->keypart_map;
-      if (use->keypart != FT_KEYPART)
+      if (!use->is_for_hash_join())
       {
-	if (use->key == prev->key && use->table == prev->table)
-	{
-	  if (prev->keypart+1 < use->keypart ||
-	      (prev->keypart == use->keypart && found_eq_constant))
-	    continue;				/* remove */
-	}
-	else if (use->keypart != 0)		// First found must be 0
-	  continue;
+        if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL)
+	  use->table->const_key_parts[use->key]|= use->keypart_map;
+        if (use->keypart != FT_KEYPART)
+        {
+	  if (use->key == prev->key && use->table == prev->table)
+	  {
+	    if (prev->keypart+1 < use->keypart ||
+	        (prev->keypart == use->keypart && found_eq_constant))
+	      continue;				/* remove */
+	  }
+	  else if (use->keypart != 0)		// First found must be 0
+	    continue;
+        }
+
+        prev= use;
+        found_eq_constant= !use->used_tables;
+        use->table->reginfo.join_tab->checked_keys.set_bit(use->key);
       }
-
 #ifdef HAVE_valgrind
       /* Valgrind complains about overlapped memcpy when save_pos==use. */
       if (save_pos != use)
 #endif
         *save_pos= *use;
-      prev=use;
-      found_eq_constant= !use->used_tables;
       /* Save ptr to first use */
       if (!use->table->reginfo.join_tab->keyuse)
-	use->table->reginfo.join_tab->keyuse=save_pos;
-      use->table->reginfo.join_tab->checked_keys.set_bit(use->key);
+	use->table->reginfo.join_tab->keyuse= save_pos;
       save_pos++;
     }
     i=(uint) (save_pos-(KEYUSE*) keyuse->buffer);
@@ -4498,6 +4569,35 @@
 }
 
 
+/* Estimate of the number matching candidates in the joined table */
+
+inline
+ha_rows matching_candidates_in_table(JOIN_TAB *s, bool with_found_constraint)
+{
+  ha_rows records= s->found_records;
+  /*
+    If there is a filtering condition on the table (i.e. ref analyzer found
+    at least one "table.keyXpartY= exprZ", where exprZ refers only to tables
+    preceding this table in the join order we're now considering), then 
+    assume that 25% of the rows will be filtered out by this condition.
+
+    This heuristic is supposed to force tables used in exprZ to be before
+    this table in join order.
+  */
+  if (with_found_constraint)
+    records-= records/4;
+
+    /*
+      If applicable, get a more accurate estimate. Don't use the two
+      heuristics at once.
+    */
+  if (s->table->quick_condition_rows != s->found_records)
+    records= s->table->quick_condition_rows;
+
+  return records;
+}
+
+
 /**
   Find the best access path for an extension of a partial execution
   plan and add this path to the plan.
@@ -4547,16 +4647,21 @@
   double tmp;
   ha_rows rec;
   bool best_uses_jbuf= FALSE;
+  MY_BITMAP *eq_join_set= &s->table->eq_join_set;
+  KEYUSE *hj_start_key= 0;
 
   Loose_scan_opt loose_scan_opt;
   DBUG_ENTER("best_access_path");
   
+  bitmap_clear_all(eq_join_set);
+
   loose_scan_opt.init(join, s, remaining_tables);
   
   if (s->keyuse)
   {                                            /* Use key if possible */
+    KEYUSE *keyuse;
+    KEYUSE *start_key=0;
     TABLE *table= s->table;
-    KEYUSE *keyuse,*start_key=0;
     double best_records= DBL_MAX;
     uint max_key_part=0;
 
@@ -4564,15 +4669,33 @@
     rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  // Assumed records/key
     for (keyuse=s->keyuse ; keyuse->table == table ;)
     {
+      KEY *keyinfo;
       key_part_map found_part= 0;
       table_map found_ref= 0;
       uint key= keyuse->key;
-      KEY *keyinfo= table->key_info+key;
       bool ft_key=  (keyuse->keypart == FT_KEYPART);
       /* Bitmap of keyparts where the ref access is over 'keypart=const': */
       key_part_map const_part= 0;
       /* The or-null keypart in ref-or-null access: */
       key_part_map ref_or_null_part= 0;
+      if (is_hash_join_key_no(key))
+      {
+        /* 
+          Hash join as any join employing join buffer can be used to join
+          only those tables that are joined after the first non const table
+	*/  
+        if (!(remaining_tables & keyuse->used_tables) &&
+            idx > join->const_tables)
+        {
+          if (!hj_start_key)
+            hj_start_key= keyuse;
+          bitmap_set_bit(eq_join_set, keyuse->keypart);
+        }
+        keyuse++;
+        continue;
+      }
+
+      keyinfo= table->key_info+key;
 
       /* Calculate how many key segments of the current key we can use */
       start_key= keyuse;
@@ -4913,6 +5036,40 @@
     records= best_records;
   }
 
+  /* 
+    If there is no key to access the table, but there is an equi-join
+    predicate connecting the table with the privious tables then we
+    consider the possibility of using hash join.
+    We need also to check that:
+    (1) s is inner table of semi-join -> join cache is allowed for semijoins
+    (2) s is inner table of outer join -> join cache is allowed for outer joins
+  */  
+  if (idx > join->const_tables && best_key == 0 && 
+     !bitmap_is_clear_all(eq_join_set) &&  !disable_jbuf &&
+      (!s->emb_sj_nest ||                     
+       join->allowed_semijoin_with_cache) &&    // (1)
+      (!(s->table->map & join->outer_join) ||
+       join->allowed_outer_join_with_cache))    // (2)
+  {
+    double join_sel= 0.1;
+    /* Estimate the cost of  the hash join access to the table */
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
+
+    tmp= s->table->file->scan_time();
+    /* We read the table as many times as join buffer becomes full. */
+    tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
+                          record_count /
+                          (double) thd->variables.join_buff_size));
+    tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+    best_time= tmp + 
+               (record_count*join_sel) / TIME_FOR_COMPARE * rnd_records;
+    best= tmp;
+    records= rows2double(rnd_records);
+    best_key= hj_start_key;
+    best_ref_depends_map= 0;
+    best_uses_jbuf= test(!disable_jbuf);
+   }
+
   /*
     Don't test table scan if it can't be better.
     Prefer key lookup if we would use the same key for scanning.
@@ -4948,25 +5105,7 @@
         ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
       !(s->table->force_index && best_key && !s->quick))                 // (4)
   {                                             // Check full join
-    ha_rows rnd_records= s->found_records;
-    /*
-      If there is a filtering condition on the table (i.e. ref analyzer found
-      at least one "table.keyXpartY= exprZ", where exprZ refers only to tables
-      preceding this table in the join order we're now considering), then 
-      assume that 25% of the rows will be filtered out by this condition.
-
-      This heuristic is supposed to force tables used in exprZ to be before
-      this table in join order.
-    */
-    if (found_constraint)
-      rnd_records-= rnd_records/4;
-
-    /*
-      If applicable, get a more accurate estimate. Don't use the two
-      heuristics at once.
-    */
-    if (s->table->quick_condition_rows != s->found_records)
-      rnd_records= s->table->quick_condition_rows;
+    ha_rows rnd_records= matching_candidates_in_table(s, found_constraint);
 
     /*
       Range optimizer never proposes a RANGE if it isn't better
@@ -5030,7 +5169,8 @@
     */
     if (best == DBL_MAX ||
         (tmp  + record_count/(double) TIME_FOR_COMPARE*rnd_records <
-         best + record_count/(double) TIME_FOR_COMPARE*records))
+         (best_key->is_for_hash_join() ? best_time :
+          best + record_count/(double) TIME_FOR_COMPARE*records)))
     {
       /*
         If the table has a range (s->quick is set) make_join_select()
@@ -5741,6 +5881,10 @@
   DBUG_EXECUTE("opt", print_plan(join, idx, record_count, read_time, read_time,
                                 "part_plan"););
 
+  /* 
+    If we are searching for the execution plan of a materialized semi-join nest
+    then allowed_tables contains bits only for the tables from this nest.
+  */
   table_map allowed_tables= ~(table_map)0;
   if (join->emb_sjm_nest)
     allowed_tables= join->emb_sjm_nest->sj_inner_tables & ~join->const_table_map;
@@ -5799,7 +5943,7 @@
           if (best_record_count >= current_record_count &&
               best_read_time >= current_read_time &&
               /* TODO: What is the reasoning behind this condition? */
-              (!(s->key_dependent & remaining_tables) ||
+              (!(s->key_dependent & allowed_tables & remaining_tables) ||
                join->positions[idx].records_read < 2.0))
           {
             best_record_count= current_record_count;
@@ -6009,39 +6153,6 @@
 }
 
 
-/**
-  @brief
-  Check whether hash join algorithm can be used to join this table   
-
-  @details
-  This function finds out whether the ref items that have been chosen
-  by the planner to access this table can be used for hash join algorithms.
-  The answer depends on a certain property of the the fields of the
-  joined tables on which the hash join key is built. If hash join is
-  allowed for all these fields the answer is positive.
-  
-  @note
-  The function is supposed to be called now only after the function
-  get_best_combination has been called.
-
-  @retval TRUE    it's possible to use hash join to join this table
-  @retval FALSE   otherwise
-*/
-
-bool JOIN_TAB::hash_join_is_possible()
-{
-  if (type != JT_REF && type != JT_EQ_REF)
-    return FALSE;
-  KEY *keyinfo= &table->key_info[ref.key];
-  for (uint i= 0; i < ref.key_parts; i++)
-  {
-    if (!keyinfo->key_part[i].field->hash_join_is_possible())
-      return FALSE;
-  }
-  return TRUE;
-}
-
-
 /* 
   @brief
   Extract pushdown conditions for a table scan
@@ -6082,6 +6193,37 @@
 }
 
 
+/**
+  @brief
+  Check whether hash join algorithm can be used to join this table   
+
+  @details
+  This function finds out whether the ref items that have been chosen
+  by the planner to access this table can be used for hash join algorithms.
+  The answer depends on a certain property of the the fields of the
+  joined tables on which the hash join key is built.
+  
+  @note
+  At present the function is supposed to be called only after the function
+  get_best_combination has been called.
+
+  @retval TRUE    it's possible to use hash join to join this table
+  @retval FALSE   otherwise
+*/
+
+bool JOIN_TAB::hash_join_is_possible()
+{
+  if (type != JT_REF && type != JT_EQ_REF)
+    return FALSE;
+  if (!is_ref_for_hash_join())
+  {
+    KEY *keyinfo= table->key_info + ref.key;
+    return keyinfo->key_part[0].field->hash_join_is_possible();
+  }
+  return TRUE;
+}
+
+
 static uint
 cache_record_length(JOIN *join,uint idx)
 {
@@ -6245,7 +6387,7 @@
 
     if (j->type == JT_SYSTEM)
       continue;
-    if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key) || 
+    if ( !(keyuse= join->best_positions[tablenr].key) || 
         (join->best_positions[tablenr].sj_strategy == SJ_OPT_LOOSE_SCAN))
     {
       j->type=JT_ALL;
@@ -6263,22 +6405,108 @@
   DBUG_RETURN(0);
 }
 
-
-static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
-			       table_map used_tables)
+/**
+  Create a descriptor of hash join key to access a given join table  
+
+  @param   join         join which the join table belongs to
+  @param   join_tab     the join table to access
+  @param   org_keyuse   beginning of the key uses to join this table
+  @param   used_tables  bitmap of the previous tables
+
+  @details
+  This function first finds key uses that can be utilized by the hash join
+  algorithm to join join_tab to the previous tables marked in the bitmap 
+  used_tables.  The tested key uses are taken from the array of all key uses
+  for 'join' starting from the position org_keyuse. After all interesting key
+  uses have been found the function builds a descriptor of the corresponding
+  key that is used by the hash join algorithm would it be chosen to join
+  the table join_tab.
+
+  @retval  FALSE  the descriptor for a hash join key is successfully created
+  @retval  TRUE   otherwise
+*/
+
+static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab,
+                                    KEYUSE *org_keyuse, table_map used_tables)
 {
-  KEYUSE *keyuse=org_keyuse;
-  bool ftkey=(keyuse->keypart == FT_KEYPART);
+  KEY *keyinfo;
+  KEY_PART_INFO *key_part_info;
+  KEYUSE *keyuse= org_keyuse;
+  uint key_parts= 0;
   THD  *thd= join->thd;
-  uint keyparts,length,key;
+  TABLE *table= join_tab->table;
+  bool first_keyuse= TRUE;
+  DBUG_ENTER("create_hj_key_for_table");
+
+  do
+  {
+    if (!(~used_tables & keyuse->used_tables) &&
+	(first_keyuse || keyuse->keypart != (keyuse-1)->keypart))
+      key_parts++;
+    first_keyuse= FALSE;
+    keyuse++;
+  } while (keyuse->table == table && keyuse->is_for_hash_join());
+  if (!key_parts)
+    DBUG_RETURN(TRUE);
+  /* This memory is allocated only once for the joined table join_tab */
+  if (!(keyinfo= (KEY *) thd->alloc(sizeof(KEY))) ||
+      !(key_part_info = (KEY_PART_INFO *) thd->alloc(sizeof(KEY_PART_INFO)*
+                                                     key_parts)))
+    DBUG_RETURN(TRUE);
+  keyinfo->usable_key_parts= keyinfo->key_parts = key_parts;
+  keyinfo->key_part= key_part_info;
+  keyinfo->key_length=0;
+  keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+  keyinfo->flags= HA_GENERATED_KEY;
+  keyinfo->name= (char *) "hj_key";
+  keyinfo->rec_per_key= (ulong*) thd->calloc(sizeof(ulong)*key_parts);
+  if (!keyinfo->rec_per_key)
+    DBUG_RETURN(TRUE);
+  keyinfo->key_part= key_part_info;
+
+  first_keyuse= TRUE;
+  keyuse= org_keyuse;
+  do
+  {
+    if (!(~used_tables & keyuse->used_tables) &&
+        (first_keyuse || keyuse->keypart != (keyuse-1)->keypart))
+    {
+      Field *field= table->field[keyuse->keypart];
+      table->create_key_part_by_field(keyinfo, key_part_info, field);
+      first_keyuse= FALSE;
+      key_part_info++;
+    }
+    keyuse++;
+  } while (keyuse->table == table && keyuse->is_for_hash_join());
+
+  join_tab->hj_key= keyinfo;
+
+  DBUG_RETURN(FALSE);
+}
+
+
+static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
+                               KEYUSE *org_keyuse, table_map used_tables)
+{
+  uint keyparts, length, key;
   TABLE *table;
   KEY *keyinfo;
+  KEYUSE *keyuse= org_keyuse;
+  bool ftkey= (keyuse->keypart == FT_KEYPART);
+  THD *thd= join->thd;
   DBUG_ENTER("create_ref_for_key");
 
   /*  Use best key from find_best */
-  table=j->table;
-  key=keyuse->key;
-  keyinfo=table->key_info+key;
+  table= j->table;
+  key= keyuse->key;
+  if (!is_hash_join_key_no(key))
+    keyinfo= table->key_info+key;
+  else
+  {
+    if (create_hj_key_for_table(join, j, org_keyuse, used_tables))
+      DBUG_RETURN(TRUE);
+    keyinfo= j->hj_key;
+  }
 
   if (ftkey)
   {
@@ -6301,27 +6529,28 @@
     {
       if (!(~used_tables & keyuse->used_tables))
       {
-	if (keyparts == keyuse->keypart &&
-	    !(found_part_ref_or_null & keyuse->optimize))
-	{
-	  keyparts++;
-	  length+= keyinfo->key_part[keyuse->keypart].store_length;
-	  found_part_ref_or_null|= keyuse->optimize;
-	}
+        if ((is_hash_join_key_no(key) && 
+            (keyparts == 0 || keyuse->keypart != (keyuse-1)->keypart)) ||
+            (!is_hash_join_key_no(key) && keyparts == keyuse->keypart &&
+             !(found_part_ref_or_null & keyuse->optimize)))
+        {
+           length+= keyinfo->key_part[keyparts].store_length;
+           keyparts++;
+           found_part_ref_or_null|= keyuse->optimize & ~KEY_OPTIMIZE_EQ;
+        }
       }
       keyuse++;
     } while (keyuse->table == table && keyuse->key == key);
   } /* not ftkey */
 
   /* set up fieldref */
-  keyinfo=table->key_info+key;
-  j->ref.key_parts=keyparts;
-  j->ref.key_length=length;
-  j->ref.key=(int) key;
+  j->ref.key_parts= keyparts;
+  j->ref.key_length= length;
+  j->ref.key= (int) key;
   if (!(j->ref.key_buff= (uchar*) thd->calloc(ALIGN_SIZE(length)*2)) ||
       !(j->ref.key_copy= (store_key**) thd->alloc((sizeof(store_key*) *
-						   (keyparts+1)))) ||
-      !(j->ref.items=    (Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
+						          (keyparts+1)))) ||
+      !(j->ref.items=(Item**) thd->alloc(sizeof(Item*)*keyparts)) ||
       !(j->ref.cond_guards= (bool**) thd->alloc(sizeof(uint*)*keyparts)))
   {
     DBUG_RETURN(TRUE);
@@ -6351,9 +6580,11 @@
     uint i;
     for (i=0 ; i < keyparts ; keyuse++,i++)
     {
-      while (keyuse->keypart != i ||
-	     ((~used_tables) & keyuse->used_tables))
-	keyuse++;				/* Skip other parts */
+      while (((~used_tables) & keyuse->used_tables) || 
+	     (keyuse->keypart != 
+              (is_hash_join_key_no(key) ?
+                 keyinfo->key_part[i].field->field_index : i))) 
+	 keyuse++;                              	/* Skip other parts */ 
 
       uint maybe_null= test(keyinfo->key_part[i].null_bit);
       j->ref.items[i]=keyuse->val;		// Save for cond removal
@@ -6364,10 +6595,12 @@
       if (!keyuse->used_tables &&
 	  !(join->select_options & SELECT_DESCRIBE))
       {					// Compare against constant
-	store_key_item tmp(thd, keyinfo->key_part[i].field,
+	store_key_item tmp(thd, 
+                           keyinfo->key_part[i].field,
                            key_buff + maybe_null,
                            maybe_null ?  key_buff : 0,
-                           keyinfo->key_part[i].length, keyuse->val,
+                           keyinfo->key_part[i].length,
+                           keyuse->val,
                            FALSE);
 	if (thd->is_fatal_error)
 	  DBUG_RETURN(TRUE);
@@ -6385,7 +6618,7 @@
       */
       if ((keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && maybe_null)
 	null_ref_key= key_buff;
-      key_buff+=keyinfo->key_part[i].store_length;
+      key_buff+= keyinfo->key_part[i].store_length;
     }
   } /* not ftkey */
   *ref_key=0;				// end_marker
@@ -7355,11 +7588,18 @@
   if (join_tab->use_join_cache)
   {
     join_tab->use_join_cache= FALSE;
+    join_tab->used_join_cache_level= 0;
     /*
       It could be only sub_select(). It could not be sub_seject_sjm because we
       don't do join buffering for the first table in sjm nest. 
     */
     join_tab[-1].next_select= sub_select;
+    if (join_tab->type == JT_REF && join_tab->is_ref_for_hash_join())
+    {
+      join_tab->type= JT_ALL;
+      join_tab->ref.key_parts= 0;
+    }
+    join_tab->join->return_tab= join_tab;
   }
 }
 
@@ -7538,14 +7778,9 @@
   SYNOPSIS
     check_join_cache_usage()
       tab                 joined table to check join buffer usage for
-      join                join for which the check is performed
       options             options of the join
       no_jbuf_after       don't use join buffering after table with this number
       prev_tab            previous join table
-      icp_other_tables_ok OUT TRUE if condition pushdown supports
-                          other tables presence
-      idx_cond_fact_out   OUT TRUE if condition pushed to the index is factored
-                          out of the condition pushed to the table
 
   DESCRIPTION
     The function finds out whether the table 'tab' can be joined using a join
@@ -7573,12 +7808,11 @@
     for semi-join operations.
     If the optimizer switch join_cache_incremental is off no incremental join
     buffers are used.
-    If the optimizer switch join_cache_hashed is off then the optimizer does
-    not use neither BNLH algorithm, nor BKAH algorithm to perform join
-    operations.
+    If the optimizer switch join_cache_hashed is off then the optimizer uses
+    neither BNLH algorithm, nor BKAH algorithm to perform join operations.
 
-    If the optimizer switch join_cache_bka is off then the optimizer does not
-    use neither BKA algprithm, nor BKAH algorithm to perform join operation.
+    If the optimizer switch join_cache_bka is off then the optimizer uses
+    neither BKA algorithm, nor BKAH algorithm to perform join operation.
     The valid settings for join_cache_level lay in the interval 0..8.
     If it set to 0 no join buffers are used to perform join operations.
     Currently we differentiate between join caches of 8 levels:
@@ -7624,11 +7858,20 @@
     If the function creates a join cache object it tries to initialize it. The
     failure to do this results in an invocation of the function that destructs
     the created object.
+    If the function decides that but some reasons no join buffer can be used
+    for a table it calls the function revise_cache_usage that checks
+    whether join cache should be denied for some previous tables. In this case
+    a pointer to the first table for which join cache usage has been denied
+    is passed in join->return_val (see the function set_join_cache_denial).
+    
+    The functions changes the value the fields tab->icp_other_tables_ok and
+    tab->idx_cond_fact_out to FALSE if the chosen join cache algorithm 
+    requires it.
  
   NOTES
     An inner table of a nested outer join or a nested semi-join can be currently
     joined only when a linked cache object is employed. In these cases setting
-    join cache level to an odd number results in denial of usage of any join
+    join_cache_incremental to 'off' results in denial of usage of any join
     buffer when joining the table.
     For a nested outer join/semi-join, currently, we either use join buffers for
     all inner tables or for none of them. 
@@ -7667,28 +7910,27 @@
 
 static
 uint check_join_cache_usage(JOIN_TAB *tab,
-                            JOIN *join, ulonglong options,
+                            ulonglong options,
                             uint no_jbuf_after,
-                            JOIN_TAB *prev_tab,
-                            bool *icp_other_tables_ok,
-                            bool *idx_cond_fact_out)
+                            JOIN_TAB *prev_tab)
 {
-  uint flags;
   COST_VECT cost;
-  ha_rows rows;
+  uint flags= 0;
+  ha_rows rows= 0;
   uint bufsz= 4096;
   JOIN_CACHE *prev_cache=0;
-  uint cache_level= join->thd->variables.join_cache_level;
+  JOIN *join= tab->join;
+  uint cache_level= tab->used_join_cache_level;
   bool force_unlinked_cache=
-    !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL);
-  bool no_hashed_cache= 
-    !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_HASHED);
+         !(join->allowed_join_cache_types & JOIN_CACHE_INCREMENTAL_BIT);
+  bool no_hashed_cache=
+         !(join->allowed_join_cache_types & JOIN_CACHE_HASHED_BIT);
   bool no_bka_cache= 
-    !optimizer_flag(join->thd, OPTIMIZER_SWITCH_JOIN_CACHE_BKA);
+         !(join->allowed_join_cache_types & JOIN_CACHE_BKA_BIT);
   uint i= tab - join->join_tab;
 
-  *icp_other_tables_ok= TRUE;
-  *idx_cond_fact_out= TRUE;
+  join->return_tab= 0;
+
   if (cache_level == 0 || i == join->const_tables || !prev_tab)
     return 0;
 
@@ -7705,10 +7947,10 @@
     goto no_join_cache;
 
   if (tab->is_inner_table_of_semi_join_with_first_match() &&
-      !optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE))
+      !join->allowed_semijoin_with_cache)
     goto no_join_cache;
   if (tab->is_inner_table_of_outer_join() &&
-      !optimizer_flag(join->thd, OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE))
+      !join->allowed_outer_join_with_cache)
     goto no_join_cache;
 
   /*
@@ -7760,8 +8002,7 @@
       goto no_join_cache; 
   }       
 
-  if (!force_unlinked_cache)
-    prev_cache= prev_tab->cache;
+  prev_cache= prev_tab->cache;
 
   switch (tab->type) {
   case JT_ALL:
@@ -7770,7 +8011,7 @@
     if ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache)) &&
         ((options & SELECT_DESCRIBE) || !tab->cache->init()))
     {
-      *icp_other_tables_ok= FALSE;
+      tab->icp_other_tables_ok= FALSE;
       return (2-test(!prev_cache));
     }
     goto no_join_cache;
@@ -7780,29 +8021,29 @@
   case JT_EQ_REF:
     if (cache_level <=2 || (no_hashed_cache && no_bka_cache))
       goto no_join_cache;
-    flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT;
-    if (tab->table->covering_keys.is_set(tab->ref.key))
-      flags|= HA_MRR_INDEX_ONLY;
-    rows= tab->table->file->multi_range_read_info(tab->ref.key, 10, 20,
-                                                  tab->ref.key_parts,
-                                                  &bufsz, &flags, &cost);
+    if (!tab->is_ref_for_hash_join())
+    {
+      flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT;
+      if (tab->table->covering_keys.is_set(tab->ref.key))
+        flags|= HA_MRR_INDEX_ONLY;
+      rows= tab->table->file->multi_range_read_info(tab->ref.key, 10, 20,
+                                                    tab->ref.key_parts,
+                                                    &bufsz, &flags, &cost);
+    }
 
     if ((cache_level <=4 && !no_hashed_cache) || no_bka_cache ||
+        tab->is_ref_for_hash_join() ||
 	((flags & HA_MRR_NO_ASSOCIATION) && cache_level <=6))
     {
       if (!tab->hash_join_is_possible() ||
           tab->make_scan_filter())
         goto no_join_cache;
       if (cache_level == 3)
-      {
-        if (prev_tab->cache)
-          goto no_join_cache;
         prev_cache= 0;
-      }
       if ((tab->cache= new JOIN_CACHE_BNLH(join, tab, prev_cache)) &&
           ((options & SELECT_DESCRIBE) || !tab->cache->init()))
       {
-        *icp_other_tables_ok= FALSE;        
+        tab->icp_other_tables_ok= FALSE;        
         return (4-test(!prev_cache));
       }
       goto no_join_cache;
@@ -7832,7 +8073,7 @@
         if ((tab->cache= new JOIN_CACHE_BKAH(join, tab, flags, prev_cache)) &&
             ((options & SELECT_DESCRIBE) || !tab->cache->init()))
 	{
-          *idx_cond_fact_out= FALSE;
+         tab->idx_cond_fact_out= FALSE;
           return (8-test(!prev_cache));
         }
         goto no_join_cache;
@@ -7843,11 +8084,90 @@
   }
 
 no_join_cache:
+  if (tab->type != JT_ALL && tab->is_ref_for_hash_join())
+    tab->type= JT_ALL;
   revise_cache_usage(tab); 
   return 0;
 }
 
 
+/* 
+  Check whether join buffers can be used to join tables of a join   
+
+  SYNOPSIS
+    check_join_cache_usage()
+      join                join whose tables are to be checked             
+      options             options of the join
+      no_jbuf_after       don't use join buffering after table with this number
+
+  DESCRIPTION
+    For each table after the first non-constant table the function checks
+    whether the table can be joined using a join buffer. If the function decides
+    that a join buffer can be employed then it selects the most appropriate join
+    cache object that contains this join buffer whose level is not greater
+    than join_cache_level set for the join. To make this check the function
+    calls the function check_join_cache_usage for every non-constant table.
+
+  NOTES
+    In some situations (e.g. for nested outer joins, for nested semi-joins) only
+    incremental buffers can be used. If it turns out that for some inner table
+    no join buffer can be used then any inner table of an outer/semi-join nest
+    cannot use join buffer. In the case when already chosen buffer must be
+    denied for a table the function recalls check_join_cache_usage()
+    starting from this table. The pointer to the table from which the check
+    has to be restarted is returned in join->return_val (see the description
+    of check_join_cache_usage).
+*/
+
+void check_join_cache_usage_for_tables(JOIN *join, ulonglong options,
+                                       uint no_jbuf_after)
+{
+  JOIN_TAB *first_sjm_table= NULL;
+  JOIN_TAB *last_sjm_table= NULL;
+
+  for (uint i= join->const_tables; i < join->tables; i++)
+    join->join_tab[i].used_join_cache_level= join->max_allowed_join_cache_level;  
+   
+  for (uint i= join->const_tables; i < join->tables; i++)
+  {
+    JOIN_TAB *tab= join->join_tab+i;
+
+    if (sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
+    {
+      first_sjm_table= tab;
+      last_sjm_table= tab + join->best_positions[i].n_sj_tables;
+      for (JOIN_TAB *sjm_tab= first_sjm_table;
+             sjm_tab != last_sjm_table; sjm_tab++)
+        sjm_tab->first_sjm_sibling= first_sjm_table;
+    } 
+    if (!(tab >= first_sjm_table && tab < last_sjm_table))
+      tab->first_sjm_sibling= NULL;
+
+    tab->icp_other_tables_ok= TRUE;
+    tab->idx_cond_fact_out= TRUE;
+    switch (tab->type) {
+    case JT_SYSTEM:
+    case JT_CONST:
+    case JT_EQ_REF:
+    case JT_REF:
+    case JT_REF_OR_NULL:
+    case JT_ALL:
+      tab->used_join_cache_level= check_join_cache_usage(tab, options,
+                                                         no_jbuf_after,
+                                                         tab == last_sjm_table ?
+						           first_sjm_table :
+                                                           tab-1); 
+      tab->use_join_cache= test(tab->used_join_cache_level);
+      if (join->return_tab)
+        i= join->return_tab-join->join_tab-1;   // always >= 0
+      break; 
+    default:
+      tab->used_join_cache_level= 0;
+    }     
+  }
+}
+
+
 /*
   Plan refinement stage: do various setup things for the executor
 
@@ -7874,13 +8194,11 @@
 make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
 {
   uint i;
-  uint jcl= 0;
+
+  DBUG_ENTER("make_join_readinfo");
+
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
   bool sorted= 1;
-  uint first_sjm_table= MAX_TABLES;
-  uint last_sjm_table= MAX_TABLES;
-  DBUG_ENTER("make_join_readinfo");
-
 
   if (!join->select_lex->sj_nests.is_empty() &&
       setup_semijoin_dups_elimination(join, options, no_jbuf_after))
@@ -7891,10 +8209,24 @@
 
   for (i=join->const_tables ; i < join->tables ; i++)
   {
+    /*
+      The approximation below for partial join cardinality is not good because
+        - it does not take into account some pushdown predicates
+        - it does not differentiate between inner joins, outer joins and semi-joins.
+      Later it should be improved.
+    */
+    JOIN_TAB *tab=join->join_tab+i;
+    tab->partial_join_cardinality= join->best_positions[i].records_read *
+                                   (i ? (tab-1)->partial_join_cardinality : 1);
+  }
+ 
+  check_join_cache_usage_for_tables(join, options, no_jbuf_after);
+
+  for (i=join->const_tables ; i < join->tables ; i++)
+  {
     JOIN_TAB *tab=join->join_tab+i;
     TABLE *table=tab->table;
-    bool icp_other_tables_ok= FALSE;
-    bool idx_cond_fact_out= FALSE;
+    uint jcl= tab->used_join_cache_level;
     tab->read_record.table= table;
     tab->read_record.file=table->file;
     tab->read_record.unlock_row= rr_unlock_row;
@@ -7902,15 +8234,6 @@
     tab->sorted= sorted;
     sorted= 0;                                  // only first must be sorted
 
-    /*
-      The approximation below for partial join cardinality is not good because
-        - it does not take into account some pushdown predicates
-        - it does not differentiate between inner joins, outer joins and semi-joins.
-      Later it should be improved.
-    */
-    tab->partial_join_cardinality= join->best_positions[i].records_read *
-                                   (i ? (tab-1)->partial_join_cardinality : 1);
- 
     if (tab->loosescan_match_tab)
     {
       if (!(tab->loosescan_buf= (uchar*)join->thd->alloc(tab->
@@ -7922,13 +8245,8 @@
     /*
      SJ-Materialization
     */
-    if (!(i >= first_sjm_table && i < last_sjm_table))
-      tab->first_sjm_sibling= NULL;
     if (sj_is_materialize_strategy(join->best_positions[i].sj_strategy))
     {
-      /* This is a start of semi-join nest */
-      first_sjm_table= i;
-      last_sjm_table= i + join->best_positions[i].n_sj_tables;
       if (i == join->const_tables)
         join->first_select= sub_select_sjm;
       else
@@ -7936,46 +8254,13 @@
 
       if (setup_sj_materialization(tab))
         return TRUE;
-      for (uint j= first_sjm_table; j != last_sjm_table; j++)
-        join->join_tab[j].first_sjm_sibling= join->join_tab + first_sjm_table;
     }
     table->status=STATUS_NO_RECORD;
     pick_table_access_method (tab);
 
-    /*
-      This loop currently can be executed only once as the function 
-      check_join_cache_usage does not change the value of tab->type.
-      It won't be true for the future code.
-    */    
-    for ( ;  ; )
-    {
-      enum join_type tab_type= tab->type; 
-      switch (tab->type) {
-      case JT_SYSTEM:
-      case JT_CONST:
-      case JT_EQ_REF:
-      case JT_REF:
-      case JT_REF_OR_NULL:
-      case JT_ALL:
-        if ((jcl= check_join_cache_usage(tab, join, options,
-                                         no_jbuf_after,
-                                         i == last_sjm_table ?
-					   join->join_tab+first_sjm_table :
-                                           tab-1, 
-                                         &icp_other_tables_ok,
-                                         &idx_cond_fact_out)))
-        {
-          tab->use_join_cache= TRUE;
-          tab[-1].next_select=sub_select_cache;
-        }
-        break;
-      default:
-       ;
-      }
-      if (tab->type == tab_type)
-        break;
-    }
-
+    if (jcl)
+       tab[-1].next_select=sub_select_cache;
+      
     switch (tab->type) {
     case JT_SYSTEM:				// Only happens with left join 
     case JT_CONST:				// Only happens with left join
@@ -7988,9 +8273,8 @@
         table->key_read=1;
         table->file->extra(HA_EXTRA_KEYREAD);
       }
-      else if (!jcl || jcl > 4)
-        push_index_cond(tab, tab->ref.key, 
-                       icp_other_tables_ok, idx_cond_fact_out);
+      else if ((!jcl || jcl > 4) && !tab->is_ref_for_hash_join()) 
+        push_index_cond(tab, tab->ref.key);
         break;
     case JT_EQ_REF:
       tab->read_record.unlock_row= join_read_key_unlock_row;
@@ -8001,9 +8285,8 @@
 	table->key_read=1;
 	table->file->extra(HA_EXTRA_KEYREAD);
       }
-      else if (!jcl || jcl > 4) 
-        push_index_cond(tab, tab->ref.key,
-                        icp_other_tables_ok, idx_cond_fact_out);
+      else if ((!jcl || jcl > 4) && !tab->is_ref_for_hash_join()) 
+        push_index_cond(tab, tab->ref.key);
       break;
     case JT_REF_OR_NULL:
     case JT_REF:
@@ -8017,9 +8300,8 @@
       if (table->covering_keys.is_set(tab->ref.key) &&
 	  !table->no_keyread)
         table->enable_keyread();
-      else if (!jcl || jcl > 4)
-        push_index_cond(tab, tab->ref.key, 
-                        icp_other_tables_ok, idx_cond_fact_out);
+      else if ((!jcl || jcl > 4) &&!tab->is_ref_for_hash_join())
+        push_index_cond(tab, tab->ref.key);
       break;
     case JT_ALL:
       /*
@@ -8100,8 +8382,7 @@
 	}
         if (tab->select && tab->select->quick &&
             tab->select->quick->index != MAX_KEY && ! tab->table->key_read)
-          push_index_cond(tab, tab->select->quick->index, 
-                          icp_other_tables_ok, idx_cond_fact_out);
+          push_index_cond(tab, tab->select->quick->index);
       }
       break;
     case JT_FT:
@@ -9973,7 +10254,7 @@
           TABLE *tab= field->table;
           KEYUSE *use;
           for (use= stat->keyuse; use && use->table == tab; use++)
-            if (possible_keys.is_set(use->key) && 
+            if (!use->is_for_hash_join() && possible_keys.is_set(use->key) && 
                 tab->key_info[use->key].key_part[use->keypart].field ==
                 field)
               tab->const_key_parts[use->key]|= use->keypart_map;
@@ -11514,7 +11795,9 @@
   bitmap_init(&table->tmp_set,
               (my_bitmap_map*) (bitmaps+ 2*bitmap_buffer_size(field_count)),
               field_count, FALSE);
-
+  bitmap_init(&table->eq_join_set,
+              (my_bitmap_map*) (bitmaps+ 3*bitmap_buffer_size(field_count)),
+              field_count, FALSE);
   /* write_set and all_set are copies of read_set */
   table->def_write_set= table->def_read_set;
   table->s->all_set= table->def_read_set;
@@ -11668,7 +11951,7 @@
                         &tmpname, (uint) strlen(tmp_table_name)+1,
                         &group_buff, (group && ! using_unique_constraint ?
                                       param->group_length : 0),
-                        &bitmaps, bitmap_buffer_size(field_count)*3,
+                        &bitmaps, bitmap_buffer_size(field_count)*4,
                         NullS))
   {
     if (temp_pool_slot != MY_BIT_NONE)
@@ -12326,7 +12609,7 @@
                         &share, sizeof(*share),
                         &field, (field_count + 1) * sizeof(Field*),
                         &blob_field, (field_count+1) *sizeof(uint),
-                        &bitmaps, bitmap_buffer_size(field_count)*3,
+                        &bitmaps, bitmap_buffer_size(field_count)*4,
                         NullS))
     return 0;
 
@@ -12778,7 +13061,7 @@
   TABLE new_table;
   TABLE_SHARE share;
   const char *save_proc_info;
-  int write_err;
+  int write_err= 0;
   DBUG_ENTER("create_internal_tmp_table_from_heap2");
 
   if (table->s->db_type() != heap_hton || 
@@ -14981,6 +15264,7 @@
   JOIN_TAB *join_tab= field->table->reginfo.join_tab;
   // No need to change const test
   if (!field->table->const_table && join_tab &&
+      !join_tab->is_ref_for_hash_join() &&
       (!join_tab->first_inner ||
        *join_tab->first_inner->on_expr_ref == root_cond))
   {
@@ -15297,19 +15581,22 @@
 static Item *
 part_of_refkey(TABLE *table,Field *field)
 {
-  if (!table->reginfo.join_tab)
+  JOIN_TAB *join_tab= table->reginfo.join_tab;
+  if (!join_tab)
     return (Item*) 0;             // field from outer non-select (UPDATE,...)
 
-  uint ref_parts=table->reginfo.join_tab->ref.key_parts;
+  uint ref_parts= join_tab->ref.key_parts;
   if (ref_parts)
   {
-    KEY_PART_INFO *key_part=
-      table->key_info[table->reginfo.join_tab->ref.key].key_part;
+    
+    uint key= join_tab->ref.key;
+    KEY *key_info= join_tab->get_keyinfo_by_key_no(key);
+    KEY_PART_INFO *key_part= key_info->key_part;
 
     for (uint part=0 ; part < ref_parts ; part++,key_part++)
       if (field->eq(key_part->field) &&
 	  !(key_part->key_part_flag & (HA_PART_KEY_SEG | HA_NULL_PART)))
-	return table->reginfo.join_tab->ref.items[part];
+	return join_tab->ref.items[part];
   }
   return (Item*) 0;
 }
@@ -18877,6 +19164,9 @@
 	  tab->type = JT_RANGE;
       }
 
+      if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG)
+        tab->type= JT_HASH;
+
       /* table */
       if (table->derived_select_number)
       {
@@ -18940,7 +19230,7 @@
       /* Build "key", "key_len", and "ref" values and add them to item_list */
       if (tab->ref.key_parts)
       {
-	KEY *key_info=table->key_info+ tab->ref.key;
+	KEY *key_info= tab->get_keyinfo_by_key_no(tab->ref.key);
         register uint length;
 	item_list.push_back(new Item_string(key_info->name,
 					    strlen(key_info->name),
@@ -19024,7 +19314,8 @@
         ha_rows examined_rows;
         if (tab->select && tab->select->quick)
           examined_rows= tab->select->quick->records;
-        else if (tab->type == JT_NEXT || tab->type == JT_ALL)
+        else if (tab->type == JT_NEXT || tab->type == JT_ALL ||
+                 tab->type == JT_HASH)
         {
           if (tab->limit)
             examined_rows= tab->limit;
@@ -19765,6 +20056,40 @@
   DBUG_RETURN(FALSE);
 }
 
+
+/**
+  @brief
+  Set allowed types of join caches that can be used for join operations
+
+  @details
+  The function sets a bitmap of allowed join buffers types in the field
+  allowed_join_cache_types of this JOIN structure:
+    bit 1 is set if tjoin buffers are allowed to be incremental
+    bit 2 is set if the join buffers are allowed to be hashed
+    but 3 is set if the join buffers are allowed to be used for BKA
+  join algorithms.
+  The allowed types are read from system variables.
+  Besides the function sets maximum allowed join cache level that is
+  also read from a system variable.
+*/
+
+void JOIN::set_allowed_join_cache_types()
+{
+  allowed_join_cache_types= 0;
+  if (optimizer_flag(thd, OPTIMIZER_SWITCH_JOIN_CACHE_INCREMENTAL))
+    allowed_join_cache_types|= JOIN_CACHE_INCREMENTAL_BIT;
+  if (optimizer_flag(thd, OPTIMIZER_SWITCH_JOIN_CACHE_HASHED))
+    allowed_join_cache_types|= JOIN_CACHE_HASHED_BIT;
+  if (optimizer_flag(thd, OPTIMIZER_SWITCH_JOIN_CACHE_BKA))
+    allowed_join_cache_types|= JOIN_CACHE_BKA_BIT;
+  allowed_semijoin_with_cache=
+    optimizer_flag(thd, OPTIMIZER_SWITCH_SEMIJOIN_WITH_CACHE);
+  allowed_outer_join_with_cache=
+    optimizer_flag(thd, OPTIMIZER_SWITCH_OUTER_JOIN_WITH_CACHE);
+  max_allowed_join_cache_level= thd->variables.join_cache_level;
+}
+
+
 /**
   @} (end of group Query_Optimizer)
 */

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-01-26 19:30:29 +0000
+++ b/sql/sql_select.h	2011-01-29 02:54:30 +0000
@@ -39,6 +39,11 @@
 /* Values in optimize */
 #define KEY_OPTIMIZE_EXISTS		1
 #define KEY_OPTIMIZE_REF_OR_NULL	2
+#define KEY_OPTIMIZE_EQ	                4
+
+inline uint get_hash_join_key_no() { return MAX_KEY; }
+
+inline bool is_hash_join_key_no(uint key) { return key == MAX_KEY; }
 
 typedef struct keyuse_t {
   TABLE *table;
@@ -68,6 +73,8 @@
      MAX_UINT  Otherwise
   */
   uint         sj_pred_no;
+
+  bool is_for_hash_join() { return is_hash_join_key_no(key); }
 } KEYUSE;
 
 class store_key;
@@ -127,7 +134,8 @@
 */
 enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF,
 		 JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL,
-		 JT_UNIQUE_SUBQUERY, JT_INDEX_SUBQUERY, JT_INDEX_MERGE};
+		 JT_UNIQUE_SUBQUERY, JT_INDEX_SUBQUERY, JT_INDEX_MERGE,
+                 JT_HASH};
 
 class JOIN;
 
@@ -159,6 +167,8 @@
   st_join_table() {}                          /* Remove gcc warning */
   TABLE		*table;
   KEYUSE	*keyuse;			/**< pointer to first used key */
+  KEY           *hj_key;       /**< descriptor of the used best hash join key
+				    not supported by any index                 */
   SQL_SELECT	*select;
   COND		*select_cond;
   COND          *on_precond;    /**< part of on condition to check before
@@ -241,7 +251,15 @@
   */ 
   ha_rows       limit; 
   TABLE_REF	ref;
+  /* TRUE <=> condition pushdown supports other tables presence */
+  bool          icp_other_tables_ok;
+  /* 
+    TRUE <=> condition pushed to the index has to be factored out of
+    the condition pushed to the table
+  */
+  bool          idx_cond_fact_out;
   bool          use_join_cache;
+  uint          used_join_cache_level;
   ulong         join_buffer_size_limit;
   JOIN_CACHE	*cache;
   /*
@@ -408,6 +426,11 @@
   double get_partial_join_cardinality() { return partial_join_cardinality; }
   bool hash_join_is_possible();
   int make_scan_filter();
+  bool is_ref_for_hash_join() { return is_hash_join_key_no(ref.key); }
+  KEY *get_keyinfo_by_key_no(uint key) 
+  {
+    return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);
+  }
 } JOIN_TAB;
 
 
@@ -681,6 +704,15 @@
   Item      *tmp_having; ///< To store having when processed temporary table
   Item      *having_history; ///< Store having for explain
   ulonglong  select_options;
+  /* 
+    Bitmap of allowed types of the join caches that
+    can be used for join operations
+  */
+  uint allowed_join_cache_types;
+  bool allowed_semijoin_with_cache;
+  bool allowed_outer_join_with_cache;
+  /* Maximum level of the join caches that can be used for join operations */ 
+  uint max_allowed_join_cache_level;
   select_result *result;
   TMP_TABLE_PARAM tmp_table_param;
   MYSQL_LOCK *lock;
@@ -947,6 +979,12 @@
   bool shrink_join_buffers(JOIN_TAB *jt, 
                            ulonglong curr_space,
                            ulonglong needed_space);
+  void set_allowed_join_cache_types();
+  bool is_allowed_hash_join_access()
+  { 
+    return test(allowed_join_cache_types & JOIN_CACHE_HASHED_BIT) &&
+           max_allowed_join_cache_level > JOIN_CACHE_HASHED_BIT;
+  }
 
 private:
   /**
@@ -1231,8 +1269,7 @@
 void eliminate_tables(JOIN *join);
 
 /* Index Condition Pushdown entry point function */
-void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok,
-                     bool factor_out);
+void push_index_cond(JOIN_TAB *tab, uint keyno);
 
 /****************************************************************************
   Temporary table support for SQL Runtime

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2011-01-14 09:55:03 +0000
+++ b/sql/table.cc	2011-01-22 06:48:28 +0000
@@ -2342,7 +2342,7 @@
   /* Allocate bitmaps */
 
   bitmap_size= share->column_bitmap_size;
-  if (!(bitmaps= (uchar*) alloc_root(&outparam->mem_root, bitmap_size*4)))
+  if (!(bitmaps= (uchar*) alloc_root(&outparam->mem_root, bitmap_size*5)))
     goto err;
   bitmap_init(&outparam->def_read_set,
               (my_bitmap_map*) bitmaps, share->fields, FALSE);
@@ -2352,6 +2352,8 @@
               (my_bitmap_map*) (bitmaps+bitmap_size*2), share->fields, FALSE);
   bitmap_init(&outparam->tmp_set,
               (my_bitmap_map*) (bitmaps+bitmap_size*3), share->fields, FALSE);
+  bitmap_init(&outparam->eq_join_set,
+              (my_bitmap_map*) (bitmaps+bitmap_size*4), share->fields, FALSE);
   outparam->default_column_bitmaps();
 
   /* The table struct is now initialized;  Open the table */
@@ -5203,6 +5205,52 @@
 }
 
 
+void TABLE::create_key_part_by_field(KEY *keyinfo,
+                                     KEY_PART_INFO *key_part_info,
+                                     Field *field)
+{   
+  field->flags|= PART_KEY_FLAG;
+  key_part_info->null_bit= field->null_bit;
+  key_part_info->null_offset= (uint) (field->null_ptr -
+                                      (uchar*) record[0]);
+  key_part_info->field= field;
+  key_part_info->offset= field->offset(record[0]);
+  key_part_info->length=   (uint16) field->pack_length();
+  keyinfo->key_length+= key_part_info->length;
+  key_part_info->key_part_flag= 0;
+  /* TODO:
+    The below method of computing the key format length of the
+    key part is a copy/paste from opt_range.cc, and table.cc.
+    This should be factored out, e.g. as a method of Field.
+    In addition it is not clear if any of the Field::*_length
+    methods is supposed to compute the same length. If so, it
+    might be reused.
+  */
+  key_part_info->store_length= key_part_info->length;
+
+  if (field->real_maybe_null())
+  {
+    key_part_info->store_length+= HA_KEY_NULL_LENGTH;
+    keyinfo->key_length+= HA_KEY_NULL_LENGTH;
+  }
+  if (field->type() == MYSQL_TYPE_BLOB || 
+      field->real_type() == MYSQL_TYPE_VARCHAR)
+  {
+    key_part_info->store_length+= HA_KEY_BLOB_LENGTH;
+    keyinfo->key_length+= HA_KEY_BLOB_LENGTH; // ???
+    key_part_info->key_part_flag|=
+      field->type() == MYSQL_TYPE_BLOB ? HA_BLOB_PART: HA_VAR_LENGTH_PART;
+  }
+
+  key_part_info->type=     (uint8) field->key_type();
+  key_part_info->key_type =
+    ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT ||
+    (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 ||
+    (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ?
+    0 : FIELDFLAG_BINARY;
+}
+
+
 /**
   Add a key to a temporary  table
 
@@ -5253,54 +5301,18 @@
   if (!keyinfo->rec_per_key)
     return TRUE;
   bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts);
+
   for (i= 0; i < key_parts; i++)
   {
     reg_field= field + next_field_no(arg);
     if (key_start)
       (*reg_field)->key_start.set_bit(key);
+    (*reg_field)->part_of_key.set_bit(key);
+    create_key_part_by_field(keyinfo, key_part_info, *reg_field);
     key_start= FALSE;
-      (*reg_field)->part_of_key.set_bit(key);
-    (*reg_field)->flags|= PART_KEY_FLAG;
-    key_part_info->null_bit= (*reg_field)->null_bit;
-    key_part_info->null_offset= (uint) ((*reg_field)->null_ptr -
-                                          (uchar*) record[0]);
-    key_part_info->field=    *reg_field;
-    key_part_info->offset=   (*reg_field)->offset(record[0]);
-    key_part_info->length=   (uint16) (*reg_field)->pack_length();
-    keyinfo->key_length+= key_part_info->length;
-    key_part_info->key_part_flag= 0;
-    /* TODO:
-      The below method of computing the key format length of the
-      key part is a copy/paste from opt_range.cc, and table.cc.
-      This should be factored out, e.g. as a method of Field.
-      In addition it is not clear if any of the Field::*_length
-      methods is supposed to compute the same length. If so, it
-      might be reused.
-    */
-    key_part_info->store_length= key_part_info->length;
-
-    if ((*reg_field)->real_maybe_null())
-    {
-      key_part_info->store_length+= HA_KEY_NULL_LENGTH;
-      keyinfo->key_length+= HA_KEY_NULL_LENGTH;
-      if (unique)
-        keyinfo->flags|= HA_NULL_ARE_EQUAL;     // def. that NULL == NULL
-    }
-    if ((*reg_field)->type() == MYSQL_TYPE_BLOB || 
-        (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR)
-    {
-      key_part_info->store_length+= HA_KEY_BLOB_LENGTH;
-      keyinfo->key_length+= HA_KEY_BLOB_LENGTH; // ???
-    }
-
-    key_part_info->type=     (uint8) (*reg_field)->key_type();
-    key_part_info->key_type =
-      ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT ||
-       (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 ||
-       (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ?
-      0 : FIELDFLAG_BINARY;
     key_part_info++;
   }
+
   set_if_bigger(s->max_key_length, keyinfo->key_length);
   s->keys++;
   return FALSE;

=== modified file 'sql/table.h'
--- a/sql/table.h	2010-12-27 22:22:05 +0000
+++ b/sql/table.h	2011-01-05 05:59:41 +0000
@@ -725,6 +725,7 @@
   uchar		*null_flags;
   my_bitmap_map	*bitmap_init_value;
   MY_BITMAP     def_read_set, def_write_set, def_vcol_set, tmp_set; 
+  MY_BITMAP     eq_join_set;         /* used to mark equi-joined fields */
   MY_BITMAP     *read_set, *write_set, *vcol_set; /* Active column sets */
   /*
    The ID of the query that opened and is using this table. Has different
@@ -955,6 +956,8 @@
   bool add_tmp_key(uint key, uint key_parts,
                    uint (*next_field_no) (uchar *), uchar *arg,
                    bool unique);
+  void create_key_part_by_field(KEY *keyinfo, KEY_PART_INFO *key_part_info,
+                                Field *field);
   bool is_children_attached(void);
   inline void enable_keyread()
   {



More information about the commits mailing list