On 05/28/2010 11:42 AM, Martin Hansson wrote:
> #At file:///data0/martin/bzr/bug53859/5.1bt/ based on
> 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
> === 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,
> + 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.
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.
t1a.b > 1 AND t1a.b IS NULL
1st step: org_key_part_used != *key_part_used
we store 1 into the field and this field is not null
2nd step: org_key_part_used == *key_part_used
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.
Attachment: [text/x-patch] p.patch