List:Commits« Previous MessageNext Message »
From:Sergey Glukhov Date:June 2 2010 7:45am
Subject:Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3387)
Bug#53859
View as plain text  
Hi Martin,

On 05/28/2010 11:42 AM, Martin Hansson wrote:
> #At file:///data0/martin/bzr/bug53859/5.1bt/ based on
> revid:sergey.glukhov@stripped
>
>   3387 Martin Hansson	2010-05-28
>        Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&,
>        Item*) at opt_sum.cc:305
>
>        Queries where MIN and MAX functions are applied to indexed
>        columns are optimized to read directly from the index if all
>        key parts of the index preceding the aggregated key part are
>        bound to constants by the WHERE clause. A prefix length is
>        also produced. If the aggregated column itself is bound to a
>        constant, however, it is also included in the prefix. The
>        function that was performing the read missed this detail and
>        tried to read outside the key buffer.
>
>        Fixed by simply reading the last key part's position in the
>        key buffer if the prefix length equals the entire buffer
>        length.
>
>      modified:
>        mysql-test/r/func_group.result
>        mysql-test/t/func_group.test
>        sql/opt_sum.cc
>
>
> === modified file 'mysql-test/t/func_group.test'
> --- a/mysql-test/t/func_group.test	2009-11-24 15:26:13 +0000
> +++ b/mysql-test/t/func_group.test	2010-05-28 07:42:14 +0000
> @@ -1083,5 +1083,20 @@ from t1 a, t1 b;
>   select *, f1 = f2 from t1;
>   drop table t1;
>   --echo #
> +--echo #
> +--echo # Bug#53859: Valgrind: opt_sum_query(TABLE_LIST*, List<Item>&,
> Item*) at
> +--echo # opt_sum.cc:305
> +--echo #
> +CREATE TABLE t1 ( a INT, b INT, KEY (b) );
> +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
> +
> +--echo # Should not give valgrid warnings.
> +SELECT MIN( t1a.b ) AS field1
> +FROM t1 t1a LEFT JOIN t1 t1b USING( a )
> +WHERE t1a.b>  1 AND t1a.b IS NULL
> +ORDER BY field1;
> +
> +DROP TABLE t1;
> +
>   --echo End of 5.1 tests
It's not necessary to use LEFT JOIN here,
The following SELECT is enough:

SELECT MIN( t1a.b ) AS field1
FROM t1 t1a
WHERE t1a.b > 1 AND t1a.b IS NULL
ORDER BY field1;

Check also reverse order of conditions
(which does not work with your patch properly).
>
>
> === modified file 'sql/opt_sum.cc'
> --- a/sql/opt_sum.cc	2010-05-12 16:10:33 +0000
> +++ b/sql/opt_sum.cc	2010-05-28 07:42:14 +0000
> @@ -126,7 +126,18 @@ static int get_index_min_value(TABLE *ta
>         key. If read fails, and we're looking for a MIN() value for a
>         nullable column, test if there is an exact match for the key.
>       */
> -    if (!(range_fl&  NEAR_MIN))
> +    if (prefix_len == ref->key_length)
> +      /*
> +         In the special case when the argument to the MIN function is also
> +         bound to a constant, i.e. SELECT MIN(a) ... WHERE a =<constant>, the
> +         prefix is the entire ref key and all we need to do is try to read
> +         once using that key.
> +      */
> +      error= table->file->index_read_map(table->record[0],
> +                                         ref->key_buff,
> +                                         make_prev_keypart_map(ref->key_parts),
> +                                         HA_READ_KEY_EXACT);
> +    else if (!(range_fl&  NEAR_MIN))
>         /*
>            Closed interval: Either The MIN argument is non-nullable, or
>            we have a>= predicate for the MIN argument.
It seems to me that problem here is in matching_cond() function
(which is called from find_key_for_maxmin()) which can not
determine impossible condition.
Re matching_cond(),
we twice process the same key_part. At first step we store
appropriate value into the field, at second step we can check
if this value corresponds with current operation.

Example:
t1a.b > 1 AND t1a.b IS NULL

1st step: org_key_part_used != *key_part_used
operation:  noeq_type
we store 1 into the field and this field is not null

2nd step: org_key_part_used == *key_part_used
operation: is_null
we check if field value is NULL and exit with  'Index can't be used'.

I send rough patch(see attachment) which fixes the problem,
but we need to check which conditions are necessary, which
are superfluous in this patch.
Please also check the fix with RQG.

Regards,
Gluh

Attachment: [text/x-patch] p.patch
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3387) Bug#53859Martin Hansson28 May
Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3387)Bug#53859Sergey Glukhov2 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3387)Bug#53859Martin Hansson3 Jun