[Commits] Rev 2860: Fixed LP bug #675922. in file:///home/igor/maria/maria-5.3-mwl128-bugs/

Igor Babaev igor at askmonty.org
Fri Nov 19 17:38:03 EET 2010


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

------------------------------------------------------------
revno: 2860
revision-id: igor at askmonty.org-20101119153802-oqm4dxnqruotwszp
parent: igor at askmonty.org-20101119142028-ymffn83m5hyvv2ck
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bugs
timestamp: Fri 2010-11-19 07:38:02 -0800
message:
  Fixed LP bug #675922.
  The bug happened when BKA join algorithm used an incremental buffer
  and some of the fields over which access keys were constructed
  - were allocated in the previous join buffers
  - were non-nullable
  - belonged to inner tables of outer joins.
  For such fields an offset to the field value in the record is saved
  in the postfix of the record, and a zero offset indicates that the value 
  is null. Before the key using the field value is constructed the
  value is read into the corresponding field of the record buffer and
  the null bit is set for the field if the offset is 0. However if
  the field is non-nullable the table->null_row must be set to 1
  for null values and to 0 for non-null values  to ensure proper reading
  of the value from the record buffer.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-19 14:20:28 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-19 15:38:02 +0000
@@ -6004,4 +6004,56 @@
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3;
+#
+# Bug #675922: incremental buffer for BKA with access from previous
+#      buffers from non-nullable columns whose values may be null
+#
+CREATE TABLE t1 (a1 varchar(32)) ;
+INSERT INTO t1 VALUES ('s'),('k');
+CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
+INSERT INTO t2 VALUES (7,'s');
+CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
+INSERT INTO t3 VALUES (7,'s');
+CREATE TABLE t4 (a4 int) ;
+INSERT INTO t4 VALUES (9);
+CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
+INSERT INTO t5 VALUES (7,0);
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT t4.a4, t5.b5 
+FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
+SELECT t4.a4, t5.b5 
+FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+a4	b5
+9	0
+9	NULL
+SET SESSION join_cache_level = 6;
+EXPLAIN
+SELECT t4.a4, t5.b5 
+FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using index
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t5	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BKA join)
+SELECT t4.a4, t5.b5 
+FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+a4	b5
+9	0
+9	NULL
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3,t4,t5;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-11-19 14:20:28 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-19 15:38:02 +0000
@@ -2655,5 +2655,50 @@
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #675922: incremental buffer for BKA with access from previous
+--echo #      buffers from non-nullable columns whose values may be null
+--echo #
+
+CREATE TABLE t1 (a1 varchar(32)) ;
+INSERT INTO t1 VALUES ('s'),('k');
+
+CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ;
+INSERT INTO t2 VALUES (7,'s');
+
+CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ;
+INSERT INTO t3 VALUES (7,'s');
+
+CREATE TABLE t4 (a4 int) ;
+INSERT INTO t4 VALUES (9);
+
+CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ;
+INSERT INTO t5 VALUES (7,0);
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT t4.a4, t5.b5 
+  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+       LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+SELECT t4.a4, t5.b5 
+  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+       LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+
+SET SESSION join_cache_level = 6;
+EXPLAIN
+SELECT t4.a4, t5.b5 
+  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+       LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+SELECT t4.a4, t5.b5 
+  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1)
+       LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3,t4,t5;
+
 # 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-13 15:47:43 +0000
+++ b/sql/sql_join_cache.cc	2010-11-19 15:38:02 +0000
@@ -1776,7 +1776,13 @@
     If the value of *len is 0 then the function sets it to the total
     length of the record fields including possible trailing offset
     values. Otherwise *len is supposed to provide this value that
-    has been obtained earlier.  
+    has been obtained earlier. 
+
+  NOTE
+    If the value of the referenced field is null then the offset
+    for the value is set to 0. If the value of a field can be null
+    then the value of flag_fields is always positive. So the offset
+    for any non-null value cannot be 0 in this case. 
 
   RETURN VALUE
     TRUE   'copy' points to a data descriptor of this join cache
@@ -1805,14 +1811,21 @@
                          size_of_fld_ofs*
                          (referenced_fields+1-copy->referenced_field_no));  
   bool is_null= FALSE;
+  Field *field= copy->field;
   if (offset == 0 && flag_fields)
     is_null= TRUE;
   if (is_null)
-    copy->field->set_null();
+  {
+    field->set_null();
+    if (!field->real_maybe_null())
+      field->table->null_row= 1;
+  }
   else
   {
     uchar *save_pos= pos;
-    copy->field->set_notnull(); 
+    field->set_notnull(); 
+    if (!field->real_maybe_null())
+      field->table->null_row= 0;
     pos= rec_ptr+offset;
     read_record_field(copy, blob_data_is_in_rec_buff(rec_ptr));
     pos= save_pos;



More information about the commits mailing list