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

[V Narayanan]

Hi,

I have committed a new patch along the suggestions given by Serg. In this
patch I have handled NULLs separately.

Thank you Jorgen, Kevin and Serg for your guidance.

Narayanan


Sergei Golubchik wrote:
> 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
>
>