[Commits] Rev 2856: Fixed LP bug #674423. in file:///home/igor/maria/maria-5.3-mwl128-bug674423/

Igor Babaev igor at askmonty.org
Sat Nov 13 17:47:43 EET 2010


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

------------------------------------------------------------
revno: 2856
revision-id: igor at askmonty.org-20101113154743-nwdowwt4cycyyprl
parent: igor at askmonty.org-20101113143554-g1q7317b61h4ced5
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bug674423
timestamp: Sat 2010-11-13 07:47:43 -0800
message:
  Fixed LP bug #674423.
  The patch that introduced the new enumeration type Match_flag
  for the values of match flags in the records put into join buffers
  missed the necessary modifications in JOIN_CACHE::set_match_flag_if_none.
  This could cause wrong results for outer joins with on expressions
  only over outer tables.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-13 14:13:34 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-13 15:47:43 +0000
@@ -5809,4 +5809,33 @@
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug #674423: outer join with ON expression over only outer tables 
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES ('9');
+CREATE TABLE t2 (pk int, a int) ;
+INSERT INTO t2 VALUES ('9',NULL), ('1',NULL);
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+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
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9;
+pk	a	a
+1	NULL	NULL
+SET SESSION join_cache_level = 1;
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+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; Using join buffer (flat, BNL join)
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+pk	a	a
+1	NULL	NULL
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-11-13 14:13:34 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-13 15:47:43 +0000
@@ -2495,5 +2495,32 @@
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # Bug #674423: outer join with ON expression over only outer tables 
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES ('9');
+
+CREATE TABLE t2 (pk int, a int) ;
+INSERT INTO t2 VALUES ('9',NULL), ('1',NULL);
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9;
+
+SET SESSION join_cache_level = 1;
+EXPLAIN
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-11-11 23:35:11 +0000
+++ b/sql/sql_join_cache.cc	2010-11-13 15:47:43 +0000
@@ -2219,9 +2219,9 @@
     DBUG_ASSERT(cache);
     rec_ptr= cache->get_rec_ref(rec_ptr);
   } 
-  if (rec_ptr[0] == 0)
+  if ((Match_flag) rec_ptr[0] != MATCH_FOUND)
   {
-    rec_ptr[0]= 1;
+    rec_ptr[0]= MATCH_FOUND;
     first_inner->found= 1;
     return TRUE;  
   }



More information about the commits mailing list