bzr push into mysql-6.0-opt branch (igor:2805 to 2806) Bug#42020

[Igor Babaev]

 2806 Igor Babaev	2009-01-11
      Fixed bug #42020.
      When an outer join is executed using a join buffer the buffer
      is scanned twice. At the first scan matches for records in the
      buffer are looked for and any record for which a match is found
      is marked. At the second scan the records without matches are
      looked through and each of them is complemented by null columns
      after having been read into the record buffer. Records with
      matches are just skipped and not even read into the join buffer.
      As a result at the end of the scan the record in the record buffer
      is not guaranteed to be the last record from join buffer. This
      can break the whole nested loop process and lead to wrong 
      results produced by the operation.
      All fields of the last record from the join buffer must be restored
      in the corresponding record buffers after the second scan.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_join_cache.cc

 2805 Sergey Petrunia	2009-01-10 [merge]
      Merge
modified:
  .bzr-mysql/default.conf
  mysql-test/r/innodb_mrr.result
  mysql-test/r/join_cache.result
  mysql-test/r/subselect3.result
  mysql-test/r/subselect3_jcl6.result
  mysql-test/t/innodb_mrr.test
  mysql-test/t/join_cache.test
  mysql-test/t/subselect3.test
  sql/sql_join_cache.cc
  sql/sql_select.cc
  sql/sql_select.h

=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2009-01-08 05:47:10 +0000
+++ b/mysql-test/r/join_cache.result	2009-01-11 22:06:01 +0000
@@ -3619,3 +3619,102 @@ COUNT(*)
 set join_buffer_size=default;
 set join_cache_level=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
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
+set join_cache_level=6;
+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	Using join buffer
+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
[phone]
+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
[phone]
[phone]
[phone]
[phone]
[phone]
+DROP INDEX idx ON t3;
+set join_cache_level=4;
+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; Using join buffer
+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
[phone]
[phone]
[phone]
[phone]
+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
[phone]
[phone]
+set join_buffer_size=default;
+set join_cache_level=default;
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2009-01-08 05:47:10 +0000
+++ b/mysql-test/t/join_cache.test	2009-01-11 22:06:01 +0000
@@ -1023,3 +1023,71 @@ set join_buffer_size=default;
 set join_cache_level=default;
 
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #42020: join buffer is used  for outer join with fields of 
+--echo #             several outer tables in join buffer
+--echo #
+
+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; 
+
+set join_cache_level=6;
+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; 
+
+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;
+
+DROP INDEX idx ON t3;
+set join_cache_level=4;
+
+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; 
+
+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; 
+
+set join_buffer_size=default;
+set join_cache_level=default;
+
+DROP TABLE t1,t2,t3;

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2009-01-08 05:47:10 +0000
+++ b/sql/sql_join_cache.cc	2009-01-11 22:06:01 +0000
@@ -1903,10 +1903,16 @@ inline bool JOIN_CACHE::check_match(ucha
 
 enum_nested_loop_state JOIN_CACHE::join_null_complements(bool skip_last)
 {
+  uint cnt; 
   enum_nested_loop_state rc= NESTED_LOOP_OK;
   bool is_first_inner= join_tab == join_tab->first_unmatched;
-  bool is_last_inner= join_tab == join_tab->first_unmatched->last_inner; 
-  uint cnt= records - (is_key_access() ? 0 : test(skip_last));
+  bool is_last_inner= join_tab == join_tab->first_unmatched->last_inner;
+ 
+  /* Return at once if there are no records in the join buffer */
+  if (!records)
+    return NESTED_LOOP_OK;
+  
+  cnt= records - (is_key_access() ? 0 : test(skip_last));
 
   /* This function may be called only for inner tables of outer joins */ 
   DBUG_ASSERT(join_tab->first_inner);
@@ -1958,6 +1964,16 @@ enum_nested_loop_state JOIN_CACHE::join_
   }
 
 finish:
+  if (is_first_inner)
+  {
+    /* 
+      Restore the values of the fields of the last record put into join buffer.
+      The value of the fields of the last record in the buffer must be restored
+      since at the null complementing pass fields of the records with matches
+      are skipped and their fields are not read into the record buffers at all. 
+    */
+    get_record_by_pos(last_rec_pos);
+  }
   return rc;
 }