summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2010-11-19 07:38:02 -0800
committerIgor Babaev <igor@askmonty.org>2010-11-19 07:38:02 -0800
commitae4b5a32a65f5c7552dcfdebbb74c7cb0b62a89d (patch)
treef15c19363e163514815ee227e0f4d1c88b883980
parent0a3922fca87b6f6122995f081ccab719e24354ca (diff)
downloadmariadb-git-ae4b5a32a65f5c7552dcfdebbb74c7cb0b62a89d.tar.gz
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.
-rw-r--r--mysql-test/r/join_cache.result52
-rw-r--r--mysql-test/t/join_cache.test45
-rw-r--r--sql/sql_join_cache.cc19
3 files changed, 113 insertions, 3 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 36152af7898..4973288cd9f 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -6004,4 +6004,56 @@ a1 a2 b2 a3 b3
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;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index bb0d1b56a3d..1905297b25b 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -2655,5 +2655,50 @@ SET SESSION join_cache_level = DEFAULT;
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;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index c3f2dfa5541..713d6f9e93f 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -1776,7 +1776,13 @@ uint JOIN_CACHE::read_record_field(CACHE_FIELD *copy, bool blob_in_rec_buff)
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 @@ bool JOIN_CACHE::read_referenced_field(CACHE_FIELD *copy,
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;