Bug #12509 ROW() = ROW() doesn't handle NULL's right
Submitted: 10 Aug 2005 21:33 Modified: 24 Aug 2005 17:22
Reporter: Michael Widenius Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 and 5.0 OS:
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[10 Aug 2005 21:33] Michael Widenius
Currently MySQL returns NULL if any element in a ROW() comparison is NULL, even if we should return 0 if any constant doesn't match.

mysql> select row(NULL,1)=(2,0);

When 0 should be the correct answer

How to repeat:
Here how it should work (From SQL-99 Complete, Really, page 237)

ROW(1,1,1) = ROW(1,1,1)  -> 1
ROW(1,1,1) = ROW(1,2,1)  -> 0
ROW(1,NULL,1) = ROW(2,2,1) -> 0
ROW(1,NULL,1) =  (1,2,1) -> NULL

Suggested fix:
As this is a not critcal problem in 4.1, better to fix it only in 5.0 (assuming the patch is not trivial)
[18 Aug 2005 9:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[19 Aug 2005 9:20] Oleksandr Byelkin
pushed to 5.0.12
[24 Aug 2005 17:22] Mike Hillyer
Documented in 5.0.12 changelog:

<listitem><para><literal>ROW()</literal> function returned incorrect result when comparison involved <literal>NULL</literal> values. (Bug #12509)</para></listitem>