#At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing-3/ based on revid:sergey.glukhov@stripped
3672 oystein.grovlen@stripped 2009-10-26
Bug#46548 IN-subqueries return 0 rows with materialization=on
This bug is related to key lookups in a materialized table. A caching
mechanism is used to check whether you are doing lookup on the same key
as the last time. In that case, the right record is assumed to already
be present in the record buffer (see cmp_buffer_with_ref()).
The problem is that it derives the key_length from the
key_info of the materialized table. However, when creating the table,
this key_length did not take into account extra bytes used to store null
information and length of variable length fields. When the key_length was
not correct, cmp_buffer_with_ref() would not compare the entire key,
and there was a risk that it would wrongly assume that it already had
the right record in the buffer.
The problem is fixed by also adjusting key_length of key_info when any of
its key_parts are adjusted (create_tmp_table()). This is the way it is
already done for keys in ordinary tables (see open_binary_frm()).
@ mysql-test/r/subselect_mat.result
Updated result file to reflect test case for Bug#46548
@ mysql-test/t/subselect_mat.test
Test case for Bug#46548
@ sql/sql_select.cc
create_tmp_table(): When adjusting store_length of key parts, make sure
to also update total key length as stored in associated key_info.
@ sql/table.cc
Add comment to indicate that much of the logic from open_binary_frm() has been
duplicated in create_tmp_table()
modified:
mysql-test/r/subselect_mat.result
mysql-test/t/subselect_mat.test
sql/sql_select.cc
sql/table.cc
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result 2009-10-09 09:35:41 +0000
+++ b/mysql-test/r/subselect_mat.result 2009-10-26 10:05:33 +0000
@@ -1196,3 +1196,26 @@ SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN
COUNT(*)
2
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk int,
+a varchar(1),
+b varchar(4),
+c varchar(4),
+d varchar(4),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR; Materialize
+SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+pk
+2
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+pk
+2
+DROP TABLE t1, t2;
=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test 2009-10-09 09:35:41 +0000
+++ b/mysql-test/t/subselect_mat.test 2009-10-26 10:05:33 +0000
@@ -866,3 +866,26 @@ EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f
SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2);
DROP TABLE t1, t2;
+
+#
+# BUG#46548 IN-subqueries return 0 rows with materialization=on
+#
+CREATE TABLE t1 (
+ pk int,
+ a varchar(1),
+ b varchar(4),
+ c varchar(4),
+ d varchar(4),
+ PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+DROP TABLE t1, t2;
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-10-25 13:41:27 +0000
+++ b/sql/sql_select.cc 2009-10-26 10:05:33 +0000
@@ -14871,10 +14871,16 @@ create_tmp_table(THD *thd,TMP_TABLE_PARA
key_part_info->store_length= key_part_info->length;
if ((*reg_field)->real_maybe_null())
+ {
key_part_info->store_length+= HA_KEY_NULL_LENGTH;
+ keyinfo->key_length+= HA_KEY_NULL_LENGTH;
+ }
if ((*reg_field)->type() == MYSQL_TYPE_BLOB ||
(*reg_field)->real_type() == MYSQL_TYPE_VARCHAR)
+ {
key_part_info->store_length+= HA_KEY_BLOB_LENGTH;
+ keyinfo->key_length+= HA_KEY_BLOB_LENGTH;
+ }
key_part_info->type= (uint8) (*reg_field)->key_type();
key_part_info->key_type =
=== modified file 'sql/table.cc'
--- a/sql/table.cc 2009-10-25 13:41:27 +0000
+++ b/sql/table.cc 2009-10-26 10:05:33 +0000
@@ -706,6 +706,10 @@ err_not_open:
/*
Read data from a binary .frm file from MySQL 3.23 - 5.0 into TABLE_SHARE
+
+ NOTE: Much of the logic here is duplicated in create_tmp_table()
+ (see sql_select.cc). Hence, changes to this function may have to be
+ repeated there.
*/
static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head,
Attachment: [text/bzr-bundle]
