MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Igor Babaev Date:October 23 2008 6:34pm
Subject:bzr commit into mysql-6.0-bka-preview branch (igor:2662) Bug#40192
View as plain text  
#At file:///home/igor/dev-bzr/mysql-6.0-bug40192/

 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

per-file messages:
  mysql-test/r/join_cache.result
    Added a test case for bug #40192.
  mysql-test/t/join_cache.test
    Added a test case for bug #40192.
  sql/sql_select.cc
    Fixed bug #40192.
    Do not use the filtering condition from the where clause
    pushed to an inner table of an outer join when BNL 
    algorithm is used for an outer join operation.
=== 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,

Thread
bzr commit into mysql-6.0-bka-preview branch (igor:2662) Bug#40192Igor Babaev23 Oct