bzr push into mysql-6.0-bka-preview branch (igor:2661 to 2662) Bug#40268, Bug#40192

[Igor Babaev]

 2662 Igor Babaev	2008-10-23
      Fixed bug #40192.
      When Blocked Nested Loops (BNL) algorithm is used for 
      for a regular join each record of the joined table first
      is checked by the condition pushed to this table. Only
      after this the matches for this record in the join buffer
      are looked for. 
      This does not work for an outer join operation as the 
      condition pushed to an inner table may include predicates
      from the where clause which should be applied only after
      a match has been found.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

 2661 Igor Babaev	2008-10-22
      Fixed bug #40268.
      This bug caused generation of extra rows in the result sets for
      some queries with nested outer joins. It could happen if
      the pushdown condition attached to not the last inner table 
      of the outer join was extracted from the where clause.
      It happened because this pushdown condition erroneously
      was not checked against matching row combinations from inner
      tables that were not the first matches for a given partial
      row generated from outer tables.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2008-10-23 04:53:32 +0000
+++ b/mysql-test/r/join_cache.result	2008-10-23 18:33:52 +0000
@@ -3292,3 +3292,27 @@ a	b	a	b	a	b
 5	3	NULL	NULL	NULL	NULL
 set join_cache_level=default;
 DROP TABLE t2, t3, t4;
+#
+# Bug #40192: outer join with where clause when using BNL 
+#
+create table t1 (a int, b int);
+insert into t1 values (2, 20), (3, 30), (1, 10);
+create table t2 (a int, c int);
+insert into t2 values (1, 101), (3, 102), (1, 100);
+set join_cache_level=6;
+select * from t1 left join t2 on t1.a=t2.a;
+a	b	a	c
+1	10	1	101
+3	30	3	102
+1	10	1	100
+2	20	NULL	NULL
+explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer
+select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+a	b	a	c
+3	30	3	102
+2	20	NULL	NULL
+set join_cache_level=default;
+drop table t1, t2;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2008-10-23 04:53:32 +0000
+++ b/mysql-test/t/join_cache.test	2008-10-23 18:33:52 +0000
@@ -838,3 +838,21 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 
 set join_cache_level=default;
 DROP TABLE t2, t3, t4;
+
+--echo #
+--echo # Bug #40192: outer join with where clause when using BNL 
+--echo #
+
+create table t1 (a int, b int);
+insert into t1 values (2, 20), (3, 30), (1, 10);
+create table t2 (a int, c int);
+insert into t2 values (1, 101), (3, 102), (1, 100);
+
+set join_cache_level=6;
+
+select * from t1 left join t2 on t1.a=t2.a;
+explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null;
+
+set join_cache_level=default;
+drop table t1, t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-23 04:53:32 +0000
+++ b/sql/sql_select.cc	2008-10-23 18:33:52 +0000
@@ -7844,7 +7844,8 @@ make_join_select(JOIN *join,SQL_SELECT *
 	      2 : 1;
 	    sel->read_tables= used_tables & ~current_map;
 	  }
-	  if (i != join->const_tables && tab->use_quick != 2)
+	  if (i != join->const_tables && tab->use_quick != 2 &&
+              !tab->first_inner)
 	  {					/* Read with cache */
 	    if (cond &&
                 (tmp=make_cond_for_table(cond,