MySQL Bugs: #54190: Comparison to row subquery produces incorrect result

Bug #54190	Comparison to row subquery produces incorrect result
Submitted:	2 Jun 2010 20:20	Modified:	13 Nov 2010 16:56
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.42+, 5.1, 5.6.99	OS:	Any
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	regression

Description:
This is a regression as of MySQL 5.1.42.

MySQL correctly handles the cases that a row subquery produces a
single row (it performs the comparison) or multiple rows (it produces
an error).

However, when a row subquery produces no rows (an empty result set),
MySQL no longer treats a comparison to the subquery as having a value
of UNKNOWN.

Consider these queries, which each produce a single row:

mysql> SELECT 1,2 FROM DUAL;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT 1,3 FROM DUAL;
+---+---+
| 1 | 3 |
+---+---+
| 1 | 3 |
+---+---+
1 row in set (0.00 sec)

Using those queries as row subqueries produces a correct comparison result
(TRUE and FALSE, respectively):

mysql> SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL); 
+-----------------------------------+
| ROW(1,2) = (SELECT 1,2 FROM DUAL) |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL); 
+-----------------------------------+
| ROW(1,2) = (SELECT 1,3 FROM DUAL) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

Modifying the queries to add an "impossible WHERE" results in an empty result
set for both queries:

mysql> SELECT 1,2 FROM DUAL WHERE 1 = 0; 
Empty set (0.00 sec)

mysql> SELECT 1,3 FROM DUAL WHERE 1 = 0; 
Empty set (0.00 sec)

But now using the queries as row subqueries produces an incorrect comparison
result. Both produce an empty result, so the comparison result should be
UNKNOWN. But it's not:

mysql> SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+-----------------------------------------------+
| ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0) |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
+-----------------------------------------------+
| ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

In both cases, the comparison should be UNKNOWN (NULL).  But it is
not so for either statement. Odder still, the comparison result
differs depending on what the empty subquery result *would have
returned* if it were not empty!

A similar phenomenon occurs when the comparison occurs elsewhere in a
statement, such as in the WHERE clause. In both of the following
row subquery comparisons, the subquery result is empty. But the outer
SELECT result differs depending on what the subquery *would have
returned* if it were not empty.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE t1 (i INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 () VALUES(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);
Empty set (0.00 sec)

How to repeat:
Test script follows. If you try it in 5.1.41, you get the correct
results. As of 5.1.42, the comparisons with empty row subqueries
are incorrect.

SELECT 1,2 FROM DUAL;
SELECT 1,3 FROM DUAL;
SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL);
SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL);
SELECT 1,2 FROM DUAL WHERE 1 = 0;
SELECT 1,3 FROM DUAL WHERE 1 = 0;
SELECT ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
SELECT ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT);
INSERT INTO t1 () VALUES(1),(2),(3);
SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM DUAL WHERE 1 = 0);
SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM DUAL WHERE 1 = 0);

Thank you for the report.

Verified as described.

Sveta,

Please test if this query works correctly:

ROW(1,2) = (SELECT 1,2 FROM DUAL);

and

ROW(1,2) = (SELECT 1,3 FROM DUAL);

This is because, AFAIK,  WHERE clause is not evaluated from DUAL table.

WHERE clause is not evaluated? This seems to show otherwise:

mysql> SELECT 1,2 FROM DUAL;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> SELECT 1,2 FROM DUAL WHERE 1=0;
Empty set (0.00 sec)

In any case, here is a modified that does not use DUAL but exhibits the same phenomenon (correct results before 5.1.42, incorrect results as of 5.1.42):

DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT, j INT);
INSERT INTO t () VALUES(0,0);
SELECT 1,2 FROM t;
SELECT 1,3 FROM t;
SELECT ROW(1,2) = (SELECT 1,2 FROM t);
SELECT ROW(1,2) = (SELECT 1,3 FROM t);
SELECT 1,2 FROM t WHERE 1 = 0;
SELECT 1,3 FROM t WHERE 1 = 0;
SELECT ROW(1,2) = (SELECT 1,2 FROM t WHERE 1 = 0);
SELECT ROW(1,2) = (SELECT 1,3 FROM t WHERE 1 = 0);

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT);
INSERT INTO t1 () VALUES(1),(2),(3);
SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,2 FROM t WHERE 1 = 0);
SELECT i FROM t1 WHERE ROW(1,2) = (SELECT 1,3 FROM t WHERE 1 = 0);

And just for reference results for Sinisa's question:

$mysql51
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1380
Server version: 5.1.48-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select ROW(1,2) = (SELECT 1,2 FROM DUAL);
+-----------------------------------+
| ROW(1,2) = (SELECT 1,2 FROM DUAL) |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.38 sec)

mysql> select ROW(1,2) = (SELECT 1,3 FROM DUAL);
+-----------------------------------+
| ROW(1,2) = (SELECT 1,3 FROM DUAL) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in 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/117864

3498 Alexey Kopytov	2010-09-09
      Bug #54190: Comparison to row subquery produces incorrect
                  result
      
      Row subqueries producing no rows were not handled as UNKNOWN
      values in row comparison expressions.
      
      That was a result of the following two problems:
      
      1. Item_singlerow_subselect did not mark the resulting row
      value as NULL/UNKNOWN when no rows were produced.
      
      2. Arg_comparator::compare_row() did not take into account that
      a whole argument may be NULL rather than just individual scalar
      values.
      
      Before bug#34384 was fixed, the above problems were hidden
      because an uninitialized (i.e. without any stored value) cached
      object would appear as NULL for scalar values in a row subquery
      returning an empty result. After the fix
      Arg_comparator::compare_row() would try to evaluate
      uninitialized cached objects.
      
      Fixed by removing the aforementioned problems.
     @ mysql-test/r/row.result
        Added a test case for bug #54190.
     @ mysql-test/r/subselect.result
        Updated the result for a test relying on wrong behavior.
     @ mysql-test/t/row.test
        Added a test case for bug #54190.
     @ sql/item_cmpfunc.cc
        If either of the argument rows is NULL, return NULL as the
        result of comparison.
     @ sql/item_subselect.cc
        Adjust null_value for Item_singlerow_subselect depending on
        whether a row has been produced by the row subquery.

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/117893

3504 Alexey Kopytov	2010-09-09
      Addendum patch for bug #54190.
      
      The patch caused some test failures when merged to 5.5 because,
      unlike 5.1, it utilizes Item_cache_row to actually cache row
      values. The problem was that Item_cache_row::bring_value()
      essentially did nothing. In particular, it did not update its
      null_value, so all Item_cache_row objects were always having
      their null_values set to TRUE. This went unnoticed previously,
      but now when Arg_comparator::compare_row() actually depends on
      the row's null_value to evaluate the comparison, the problem
      has surfaced.
      
      Fixed by calling the underlying item's bring_value() and
      updating null_value in Item_cache_row::bring_value().
      
      Since the problem also exists in 5.1 code (albeit hidden, since
      the relevant code is not used anywhere), the addendum patch is
      against 5.1.

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/117897

3198 Alexey Kopytov	2010-09-09 [merge]
      Manual merge of the fix for bug #54190 and the addendum patch
      to 5.5 (removed one test case as it is no longer valid).
     @ mysql-test/r/select.result
        Removed a part of the test case for bug#48291 since it is not
        valid anymore. The comments for the removed part were actually
        describing a side-effect from the problem addressed by the
        addendum patch for bug #54190.
     @ mysql-test/t/select.test
        Removed a part of the test case for bug#48291 since it is not
        valid anymore. The comments for the removed part were actually
        describing a side-effect from the problem addressed by the
        addendum patch for bug #54190.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)

Noted in 5.5.7, 5.6.1 changelogs.

Row subqueries producing no rows were not handled as UNKNOWN values
in row comparison expressions. 

Setting report to Need Merge pending push to 5.1.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/119565

3259 Tor Didriksen	2010-09-30
      Bug #54190: Comparison to row subquery produces incorrect result

Setting to Need Merge pending push to 5.1.x.

Noted in 5.1.52 changelog.

Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)