Bug #56711 Wrong result with ROW(...) IN(SELECT aggregate,...)
Submitted: 10 Sep 2010 9:41 Modified: 10 Sep 2010 9:49
Reporter: Gleb Shchepa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to:
Triage: Triaged: D2 (Serious)

[10 Sep 2010 9:41] Gleb Shchepa
Description:
In the query

  SELECT
    a AS x,
    ROW(11, 12) = (SELECT MAX(x), 12),
    ROW(11, 12) IN (SELECT MAX(x), 12)
  FROM t1;

the 2nd and the 3rd columns should return the same result, but they don't.

There is a similar bug #39069, but its old fix doesn't cover the case with aggregate functions (at the same time its test suite contains "regression" test for aggregates
that returns wrong result).

Original commentary from the bug #39069 entry:

10 Sep 7:18] Timour Katchaounov
This patch for this bug contains wrong result:
Instead of the following result:

+# 2nd and 3rd columns should be same for t1.a == 11 only
+SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12)
FROM t1;
+x	ROW(11, 12) = (SELECT MAX(x), 12)	ROW(11, 12) IN (SELECT MAX(x), 12)
+1	0	1
+2	0	1
+11	1	1

The result should be:
1  0 0
2  0 0
11 1 1

Therefore the patch is either wrong or incomplete.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);
SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;

Wrong result:

+------+-----------------------------------+------------------------------------+
| x    | ROW(11, 12) = (SELECT MAX(x), 12) | ROW(11, 12) IN (SELECT MAX(x), 12) |
+------+-----------------------------------+------------------------------------+
|    1 |                                 0 |                                  1 | 
|    2 |                                 0 |                                  1 | 
|   11 |                                 1 |                                  1 | 
+------+-----------------------------------+------------------------------------+
3 rows in set (0.00 sec)

Expected result:
+------+-----------------------------------+------------------------------------+
| x    | ROW(11, 12) = (SELECT MAX(x), 12) | ROW(11, 12) IN (SELECT MAX(x), 12) |
+------+-----------------------------------+------------------------------------+
|    1 |                                 0 |                                  0 | 
|    2 |                                 0 |                                  0 | 
|   11 |                                 1 |                                  1 | 
+------+-----------------------------------+------------------------------------+
[10 Sep 2010 9:49] Valerii Kravchuk
Verified as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.1.50-community MySQL Community Server (GPL)

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> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

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

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

mysql> SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT
MAX(x), 12) FROM
    -> t1;
+------+-----------------------------------+------------------------------------
+
| x    | ROW(11, 12) = (SELECT MAX(x), 12) | ROW(11, 12) IN (SELECT MAX(x), 12)
|
+------+-----------------------------------+------------------------------------
+
|    1 |                                 0 |                                  1
|
|    2 |                                 0 |                                  1
|
|   11 |                                 1 |                                  1
|
+------+-----------------------------------+------------------------------------
+
3 rows in set (0.06 sec)
[23 Nov 2010 9:18] Shane Bester
seen bug 57964 ?