List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 8 2011 1:25pm
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12316645
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-work0/ based on revid:tor.didriksen@strippedwjxy2y8

 3379 Roy Lyseng	2011-06-08
      Bug#12316645: Wrong cost calculation with optimizer_join_cache_level settings
      
      best_access_path() did not take properly into account the setting of
      optimizer_join_cache_level when calculating the access path that gives
      the smallest cost to access a given table.
      
      There are three changes applied in this bug fix:
       - best_access_path() properly accounts for join cache level 0, ie
         do not use join caching.
       - best_access_path() does account for join cache level greater than 2
         when a table is outer-joined to the current join prefix.
       - best_access_path() does account for join cache level greater than 2
         when a table is semi-joined to the current join prefix.
      
      Static properties for setting join buffering have been centralized to
      a new function set_join_buffer_properties().
      
      Further on, setting of join cache level is changed.
      check_join_cache_usage() is renamed to setup_join_buffering().
      
      Cost calculation for joins with join buffering is better than it used
      to be, but it is still not fair. Sometimes, cost is calculated using
      join buffering, but the decision has to be reverted later.
      Cost calculation for BKA join strategy is also not considered properly
      (but this patch does not change the cost calculations for BKA).
      
      mysql-test/t/join_cache_jcl0.test
        Added test cases for joins where join buffering is turned off.
      
      mysql-test/r/join_cache_jcl0.result
        Added test case results for joins where join buffering is turned off.
      
      mysql-test/r/count_distinct.result
      mysql-test/r/distinct.result
      mysql-test/r/group_by.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
      mysql-test/r/join_nested.result
      mysql-test/r/join_nested_jcl6.result
      mysql-test/r/join_outer.result
      mysql-test/r/join_outer_jcl6.result
      mysql-test/r/null_key_all.result
      mysql-test/r/null_key_icp.result
      mysql-test/r/null_key_none.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      mysql-test/r/select_all.result
      mysql-test/r/select_all_jcl6.result
      mysql-test/r/select_icp_mrr.result
      mysql-test/r/select_icp_mrr_jcl6.result
      mysql-test/r/select_none.result
      mysql-test/r/select_none_jcl6.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subselect_innodb.result
        Lots of changed plans for bug#12316645.
        Generally the new costing will benefit BNL join buffering, sometimes
        over ref access.
        count_distinct.result: Changed result because of non-deterministic query.
      
      sql/sql_select.cc
        Modified the argument disable_jbuf locally in best_access_path()
        according to join cache level setting, and whether the table to be
        added is outer-joined or semi-joined.
        Added function set_join_buffer_properties() that calculates whether
        join buffering can be used for the tables of a query.
        check_join_cache_usage() is renamed to setup_join_buffering().
        Checks for join buffering have been refactored somewhat:
         - Attempted to group the tests better (general checks before
           outer join tests before semi-join tests).
         - Outer join test is simplified: The criterion is that if one table
           in an outer join operation cannot use join buffering, then all
           tables in that outer join nest and any inner outer join nest
           cannot use join buffering. This is handled in two tests:
           1. If the first inner table of the current outer join nest does
              not use join buffering, this table cannot use it either.
           2. If an immediate outer join nest does not use join buffering,
              this table cannot use join buffering.
        The initial join buffering strategy is now selected in
        greedy_search() and possibly modified in setup_join_buffering().
        If join buffering is to be used, setup_join_buffering() will
        set up the join cache for the table.
        best_extension_by_limited_search(): Variable 's' made const
        throughout it's use.
      
      sql/sql_select.h
        Added more documentation to the field use_join_cache in JOIN_TAB.
        The value is set to the set of possible join buffering strategies
        for this table when starting the join optimization.

    added:
      mysql-test/r/join_cache_jcl0.result
      mysql-test/t/join_cache_jcl0.test
    modified:
      mysql-test/include/join_cache.inc
      mysql-test/r/count_distinct.result
      mysql-test/r/distinct.result
      mysql-test/r/group_by.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
      mysql-test/r/join_nested.result
      mysql-test/r/join_nested_jcl6.result
      mysql-test/r/join_outer.result
      mysql-test/r/join_outer_jcl6.result
      mysql-test/r/null_key_all.result
      mysql-test/r/null_key_icp.result
      mysql-test/r/null_key_none.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      mysql-test/r/select_all.result
      mysql-test/r/select_all_jcl6.result
      mysql-test/r/select_icp_mrr.result
      mysql-test/r/select_icp_mrr_jcl6.result
      mysql-test/r/select_none.result
      mysql-test/r/select_none_jcl6.result
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subselect_innodb.result
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc	2011-04-26 08:49:10 +0000
+++ b/mysql-test/include/join_cache.inc	2011-06-08 13:24:56 +0000
@@ -516,7 +516,6 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
   (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
   (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
---echo The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
   from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;

=== modified file 'mysql-test/r/count_distinct.result'
--- a/mysql-test/r/count_distinct.result	2010-04-13 09:38:28 +0000
+++ b/mysql-test/r/count_distinct.result	2011-06-08 13:24:56 +0000
@@ -49,7 +49,7 @@ isbn	city	@bar:=t1.libname	a
 000	New York	New York Public Libra	2
 SELECT @bar;
 @bar
-Berkeley Public2
+NYC Lib
 select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a
 from t3 left join t1 on t3.libname=t1.libname left join t2
 on t3.isbn=t2.isbn group by city having count(distinct
@@ -59,7 +59,7 @@ isbn	city	concat(@bar:=t1.libname)	a
 000	New York	New York Public Libra	2
 SELECT @bar;
 @bar
-Berkeley Public2
+NYC Lib
 drop table t1, t2, t3;
 create table t1 (f1 int);
 insert into t1 values (1);

=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2011-05-11 12:32:23 +0000
+++ b/mysql-test/r/distinct.result	2011-06-08 13:24:56 +0000
@@ -303,9 +303,9 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t2_lj	ref	id	id	4	test.j_lj_t2.id	1	Using where; Using index; Distinct
+1	SIMPLE	t2_lj	index	id	id	8	NULL	1	Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	j_lj_t3	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t3_lj	ref	id	id	4	test.j_lj_t3.id	1	Using where; Using index; Distinct
+1	SIMPLE	t3_lj	index	id	id	8	NULL	1	Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
 SELECT DISTINCT
 t1.id
 from

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/group_by.result	2011-06-08 13:24:56 +0000
@@ -496,8 +496,8 @@ c1.id AND c1.active = 'Yes' LEFT JOIN t3
 c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
 pid	c1id	c2id	value	id	active	id	active
 1	1	NULL	1	1	Yes	NULL	NULL
-1	NULL	3	3	NULL	NULL	3	Yes
 1	4	NULL	4	4	Yes	NULL	NULL
+1	NULL	3	3	NULL	NULL	3	Yes
 select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON 
 m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = 
 c2.id AND c2.active = 'Yes' WHERE m.pid=1  AND (c1.id IS NOT NULL OR c2.id IS 

=== added file 'mysql-test/r/join_cache_jcl0.result'
--- a/mysql-test/r/join_cache_jcl0.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/join_cache_jcl0.result	2011-06-08 13:24:56 +0000
@@ -0,0 +1,2308 @@
+set optimizer_join_cache_level = 0;;
+set optimizer_switch='mrr_cost_based=off';
+DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
+DROP DATABASE IF EXISTS world;
+set names utf8;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL
+);
+CREATE TABLE City (
+ID int(11) NOT NULL,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0'
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0'
+);
+SELECT COUNT(*) FROM Country;
+COUNT(*)
+239
+SELECT COUNT(*) FROM City;
+COUNT(*)
+4079
+SELECT COUNT(*) FROM CountryLanguage;
+COUNT(*)
+984
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	131072
+show variables like 'optimizer_join_cache_level';
+Variable_name	Value
+optimizer_join_cache_level	0
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name	Name	Language
+La Ceiba	Honduras	Spanish
+La Habana	Cuba	Spanish
+La Matanza	Argentina	Spanish
+La Paz	Bolivia	Spanish
+La Paz	Mexico	Spanish
+La Paz	Mexico	Spanish
+La Plata	Argentina	Spanish
+La Rioja	Argentina	Spanish
+La Romana	Dominican Republic	Spanish
+La Serena	Chile	Spanish
+La Spezia	Italy	Italian
+Lafayette	United States	English
+Lages	Brazil	Portuguese
+Lagos de Moreno	Mexico	Spanish
+Lahti	Finland	Finnish
+Laiwu	China	Chinese
+Laiyang	China	Chinese
+Laizhou	China	Chinese
+Lakewood	United States	English
+Lalitapur	Nepal	Nepali
+Lambaré	Paraguay	Spanish
+Lancaster	United States	English
+Langfang	China	Chinese
+Lansing	United States	English
+Lanzhou	China	Chinese
+Lanús	Argentina	Spanish
+Laohekou	China	Chinese
+Laredo	United States	English
+Larisa	Greece	Greek
+Las Heras	Argentina	Spanish
+Las Margaritas	Mexico	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+Las Vegas	United States	English
+Lashio (Lasho)	Myanmar	Burmese
+Latakia	Syria	Arabic
+Latina	Italy	Italian
+Lauro de Freitas	Brazil	Portuguese
+Lausanne	Switzerland	German
+Laval	Canada	English
+Le Havre	France	French
+Le Mans	France	French
+Le-Cap-Haïtien	Haiti	Haiti Creole
+Lecce	Italy	Italian
+Leeds	United Kingdom	English
+Leganés	Spain	Spanish
+Legnica	Poland	Polish
+Leicester	United Kingdom	English
+Leiden	Netherlands	Dutch
+Leipzig	Germany	German
+Leiyang	China	Chinese
+Lengshuijiang	China	Chinese
+Leninsk-Kuznetski	Russian Federation	Russian
+Lerdo	Mexico	Spanish
+Lerma	Mexico	Spanish
+Leshan	China	Chinese
+Leverkusen	Germany	German
+Lexington-Fayette	United States	English
+León	Mexico	Spanish
+León	Nicaragua	Spanish
+León	Spain	Spanish
+Lhasa	China	Chinese
+Liangcheng	China	Chinese
+Lianyuan	China	Chinese
+Lianyungang	China	Chinese
+Liaocheng	China	Chinese
+Liaoyang	China	Chinese
+Liaoyuan	China	Chinese
+Liberec	Czech Republic	Czech
+Lida	Belarus	Belorussian
+Liling	China	Chinese
+Lille	France	French
+Lilongwe	Malawi	Chichewa
+Lima	Peru	Spanish
+Limeira	Brazil	Portuguese
+Limoges	France	French
+Linchuan	China	Chinese
+Lincoln	United States	English
+Linfen	China	Chinese
+Linhai	China	Chinese
+Linhares	Brazil	Portuguese
+Linhe	China	Chinese
+Linköping	Sweden	Swedish
+Linqing	China	Chinese
+Linyi	China	Chinese
+Linz	Austria	German
+Lipetsk	Russian Federation	Russian
+Lisboa	Portugal	Portuguese
+Little Rock	United States	English
+Liupanshui	China	Chinese
+Liuzhou	China	Chinese
+Liu´an	China	Chinese
+Liverpool	United Kingdom	English
+Livonia	United States	English
+Livorno	Italy	Italian
+Liyang	China	Chinese
+Liège	Belgium	Dutch
+Ljubertsy	Russian Federation	Russian
+Lleida (Lérida)	Spain	Spanish
+Logroño	Spain	Spanish
+Loja	Ecuador	Spanish
+Lomas de Zamora	Argentina	Spanish
+London	Canada	English
+London	United Kingdom	English
+Londrina	Brazil	Portuguese
+Long Beach	United States	English
+Long Xuyen	Vietnam	Vietnamese
+Longjing	China	Chinese
+Longkou	China	Chinese
+Longueuil	Canada	English
+Longyan	China	Chinese
+Los Angeles	Chile	Spanish
+Los Angeles	United States	English
+Los Cabos	Mexico	Spanish
+Los Teques	Venezuela	Spanish
+Loudi	China	Chinese
+Louisville	United States	English
+Lowell	United States	English
+Lower Hutt	New Zealand	English
+Lubbock	United States	English
+Lublin	Poland	Polish
+Luchou	Taiwan	Min
+Ludwigshafen am Rhein	Germany	German
+Lugansk	Ukraine	Ukrainian
+Lund	Sweden	Swedish
+Lungtan	Taiwan	Min
+Luohe	China	Chinese
+Luoyang	China	Chinese
+Luton	United Kingdom	English
+Lutsk	Ukraine	Ukrainian
+Luxor	Egypt	Arabic
+Luzhou	China	Chinese
+Luziânia	Brazil	Portuguese
+Lviv	Ukraine	Ukrainian
+Lyon	France	French
+Lysyt?ansk	Ukraine	Ukrainian
+L´Hospitalet de Llobregat	Spain	Spanish
+Lázaro Cárdenas	Mexico	Spanish
+Lódz	Poland	Polish
+Lübeck	Germany	German
+Lünen	Germany	German
+set join_buffer_size=256;
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	256
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	CountryLanguage	ALL	NULL	NULL	NULL	NULL	984	Using where
+1	SIMPLE	City	ALL	NULL	NULL	NULL	NULL	4079	Using where
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name	Name	Language
+La Ceiba	Honduras	Spanish
+La Habana	Cuba	Spanish
+La Matanza	Argentina	Spanish
+La Paz	Bolivia	Spanish
+La Paz	Mexico	Spanish
+La Paz	Mexico	Spanish
+La Plata	Argentina	Spanish
+La Rioja	Argentina	Spanish
+La Romana	Dominican Republic	Spanish
+La Serena	Chile	Spanish
+La Spezia	Italy	Italian
+Lafayette	United States	English
+Lages	Brazil	Portuguese
+Lagos de Moreno	Mexico	Spanish
+Lahti	Finland	Finnish
+Laiwu	China	Chinese
+Laiyang	China	Chinese
+Laizhou	China	Chinese
+Lakewood	United States	English
+Lalitapur	Nepal	Nepali
+Lambaré	Paraguay	Spanish
+Lancaster	United States	English
+Langfang	China	Chinese
+Lansing	United States	English
+Lanzhou	China	Chinese
+Lanús	Argentina	Spanish
+Laohekou	China	Chinese
+Laredo	United States	English
+Larisa	Greece	Greek
+Las Heras	Argentina	Spanish
+Las Margaritas	Mexico	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+Las Vegas	United States	English
+Lashio (Lasho)	Myanmar	Burmese
+Latakia	Syria	Arabic
+Latina	Italy	Italian
+Lauro de Freitas	Brazil	Portuguese
+Lausanne	Switzerland	German
+Laval	Canada	English
+Le Havre	France	French
+Le Mans	France	French
+Le-Cap-Haïtien	Haiti	Haiti Creole
+Lecce	Italy	Italian
+Leeds	United Kingdom	English
+Leganés	Spain	Spanish
+Legnica	Poland	Polish
+Leicester	United Kingdom	English
+Leiden	Netherlands	Dutch
+Leipzig	Germany	German
+Leiyang	China	Chinese
+Lengshuijiang	China	Chinese
+Leninsk-Kuznetski	Russian Federation	Russian
+Lerdo	Mexico	Spanish
+Lerma	Mexico	Spanish
+Leshan	China	Chinese
+Leverkusen	Germany	German
+Lexington-Fayette	United States	English
+León	Mexico	Spanish
+Leinese
+Liangcheng	China	Chinese
+Lianyuan	China	Chinese
+Lianyungang	China	Chinese
+Liaocheng	China	Chinese
+Liaoyang	China	Chinese
+Liaoyuan	China	Chinese
+Liberec	Czech Republic	Czech
+Lida	Belarus	Belorussian
+Liling	China	Chinese
+Lille	France	French
+Lilongwe	Malawi	Chichewa
+Lima	Peru	Spanish
+Limeira	Brazil	Portuguese
+Limoges	France	French
+Linchuan	China	Chinese
+Lincoln	United States	English
+Linfen	China	Chinese
+Linhai	China	Chinese
+Linhares	Brazil	Portuguese
+Linhe	China	Chinese
+Linköping	Sweden	Swedish
+Linqing	China	Chinese
+Linyi	China	Chinese
+Linz	Austria	German
+Lipetsk	Russian Federation	Russian
+Lisboa	Portugal	Portuguese
+Little Rock	United States	English
+Liupanshui	China	Chinese
+Liuzhou	China	Chinese
+Liu´an	China	Chinese
+Liverpool	United Kingdom	English
+Livonia	United States	English
+Livorno	Italy	Italian
+Liyang	China	Chinese
+Liège	Belgium	Dutch
+Ljubertsy	Russian Federation	Russian
+Lleida (Lérida)	Spain	Spanish
+Logroño	Spain	Spanish
+Loja	Ecuador	Spanish
+Lomas de Zamora	Argentina	Spanish
+London	Canada	English
+London	United Kingdom	English
+Londrina	Brazil	Portuguese
+Long Beach	United States	English
+Long Xuyen	Vietnam	Vietnamese
+Longjing	China	Chinese
+Longkou	China	Chinese
+Longueuil	Canada	English
+Longyan	China	Chinese
+Los Angeles	Chile	Spanish
+Los Angeles	United States	English
+Los Cabos	Mexico	Spanish
+Los Teques	Venezuela	Spanish
+Loudi	China	Chinese
+Louisville	United States	English
+Lowell	United States	English
+Lower Hutt	New Zealand	English
+Lubbock	United States	English
+Lublin	Poland	Polish
+Luchou	Taiwan	Min
+Ludwigshafen am Rhein	Germany	German
+Lugansk	Ukraine	Ukrainian
+Lund	Sweden	Swedish
+Lungtan	Taiwan	Min
+Luohe	China	Chinese
+Luoyang	China	Chinese
+Luton	United Kingdom	English
+Lutsk	Ukraine	Ukrainian
+Luxor	Egypt	Arabic
+Luzhou	China	Chinese
+Luziânia	Brazil	Portuguese
+Lviv	Ukraine	Ukrainian
+Lyon	France	French
+Lysyt?ansk	Ukraine	Ukrainian
+L´Hospitalet de Llobregat	Spain	Spanish
+Lázaro Cárdenas	Mexico	Spanish
+Lódz	Poland	Polish
+Lübeck	Germany	German
+Lünen	Germany	German
+set join_buffer_size=default;
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	131072
+DROP DATABASE world;
+CREATE DATABASE world;
+use world;
+CREATE TABLE Country (
+Code char(3) NOT NULL default '',
+Name char(52) NOT NULL default '',
+SurfaceArea float(10,2) NOT NULL default '0.00',
+Population int(11) NOT NULL default '0',
+Capital int(11) default NULL,
+PRIMARY KEY  (Code),
+UNIQUE INDEX (Name)
+);
+CREATE TABLE City (
+ID int(11) NOT NULL auto_increment,
+Name char(35) NOT NULL default '',
+Country char(3) NOT NULL default '',
+Population int(11) NOT NULL default '0',
+PRIMARY KEY  (ID),
+INDEX (Population),
+INDEX (Country) 
+);
+CREATE TABLE CountryLanguage (
+Country char(3) NOT NULL default '',
+Language char(30) NOT NULL default '',
+Percentage float(3,1) NOT NULL default '0.0',
+PRIMARY KEY  (Country, Language),
+INDEX (Percentage)
+);
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	131072
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name	Name	Language
+La Ceiba	Honduras	Spanish
+La Habana	Cuba	Spanish
+La Matanza	Argentina	Spanish
+La Paz	Bolivia	Spanish
+La Paz	Mexico	Spanish
+La Paz	Mexico	Spanish
+La Plata	Argentina	Spanish
+La Rioja	Argentina	Spanish
+La Romana	Dominican Republic	Spanish
+La Serena	Chile	Spanish
+La Spezia	Italy	Italian
+Lafayette	United States	English
+Lages	Brazil	Portuguese
+Lagos de Moreno	Mexico	Spanish
+Lahti	Finland	Finnish
+Laiwu	China	Chinese
+Laiyang	China	Chinese
+Laizhou	China	Chinese
+Lakewood	United States	English
+Lalitapur	Nepal	Nepali
+Lambaré	Paraguay	Spanish
+Lancaster	United States	English
+Langfang	China	Chinese
+Lansing	United States	English
+Lanzhou	China	Chinese
+Lanús	Argentina	Spanish
+Laohekou	China	Chinese
+Laredo	United States	English
+Larisa	Greece	Greek
+Las Heras	Argentina	Spanish
+Las Margaritas	Mexico	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+Las Vegas	United States	English
+Lashio (Lasho)	Myanmar	Burmese
+Latakia	Syria	Arabic
+Latina	Italy	Italian
+Lauro de Freitas	Brazil	Portuguese
+Lausanne	Switzerland	German
+Laval	Canada	English
+Le Havre	France	French
+Le Mans	France	French
+Le-Cap-Haïtien	Haiti	Haiti Creole
+Lecce	Italy	Italian
+Leeds	United Kingdom	English
+Leganés	Spain	Spanish
+Legnica	Poland	Polish
+Leicester	United Kingdom	English
+Leiden	Netherlands	Dutch
+Leipzig	Germany	German
+Leiyang	China	Chinese
+Lengshuijiang	China	Chinese
+Leninsk-Kuznetski	Russian Federation	Russian
+Lerdo	Mexico	Spanish
+Lerma	Mexico	Spanish
+Leshan	China	Chinese
+Leverkusen	Germany	German
+Lexington-Fayette	United States	English
+León	Mexico	Spanish
+León	Nicaragua	Spanish
+León	Spain	Spanish
+Lhasa	China	Chinese
+Liangcheng	China	Chinese
+Lianyuan	China	Chinese
+Lianyungang	China	Chinese
+Liaocheng	China	Chinese
+Liaoyang	China	Chinese
+Liaoyuan	China	Chinese
+Liberec	Czech Republic	Czech
+Lida	Belarus	Belorussian
+Liling	China	Chinese
+Lille	France	French
+Lilongwe	Malawi	Chichewa
+Lima	Peru	Spanish
+Limeira	Brazil	Portuguese
+Limoges	France	French
+Linchuan	China	Chinese
+Lincoln	United States	English
+Linfen	China	Chinese
+Linhai	China	Chinese
+Linhares	Brazil	Portuguese
+Linhe	China	Chinese
+Linköping	Sweden	Swedish
+Linqing	China	Chinese
+Linyi	China	Chinese
+Linz	Austria	German
+Lipetsk	Russian Federation	Russian
+Lisboa	Portugal	Portuguese
+Little Rock	United States	English
+Liupanshui	China	Chinese
+Liuzhou	China	Chinese
+Liu´an	China	Chinese
+Liverpool	United Kingdom	English
+Livonia	United States	English
+Livorno	Italy	Italian
+Liyang	China	Chinese
+Liège	Belgium	Dutch
+Ljubertsy	Russian Federation	Russian
+Lleida (Lérida)	Spain	Spanish
+Logroño	Spain	Spanish
+Loja	Ecuador	Spanish
+Lomas de Zamora	Argentina	Spanish
+London	Canada	English
+London	United Kingdom	English
+Londrina	Brazil	Portuguese
+Long Beach	United States	English
+Long Xuyen	Vietnam	Vietnamese
+Longjing	China	Chinese
+Longkou	China	Chinese
+Longueuil	Canada	English
+Longyan	China	Chinese
+Los Angeles	Chile	Spanish
+Los Angeles	United States	English
+Los Cabos	Mexico	Spanish
+Los Teques	Venezuela	Spanish
+Loudi	China	Chinese
+Louisville	United States	English
+Lowell	United States	English
+Lower Hutt	New Zealand	English
+Lubbock	United States	English
+Lublin	Poland	Polish
+Luchou	Taiwan	Min
+Ludwigshafen am Rhein	Germany	German
+Lugansk	Ukraine	Ukrainian
+Lund	Sweden	Swedish
+Lungtan	Taiwan	Min
+Luohe	China	Chinese
+Luoyang	China	Chinese
+Luton	United Kingdom	English
+Lutsk	Ukraine	Ukrainian
+Luxor	Egypt	Arabic
+Luzhou	China	Chinese
+Luziânia	Brazil	Portuguese
+Lviv	Ukraine	Ukrainian
+Lyon	France	French
+Lysyt?ansk	Ukraine	Ukrainian
+L´Hospitalet de Llobregat	Spain	Spanish
+Lázaro Cárdenas	Mexico	Spanish
+Lódz	Poland	Polish
+Lübeck	Germany	German
+Lünen	Germany	German
+EXPLAIN
+SELECT Name FROM City
+WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	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
+SELECT Name FROM City
+WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+City.Population > 100000;
+Name
+?iauliai
+Beirut
+Bengasi
+Daugavpils
+Kaunas
+Klaipeda
+Maseru
+Misrata
+Monrovia
+Panevezys
+Riga
+Tripoli
+Tripoli
+Vientiane
+Vilnius
+EXPLAIN
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+FROM Country LEFT JOIN CountryLanguage ON
+(CountryLanguage.Country=Country.Code AND Language='English')
+WHERE 
+Country.Population > 10000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	ALL	NULL	NULL	NULL	NULL	239	Using where
+1	SIMPLE	CountryLanguage	eq_ref	PRIMARY	PRIMARY	33	world.Country.Code,const	1	Using where
+SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+FROM Country LEFT JOIN CountryLanguage ON
+(CountryLanguage.Country=Country.Code AND Language='English')
+WHERE 
+Country.Population > 10000000;
+Name	IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage)
+Afghanistan	NULL
+Algeria	NULL
+Angola	NULL
+Argentina	NULL
+Australia	81.2
+Bangladesh	NULL
+Belarus	NULL
+Belgium	NULL
+Brazil	NULL
+Burkina Faso	NULL
+Cambodia	NULL
+Cameroon	NULL
+Canada	60.4
+Chile	NULL
+China	NULL
+Colombia	NULL
+Congo, The Democratic Republic of the	NULL
+Cuba	NULL
+Czech Republic	NULL
+Côte d?Ivoire	NULL
+Ecuador	NULL
+Egypt	NULL
+Ethiopia	NULL
+France	NULL
+Germany	NULL
+Ghana	NULL
+Greece	NULL
+Guatemala	NULL
+Hungary	NULL
+India	NULL
+Indonesia	NULL
+Iran	NULL
+Iraq	NULL
+Italy	NULL
+Japan	0.1
+Kazakstan	NULL
+Kenya	NULL
+Madagascar	NULL
+Malawi	NULL
+Malaysia	1.6
+Mali	NULL
+Mexico	NULL
+Morocco	NULL
+Mozambique	NULL
+Myanmar	NULL
+Nepal	NULL
+Netherlands	NULL
+Niger	NULL
+Nigeria	NULL
+North Korea	NULL
+Pakistan	NULL
+Peru	NULL
+Philippines	NULL
+Poland	NULL
+Romania	NULL
+Russian Federation	NULL
+Saudi Arabia	NULL
+Somalia	NULL
+South Africa	8.5
+South Korea	NULL
+Spain	NULL
+Sri Lanka	NULL
+Sudan	NULL
+Syria	NULL
+Taiwan	NULL
+Tanzania	NULL
+Thailand	NULL
+Turkey	NULL
+Uganda	NULL
+Ukraine	NULL
+United Kingdom	97.3
+United States	86.2
+Uzbekistan	NULL
+Venezuela	NULL
+Vietnam	NULL
+Yemen	NULL
+Yugoslavia	NULL
+Zimbabwe	2.2
+set join_buffer_size=256;
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	256
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	range	PRIMARY,Name	Name	52	NULL	10	Using index condition; Using MRR
+1	SIMPLE	City	ref	Population,Country	Country	3	world.Country.Code	18	Using where
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
+1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name	Name	Language
+La Ceiba	Honduras	Spanish
+La Habana	Cuba	Spanish
+La Matanza	Argentina	Spanish
+La Paz	Bolivia	Spanish
+La Paz	Mexico	Spanish
+La Paz	Mexico	Spanish
+La Plata	Argentina	Spanish
+La Rioja	Argentina	Spanish
+La Romana	Dominican Republic	Spanish
+La Serena	Chile	Spanish
+La Spezia	Italy	Italian
+Lafayette	United States	English
+Lages	Brazil	Portuguese
+Lagos de Moreno	Mexico	Spanish
+Lahti	Finland	Finnish
+Laiwu	China	Chinese
+Laiyang	China	Chinese
+Laizhou	China	Chinese
+Lakewood	United States	English
+Lalitapur	Nepal	Nepali
+Lambaré	Paraguay	Spanish
+Lancaster	United States	English
+Langfang	China	Chinese
+Lansing	United States	English
+Lanzhou	China	Chinese
+Lanús	Argentina	Spanish
+Laohekou	China	Chinese
+Laredo	United States	English
+Larisa	Greece	Greek
+Las Heras	Argentina	Spanish
+Las Margaritas	Mexico	Spanish
+Las Palmas de Gran Canaria	Spain	Spanish
+Las Vegas	United States	English
+Lashio (Lasho)	Myanmar	Burmese
+Latakia	Syria	Arabic
+Latina	Italy	Italian
+Lauro de Freitas	Brazil	Portuguese
+Lausanne	Switzerland	German
+Laval	Canada	English
+Le Havre	France	French
+Le Mans	France	French
+Le-Cap-Haïtien	Haiti	Haiti Creole
+Lecce	Italy	Italian
+Leeds	United Kingdom	English
+Leganés	Spain	Spanish
+Legnica	Poland	Polish
+Leicester	United Kingdom	English
+Leiden	Netherlands	Dutch
+Leipzig	Germany	German
+Leiyang	China	Chinese
+Lengshuijiang	China	Chinese
+Leninsk-Kuznetski	Russian Federation	Russian
+Lerdo	Mexico	Spanish
+Lerma	Mexico	Spanish
+Leshan	China	Chinese
+Leverkusen	Germany	German
+Lexington-Fayette	United States	English
+León	Mexico	Spanish
+León	Nicaragua	Spanish
+León	Spain	Spanish
+Lhasa	China	Chinese
+Liangcheng	China	Chinese
+Lianyuan	China	Chinese
+Lianyungang	China	Chinese
+Liaocheng	China	Chinese
+Liaoyang	China	Chinese
+Liaoyuan	China	Chinese
+Liberec	Czech Republic	Czech
+Lida	Belarus	Belorussian
+Liling	China	Chinese
+Lille	France	French
+Lilongwe	Malawi	Chichewa
+Lima	Peru	Spanish
+Limeira	Brazil	Portuguese
+Limoges	France	French
+Linchuan	China	Chinese
+Lincoln	United States	English
+Linfen	China	Chinese
+Linhai	China	Chinese
+Linhares	Brazil	Portuguese
+Linhe	China	Chinese
+Linköping	Sweden	Swedish
+Linqing	China	Chinese
+Linyi	China	Chinese
+Linz	Austria	German
+Lipetsk	Russian Federation	Russian
+Lisboa	Portugal	Portuguese
+Little Rock	United States	English
+Liupanshui	China	Chinese
+Liuzhou	China	Chinese
+Liu´an	China	Chinese
+Liverpool	United Kingdom	English
+Livonia	United States	English
+Livorno	Italy	Italian
+Liyang	China	Chinese
+Liège	Belgium	Dutch
+Ljubertsy	Russian Federation	Russian
+Lleida (Lérida)	Spain	Spanish
+Logroño	Spain	Spanish
+Loja	Ecuador	Spanish
+Lomas de Zamora	Argentina	Spanish
+London	Canada	English
+London	United Kingdom	English
+Londrina	Brazil	Portuguese
+Long Beach	United States	English
+Long Xuyen	Vietnam	Vietnamese
+Longjing	China	Chinese
+Longkou	China	Chinese
+Longueuil	Canada	English
+Longyan	China	Chinese
+Los Angeles	Chile	Spanish
+Los Angeles	United States	English
+Los Cabos	Mexico	Spanish
+Los Teques	Venezuela	Spanish
+Loudi	China	Chinese
+Louisville	United States	English
+Lowell	United States	English
+Lower Hutt	New Zealand	English
+Lubbock	United States	English
+Lublin	Poland	Polish
+Luchou	Taiwan	Min
+Ludwigshafen am Rhein	Germany	German
+Lugansk	Ukraine	Ukrainian
+Lund	Sweden	Swedish
+Lungtan	Taiwan	Min
+Luohe	China	Chinese
+Luoyang	China	Chinese
+Luton	United Kingdom	English
+Lutsk	Ukraine	Ukrainian
+Luxor	Egypt	Arabic
+Luzhou	China	Chinese
+Luziânia	Brazil	Portuguese
+Lviv	Ukraine	Ukrainian
+Lyon	France	French
+Lysyt?ansk	Ukraine	Ukrainian
+L´Hospitalet de Llobregat	Spain	Spanish
+Lázaro Cárdenas	Mexico	Spanish
+Lódz	Poland	Polish
+Lübeck	Germany	German
+Lünen	Germany	German
+EXPLAIN
+SELECT Name FROM City
+WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+City.Population > 100000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	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
+SELECT Name FROM City
+WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND
+City.Population > 100000;
+Name
+?iauliai
+Beirut
+Bengasi
+Daugavpils
+Kaunas
+Klaipeda
+Maseru
+Misrata
+Monrovia
+Panevezys
+Riga
+Tripoli
+Tripoli
+Vientiane
+Vilnius
+set join_buffer_size=default;
+show variables like 'join_buffer_size';
+Variable_name	Value
+join_buffer_size	131072
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND City.Population > 3000000;
+Name	Name
+Alexandria	Egypt
+Ankara	Turkey
+Baghdad	Iraq
+Bangkok	Thailand
+Berlin	Germany
+Cairo	Egypt
+Calcutta [Kolkata]	India
+Chengdu	China
+Chennai (Madras)	India
+Chongqing	China
+Ciudad de México	Mexico
+Delhi	India
+Dhaka	Bangladesh
+Harbin	China
+Ho Chi Minh City	Vietnam
+Istanbul	Turkey
+Jakarta	Indonesia
+Jokohama [Yokohama]	Japan
+Kanton [Guangzhou]	China
+Karachi	Pakistan
+Kinshasa	Congo, The Democratic Republic of the
+Lahore	Pakistan
+Lima	Peru
+London	United Kingdom
+Los Angeles	United States
+Moscow	Russian Federation
+Mumbai (Bombay)	India
+New York	United States
+Peking	China
+Pusan	South Korea
+Rangoon (Yangon)	Myanmar
+Rio de Janeiro	Brazil
+Riyadh	Saudi Arabia
+Santafé de Bogotá	Colombia
+Santiago de Chile	Chile
+Seoul	South Korea
+Shanghai	China
+Shenyang	China
+Singapore	Singapore
+St Petersburg	Russian Federation
+Sydney	Australia
+São Paulo	Brazil
+Teheran	Iran
+Tianjin	China
+Tokyo	Japan
+Wuhan	China
+set join_buffer_size=256;
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND City.Population > 3000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	range	Population,Country	Population	4	NULL	#	Using index condition; Using MRR
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND City.Population > 3000000;
+Name	Name
+Alexandria	Egypt
+Ankara	Turkey
+Baghdad	Iraq
+Bangkok	Thailand
+Berlin	Germany
+Cairo	Egypt
+Calcutta [Kolkata]	India
+Chengdu	China
+Chennai (Madras)	India
+Chongqing	China
+Ciudad de México	Mexico
+Delhi	India
+Dhaka	Bangladesh
+Harbin	China
+Ho Chi Minh City	Vietnam
+Istanbul	Turkey
+Jakarta	Indonesia
+Jokohama [Yokohama]	Japan
+Kanton [Guangzhou]	China
+Karachi	Pakistan
+Kinshasa	Congo, The Democratic Republic of the
+Lahore	Pakistan
+Lima	Peru
+London	United Kingdom
+Los Angeles	United States
+Moscow	Russian Federation
+Mumbai (Bombay)	India
+New York	United States
+Peking	China
+Pusan	South Korea
+Rangoon (Yangon)	Myanmar
+Rio de Janeiro	Brazil
+Riyadh	Saudi Arabia
+Santafé de Bogotá	Colombia
+Santiago de Chile	Chile
+Seoul	South Korea
+Shanghai	China
+Shenyang	China
+Singapore	Singapore
+St Petersburg	Russian Federation
+Sydney	Australia
+São Paulo	Brazil
+Teheran	Iran
+Tianjin	China
+Tokyo	Japan
+Wuhan	China
+set join_buffer_size=default;
+ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default '';
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default '';
+SELECT City.Name, Country.Name FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name
+?iauliai	Lithuania
+Beirut	Lebanon
+Bengasi	Libyan Arab Jamahiriya
+Daugavpils	Latvia
+Kaunas	Lithuania
+Klaipeda	Lithuania
+Maseru	Lesotho
+Misrata	Libyan Arab Jamahiriya
+Monrovia	Liberia
+Panevezys	Lithuania
+Riga	Latvia
+Tripoli	Lebanon
+Tripoli	Libyan Arab Jamahiriya
+Vientiane	Laos
+Vilnius	Lithuania
+ALTER TABLE Country ADD COLUMN PopulationBar text;
+UPDATE Country 
+SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int));
+SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name	PopulationBar
+?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
+Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+set join_buffer_size=256;
+SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
+WHERE City.Country=Country.Code AND 
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name	Name	PopulationBar
+?iauliai	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Beirut	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Bengasi	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Daugavpils	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Kaunas	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Maseru	Lesotho	xxxxxxxxxxxxxxxxxxxxxx
+Misrata	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Monrovia	Liberia	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Riga	Latvia	xxxxxxxxxxxxxxxxxxxxxxxx
+Tripoli	Lebanon	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Tripoli	Libyan Arab Jamahiriya	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Vientiane	Laos	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+Vilnius	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
+set join_buffer_size=default;
+DROP DATABASE world;
+use test;
+CREATE TABLE t1(
+affiliatetometaid int  NOT NULL default '0',
+uniquekey int NOT NULL default '0',
+metaid int  NOT NULL default '0',
+affiliateid int  NOT NULL default '0',
+xml text,
+isactive char(1) NOT NULL default 'Y',
+PRIMARY KEY  (affiliatetometaid)
+);
+CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey);
+CREATE INDEX t1_affiliateid ON t1(affiliateid);
+CREATE INDEX t1_metaid on t1 (metaid);
+INSERT INTO t1 VALUES
+(1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y');
+CREATE TABLE t2(
+metaid int  NOT NULL default '0',
+name varchar(80) NOT NULL default '',
+dateadded timestamp NOT NULL ,
+xml text,
+status int default NULL,
+origin int default NULL,
+gid int NOT NULL default '1',
+formattypeid int  default NULL,
+PRIMARY KEY  (metaid)
+);
+CREATE INDEX t2_status ON t2(status);
+CREATE INDEX t2_gid ON t2(gid);
+CREATE INDEX t2_formattypeid ON t2(formattypeid);
+INSERT INTO t2 VALUES
+(1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL),
+(1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL);
+CREATE TABLE t3(
+mediaid int  NOT NULL ,
+metaid int  NOT NULL default '0',
+formatid int  NOT NULL default '0',
+status int default NULL,
+path varchar(100) NOT NULL default '',
+datemodified timestamp NOT NULL ,
+resourcetype int  NOT NULL default '1',
+parameters text,
+signature int  default NULL,
+quality int  NOT NULL default '255',
+PRIMARY KEY  (mediaid)
+);
+CREATE INDEX t3_metaid ON t3(metaid);
+CREATE INDEX t3_formatid ON t3(formatid);
+CREATE INDEX t3_status ON t3(status);
+CREATE INDEX t3_metaidformatid ON t3(metaid,formatid);
+CREATE INDEX t3_signature ON t3(signature);
+CREATE INDEX t3_quality ON t3(quality);
+INSERT INTO t3 VALUES
+(6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255),
+(3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255);
+CREATE TABLE t4(
+formatid int  NOT NULL ,
+name varchar(60) NOT NULL default '',
+formatclassid int  NOT NULL default '0',
+mime varchar(60) default NULL,
+extension varchar(10) default NULL,
+priority int NOT NULL default '0',
+canaddtocapability char(1) NOT NULL default 'Y',
+PRIMARY KEY  (formatid)
+);
+CREATE INDEX t4_formatclassid ON t4(formatclassid);
+CREATE INDEX t4_formats_idx ON t4(canaddtocapability);
+INSERT INTO t4 VALUES
+(19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'),
+(54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y');
+CREATE TABLE t5(
+formatclassid int  NOT NULL ,
+name varchar(60) NOT NULL default '',
+priority int NOT NULL default '0',
+formattypeid int  NOT NULL default '0',
+PRIMARY KEY  (formatclassid)
+);
+CREATE INDEX t5_formattypeid on t5(formattypeid);
+INSERT INTO t5 VALUES
+(11, "Info", 0, 4), (13, "Digital Audio", 0, 2);
+CREATE TABLE t6(
+formattypeid int  NOT NULL ,
+name varchar(60) NOT NULL default '',
+priority int default NULL,
+PRIMARY KEY  (formattypeid)
+);
+INSERT INTO t6 VALUES
+(2, "Ringtones", 0);
+CREATE TABLE t7(
+metaid int  NOT NULL default '0',
+artistid int  NOT NULL default '0',
+PRIMARY KEY  (metaid,artistid)
+);
+INSERT INTO t7 VALUES
+(4, 5), (3, 4);
+CREATE TABLE t8(
+artistid int  NOT NULL ,
+name varchar(80) NOT NULL default '',
+PRIMARY KEY  (artistid)
+);
+INSERT INTO t8 VALUES
+(5, "Anastacia"), (4, "John Mayer");
+CREATE TABLE t9(
+subgenreid int  NOT NULL default '0',
+metaid int  NOT NULL default '0',
+PRIMARY KEY  (subgenreid,metaid)
+) ;
+CREATE INDEX t9_subgenreid ON t9(subgenreid);
+CREATE INDEX t9_metaid ON t9(metaid);
+INSERT INTO t9 VALUES 
+(138, 4), (31, 3);
+CREATE TABLE t10(
+subgenreid int  NOT NULL ,
+genreid int  NOT NULL default '0',
+name varchar(80) NOT NULL default '',
+PRIMARY KEY  (subgenreid)
+) ;
+CREATE INDEX t10_genreid ON t10(genreid);
+INSERT INTO t10 VALUES 
+(138, 19, ''), (31, 3, '');
+CREATE TABLE t11(
+genreid int  NOT NULL default '0',
+name char(80) NOT NULL default '',
+priority int NOT NULL default '0',
+masterclip char(1) default NULL,
+PRIMARY KEY  (genreid)
+) ;
+CREATE INDEX t11_masterclip ON t11( masterclip);
+INSERT INTO t11 VALUES
+(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y');
+EXPLAIN
+SELECT t1.uniquekey, t1.xml AS affiliateXml,
+t8.name AS artistName, t8.artistid, 
+t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
+t10.subgenreid, t10.name AS subgenreName,
+t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
+t4.priority + t5.priority + t6.priority AS overallPriority,
+t3.path AS path, t3.mediaid, 
+t4.formatid, t4.name AS formatName, 
+t5.formatclassid, t5.name AS formatclassName, 
+t6.formattypeid, t6.name AS formattypeName 
+FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
+WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
+t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
+t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
+t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
+t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
+t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
+t1.metaid = t2.metaid AND t1.affiliateid = '2';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	system	PRIMARY	NULL	NULL	NULL	1	
+1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
+1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where
+1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
+1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
+1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition
+1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
+1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
+1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where
+SELECT t1.uniquekey, t1.xml AS affiliateXml,
+t8.name AS artistName, t8.artistid, 
+t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
+t10.subgenreid, t10.name AS subgenreName,
+t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
+t4.priority + t5.priority + t6.priority AS overallPriority,
+t3.path AS path, t3.mediaid, 
+t4.formatid, t4.name AS formatName, 
+t5.formatclassid, t5.name AS formatclassName, 
+t6.formattypeid, t6.name AS formattypeName 
+FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
+WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
+t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND 
+t10.genreid = t11.genreid AND  t3.metaid = t2.metaid AND
+t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
+t4.canaddtocapability =  'Y' AND t5.formattypeid = t6.formattypeid AND
+t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
+t1.metaid = t2.metaid AND t1.affiliateid = '2';
+uniquekey	affiliateXml	artistName	artistid	genreName	genreid	genrePriority	subgenreid	subgenreName	metaName	metaid	metaXml	overallPriority	path	mediaid	formatid	formatName	formatclassid	formatclassName	formattypeid	formattypeName
+DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
+CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' );
+CREATE TABLE t2 (
+a2 int, b2 int, filler2 char(64) default ' ', 
+PRIMARY KEY idx(a2,b2,filler2)
+) ;
+CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3));
+INSERT INTO t1(a1) VALUES 
+(4), (7), (1), (9), (8), (5), (3), (6), (2);
+INSERT INTO t2(a2,b2) VALUES
+(1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56),
+(4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81),
+(5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51),
+(9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79),
+(3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11);
+INSERT INTO t3 VALUES
+(30,302), (92,923), (18,187), (45,459), (30,309), 
+(39,393), (68,685), (45,458), (21,210), (81,817),
+(40,405), (61,618), (73,738), (92,929), (27,275),
+(18,188), (84,846), (56,564), (14,144), (76,763), 
+(98,982), (55,551), (17,174), (99,998), (51,513),
+(28,282), (52,527), (33,336), (13,138), (87,878), 
+(43,431), (91,916), (62,624), (79,797), (49,494),
+(93,933), (34,347), (82,829), (78,780), (63,634), 
+(32,329), (22,228), (11,114), (74,749), (23,236);
+EXPLAIN
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
+1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+a1<>a2	a1	a2	b2	b3	c3	s1	s2
+0	1	1	30	30	309		
+0	1	1	32	32	329		
+0	2	2	61	61	618		
+0	3	3	45	45	458		
+0	3	3	45	45	459		
+0	4	4	13	13	138		
+0	4	4	18	18	188		
+0	5	5	82	82	829		
+0	5	5	87	87	878		
+0	6	6	73	73	738		
+0	6	6	74	74	749		
+0	8	8	92	92	929		
+0	8	8	99	99	998		
+0	9	9	22	22	228		
+set join_buffer_size=512;
+EXPLAIN
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a1	1	Using index
+1	SIMPLE	t3	ref	idx	idx	5	test.t2.b2	5	Using where
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+a1<>a2	a1	a2	b2	b3	c3	s1	s2
+0	1	1	30	30	309		
+0	1	1	32	32	329		
+0	2	2	61	61	618		
+0	3	3	45	45	458		
+0	3	3	45	45	459		
+0	4	4	13	13	138		
+0	4	4	18	18	188		
+0	5	5	82	82	829		
+0	5	5	87	87	878		
+0	6	6	73	73	738		
+0	6	6	74	74	749		
+0	8	8	92	92	929		
+0	8	8	99	99	998		
+0	9	9	22	22	228		
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int, b int, INDEX idx(b));
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20);
+INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20);
+INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20);
+set join_buffer_size=32;
+Warnings:
+Warning	1292	Truncated incorrect join_buffer_size value: '32'
+EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	2	
+SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;
+a	b	a	b
+7	40	7	10
+7	40	7	10
+7	40	7	20
+8	30	8	10
+8	30	8	20
+DROP TABLE t1,t2;
+
+BUG#40136: Group by is ignored when join buffer is used for an outer join
+
+create table t1(a int PRIMARY KEY, b int);
+insert into t1 values
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+create table t2 (p int, a int, INDEX i_a(a));
+insert into t2 values
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+explain
+select t1.a, count(t2.p) as count
+from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where
+select t1.a, count(t2.p) as count
+from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
+a	count
+1	1
+2	0
+3	2
+5	0
+6	0
+7	2
+8	0
+9	0
+drop table t1, t2;
+#
+# Bug #40134: outer join with not exists optimization and join buffer
+#
+set join_buffer_size=default;
+CREATE TABLE t1 (a int NOT NULL);
+INSERT INTO t1 VALUES (2), (4), (3), (5), (1);
+CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a));
+INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20);
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+a	a	b
+3	NULL	NULL
+5	NULL	NULL
+1	NULL	NULL
+DROP TABLE t1, t2;
+#
+# BUG#40268: Nested outer join with not null-rejecting where condition
+#            over an inner table which is not the last in the nest
+#
+CREATE TABLE t2 (a int, b int, c int);
+CREATE TABLE t3 (a int, b int, c int);
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0);
+INSERT INTO t3 VALUES (1,2,0), (2,2,0);
+INSERT INTO t4 VALUES (3,2,0), (4,2,0);
+SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
+FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b
+WHERE t3.a+2<t2.a OR t3.c IS NULL;
+a	b	a	b	a	b
+3	3	NULL	NULL	NULL	NULL
+4	2	1	2	3	2
+4	2	1	2	4	2
+5	3	NULL	NULL	NULL	NULL
+DROP TABLE t2, t3, t4;
+#
+# Bug #40192: outer join with where clause when using BNL 
+#
+create table t1 (a int, b int);
+insert into t1 values (2, 20), (3, 30), (1, 10);
+create table t2 (a int, c int);
+insert into t2 values (1, 101), (3, 102), (1, 100);
+select * from t1 left join t2 on t1.a=t2.a;
+a	b	a	c
+1	10	1	100
+1	10	1	101
+2	20	NULL	NULL
+3	30	3	102
+explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+a	b	a	c
+2	20	NULL	NULL
+3	30	3	102
+drop table t1, t2;
+#
+# Bug #40317: outer join with with constant on expression equal to FALSE
+#
+create table t1 (a int);
+insert into t1 values (30), (40), (20);
+create table t2 (b int);
+insert into t2 values (200), (100);
+select * from t1 left join t2 on (1=0);
+a	b
+30	NULL
+40	NULL
+20	NULL
+explain select * from t1 left join t2 on (1=0) where a=40;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from t1 left join t2 on (1=0) where a=40;
+a	b
+40	NULL
+drop table t1, t2;
+#
+# Bug #41204: small buffer with big rec_per_key for ref access
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1 VALUES (20000), (10000);
+CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b));
+INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5);
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+INSERT INTO t2(b,c) SELECT b,c FROM t2;
+ANALYZE TABLE t1,t2;
+set join_buffer_size=1024;
+EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2050	Using where
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	640	
+SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b;
+AVG(c)
+5.0000
+set join_buffer_size=default;
+DROP TABLE t1, t2;
+#
+# Bug #41894: big join buffer of level 7 used to join records
+#              with null values in place of varchar strings
+#
+CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY,
+b varchar(127) DEFAULT NULL);
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
+INSERT INTO t2 SELECT * FROM t1;
+CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
+INSERT INTO t3 SELECT * FROM t1;
+set join_buffer_size=1024*1024;
+EXPLAIN
+SELECT COUNT(*) FROM t1,t2,t3
+WHERE t1.a=t2.a AND t2.a=t3.a AND
+t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	16384	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+SELECT COUNT(*) FROM t1,t2,t3
+WHERE t1.a=t2.a AND t2.a=t3.a AND
+t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
+COUNT(*)
+16384
+set join_buffer_size=default;
+DROP TABLE t1,t2,t3;
+#
+# Bug #42020: join buffer is used  for outer join with fields of 
+#             several outer tables in join buffer
+#
+CREATE TABLE t1 (
+a bigint NOT NULL,
+PRIMARY KEY (a) 
+);
+INSERT INTO t1 VALUES
+(2), (1);
+CREATE TABLE t2 (
+a bigint NOT NULL,
+b bigint NOT NULL,
+PRIMARY KEY (a,b)
+);
+INSERT INTO t2 VALUES
+(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
+(1,10), (1, 20), (1,30), (1,40), (1,50);
+CREATE TABLE t3 (
+pk bigint NOT NULL AUTO_INCREMENT,
+a bigint NOT NULL,
+b bigint NOT NULL,
+val bigint DEFAULT '0',
+PRIMARY KEY (pk),
+KEY idx (a,b)
+);
+INSERT INTO t3(a,b) VALUES
+(2,30), (2,40), (2,50), (2,60), (2,70), (2,80),
+(4,30), (4,40), (4,50), (4,60), (4,70), (4,80),
+(5,30), (5,40), (5,50), (5,60), (5,70), (5,80),
+(7,30), (7,40), (7,50), (7,60), (7,70), (7,80);
+SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
+FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
+WHERE t1.a=t2.a;
+a	a	a	b	b	val
+1	1	NULL	10	NULL	NULL
+1	1	NULL	20	NULL	NULL
+1	1	NULL	30	NULL	NULL
+1	1	NULL	40	NULL	NULL
+1	1	NULL	50	NULL	NULL
+2	2	2	30	30	0
+2	2	2	40	40	0
+2	2	2	50	50	0
+2	2	2	60	60	0
+2	2	2	70	70	0
+2	2	2	80	80	0
+set join_buffer_size=256;
+EXPLAIN
+SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
+FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
+WHERE t1.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
+1	SIMPLE	t3	ref	idx	idx	16	test.t1.a,test.t2.b	2	
+SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
+FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
+WHERE t1.a=t2.a;
+a	a	a	b	b	val
+1	1	NULL	10	NULL	NULL
+1	1	NULL	20	NULL	NULL
+1	1	NULL	30	NULL	NULL
+1	1	NULL	40	NULL	NULL
+1	1	NULL	50	NULL	NULL
+2	2	2	30	30	0
+2	2	2	40	40	0
+2	2	2	50	50	0
+2	2	2	60	60	0
+2	2	2	70	70	0
+2	2	2	80	80	0
+DROP INDEX idx ON t3;
+EXPLAIN
+SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
+FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
+WHERE t1.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	8	NULL	2	Using index
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	24	Using where
+SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val 
+FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) 
+WHERE t1.a=t2.a;
+a	a	a	b	b	val
+1	1	NULL	10	NULL	NULL
+1	1	NULL	20	NULL	NULL
+1	1	NULL	30	NULL	NULL
+1	1	NULL	40	NULL	NULL
+1	1	NULL	50	NULL	NULL
+2	2	2	30	30	0
+2	2	2	40	40	0
+2	2	2	50	50	0
+2	2	2	60	60	0
+2	2	2	70	70	0
+2	2	2	80	80	0
+set join_buffer_size=default;
+DROP TABLE t1,t2,t3;
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2));
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+(2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+(4,4, 'qwerty');
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+(2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+(4,4, 'qwerty');
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+f1	f2	f3
+1	1	qwerty
+1	1	qwerty
+2	2	qwerty
+2	2	qwerty
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	3	Using index condition
+drop table t1,t2;
+#
+# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled 
+#             
+create table t1 (d int, id1 int, index idx1 (d, id1));
+insert into t1 values
+(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30);
+create table t2 (id1 int, id2 int, index idx2 (id1));
+insert into t2 values 
+(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
+(40, 200), (30, 300), (10, 400), (20, 200), (20, 300);
+explain
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
+where t1.d=3 group by t1.id1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index
+1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
+where t1.d=3 group by t1.id1;
+id1	sum(t2.id2)
+10	900
+20	2000
+30	900
+explain
+select t1.id1  from t1 join t2 on t1.id1=t2.id1 
+where t1.d=3 and t2.id2 > 200 order by t1.id1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index
+1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where
+select t1.id1  from t1 join t2 on t1.id1=t2.id1 
+where t1.d=3 and t2.id2 > 200 order by t1.id1;
+id1
+10
+10
+20
+20
+20
+20
+30
+30
+drop table t1,t2;
+#
+# Bug #44019: star-like multi-join query executed optimizer_join_cache_level=6 
+#             
+create table t1 (a int, b int, c int, d int);
+create table t2 (b int, e varchar(16), index idx(b));
+create table t3 (d int, f varchar(16), index idx(d));
+create table t4 (c int, g varchar(16), index idx(c));
+insert into t1 values
+(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000),
+(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800),
+(7, 70, 700, 7000);
+insert into t2 values
+(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
+(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
+(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
+insert into t3 values
+(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
+(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
+(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
+insert into t4 values
+(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
+(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
+(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
+analyze table t2,t3,t4;
+explain 
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+a	b	c	d	e	f	g
+3	30	300	3000	bbb	ddd	ccc
+drop table t1,t2,t3,t4;
+#
+# Bug #44250: Corruption of linked join buffers when using BKA 
+#             
+CREATE TABLE t1 (
+id1 bigint(20) DEFAULT NULL,
+id2 bigint(20) DEFAULT NULL,
+id3 bigint(20) DEFAULT NULL,
+num1 bigint(20) DEFAULT NULL,
+num2 int(11) DEFAULT NULL,
+num3 bigint(20) DEFAULT NULL
+);
+CREATE TABLE t2 (
+id3 bigint(20) NOT NULL DEFAULT '0',
+id4 bigint(20) DEFAULT NULL,
+enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL,
+PRIMARY KEY (id3)
+);
+CREATE TABLE t3 (
+id4 bigint(20) NOT NULL DEFAULT '0',
+text1 text,
+PRIMARY KEY (id4)
+);
+CREATE TABLE t4 (
+id2 bigint(20) NOT NULL DEFAULT '0',
+dummy int(11) DEFAULT '0',
+PRIMARY KEY (id2)
+);
+CREATE TABLE t5 (
+id1 bigint(20) NOT NULL DEFAULT '0',
+id2 bigint(20) NOT NULL DEFAULT '0',
+enum2 enum('Active','Deleted','Paused') DEFAULT NULL,
+PRIMARY KEY (id1,id2)
+);
+set join_buffer_size=2048;
+EXPLAIN
+SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
+FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
+WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
+t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	349	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	8	test.t1.id3	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	8	test.t2.id4	1	Using where
+1	SIMPLE	t4	eq_ref	PRIMARY	PRIMARY	8	test.t1.id2	1	
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	16	test.t1.id1,test.t1.id2	1	Using where
+SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy
+FROM t1 JOIN  t2 JOIN  t3 JOIN  t4 JOIN  t5 
+WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and  t4.id2=t1.id2 AND
+t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D';
+id1	num3	text1	id4	id3	dummy
+228172702	134	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
+228172702	14	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
+228172702	15	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
+228172702	3	AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA	2567095402	2667134182	0
+228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	1	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	10	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	13	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	14	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	17	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	18	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	19	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	26	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	28	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	3	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	4	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	50	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	6	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	60	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	61	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	826928662	935693782	0
+228808822	62	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	84	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	89	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+228808822	9	CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC	2381969632	2482416112	0
+set join_buffer_size=default;
+DROP TABLE t1,t2,t3,t4,t5;
+#
+# Bug #46328: Use of aggregate function without GROUP BY clause 
+#             returns many rows (vs. one )
+#             
+CREATE TABLE t1 (
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO t1 VALUES
+(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9);
+CREATE TABLE t2 (
+int_key int(11) NOT NULL,
+KEY int_key (int_key)
+);
+INSERT INTO t2 VALUES (2),(3);
+
+# The query shall return 1 record with a max value 9 and one of the 
+# int_key values inserted above (undefined which one). A changed 
+# execution plan may change the value in the second column
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+MAX(t1.int_key)	int_key
+9	0
+
+explain 
+SELECT  MAX(t1.int_key), t1.int_key
+FROM t1 STRAIGHT_JOIN t2  
+ORDER BY t1.int_key;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	int_key	4	NULL	14	Using index
+1	SIMPLE	t2	index	NULL	int_key	4	NULL	2	Using index
+
+DROP TABLE t1,t2;
+#
+# Bug #45019: join buffer contains two blob columns one of which is
+#             used in the key employed to access the joined table
+#
+CREATE TABLE t1 (c1 int, c2 int, key (c2));
+INSERT INTO t1 VALUES (1,1);
+INSERT INTO t1 VALUES (2,2);
+CREATE TABLE t2 (c1 text, c2 text);
+INSERT INTO t2 VALUES('tt', 'uu');
+INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx');
+ANALYZE TABLE t1,t2;
+SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2
+WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1);
+c1	c2	c1	c2	LENGTH(t2.c1)	LENGTH(t2.c2)
+2	2	tt	uu	2	2
+DROP TABLE t1,t2;
+#
+# Regression test for
+# Bug#46733 - NULL value not returned for aggregate on empty result 
+#             set w/ semijoin on
+CREATE TABLE t1 (
+i int(11) NOT NULL,
+v varchar(1) DEFAULT NULL,
+PRIMARY KEY (i)
+);
+INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d');
+CREATE TABLE t2 (
+i int(11) NOT NULL,
+v varchar(1) DEFAULT NULL,
+PRIMARY KEY (i)
+);
+INSERT INTO t2 VALUES (1,'x'),(2,'y');
+
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+MAX(t1.i)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: 'x'
+Warning	1292	Truncated incorrect INTEGER value: 'y'
+
+EXPLAIN
+SELECT MAX(t1.i) 
+FROM t1 JOIN t2 ON t2.v
+ORDER BY t2.v;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+
+DROP TABLE t1,t2;
+#
+# Bug#51092: Linked join buffer gives wrong result 
+#            for 3-way cross join
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 (a INT, b INT);
+INSERT INTO t2 VALUES (1,1),(2,2);
+CREATE TABLE t3 (a INT, b INT);
+INSERT INTO t3 VALUES (1,1),(2,2);
+EXPLAIN SELECT t1.* FROM t1,t2,t3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT t1.* FROM t1,t2,t3;
+a	b
+1	1
+1	1
+1	1
+1	1
+2	2
+2	2
+2	2
+2	2
+DROP TABLE t1,t2,t3;
+#
+# BUG#52394 Segfault in JOIN_CACHE::get_offset () at sql_select.h:445
+#
+CREATE TABLE C(a int);
+INSERT INTO C VALUES(1),(2),(3),(4),(5);
+CREATE TABLE D (a int(11), b varchar(1));
+INSERT INTO D VALUES (6,'r'),(27,'o');
+CREATE TABLE E (a int(11) primary key, b varchar(1));
+INSERT INTO E VALUES
+(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'),(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'c');
+SELECT 1 FROM C,D,E WHERE D.a = E.a AND D.b = E.b;
+1
+DROP TABLE C,D,E;
+#
+# BUG#52540 Crash in JOIN_CACHE::set_match_flag_if_none () at sql_join_cache.cc:1883
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (a varchar(10));
+INSERT INTO t2 VALUES ('f'),('x');
+CREATE TABLE t3 (pk int(11) PRIMARY KEY);
+INSERT INTO t3 VALUES (2);
+CREATE TABLE t4 (a varchar(10));
+EXPLAIN SELECT 1
+FROM t2 LEFT JOIN
+((t1 JOIN t3 ON t1.a = t3.pk)
+LEFT JOIN t4 ON 1 )
+ON 1 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	0	Using where
+SELECT 1
+FROM t2 LEFT JOIN
+((t1 JOIN t3 ON t1.a = t3.pk)
+LEFT JOIN t4 ON 1 )
+ON 1 ;
+1
+1
+1
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug#51084: Batched key access crashes for SELECT with
+#            derived table and LEFT JOIN 
+#
+CREATE TABLE t1 (
+carrier int,
+id int PRIMARY KEY
+);
+INSERT INTO t1 VALUES (1,11),(1,12),(2,13);
+CREATE TABLE t2 (
+scan_date int,
+package_id int
+);
+INSERT INTO t2 VALUES (2008,21),(2008,22);
+CREATE TABLE t3 (
+carrier int PRIMARY KEY,
+id int
+);
+INSERT INTO t3 VALUES (1,31);
+CREATE TABLE t4 (
+carrier_id int,
+INDEX carrier_id(carrier_id)
+);
+INSERT INTO t4 VALUES (31),(32);
+
+SELECT COUNT(*)
+FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
+ON t3.carrier = t1.carrier;
+COUNT(*)
+6
+
+EXPLAIN
+SELECT COUNT(*)
+FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id)
+ON t3.carrier = t1.carrier;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	
+1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
+
+DROP TABLE t1,t2,t3,t4;
+#
+# Bug#45267: Incomplete check caused wrong result.
+#
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+CREATE TABLE t3 (
+`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
+);
+INSERT INTO t3 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
+(16),(17),(18),(19),(20);
+CREATE TABLE t2 (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`int_nokey` int(11) NOT NULL,
+`time_key` time NOT NULL,
+PRIMARY KEY (`pk`),
+KEY `time_key` (`time_key`)
+);
+INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46');
+SELECT DISTINCT t1.`pk`
+FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey`  ON t2.`time_key`
+GROUP BY 1;
+pk
+NULL
+DROP TABLE IF EXISTS t1, t2, t3;
+#
+# BUG#52636 6.0 allowing JOINs on NULL values w/ optimizer_join_cache_level = 5-8
+#
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),(3);
+CREATE TABLE t2 (a int, b int, KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,200);
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+delete from t1;
+INSERT INTO t1 VALUES (NULL),(NULL);
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+DROP TABLE t1,t2;
+CREATE TABLE t1 (b varchar(100));
+INSERT INTO t1 VALUES (NULL),("some varchar");
+CREATE TABLE t2 (a int, b varchar(100), KEY (b));
+INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar");
+explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+a
+NULL
+NULL
+DROP TABLE t1,t2;
+#
+# BUG#54359 "Extra rows with join_cache_level=7,8 and two joins
+# --and multi-column index"
+#
+CREATE TABLE t1 (
+`pk` int(11) NOT NULL,
+`col_int_key` int(11) DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`))
+;
+INSERT INTO t1 VALUES (4,9,'k','k');
+INSERT INTO t1 VALUES (12,5,'k','k');
+explain SELECT table2 .`col_int_key` FROM t1 table2,
+t1 table3 force index (`col_varchar_key`)
+where table3 .`pk` and table3 .`col_int_key`  >= table2 .`pk`  
+and table3 .`col_varchar_key`  = table2 .`col_varchar_nokey`;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	table2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	table3	ref	col_varchar_key	col_varchar_key	4	test.table2.col_varchar_nokey	1	Using index condition; Using where
+SELECT table2 .`col_int_key` FROM t1 table2,
+t1 table3 force index (`col_varchar_key`)
+where table3 .`pk` and table3 .`col_int_key`  >= table2 .`pk`  
+and table3 .`col_varchar_key`  = table2 .`col_varchar_nokey`;
+col_int_key
+9
+9
+drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+# and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime 
+FROM t1,t2
+WHERE t2.col_int_key = 1 AND t2.col_int >= 3
+GROUP BY t1.col_int_key
+ORDER BY t1.col_int_key, t1.col_datetime
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_int_key	col_int_key	5	const	1	Using where
+SELECT t1.col_int_key, t1.col_datetime 
+FROM t1,t2
+WHERE t2.col_int_key = 1 AND t2.col_int >= 3
+GROUP BY t1.col_int_key
+ORDER BY t1.col_int_key, t1.col_datetime
+LIMIT 2;
+col_int_key	col_datetime
+0	2000-09-26 07:45:57
+2	2003-02-11 21:19:41
+explain SELECT t1.col_int_key, t1.col_datetime 
+FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
+WHERE t2.col_int_key = 1 AND t2.col_int >= 3
+GROUP BY t1.col_int_key
+ORDER BY t1.col_int_key, t1.col_datetime
+LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t1.col_int_key, t1.col_datetime 
+FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
+WHERE t2.col_int_key = 1 AND t2.col_int >= 3
+GROUP BY t1.col_int_key
+ORDER BY t1.col_int_key, t1.col_datetime
+LIMIT 2;
+col_int_key	col_datetime
+0	2000-09-26 07:45:57
+2	2003-02-11 21:19:41
+drop table t1,t2;
+
+# Bug#11766522 "59651: ASSERTION `TABLE_REF->HAS_RECORD' FAILED
+# WITH JOIN_CACHE_LEVEL=3"
+
+CREATE TABLE t1 (
+b varchar(20)
+)  ;
+INSERT INTO t1 VALUES ('1'),('1');
+CREATE TABLE t4 (
+col253 text
+)  ;
+INSERT INTO t4 VALUES (''),('pf');
+CREATE TABLE t6 (
+col282 timestamp
+)  ;
+INSERT INTO t6 VALUES ('2010-11-07 01:04:45'),('2010-12-13 01:36:32');
+CREATE TABLE t7 (
+col319 timestamp NOT NULL,
+UNIQUE KEY idx263 (col319)
+)  ;
+insert into t7 values("2000-01-01"),("2000-01-02");
+CREATE TABLE t3 (
+col582 char(230) CHARACTER SET utf8 DEFAULT NULL
+)  ;
+INSERT INTO t3 VALUES ('cymej'),('spb');
+CREATE TABLE t5 (
+col712 time
+)  ;
+insert into t5 values(0),(0);
+CREATE TABLE t8 (
+col804 char(169),
+col805 varchar(51)
+)  ;
+INSERT INTO t8 VALUES ('tmqcb','pwk');
+CREATE TABLE t2 (
+col841 varchar(10)
+)  ;
+INSERT INTO t2 VALUES (''),('');
+set join_buffer_size=1;
+Warnings:
+Warning	1292	Truncated incorrect join_buffer_size value: '1'
+select @@join_buffer_size;
+@@join_buffer_size
+128
+select count(*) from
+(t1 join t2 join t3)
+left join t4 on 1
+left join t5 on 1 like t4.col253
+left join t6 on t5.col712 is null
+left join t7 on t1.b <=>t7.col319
+left join t8 on t3.col582 <=  1;
+count(*)
+32
+drop table t1,t2,t3,t4,t5,t6,t7,t8;
+set @@join_buffer_size=default;
+set optimizer_join_cache_level = default;
+set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/join_cache_jcl1.result	2011-06-08 13:24:56 +0000
@@ -1444,7 +1444,6 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;

=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/join_cache_jcl2.result	2011-06-08 13:24:56 +0000
@@ -1444,7 +1444,6 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;

=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/join_cache_jcl3.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/join_cache_jcl4.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2097,8 +2096,8 @@ ON t3.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	
-1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
+1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	index	carrier_id	carrier_id	5	NULL	2	Using where; Using index; Using join buffer (BNL, incremental buffers)
 
 DROP TABLE t1,t2,t3,t4;
 #
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result	2011-04-26 08:49:10 +0000
+++ b/mysql-test/r/join_cache_jcl5.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA, regular buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (BKA, regular buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer (BKA, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result	2011-04-26 08:49:10 +0000
+++ b/mysql-test/r/join_cache_jcl6.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2097,8 +2096,8 @@ ON t3.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	
-1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
+1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	index	carrier_id	carrier_id	5	NULL	2	Using where; Using index; Using join buffer (BNL, incremental buffers)
 
 DROP TABLE t1,t2,t3,t4;
 #
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result	2011-04-26 08:49:10 +0000
+++ b/mysql-test/r/join_cache_jcl7.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (BKA_UNIQUE, regular buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result	2011-04-26 08:49:10 +0000
+++ b/mysql-test/r/join_cache_jcl8.result	2011-06-08 13:24:56 +0000
@@ -1444,13 +1444,12 @@ create table t2 (p int, a int, INDEX i_a
 insert into t2 values
 (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
 (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
-The following must not show "using join cache":
 explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
-1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 a	count
@@ -1475,7 +1474,7 @@ EXPLAIN
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer (BKA_UNIQUE, incremental buffers)
+1	SIMPLE	t2	ALL	i_a	NULL	NULL	NULL	5	Using where; Not exists; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
 a	a	b
 3	NULL	NULL
@@ -2097,8 +2096,8 @@ ON t3.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.carrier	1	
-1	SIMPLE	t4	ref	carrier_id	carrier_id	5	test.t3.id	2	Using index
+1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	index	carrier_id	carrier_id	5	NULL	2	Using where; Using index; Using join buffer (BNL, incremental buffers)
 
 DROP TABLE t1,t2,t3,t4;
 #
@@ -2137,7 +2136,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,20
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2147,7 +2146,7 @@ INSERT INTO t1 VALUES (NULL),(NULL);
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL
@@ -2160,7 +2159,7 @@ INSERT INTO t2 VALUES (100,NULL),(150,"v
 explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ref	b	b	103	test.t1.b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
 a
 NULL

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/join_nested.result	2011-06-08 13:24:56 +0000
@@ -851,8 +851,8 @@ ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t2	ref	idx_b	idx_b	5	test.t3.b	2	100.00	Using where
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t2	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -862,12 +862,12 @@ LEFT JOIN              
 ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
 a	b	a	b	a	b
 4	2	1	2	3	2
-4	2	1	2	3	2
-4	2	1	2	3	2
-NULL	NULL	2	2	3	2
 4	2	1	2	4	2
+4	2	1	2	3	2
 4	2	1	2	4	2
+4	2	1	2	3	2
 4	2	1	2	4	2
+NULL	NULL	2	2	3	2
 NULL	NULL	2	2	4	2
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -957,13 +957,13 @@ t0.b=t1.b AND          
 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 (BNL, incremental buffers)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t8	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`)))
@@ -1006,13 +1006,13 @@ t0.b=t1.b AND          
 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 (BNL, incremental buffers)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t8	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`)))
@@ -1056,13 +1056,13 @@ t0.b=t1.b AND          
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ref	idx_a	idx_a	5	const	1	100.00	Using where
 1	SIMPLE	t1	ref	idx_b	idx_b	5	test.t0.b	2	100.00	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t8	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`)))
@@ -1101,23 +1101,23 @@ t0.b=t1.b AND          
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
-1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
-1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
-1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
-1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
+1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
-1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
+1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	1
+1	2	2	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 SELECT t2.a,t2.b
 FROM t2;
 a	b
@@ -1729,11 +1729,11 @@ LEFT JOIN 
 (t5 JOIN t4 ON t5.carrier_id = t4.id)
 ON t4.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	package_id	package_id	5	NULL	45	Using where; Using index
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t2	ref	package_id	package_id	5	test.t3.package_id	5	Using where; Using index
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)

=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/join_nested_jcl6.result	2011-06-08 13:24:56 +0000
@@ -856,8 +856,8 @@ ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t2	ref	idx_b	idx_b	5	test.t3.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t2	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
@@ -964,7 +964,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
@@ -1013,11 +1013,11 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t8	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`)))
@@ -1063,11 +1063,11 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	ref	idx_b	idx_b	5	test.t0.b	2	100.00	Using join buffer (BKA, incremental buffers)
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t4	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t7	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t8	ALL	idx_b	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (BNL, incremental buffers)
 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`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) 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`)))
@@ -1734,11 +1734,11 @@ LEFT JOIN 
 (t5 JOIN t4 ON t5.carrier_id = t4.id)
 ON t4.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	package_id	package_id	5	NULL	45	Using where; Using index
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t2	ref	package_id	package_id	5	test.t3.package_id	5	Using where; Using index
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)
@@ -1771,9 +1771,9 @@ ON t6.b >= 2 AND t5.b=t7.b AND
 (t8.a > 0 OR t8.c IS NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	
-1	SIMPLE	t7	ref	b_i	b_i	5	test.t5.b	2	Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t7	ALL	b_i	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t6	ALL	b_i	NULL	NULL	NULL	3	Using where; Using join buffer (BNL, incremental buffers)
-1	SIMPLE	t8	ref	b_i	b_i	5	test.t7.b	2	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t8	ALL	b_i	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
 LEFT JOIN 
@@ -1825,7 +1825,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t7	ref	b_i	b_i	5	const	0	Using join buffer (BKA, incremental buffers)
-1	SIMPLE	t8	ALL	b_i	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t8	ref	b_i	b_i	5	test.t5.b	2	Using join buffer (BKA, incremental buffers)
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 LEFT JOIN                
 (t6 LEFT JOIN t7 ON t7.b=2, t8)

=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2011-02-02 08:15:16 +0000
+++ b/mysql-test/r/join_outer.result	2011-06-08 13:24:56 +0000
@@ -39,11 +39,11 @@ NULL	NULL	NULL	4	7	D	7
 select t1.*,t2.* from t1 left join t2 using (a);
 grp	a	c	id	a	c	d
 1	1	a	1	1	a	1
-2	2	b	NULL	NULL	NULL	NULL
-2	3	c	NULL	NULL	NULL	NULL
 3	4	E	3	4	A	4
 3	5	C	3	5	B	5
 3	6	D	3	6	C	6
+2	2	b	NULL	NULL	NULL	NULL
+2	3	c	NULL	NULL	NULL	NULL
 NULL	NULL		NULL	NULL	NULL	NULL
 select t1.*,t2.* from t1 left join t2 using (a) where t1.a=t2.a;
 grp	a	c	id	a	c	d
@@ -95,7 +95,7 @@ id	select_type	table	type	possible_keys	
 explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 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	eq_ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using where
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 grp	a	c	id	a	c	d	a
 1	1	a	1	1	a	1	1
@@ -1023,40 +1023,40 @@ a	b	a	b
 7	8	7	5
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b;
 a	b	a	b
-2	1	NULL	NULL
 3	2	3	0
 4	3	4	1
 6	5	6	4
+2	1	NULL	NULL
 8	7	NULL	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b;
 a	b	a	b
-2	1	NULL	NULL
 3	2	3	0
 4	3	4	1
 6	5	6	4
+2	1	NULL	NULL
 8	7	NULL	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b);
 a	b	a	b
-2	1	NULL	NULL
 3	2	3	0
 4	3	4	1
 6	5	6	4
+2	1	NULL	NULL
 8	7	NULL	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b;
 a	b	a	b
-2	1	NULL	NULL
 3	2	3	0
 4	3	4	1
 6	5	6	4
 7	8	7	5
+2	1	NULL	NULL
 8	7	NULL	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b);
 a	b	a	b
-2	1	NULL	NULL
 3	2	3	0
 4	3	4	1
 6	5	6	4
 7	8	7	5
+2	1	NULL	NULL
 8	7	NULL	NULL
 SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b);
 a	b	a	b
@@ -1228,7 +1228,7 @@ EXPLAIN
 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	idx	idx	4	test.t1.id	2	Using where; Not exists
+1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	9	Using where; Not exists; Using join buffer (BNL, incremental buffers)
 flush status;
 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 id	a
@@ -1237,12 +1237,12 @@ id	a
 show status like 'Handler_read%';
 Variable_name	Value
 Handler_read_first	0
-Handler_read_key	5
+Handler_read_key	0
 Handler_read_last	0
 Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
-Handler_read_rnd_next	6
+Handler_read_rnd_next	16
 DROP TABLE t1,t2;
 CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
 INSERT INTO t1 VALUES (1,0), (2,1);

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2011-06-08 13:24:56 +0000
@@ -100,7 +100,7 @@ id	select_type	table	type	possible_keys	
 explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1;
 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	eq_ref	PRIMARY	PRIMARY	8	test.t1.a	1	Using where; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 select t1.*,t2.*,t3.a from t1 left join t2 on (t1.a=t2.a) left join t1 as t3 on (t2.a=t3.a);
 grp	a	c	id	a	c	d	a
 1	1	a	1	1	a	1	1
@@ -1233,7 +1233,7 @@ EXPLAIN
 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	idx	idx	4	test.t1.id	2	Using where; Not exists; Using join buffer (BKA, incremental buffers)
+1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	9	Using where; Not exists; Using join buffer (BNL, incremental buffers)
 flush status;
 SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
 id	a
@@ -1242,12 +1242,12 @@ id	a
 show status like 'Handler_read%';
 Variable_name	Value
 Handler_read_first	0
-Handler_read_key	5
+Handler_read_key	0
 Handler_read_last	0
-Handler_read_next	9
+Handler_read_next	0
 Handler_read_prev	0
-Handler_read_rnd	3
-Handler_read_rnd_next	6
+Handler_read_rnd	0
+Handler_read_rnd_next	16
 DROP TABLE t1,t2;
 CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
 INSERT INTO t1 VALUES (1,0), (2,1);

=== modified file 'mysql-test/r/null_key_all.result'
--- a/mysql-test/r/null_key_all.result	2010-11-02 18:47:47 +0000
+++ b/mysql-test/r/null_key_all.result	2011-06-08 13:24:56 +0000
@@ -408,15 +408,15 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FRO
 LEFT JOIN t3 ON t2.b=t3.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	1	
+1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ref	idx	idx	5	test.t2.b	1	Using index
 FLUSH STATUS ;
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
 LEFT JOIN t3 ON t2.b=t3.b;
 a	a	b	b
 1	1	1	NULL
-2	NULL	NULL	NULL
 3	3	1	NULL
+2	NULL	NULL	NULL
 4	NULL	NULL	NULL
 SELECT FOUND_ROWS();
 FOUND_ROWS()
@@ -424,12 +424,12 @@ FOUND_ROWS()
 SHOW STATUS LIKE "handler_read%";
 Variable_name	Value
 Handler_read_first	0
-Handler_read_key	6
+Handler_read_key	2
 Handler_read_last	0
-Handler_read_next	2
+Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
-Handler_read_rnd_next	5
+Handler_read_rnd_next	8
 DROP TABLE t1,t2,t3,t4;
 CREATE TABLE t1 (
 a int(11) default NULL,

=== modified file 'mysql-test/r/null_key_icp.result'
--- a/mysql-test/r/null_key_icp.result	2010-11-02 18:47:47 +0000
+++ b/mysql-test/r/null_key_icp.result	2011-06-08 13:24:56 +0000
@@ -408,15 +408,15 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FRO
 LEFT JOIN t3 ON t2.b=t3.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	1	
+1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ref	idx	idx	5	test.t2.b	1	Using index
 FLUSH STATUS ;
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
 LEFT JOIN t3 ON t2.b=t3.b;
 a	a	b	b
 1	1	1	NULL
-2	NULL	NULL	NULL
 3	3	1	NULL
+2	NULL	NULL	NULL
 4	NULL	NULL	NULL
 SELECT FOUND_ROWS();
 FOUND_ROWS()
@@ -424,12 +424,12 @@ FOUND_ROWS()
 SHOW STATUS LIKE "handler_read%";
 Variable_name	Value
 Handler_read_first	0
-Handler_read_key	6
+Handler_read_key	2
 Handler_read_last	0
-Handler_read_next	2
+Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
-Handler_read_rnd_next	5
+Handler_read_rnd_next	8
 DROP TABLE t1,t2,t3,t4;
 CREATE TABLE t1 (
 a int(11) default NULL,

=== modified file 'mysql-test/r/null_key_none.result'
--- a/mysql-test/r/null_key_none.result	2010-08-27 06:36:54 +0000
+++ b/mysql-test/r/null_key_none.result	2011-06-08 13:24:56 +0000
@@ -407,15 +407,15 @@ EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FRO
 LEFT JOIN t3 ON t2.b=t3.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
-1	SIMPLE	t2	ref	idx	idx	5	test.t1.a	1	
+1	SIMPLE	t2	ALL	idx	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	ref	idx	idx	5	test.t2.b	1	Using index
 FLUSH STATUS ;
 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
 LEFT JOIN t3 ON t2.b=t3.b;
 a	a	b	b
 1	1	1	NULL
-2	NULL	NULL	NULL
 3	3	1	NULL
+2	NULL	NULL	NULL
 4	NULL	NULL	NULL
 SELECT FOUND_ROWS();
 FOUND_ROWS()
@@ -423,12 +423,12 @@ FOUND_ROWS()
 SHOW STATUS LIKE "handler_read%";
 Variable_name	Value
 Handler_read_first	0
-Handler_read_key	6
+Handler_read_key	2
 Handler_read_last	0
-Handler_read_next	2
+Handler_read_next	0
 Handler_read_prev	0
 Handler_read_rnd	0
-Handler_read_rnd_next	5
+Handler_read_rnd_next	8
 DROP TABLE t1,t2,t3,t4;
 CREATE TABLE t1 (
 a int(11) default NULL,

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2011-05-13 12:41:14 +0000
+++ b/mysql-test/r/order_by_all.result	2011-06-08 13:24:56 +0000
@@ -185,8 +185,8 @@ LEFT JOIN t2 USING(c)
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
@@ -194,8 +194,8 @@ LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2011-04-05 13:28:34 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2011-06-08 13:24:56 +0000
@@ -185,8 +185,8 @@ LEFT JOIN t2 USING(c)
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
@@ -194,8 +194,8 @@ LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1

=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result	2011-04-05 13:28:34 +0000
+++ b/mysql-test/r/order_by_none.result	2011-06-08 13:24:56 +0000
@@ -184,8 +184,8 @@ LEFT JOIN t2 USING(c)
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1
@@ -193,8 +193,8 @@ LEFT JOIN t2 ON t1.c = t2.c
 LEFT JOIN t3 ON t3.c = t1.c;
 a	b	if(b = 1,i,if(b = 2,v,''))
 1	1	50
-2	1	25
 3	2	123 Park Place
+2	1	25
 4	2	453 Boardwalk
 SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
 FROM      t1

=== modified file 'mysql-test/r/select_all.result'
--- a/mysql-test/r/select_all.result	2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_all.result	2011-06-08 13:24:56 +0000
@@ -2398,7 +2398,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3859,17 +3859,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3933,17 +3933,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/select_all_jcl6.result'
--- a/mysql-test/r/select_all_jcl6.result	2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_all_jcl6.result	2011-06-08 13:24:56 +0000
@@ -2402,7 +2402,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3863,17 +3863,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3937,17 +3937,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/select_icp_mrr.result'
--- a/mysql-test/r/select_icp_mrr.result	2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_icp_mrr.result	2011-06-08 13:24:56 +0000
@@ -2398,7 +2398,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3859,17 +3859,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3933,17 +3933,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/select_icp_mrr_jcl6.result'
--- a/mysql-test/r/select_icp_mrr_jcl6.result	2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_icp_mrr_jcl6.result	2011-06-08 13:24:56 +0000
@@ -2402,7 +2402,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3863,17 +3863,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3937,17 +3937,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/select_none.result'
--- a/mysql-test/r/select_none.result	2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/select_none.result	2011-06-08 13:24:56 +0000
@@ -2397,7 +2397,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3858,17 +3858,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3932,17 +3932,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/select_none_jcl6.result'
--- a/mysql-test/r/select_none_jcl6.result	2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/select_none_jcl6.result	2011-06-08 13:24:56 +0000
@@ -2401,7 +2401,7 @@ INSERT INTO t2 VALUES ('one'),('two'),('
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USE INDEX (a) 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	ref	a	a	23	test.t1.a	2	Using where
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 FORCE INDEX (a) 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	
@@ -3862,17 +3862,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (name text);
 CREATE TABLE t2 (name text, n int, KEY (name(3)));
@@ -3936,17 +3936,17 @@ cc 	3	7
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	SIMPLE	t2	ref	name	name	6	test.t1.name	2	Using where
+1	SIMPLE	t2	ALL	name	NULL	NULL	NULL	7	Using where; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 name	name	n
-ccc	NULL	NULL
 bb	bb	1
-bb	bb 	6
+aa  	aa	2
+aa	aa	2
 cc 	cc   	3
 cc 	cc	5
+bb	bb 	6
 cc 	cc 	7
-aa  	aa	2
-aa	aa	2
+ccc	NULL	NULL
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 access_id int NOT NULL default '0',

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-06-08 13:24:56 +0000
@@ -1688,7 +1688,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1719,8 +1719,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1835,7 +1835,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1893,9 +1893,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1904,9 +1904,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1926,9 +1926,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1944,9 +1944,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -5163,8 +5163,8 @@ insert into t3 values(2),(2);
 explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 a
 1
@@ -5183,10 +5183,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5405,9 +5405,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-08 13:24:56 +0000
@@ -1692,7 +1692,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1723,8 +1723,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1839,7 +1839,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1897,9 +1897,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1908,9 +1908,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1930,9 +1930,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1948,9 +1948,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -5167,8 +5167,8 @@ insert into t3 values(2),(2);
 explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 a
 1
@@ -5187,10 +5187,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5409,9 +5409,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-08 13:24:56 +0000
@@ -1692,7 +1692,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1723,8 +1723,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1839,7 +1839,7 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1897,9 +1897,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1908,9 +1908,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1930,9 +1930,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1948,9 +1948,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -5167,8 +5167,8 @@ insert into t3 values(2),(2);
 explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
 a
 1

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-06-08 13:24:56 +0000
@@ -1719,8 +1719,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -5183,10 +5183,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5405,9 +5405,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
@@ -5451,9 +5451,9 @@ FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 SELECT *
 FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
@@ -5467,9 +5467,9 @@ FROM t2 AS nt2, t8 AS nt4
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 1	PRIMARY	nt4	ALL	NULL	NULL	NULL	NULL	8	
 SELECT *
 FROM t2 AS nt2, t8 AS nt4
@@ -5499,9 +5499,9 @@ WHERE ot1.a IN (SELECT it2.a
 FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(ot1)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(ot1)
 1	PRIMARY	nt3	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(nt3)
 SELECT *
 FROM t0 as ot1, t2 AS nt3
 WHERE ot1.a IN (SELECT it2.a

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-08 13:24:56 +0000
@@ -1723,8 +1723,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -5187,10 +5187,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5409,9 +5409,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
@@ -5455,9 +5455,9 @@ FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 SELECT *
 FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
@@ -5471,9 +5471,9 @@ FROM t2 AS nt2, t8 AS nt4
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 1	PRIMARY	nt4	ALL	NULL	NULL	NULL	NULL	8	
 SELECT *
 FROM t2 AS nt2, t8 AS nt4
@@ -5503,9 +5503,9 @@ WHERE ot1.a IN (SELECT it2.a
 FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(ot1)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(ot1)
 1	PRIMARY	nt3	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(nt3)
 SELECT *
 FROM t0 as ot1, t2 AS nt3
 WHERE ot1.a IN (SELECT it2.a

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-08 13:24:56 +0000
@@ -1723,8 +1723,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1); Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -5187,10 +5187,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5455,9 +5455,9 @@ FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 SELECT *
 FROM t2 AS nt2
 WHERE 1 IN (SELECT it1.a
@@ -5471,9 +5471,9 @@ FROM t2 AS nt2, t8 AS nt4
 WHERE 1 IN (SELECT it1.a
 FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch
 1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
 1	PRIMARY	nt4	ALL	NULL	NULL	NULL	NULL	8	
 SELECT *
 FROM t2 AS nt2, t8 AS nt4
@@ -5503,9 +5503,9 @@ WHERE ot1.a IN (SELECT it2.a
 FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(ot1)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(ot1)
 1	PRIMARY	nt3	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(nt3)
 SELECT *
 FROM t0 as ot1, t2 AS nt3
 WHERE ot1.a IN (SELECT it2.a

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-06-08 13:24:56 +0000
@@ -1893,9 +1893,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1904,9 +1904,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1926,9 +1926,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1944,9 +1944,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -5183,10 +5183,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5405,9 +5405,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-08 13:24:56 +0000
@@ -1897,9 +1897,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1908,9 +1908,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1930,9 +1930,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1948,9 +1948,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -5187,10 +5187,10 @@ explain SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1
 WHERE t1.a IN (SELECT t2.a
 FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
@@ -5409,9 +5409,9 @@ WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t2
 WHERE f2 IN (SELECT t1.f1
 FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2011-03-17 11:23:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-08 13:24:56 +0000
@@ -1897,9 +1897,9 @@ SELECT (SELECT COUNT(*) from t1),
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1908,9 +1908,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -1930,9 +1930,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1948,9 +1948,9 @@ i
 EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i

=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2011-05-26 09:22:35 +0000
+++ b/mysql-test/r/subselect_innodb.result	2011-06-08 13:24:56 +0000
@@ -277,8 +277,8 @@ c373e9f5ad07993f3859444553544200	Last Di
 EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	DDOCTYPEID_IDX	NULL	NULL	NULL	9	100.00	Using where
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCID	1	100.00	
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	34	test.t2.DOCTYPEID	1	100.00	
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (BNL, incremental buffers)
 2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	func	1	100.00	Using where
 2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where
 2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	100.00	Using where

=== added file 'mysql-test/t/join_cache_jcl0.test'
--- a/mysql-test/t/join_cache_jcl0.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/join_cache_jcl0.test	2011-06-08 13:24:56 +0000
@@ -0,0 +1,14 @@
+let $requires_join_cache_level = 0;
+--source include/have_join_cache_level.inc
+
+--eval set optimizer_join_cache_level = $requires_join_cache_level;
+
+if (`select locate('mrr_cost_based', @@optimizer_switch) > 0`) 
+{
+  set optimizer_switch='mrr_cost_based=off';
+}
+
+--source include/join_cache.inc
+
+set optimizer_join_cache_level = default;
+set optimizer_switch = default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-05-26 06:03:02 +0000
+++ b/sql/sql_select.cc	2011-06-08 13:24:56 +0000
@@ -339,6 +339,7 @@ private:
                            const JOIN_TAB *tab);
   void optimize_straight_join(table_map join_tables);
   bool greedy_search(table_map remaining_tables);
+  void set_join_buffer_properties();
   bool best_extension_by_limited_search(table_map remaining_tables,
                                         uint idx,
                                         double record_count,
@@ -1621,11 +1622,10 @@ bool setup_semijoin_dups_elimination(JOI
         {
           /*
             The final decision on whether or not join buffering will
-            be used is taken in check_join_cache_usage(), which is
+            be used is taken in setup_join_buffering(), which is
             called from make_join_readinfo()'s main loop.
-            check_join_cache_usage() needs to know if duplicate
-            weedout is used, so moving
-            setup_semijoin_dups_elimination() from before the main
+            setup_join_buffering() needs to know if duplicate weedout is used,
+            so moving setup_semijoin_dups_elimination() from before the main
             loop to after it is not possible. I.e.,
             join->best_positions[sj_tableno].use_join_buffer is not
             trustworthy at this point.
@@ -7083,6 +7083,14 @@ best_access_path(JOIN      *join,
   table_map best_ref_depends_map= 0;
   double tmp;
   bool best_uses_jbuf= false;
+  /*
+    Cannot use join buffering if either
+     1. This is the first table in the join sequence, or
+     2. Join buffering impossible for this table
+  */
+  disable_jbuf= disable_jbuf ||
+      idx == join->const_tables ||                                     // 1
+      s->use_join_cache == JOIN_CACHE::ALG_NONE;                       // 2
 
   Loose_scan_opt loose_scan_opt;
   DBUG_ENTER("best_access_path");
@@ -7553,7 +7561,7 @@ best_access_path(JOIN      *join,
       else
         tmp= s->table->file->scan_time();
 
-      if ((s->table->map & join->outer_join) || disable_jbuf)     // Can't use join cache
+      if (disable_jbuf)
       {
         /*
           For each record we have to:
@@ -7602,8 +7610,7 @@ best_access_path(JOIN      *join,
       best_key= 0;
       /* range/index_merge/ALL/index access method are "independent", so: */
       best_ref_depends_map= 0;
-      best_uses_jbuf= test(!disable_jbuf && !((s->table->map & 
-                                               join->outer_join)));
+      best_uses_jbuf= test(!disable_jbuf);
     }
   }
   
@@ -7661,6 +7668,8 @@ bool Optimize_table_order::choose_table_
   reset_nj_counters(join->join_list);
   qsort2_cmp jtab_sort_func;
 
+  set_join_buffer_properties();
+
   const bool straight_join= test(join->select_options & SELECT_STRAIGHT_JOIN);
   table_map join_tables;      ///< The tables involved in order selection
 
@@ -7714,6 +7723,48 @@ bool Optimize_table_order::choose_table_
 
 
 /**
+  Set static join buffer properties for the tables involved in a join,
+  regardless of access methods and join order.
+  The property is pre-computed here and later used in best_access_path().
+*/
+
+void Optimize_table_order::set_join_buffer_properties()
+{
+  const bool force_unlinked_cache=
+    test(thd->variables.optimizer_join_cache_level & 1);
+
+  for (uint tableno= 0; tableno < join->tables; tableno++)
+  {
+    JOIN_TAB   *const tab= join->join_tab + tableno;
+    TABLE_LIST *const tl=  tab->table->pos_in_table_list;
+    /*
+      Cannot use join buffering if either
+       1. Join buffering is disabled by the user, or
+       2. Outer joined table, and join buffering disabled for outer join, or
+       3. Semi-joined table, and join buffering disabled for semi-join, or
+       4. Unlinked cache is forced and there are multiple inner tables of
+          this outer join.
+    */
+    if (thd->variables.optimizer_join_cache_level == 0 ||              // 1
+        ((tab->table->map & join->outer_join) &&                       // 2
+         thd->variables.optimizer_join_cache_level <= 2) ||            // 2
+        (tab->emb_sj_nest != NULL &&                                   // 3
+         thd->variables.optimizer_join_cache_level <= 2) ||            // 3
+        (force_unlinked_cache &&                                       // 4
+         tl->in_outer_join_nest() &&                                   // 4
+         tl->embedding->nested_join->join_list.elements > 1))          // 4
+      tab->use_join_cache= JOIN_CACHE::ALG_NONE;
+    else if (thd->variables.optimizer_join_cache_level <= 4)
+      tab->use_join_cache= JOIN_CACHE::ALG_BNL;
+    else if (thd->variables.optimizer_join_cache_level <= 6)
+      tab->use_join_cache= JOIN_CACHE::ALG_BNL | JOIN_CACHE::ALG_BKA;
+    else if (thd->variables.optimizer_join_cache_level <= 8)
+      tab->use_join_cache= JOIN_CACHE::ALG_BNL | JOIN_CACHE::ALG_BKA_UNIQUE;
+  }
+}
+
+
+/**
   Compare two JOIN_TAB objects based on the number of accessed records.
 
   @param ptr1 pointer to first JOIN_TAB object
@@ -8357,14 +8408,14 @@ bool Optimize_table_order::best_extensio
   const bool has_sj=
     !(join->select_lex->sj_nests.is_empty() || join->emb_sjm_nest);
 
-  JOIN_TAB *s;
   JOIN_TAB *saved_refs[MAX_TABLES];
   // Save 'best_ref[]' as we has to restore before return.
   memcpy(saved_refs, join->best_ref + idx, 
          sizeof(JOIN_TAB*) * (join->tables - idx));
 
-  for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
+  for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)
   {
+    JOIN_TAB *const s= *pos;
     const table_map real_table_bit= s->table->map;
 
     /*
@@ -8959,6 +9010,13 @@ static bool get_best_combination(JOIN *j
     if (j->type == JT_CONST)
       continue;					// Handled in make_join_stat..
 
+    /*
+      Assign preliminary join cache setting based on decision from
+      greedy optimizer.
+    */
+    j->use_join_cache= join->best_positions[tableno].use_join_buffer ?
+                         JOIN_CACHE::ALG_BNL : JOIN_CACHE::ALG_NONE;
+
     j->loosescan_match_tab= NULL;  //non-nulls will be set later
     j->ref.key = -1;
     j->ref.key_parts=0;
@@ -9006,7 +9064,7 @@ static bool get_best_combination(JOIN *j
     case SJ_OPT_FIRST_MATCH:
       /*
         Remember the first and last semijoin inner tables; this serves to tell
-        a JOIN_TAB's semijoin strategy (like in check_join_cache_usage()).
+        a JOIN_TAB's semijoin strategy (like in setup_join_buffering()).
       */
       JOIN_TAB *last_sj_tab= tab + pos->n_sj_tables - 1;
       JOIN_TAB *last_sj_inner=
@@ -10416,7 +10474,7 @@ static void push_index_cond(JOIN_TAB *ta
       if (tab->use_join_cache &&
           /*
             if cache is used then the value is TRUE only 
-            for BKA[_UNIQUE] cache (see check_join_cache_usage func).
+            for BKA[_UNIQUE] cache (see setup_join_buffering() func).
             In this case other_tbls_ok is an equivalent of
             cache->is_key_access().
           */
@@ -10661,7 +10719,7 @@ void revise_cache_usage(JOIN_TAB *join_t
 
 
 /**
-  Check whether a join buffer can be used to join the specified table   
+  Set up join buffering for a specified table, if possible.
 
   @param tab             joined table to check join buffer usage for
   @param join            join for which the check is performed
@@ -10670,12 +10728,29 @@ void revise_cache_usage(JOIN_TAB *join_t
   @param icp_other_tables_ok[out] TRUE if condition pushdown supports
                                   other tables presence
 
+  @return false if successful, true if error.
+          Currently, allocation errors for join cache objects are ignored,
+          and regular execution is chosen silently.
+
   @details
     The function finds out whether the table 'tab' can be joined using a join
     buffer. This check is performed after the best execution plan for 'join'
     has been chosen. 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.
+    If it has already been decided to not use join buffering for this table,
+    no action is taken.
+
+    Often it is already decided that join buffering will be used earlier in
+    the optimization process, and this will also ensure that the most correct
+    cost for the operation is calculated, and hence the probability of
+    choosing an optimal join plan is higher. However, some join buffering
+    decisions cannot currently be taken before this stage, hence we need this
+    function to decide the most accurate join buffering strategy.
+
+    @todo Long-term it is the goal that join buffering strategy is decided
+    when the plan is selected.
+
     The result of the check and the type of the the join buffer to be used
     depend on:
       - the access method to access rows of the joined table
@@ -10705,6 +10780,12 @@ void revise_cache_usage(JOIN_TAB *join_t
     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.
+
+    Bitmap describing the chosen cache's properties is assigned to
+    tab->use_join_cache:
+    1) the algorithm (JOIN_CACHE::ALG_NONE, JOIN_CACHE::ALG_BNL,
+    JOIN_CACHE::ALG_BKA, JOIN_CACHE::ALG_BKA_UNIQUE)
+    2) the buffer's type (JOIN_CACHE::NON_INCREMENTAL_BUFFER or not).
  
   @note
     An inner table of a nested outer join or a nested semi-join can be currently
@@ -10742,34 +10823,29 @@ void revise_cache_usage(JOIN_TAB *join_t
         }
 #endif
 
-  @return
-    Bitmap describing the chosen cache's properties:
-    1) the algorithm (JOIN_CACHE::ALG_NONE, JOIN_CACHE::ALG_BNL,
-    JOIN_CACHE::ALG_BKA, JOIN_CACHE::ALG_BKA_UNIQUE)
-    2) the buffer's type (JOIN_CACHE::NON_INCREMENTAL_BUFFER or not).
 */
 
-static
-uint check_join_cache_usage(JOIN_TAB *tab,
-                            JOIN *join, ulonglong options,
-                            uint no_jbuf_after,
-                            bool *icp_other_tables_ok)
+static bool setup_join_buffering(JOIN_TAB *tab, JOIN *join,
+                                 ulonglong options, uint no_jbuf_after,
+                                 bool *icp_other_tables_ok)
 {
   uint flags;
   COST_VECT cost;
   ha_rows rows;
   uint bufsz= 4096;
   JOIN_CACHE *prev_cache=0;
-  uint cache_level= join->thd->variables.optimizer_join_cache_level;
-  uint force_unlinked_cache= (cache_level & 1) ?
+  const uint cache_level= join->thd->variables.optimizer_join_cache_level;
+  const uint force_unlinked_cache= (cache_level & 1) ?
     JOIN_CACHE::NON_INCREMENTAL_BUFFER : 0;
-  uint i= tab-join->join_tab;
+  const uint tableno= tab - join->join_tab;
   const uint tab_sj_strategy= tab->get_sj_strategy();
   *icp_other_tables_ok= TRUE;
-  
-  if (cache_level == 0 || i == join->const_tables)
-    return JOIN_CACHE::ALG_NONE;
 
+  if (cache_level == 0 || tableno == join->const_tables)
+  {
+    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
+    return false;
+  }
   if (options & SELECT_NO_JOIN_CACHE)
     goto no_join_cache;
   /* 
@@ -10778,80 +10854,81 @@ uint check_join_cache_usage(JOIN_TAB *ta
   */
   if (tab->use_quick == QS_DYNAMIC_RANGE)
     goto no_join_cache;
-  
+
+  /* No join buffering if prevented by no_jbuf_after */
+  if (tableno > no_jbuf_after)
+  {
+    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
+    goto no_join_cache;
+  }
+  /* Non-linked join buffers can't guarantee one match */
+  if (force_unlinked_cache &&
+      tab->is_inner_table_of_outer_join() &&
+      !tab->is_single_inner_of_outer_join())
+  {
+    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
+    goto no_join_cache;
+  }
+  /*
+    An inner table of an outer join nest must not use join buffering if
+    the first inner table of that outer join nest does not use join buffering.
+    This condition is not handled by earlier optimizer stages.
+  */
+  if (tab->first_inner != NULL &&
+      tab->first_inner != tab &&
+      !tab->first_inner->use_join_cache)
+    goto no_join_cache;
+  /*
+    The first inner table of an outer join nest must not use join buffering
+    if the tables in the embedding outer join nest do not use join buffering.
+    This condition is not handled by earlier optimizer stages.
+  */
+  if (tab->first_upper != NULL &&
+      !tab->first_upper->use_join_cache)
+    goto no_join_cache;
   /*
     Use join cache with FirstMatch semi-join strategy only when semi-join
     contains only one table.
   */
   if (tab_sj_strategy == SJ_OPT_FIRST_MATCH &&
       !tab->is_single_inner_of_semi_join())
+  {
+    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
     goto no_join_cache;
-  /*
-    Non-linked join buffers can't guarantee one match
-  */
-  if (force_unlinked_cache &&
-      tab->is_inner_table_of_outer_join() &&
-      !tab->is_single_inner_of_outer_join())
-    goto no_join_cache;
-
-  /* No join buffering if prevented by no_jbuf_after */
-  if (i > no_jbuf_after)
-    goto no_join_cache;
-
+  }
   /* No join buffering if this semijoin nest is handled by loosescan */
   if (tab_sj_strategy == SJ_OPT_LOOSE_SCAN)
+  {
+    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
     goto no_join_cache;
-      
-  /* Neither if semijoin Materialization */
+  }      
+  /*
+    No join buffering if this semijoin nest is handled by materialization.
+    This condition is not handled by earlier optimizer stages.
+  */
   if (sj_is_materialize_strategy(tab_sj_strategy))
     goto no_join_cache;
 
-  for (JOIN_TAB *first_inner= tab->first_inner; first_inner;
-       first_inner= first_inner->first_upper)
-  {
-    if (first_inner != tab && !first_inner->use_join_cache)
-      goto no_join_cache;
-  }
-  if (tab_sj_strategy == SJ_OPT_FIRST_MATCH &&
-      tab->first_sj_inner_tab != tab &&
-      !tab->first_sj_inner_tab->use_join_cache)
-    goto no_join_cache;
-  if (!tab[-1].use_join_cache)
-  {
-    /* 
-      Check whether table tab and the previous one belong to the same nest of
-      inner tables and if so do not use join buffer when joining table tab. 
-    */
-    if (tab->first_inner)
-    {
-      for (JOIN_TAB *first_inner= tab[-1].first_inner;
-           first_inner;
-           first_inner= first_inner->first_upper)
-      {
-        if (first_inner == tab->first_inner)
-          goto no_join_cache;
-      }
-    }
-    else if (tab_sj_strategy == SJ_OPT_FIRST_MATCH &&
-             tab->first_sj_inner_tab == tab[-1].first_sj_inner_tab)
-      goto no_join_cache; 
-  }       
-
   if (!force_unlinked_cache)
     prev_cache= tab[-1].cache;
 
   switch (tab->type) {
   case JT_ALL:
+    /* Outer joined and semi-joined tables require join cache level > 2 */
     if (cache_level <= 2 &&
         (tab->first_inner || tab_sj_strategy == SJ_OPT_FIRST_MATCH))
+    {
+      DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
       goto no_join_cache;
+    }
     if ((options & SELECT_DESCRIBE) ||
         ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache)) &&
          !tab->cache->init()))
     {
       *icp_other_tables_ok= FALSE;
       DBUG_ASSERT(might_do_join_buffering(cache_level, tab));
-      return JOIN_CACHE::ALG_BNL | force_unlinked_cache;
+      tab->use_join_cache= JOIN_CACHE::ALG_BNL | force_unlinked_cache;
+      return false;
     }
     goto no_join_cache;
   case JT_SYSTEM:
@@ -10859,7 +10936,10 @@ uint check_join_cache_usage(JOIN_TAB *ta
   case JT_REF:
   case JT_EQ_REF:
     if (cache_level <= 4)
+    {
+      DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
       goto no_join_cache;
+    }
     flags= HA_MRR_NO_NULL_ENDPOINTS;
     if (tab->table->covering_keys.is_set(tab->ref.key))
       flags|= HA_MRR_INDEX_ONLY;
@@ -10876,17 +10956,20 @@ uint check_join_cache_usage(JOIN_TAB *ta
     {
       DBUG_ASSERT(might_do_join_buffering(cache_level, tab));
       if (cache_level <= 6)
-        return JOIN_CACHE::ALG_BKA | force_unlinked_cache;
-      return JOIN_CACHE::ALG_BKA_UNIQUE | force_unlinked_cache;
+        tab->use_join_cache= JOIN_CACHE::ALG_BKA | force_unlinked_cache;
+      else
+        tab->use_join_cache= JOIN_CACHE::ALG_BKA_UNIQUE | force_unlinked_cache;
+      return false;
     }
     goto no_join_cache;
   default : ;
   }
 
 no_join_cache:
-  if (cache_level>2)
-    revise_cache_usage(tab); 
-  return JOIN_CACHE::ALG_NONE;
+  if (cache_level > 2)
+    revise_cache_usage(tab);
+  tab->use_join_cache= JOIN_CACHE::ALG_NONE;
+  return false;
 }
 
 /*
@@ -11280,12 +11363,10 @@ make_join_readinfo(JOIN *join, ulonglong
     JOIN_TAB *const tab= join->join_tab+i;
     TABLE    *const table= tab->table;
     bool icp_other_tables_ok;
-    uint jcl;
     tab->read_record.table= table;
     tab->read_record.file=table->file;
     tab->read_record.unlock_row= rr_unlock_row;
     tab->next_select=sub_select;		/* normal select */
-    tab->use_join_cache= JOIN_CACHE::ALG_NONE;
     tab->cache_idx_cond= 0;
     tab->sorted= sorted;
     sorted= false;                              // only first must be sorted
@@ -11325,12 +11406,12 @@ make_join_readinfo(JOIN *join, ulonglong
     case JT_SYSTEM: 
     case JT_CONST:
       /* Only happens with outer joins */
-      if ((jcl= check_join_cache_usage(tab, join, options, no_jbuf_after,
-                                       &icp_other_tables_ok)))
-      {
-        tab->use_join_cache= jcl;
+      if (setup_join_buffering(tab, join, options, no_jbuf_after,
+                               &icp_other_tables_ok))
+        DBUG_RETURN(true);
+      if (tab->use_join_cache != JOIN_CACHE::ALG_NONE)
         tab[-1].next_select= sub_select_cache;
-      }
+
       if (table->covering_keys.is_set(tab->ref.key) &&
 	  !table->no_keyread)
         table->set_keyread(TRUE);
@@ -11338,12 +11419,12 @@ make_join_readinfo(JOIN *join, ulonglong
         push_index_cond(tab, tab->ref.key, icp_other_tables_ok);
       break;
     case JT_ALL:
-      if ((jcl= check_join_cache_usage(tab, join, options, no_jbuf_after,
-                                       &icp_other_tables_ok)))
-      {
-        tab->use_join_cache= jcl;
+      if (setup_join_buffering(tab, join, options, no_jbuf_after,
+                               &icp_other_tables_ok))
+        DBUG_RETURN(true);
+      if (tab->use_join_cache != JOIN_CACHE::ALG_NONE)
         tab[-1].next_select=sub_select_cache;
-      }
+
       /* These init changes read_record */
       if (tab->use_quick == QS_DYNAMIC_RANGE)
       {
@@ -11434,7 +11515,7 @@ make_join_readinfo(JOIN *join, ulonglong
   for (uint i= join->const_tables; i < join->tables; i++)
   {
     JOIN_TAB *const tab=join->join_tab + i;
-    if (tab->use_join_cache)
+    if (tab->use_join_cache != JOIN_CACHE::ALG_NONE)
     {
       JOIN_TAB *const sort_by_tab= join->get_sort_by_join_tab();
       if (sort_by_tab)

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-05-26 06:03:02 +0000
+++ b/sql/sql_select.h	2011-06-08 13:24:56 +0000
@@ -361,7 +361,11 @@ public:
   */ 
   ha_rows       limit; 
   TABLE_REF	ref;
-  /** Join cache type (same as return code of check_join_cache_level() */
+  /**
+    Join buffering strategy (same as return code of check_join_cache_level().
+    During optimization, this contains allowed join buffering strategies,
+    after optimization it contains chosen join buffering strategy (if any).
+   */
   uint          use_join_cache;
   JOIN_CACHE	*cache;
   /*
@@ -556,7 +560,7 @@ st_join_table::st_join_table()
 
     limit(0),
     ref(),
-    use_join_cache(FALSE),
+    use_join_cache(0),
     cache(NULL),
 
     cache_idx_cond(NULL),

Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110608132456-r4djo01lnv937do1.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3379) Bug#12316645Roy Lyseng8 Jun