bzr commit into mysql-6.0-bka-preview branch (igor:2658) Bug#40134

[Igor Babaev]

#At file:///home/igor/dev-bzr/mysql-6.0-bug40134/

 2658 Igor Babaev	2008-10-18
      Fixed bug #40134.
      After a match for the on expression of an outer join has been
      found the pushdown condition attached to the inner tables must
      be re-evaluated as they contain conditional expressions 
      dependent on the value of the flag 'found' that is set on
      the 'match found' event.
      By mistake when a join buffer was used to join an inner table
      of an outer join and 'not exist' optimization was applied to
      the join operation no re-evaluation of the pushdown condition
      attached to the table was performed.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/join_cache.result
    Added a test case for bug #40134.
  mysql-test/t/join_cache.test
    Added a test case for bug #40134.
  sql/sql_select.cc
    Fixed bug #40134.
    After a match for the on expression of an outer join has been
    found the pushdown condition attached to the inner tables must
    be re-evaluated as they contain conditional expressions 
    dependent on the value of the flag 'found' that is set on
    the 'match found' event.
    Fixed the code of the function JOIN_CACHE::check_match 
    appropriately.
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2008-07-22 04:54:18 +0000
+++ b/mysql-test/r/join_cache.result	2008-10-19 03:09:24 +0000
@@ -3206,3 +3206,33 @@ a	b	a	b
 8	30	8	10
 8	30	8	20
 DROP TABLE t1,t2;
+set join_cache_level=default;
+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
+set join_cache_level=6;
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	i_a	i_a	4	test.t1.a	2	Using where; Not exists; Using join buffer
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+a	a	b
+3	NULL	NULL
+5	NULL	NULL
+1	NULL	NULL
+DROP TABLE t1, t2;
+set join_cache_level=default;
+set join_buffer_size=default;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2008-07-06 20:22:50 +0000
+++ b/mysql-test/t/join_cache.test	2008-10-19 03:09:24 +0000
@@ -769,4 +769,30 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t
 SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30;  
 
 DROP TABLE t1,t2;
- 
\ No newline at end of file
+ 
+#
+# Bug #40134: outer join with not exists optimization and join buffer
+#
+
+set join_cache_level=default;
+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;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+
+set join_cache_level=6;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL;
+
+DROP TABLE t1, t2;
+
+set join_cache_level=default;
+set join_buffer_size=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-18 06:23:35 +0000
+++ b/sql/sql_select.cc	2008-10-19 03:09:24 +0000
@@ -18789,18 +18789,17 @@ inline bool JOIN_CACHE::check_match(ucha
     if (!set_match_flag_if_none(first_inner, rec_ptr))
       continue;
     if (first_inner->check_only_first_match() &&
-        !first_inner->first_upper)
+        !join_tab->first_inner)
       return TRUE;
     /* 
       This is the first match for the outer table row.
       The function set_match_flag_if_none has turned the flag
       first_inner->found on. The pushdown predicates for
       inner tables must be re-evaluated with this flag on.
-      Note that, if first_inner is the first inner table of
-      a semi-join or of an outer join such that 'not exist'
-      optimization can  be applied to it and it is not embedded
-      outer join then the re-valuation of the pushdown predicates
-      is not needed.
+      Note that, if first_inner is the first inner table 
+      of a semi-join, but is not an inner table of an outer join
+      such that 'not exists' optimization can  be applied to it, 
+      the re-evaluation of the pushdown predicates is not needed.
     */      
     for (JOIN_TAB *tab= first_inner; tab <= join_tab; tab++)
     {