Below is the list of changes that have just been committed into a local
6.0 repository of igor. When igor does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-06-01 17:28:16-07:00, igor@stripped +3 -0
Fixed bug #37171.
At the very end of the function JOIN_CACHE_BKA::join_matching_records
the the fields of the last record written into the join buffer must
be copied back into the record buffers.
It is easy to show that if not to do it for the fields from the tables
that precede that the join table to which the join buffer is attached
the returned result set in many cases will be wrong.
mysql-test/r/join_cache.result@stripped, 2008-06-01 17:28:11-07:00, igor@stripped +38 -0
Added a test case for bug #37171.
mysql-test/t/join_cache.test@stripped, 2008-06-01 17:28:11-07:00, igor@stripped +56 -0
Added a test case for bug #37171.
sql/sql_select.cc@stripped, 2008-06-01 17:28:11-07:00, igor@stripped +9 -0
Fixed bug #37171.
At the very end of the function JOIN_CACHE_BKA::join_matching_records
the the fields of the last record written into the join buffer must
be copied back into the record buffers.
It is easy to show that if not to do it for the fields from the tables
that precede that the join table to which the join buffer is attached
the returned result set in many cases will be wrong.
diff -Nrup a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
--- a/mysql-test/r/join_cache.result 2008-05-15 14:08:57 -07:00
+++ b/mysql-test/r/join_cache.result 2008-06-01 17:28:11 -07:00
@@ -1623,3 +1623,41 @@ id select_type table type possible_keys
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer
uniquekey affiliateXml artistName artistid genreName genreid genrePriority subgenreid subgenreName metaName metaid metaXml overallPriority path mediaid formatid formatName formatclassid formatclassName formattypeid formattypeName
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 9
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
+1 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where
+a1<>a2 a1 a2 b2 b3 c3 s1 s2
+0 4 4 13 13 138
+0 4 4 18 18 188
+0 1 1 30 30 309
+0 1 1 32 32 329
+0 9 9 22 22 228
+0 8 8 92 92 929
+0 8 8 99 99 998
+0 5 5 82 82 829
+0 5 5 87 87 878
+0 3 3 45 45 459
+0 3 3 45 45 458
+0 6 6 73 73 738
+0 6 6 74 74 749
+0 2 2 61 61 618
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 9
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
+1 SIMPLE t3 ref idx idx 5 test.t2.b2 5 Using where; Using join buffer
+a1<>a2 a1 a2 b2 b3 c3 s1 s2
+0 4 4 18 18 188
+0 4 4 13 13 138
+0 1 1 30 30 309
+0 1 1 32 32 329
+0 8 8 92 92 929
+0 9 9 22 22 228
+0 8 8 99 99 998
+0 5 5 87 87 878
+0 5 5 82 82 829
+0 3 3 45 45 459
+0 3 3 45 45 458
+0 6 6 73 73 738
+0 6 6 74 74 749
+0 2 2 61 61 618
diff -Nrup a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
--- a/mysql-test/t/join_cache.test 2008-05-15 14:08:57 -07:00
+++ b/mysql-test/t/join_cache.test 2008-06-01 17:28:11 -07:00
@@ -525,3 +525,59 @@ WHERE t7.metaid = t2.metaid AND t7.artis
t1.metaid = t2.metaid AND t1.affiliateid = '2';
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
+
+#
+# Bug #37131: 3-way join query with BKA used with a small buffer and
+# only for the third table
+#
+
+CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' );
+CREATE TABLE t2 (
+ a2 int, b2 int, filler2 char(64) default ' ',
+ PRIMARY KEY idx(a2,b2,filler2)
+) ;
+CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3));
+
+INSERT INTO t1(a1) VALUES
+ (4), (7), (1), (9), (8), (5), (3), (6), (2);
+INSERT INTO t2(a2,b2) VALUES
+ (1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56),
+ (4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81),
+ (5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51),
+ (9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79),
+ (3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11);
+INSERT INTO t3 VALUES
+ (30,302), (92,923), (18,187), (45,459), (30,309),
+ (39,393), (68,685), (45,458), (21,210), (81,817),
+ (40,405), (61,618), (73,738), (92,929), (27,275),
+ (18,188), (84,846), (56,564), (14,144), (76,763),
+ (98,982), (55,551), (17,174), (99,998), (51,513),
+ (28,282), (52,527), (33,336), (13,138), (87,878),
+ (43,431), (91,916), (62,624), (79,797), (49,494),
+ (93,933), (34,347), (82,829), (78,780), (63,634),
+ (32,329), (22,228), (11,114), (74,749), (23,236);
+
+set join_cache_level=1;
+
+EXPLAIN
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+ SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+ SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+
+set join_cache_level=5;
+set join_buffer_size=512;
+
+EXPLAIN
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+ SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+
+SELECT a1<>a2, a1, a2, b2, b3, c3,
+ SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2
+FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
+
+DROP TABLE t1,t2,t3;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-05-15 14:08:57 -07:00
+++ b/sql/sql_select.cc 2008-06-01 17:28:11 -07:00
@@ -18619,6 +18619,15 @@ enum_nested_loop_state JOIN_CACHE_BKA::j
finish:
for (tab=join->join_tab; tab != join_tab ; tab++)
tab->table->status= tab->status;
+ /*
+ Restore the values of the fields of the last record put into join buffer.
+ These value most probably has been overwritten by the field values
+ from other records when they were read from the join buffer into the
+ record buffer in order to check pushdown predicates.
+ TODO. Investigate whether the restoration of the fields of the last
+ table whose rows are to be stored in the join buffer is really needed.
+ */
+ get_record_by_pos(last_rec_pos);
return rc;
}
| Thread |
|---|
| • bk commit into 6.0 tree (igor:1.2629) BUG#37171 | igor | 2 Jun |
