#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#44019 | Igor Babaev | 4 Apr |
