Bug #27704 row comparisation returns wrong results
Submitted: 8 Apr 2007 15:35 Modified: 24 Apr 2007 17:38
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.23,5.0.40,5.1BK OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: compare, Contribution, null, qc, row

[8 Apr 2007 15:35] Martin Friebe
Description:
Mysql behaves inconsistent when comparing row() with some null values in them.

This applies to ne, gt, lt, ge, gt, but not eq operations. 
The compare_row function has an optimization (abort_on_null) which only works for eq.

See How to repeat, and supplied test case.

The behaviour changed between 4.1 and 5.0

In 4.1 comparing rows with a null in at least one value always returned null
(4.1 is therefore not affected, but behaves different than 5.0)

In 5.0 two rows are distinct, if at least on value pair is distinct. This seems intended. Looking at the following comment in the code
 // continue comparison (maybe we will meet explicit difference)

The behavior for comparing gt / lt is not documented, but appears to be a left to right evaluation.

How to repeat:
# Condition evaluates TRUE in select list
select (1,3) <> (null,4);
+-------------------+
| (1,3) <> (null,4) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.02 sec)

# But FALSE or NULL in where clause
select 1 from dual where (1,3) <> (null,4);
Empty set (0.00 sec)

# same with lt operator
# except that the result should be NULL both times
select  (1,3) < (null,4);
+------------------+
| (1,3) < (null,4) |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

select 1 from DUAL where (1,3) < (null,4);
Empty set (0.00 sec)

Suggested fix:
see patch
[8 Apr 2007 15:35] Martin Friebe
patch

Attachment: row_cmp.patch (text/x-patch), 4.38 KiB.

[8 Apr 2007 15:35] Martin Friebe
test

Attachment: row_cmp_test.tar.gz (application/x-gzip, text), 1.78 KiB.

[8 Apr 2007 22:19] Miguel Solorzano
Thank you for the bug report and contribution patch.
[18 Apr 2007 16:30] 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:

  http://lists.mysql.com/commits/24802

ChangeSet@1.2451, 2007-04-18 21:30:41+05:00, gshchepa@gshchepa.loc +4 -0
  Bug#27704: row comparisation returns wrong results.
  Support for NULL cells was incomplete for row comparison.
  Fixed.
[19 Apr 2007 10:36] 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:

  http://lists.mysql.com/commits/24895

ChangeSet@1.2636, 2007-04-19 15:31:34+05:00, gshchepa@gshchepa.loc +4 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison.
  Fixed.
[19 Apr 2007 14:36] 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:

  http://lists.mysql.com/commits/24904

ChangeSet@1.2636, 2007-04-19 19:40:27+05:00, gshchepa@gshchepa.loc +4 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison.
  Fixed.
[19 Apr 2007 18:35] 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:

  http://lists.mysql.com/commits/24931

ChangeSet@1.2636, 2007-04-19 23:40:20+05:00, gshchepa@gshchepa.loc +5 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison,
  fixed.  Added support for abort_on_null at compare_row() like
  in 5.x
[19 Apr 2007 19:40] Gleb Shchepa
4.1 is affected too, but only <> operation is incorrect:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select 1 from dual where (1,3) <> (null,4);
Empty set (0.00 sec)

mysql> select  (1,3) < (null,4);
+------------------+
| (1,3) < (null,4) |
+------------------+
|             NULL |
+------------------+
1 row in set (0.00 sec)

mysql> select 1 from DUAL where (1,3) < (null,4);
Empty set (0.00 sec)
[19 Apr 2007 20:53] 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:

  http://lists.mysql.com/commits/24952

ChangeSet@1.2636, 2007-04-20 01:59:16+05:00, gshchepa@gshchepa.loc +5 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison,
  fixed.  Added support for abort_on_null at compare_row() like
  in 5.x
[19 Apr 2007 21:50] Martin Friebe
Actually in 4.1 both the = and the <> are affected.
4.1 is inconsistent. If the null occurs before the explicit diff, the result is null. If it occurs after the diff, the result is non-null

Results from 4.1

# explicit diff only after null
select row(1,null,2) = row(1,null,3), row(1,null,2) <> row(1,null,3);
+-------------------------------+--------------------------------+
| row(1,null,2) = row(1,null,3) | row(1,null,2) <> row(1,null,3) |
+-------------------------------+--------------------------------+
|                          NULL |                           NULL |
+-------------------------------+--------------------------------+

# explicit diff before null
select row(3,null,2) = row(1,null,3), row(3,null,2) <> row(1,null,3);
+-------------------------------+--------------------------------+
| row(3,null,2) = row(1,null,3) | row(3,null,2) <> row(1,null,3) |
+-------------------------------+--------------------------------+
|                             0 |                              1 |
+-------------------------------+--------------------------------+
[20 Apr 2007 9:43] Gleb Shchepa
In 4.1 incorrect row comparison affects subselects:

mysql> drop table t, t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int, b int, c varchar(10));
Query OK, 0 rows affected (1.51 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (1.39 sec)

mysql> insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1),(2),(NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select a, 
             (select a,b,c from t1 where t1.a=t2.a) = ROW(a,2,'a') as sub1,
             (select c from t1 where a=t2.a) as sub2 from t2;
+------+------+------+
| a    | sub1 | sub2 |
+------+------+------+
|    1 |    1 | a    |
|    2 |    0 | b    |
| NULL | NULL | NULL |
+------+------+------+
3 rows in set (0.00 sec)

Last query result shold be:

+------+------+------+
| a    | sub1 | sub2 |
+------+------+------+
|    1 |    1 | a    | 
|    2 |    0 | b    | 
| NULL |    0 | NULL | 
+------+------+------+
3 rows in set (0.00 sec)

because

mysql> select a,b,c from t1 where t1.a=NULL;
Empty set (0.00 sec)

and the result of equality check of empty set and some row is FALSE (or 0), not NULL.

This bug doesn't affect 5.x.
[20 Apr 2007 10:09] 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:

  http://lists.mysql.com/commits/24988

ChangeSet@1.2636, 2007-04-20 15:14:09+05:00, gshchepa@gshchepa.loc +5 -0
  Bug#27704: incorrect comparison of rows with NULL components
  Support for NULL components was incomplete for row comparison,
  fixed.  Added support for abort_on_null at compare_row() like
  in 5.x
[21 Apr 2007 15:19] Bugs System
Pushed into 5.1.18-beta
[21 Apr 2007 15:20] Bugs System
Pushed into 5.0.42
[21 Apr 2007 15:21] Bugs System
Pushed into 4.1.23
[24 Apr 2007 17:38] Paul Dubois
Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

Comparisons using row constructors could fail for rows containing
NULL values.