List:Commits« Previous MessageNext Message »
From:Igor Babaev Date:April 4 2009 5:41am
Subject:bzr commit into mysql-6.0-opt branch (igor:2734) Bug#44019
View as plain text  
#At file:///home/igor/dev-bzr/mysql-6.0-opt-bug44019/

 2734 Igor Babaev	2009-04-03
      Fixed bug #44019.
      This bug happened when incremental (linked) join buffers were
      employed to join several tables. 
      If some fields stored in one of such incremental join buffers
      are used to build keys to access tables that follow the next
      joined table, then offsets of these fields are saved at the 
      very end of the records stored in the join buffer.
      These offsets allow us to read only those record fields that
      are needed for building key values.
      Each field whose offset is saved gets its own unique number
      that determines the position of the offset for the field in
      the sequence of field offsets stored for a record.
      In a general case the order of the offsets in the sequence
      does not comply with the order of the corresponding fields.
      This fact was ignored by the code of the function
      JOIN_CACHE:: write_record_data. 
         
      modified:
        mysql-test/r/join_cache.result
        mysql-test/t/join_cache.test
        sql/sql_join_cache.cc

per-file messages:
  mysql-test/r/join_cache.result
    Added a test case for bug #44019.
  mysql-test/t/join_cache.test
    Added a test case for bug #44019.
  sql/sql_join_cache.cc
    Fixed bug #44019.
    Modified the function JOIN_CACHE:: write_record_data to
    make it write optional field offsets in the same order
    in which they are read from the join buffer.
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2009-03-10 01:34:11 +0000
+++ b/mysql-test/r/join_cache.result	2009-04-04 05:40:52 +0000
@@ -3829,3 +3829,55 @@ id1
 30
 set join_cache_level=default;
 drop table t1,t2;
+#
+# Bug #44019: star-like multi-join query executed join_cache_level=6 
+#             
+create table t1 (a int, b int, c int, d int);
+create table t2 (b int, e varchar(16), index idx(b));
+create table t3 (d int, f varchar(16), index idx(d));
+create table t4 (c int, g varchar(16), index idx(c));
+insert into t1 values
+(5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000),
+(2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800),
+(7, 70, 700, 7000);
+insert into t2 values
+(30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
+(31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
+(32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
+insert into t3 values
+(4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
+(4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
+(4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
+insert into t4 values
+(200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
+(201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
+(202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
+analyze table t2,t3,t4;
+set join_cache_level=1;
+explain 
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+a	b	c	d	e	f	g
+3	30	300	3000	bbb	ddd	ccc
+set join_cache_level=6;
+explain 
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.b	1	Using join buffer
+1	SIMPLE	t3	ref	idx	idx	5	test.t1.d	1	Using join buffer
+1	SIMPLE	t4	ref	idx	idx	5	test.t1.c	1	Using join buffer
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+a	b	c	d	e	f	g
+3	30	300	3000	bbb	ddd	ccc
+set join_cache_level=default;
+drop table t1,t2,t3,t4;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2009-03-10 01:34:11 +0000
+++ b/mysql-test/t/join_cache.test	2009-04-04 05:40:52 +0000
@@ -1176,3 +1176,54 @@ set join_cache_level=default;
 
 drop table t1,t2;
 
+--echo #
+--echo # Bug #44019: star-like multi-join query executed join_cache_level=6 
+--echo #             
+
+create table t1 (a int, b int, c int, d int);
+create table t2 (b int, e varchar(16), index idx(b));
+create table t3 (d int, f varchar(16), index idx(d));
+create table t4 (c int, g varchar(16), index idx(c));
+
+insert into t1 values
+  (5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000),
+  (2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800),
+  (7, 70, 700, 7000);
+insert into t2 values
+  (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'),
+  (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'),
+  (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb');
+insert into t3 values
+  (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'),
+  (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'),
+  (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd');
+insert into t4 values
+  (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'),
+  (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'),
+  (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc');
+
+--disable_result_log
+--disable_warnings
+analyze table t2,t3,t4;
+--enable_warnings
+--enable_result_log
+
+set join_cache_level=1;
+explain 
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+  where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+  where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+
+set join_cache_level=6;
+explain 
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+  where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+
+select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4
+  where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c;
+
+set join_cache_level=default;
+
+drop table t1,t2,t3,t4;

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2009-02-13 05:56:16 +0000
+++ b/sql/sql_join_cache.cc	2009-04-04 05:40:52 +0000
@@ -1098,14 +1098,17 @@ uint JOIN_CACHE::write_record_data(uchar
   /* Add the offsets of the fields that are referenced from other caches */ 
   if (referenced_fields)
   {
+    uint cnt= 0;
     for (copy= field_descr+flag_fields; copy < copy_end ; copy++)
     {
       if (copy->referenced_field_no)
       {
-        store_fld_offset(cp, copy->offset);
-        cp+= size_of_fld_ofs;
+        store_fld_offset(cp+size_of_fld_ofs*(copy->referenced_field_no-1),
+                         copy->offset);
+        cnt++;
       }
     }
+    cp+= size_of_fld_ofs*cnt;
   }
 
   if (rec_len_ptr)

Thread
bzr commit into mysql-6.0-opt branch (igor:2734) Bug#44019Igor Babaev4 Apr