Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3197) Bug#47762

[Sergei Golubchik]

Hi, VN !

On Oct 30, V Narayanan wrote:
>  3197 V Narayanan	2009-10-30
>       Bug#47762 Incorrect result from MIN() when WHERE tests NOT NULL
>       column for NULL
>       
>       The lookup of a NULL value in a column having a NOT NULL
>       index was resulting in wrong query result. This was happening
>       due to the return value, from the function that stored the
>       field value, being ignored.

Okay.

The main problem here is that there is no special handling of
comparisons with NULL in opt_sum_query().

When you compare a not-nullable field with a NULL,
set_field_to_null_with_conversions() fails - although, in fact it should
not fail, it's called with CHECK_FIELD_IGNORE, which is a bug on
itself - and the record is not updated, it holds the old (basically,
arbitrary) value which is used later in key lookups. MySQL trust the
index lookup here and does not verify that the found value, indeed,
satisfies WHERE condition. Your changeset fixes this special case.

When you compare a nullable field with a NULL, NULL is stored in the
record and it's looked up in the index. If there's no matching record
(with NULL) in the table, as in Kevin's test case, MySQL rightfully
decides that no record was found.

If there is a matching record (add INSERT t2 VALUES (NULL) to Kevin's
test), it *is* found and returned. But as it contains NULL, min(NULL) is
NULL, and the result appears to be correct, even if it was found using
the completely wrong logic :)

Well, if you use > NULL - as Kevin has shown - that wrong logic breaks
and no longer delivers correct results.

Therefore, I think the correct and complete solution to this bug and
other anomalies is to treat NULLs specially in opt_sum_query().

Regards / Mit vielen Grüßen,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@stripped>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring