MySQL Bugs: #27704: row comparisation returns wrong results

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

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

patch

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

test

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

Thank you for the bug report and contribution patch.

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.

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.

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.

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

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)

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

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 |
+-------------------------------+--------------------------------+

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.

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

Pushed into 5.1.18-beta

Pushed into 5.0.42

Pushed into 4.1.23

Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

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