Hi,
Approved by me, given that you add one row to each table in the test
case since test case does not fail for me when const table optimization
is triggered.
--
Øystein
On 27/05/2011 10:34, Evgeny Potemkin wrote:
> #At file:///work/bzrroot/11825482-bug/ based on
> revid:epotemkin@stripped
>
> 3004 Evgeny Potemkin 2011-05-27
> Bug#11825482: Broken key length calculation for btree index.
> Materializable views/derived tables employs HEAP engine for result small
> enough to fit into memory. For doing btree index lookups heap engine
> converts key provided by mysql server into internal format. When a null value
> were given for a variable length field converter wasn't taking into account
> length part thus creating a wrong internal key. This led to a wrong result.
> Also fixed a 'failed assertion' bug that manifests itself while explaining
> complex queries.
>
> Now hp_rb_pack_key function takes int account length part of key when
> skipping
> null values for a variable length fields.
> Item_sum_num::fix_fields now checks 'fixed' flag prior to calling fix_fields
> function.
> @ mysql-test/r/heap.result
> Added a test case for the bug#11825482.
> @ mysql-test/t/heap.test
> Added a test case for the bug#11825482.
> Minor improvement for Vim's syntax highligh.
> @ sql/item_sum.cc
> Bug#11825482: Broken key length calculation for btree index.
> Item_sum_num::fix_fields now checks 'fixed' flag prior to calling
> fix_fields
> function.
> @ storage/heap/hp_hash.c
> Bug#11825482: Broken key length calculation for btree index.
> Now hp_rb_pack_key function takes int account length part of key when
> skipping
> null values for a variable length fields.
>
> modified:
> mysql-test/r/heap.result
> mysql-test/t/heap.test
> sql/item_sum.cc
> storage/heap/hp_hash.c
> === modified file 'mysql-test/r/heap.result'
> --- a/mysql-test/r/heap.result 2010-10-25 09:20:53 +0000
> +++ b/mysql-test/r/heap.result 2011-05-27 08:34:45 +0000
> @@ -701,6 +701,9 @@ insert into t1 values ("abcdefghijklmnop
> insert into t1 values
> ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
> ERROR 23000: Duplicate entry
> 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl' for key 'PRIMARY'
> drop table t1;
> +#
> +# Bug 12796: Record doesn't show when selecting through index
> +#
> CREATE TABLE t1 (a int, key(a)) engine=heap;
> insert into t1 values (0);
> delete from t1;
> @@ -738,3 +741,37 @@ SELECT c2 FROM t1;
> c2
> 0
> DROP TABLE t1;
> +#
> +# BUG#11825482: Broken key length calculation for btree index
> +#
> +CREATE TABLE h1 (f1 VARCHAR(1), f2 INT NOT NULL,
> +UNIQUE KEY h1i (f1,f2) USING BTREE ) ENGINE=HEAP;
> +INSERT INTO h1 VALUES(NULL,0),(NULL,1);
> +SELECT 'wrong' FROM dual WHERE ('h', 0) NOT IN (SELECT * FROM h1);
> +wrong
> +CREATE TABLE t1 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +col_int_nokey INT,
> +col_varchar_nokey VARCHAR(1),
> +PRIMARY KEY (pk)
> +);
> +INSERT INTO t1 VALUES (19,5,'h');
> +CREATE TABLE t2 (col_int_nokey INT);
> +INSERT INTO t2 VALUES (1);
> +CREATE OR REPLACE VIEW v1 AS
> +SELECT col_varchar_nokey, COUNT( col_varchar_nokey )
> +FROM t1
> +WHERE col_int_nokey<= 141 AND pk<= 4
> +;
> +SELECT col_int_nokey FROM t2
> +WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
> +col_int_nokey
> +# shouldn't crash
> +EXPLAIN SELECT col_int_nokey FROM t2
> +WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading
> const tables
> +2 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1
> +3 DERIVED NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading
> const tables
> +DROP TABLE t1,t2,h1;
> +DROP VIEW v1;
>
> === modified file 'mysql-test/t/heap.test'
> --- a/mysql-test/t/heap.test 2010-10-25 09:20:53 +0000
> +++ b/mysql-test/t/heap.test 2011-05-27 08:34:45 +0000
> @@ -436,9 +436,9 @@ insert into t1 values ("abcdefghijklmnop
> insert into t1 values
> ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
> drop table t1;
>
> -#
> -# Bug 12796: Record doesn't show when selecting through index
> -#
> +--echo #
> +--echo # Bug 12796: Record doesn't show when selecting through index
> +--echo #
> CREATE TABLE t1 (a int, key(a)) engine=heap;
> insert into t1 values (0);
> delete from t1;
> @@ -480,3 +480,39 @@ INSERT INTO t1 VALUES('', 0);
> ALTER TABLE t1 MODIFY c1 VARCHAR(101);
> SELECT c2 FROM t1;
> DROP TABLE t1;
> +
> +--echo #
> +--echo # BUG#11825482: Broken key length calculation for btree index
> +--echo #
> +CREATE TABLE h1 (f1 VARCHAR(1), f2 INT NOT NULL,
> + UNIQUE KEY h1i (f1,f2) USING BTREE ) ENGINE=HEAP;
> +INSERT INTO h1 VALUES(NULL,0),(NULL,1);
> +SELECT 'wrong' FROM dual WHERE ('h', 0) NOT IN (SELECT * FROM h1);
> +
> +CREATE TABLE t1 (
> + pk int(11) NOT NULL AUTO_INCREMENT,
> + col_int_nokey INT,
> + col_varchar_nokey VARCHAR(1),
> + PRIMARY KEY (pk)
> +);
> +
> +INSERT INTO t1 VALUES (19,5,'h');
> +
> +CREATE TABLE t2 (col_int_nokey INT);
> +
> +INSERT INTO t2 VALUES (1);
> +
> +CREATE OR REPLACE VIEW v1 AS
> + SELECT col_varchar_nokey, COUNT( col_varchar_nokey )
> + FROM t1
> + WHERE col_int_nokey<= 141 AND pk<= 4
> +;
> +
> +SELECT col_int_nokey FROM t2
> +WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
> +--echo # shouldn't crash
> +EXPLAIN SELECT col_int_nokey FROM t2
> +WHERE ('h', 0) NOT IN ( SELECT * FROM v1);
> +
> +DROP TABLE t1,t2,h1;
> +DROP VIEW v1;
>
> === modified file 'sql/item_sum.cc'
> --- a/sql/item_sum.cc 2011-02-02 09:21:41 +0000
> +++ b/sql/item_sum.cc 2011-05-27 08:34:45 +0000
> @@ -1108,7 +1108,8 @@ Item_sum_num::fix_fields(THD *thd, Item
> maybe_null=0;
> for (uint i=0 ; i< arg_count ; i++)
> {
> - if (args[i]->fix_fields(thd, args + i) || args[i]->check_cols(1))
> + if ((!args[i]->fixed&& args[i]->fix_fields(thd, args + i)) ||
> + args[i]->check_cols(1))
> return TRUE;
> set_if_bigger(decimals, args[i]->decimals);
> maybe_null |= args[i]->maybe_null;
>
> === modified file 'storage/heap/hp_hash.c'
> --- a/storage/heap/hp_hash.c 2010-07-27 10:16:49 +0000
> +++ b/storage/heap/hp_hash.c 2011-05-27 08:34:45 +0000
> @@ -805,9 +805,19 @@ uint hp_rb_pack_key(HP_KEYDEF *keydef, u
> keypart_map>>= 1;
> if (seg->null_bit)
> {
> + /* Convert NULL from MySQL representation into HEAP's. */
> if (!(*key++= (char) 1 - *old++))
> + {
> + /*
> + Skip length part of a variable length field.
> + Length of key-part used with heap_rkey() always 2.
> + See also hp_hashnr().
> + */
> + if (seg->flag& (HA_VAR_LENGTH_PART | HA_BLOB_PART))
> + old+= 2;
> continue;
> }
> + }
> if (seg->flag& HA_SWAP_KEY)
> {
> uint length= seg->length;
>
>
>
>
>
--
Øystein Grøvlen, Principal Software Engineer
MySQL Group, Oracle
Trondheim, Norway
