Bug #56711 Submitted: Wrong result with ROW(...) IN(SELECT aggregate,...) 10 Sep 2010 9:41 5 May 2018 10:40 Gleb Shchepa Closed None MySQL Server: Optimizer S3 (Non-critical) 5.1+ Any Any

[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:

+# 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] Valeriy 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)

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] MySQL Verification Team
`seen bug 57964 ?`
[6 Feb 2018 18:34] Sveta Smirnova
```Seems to be fixed in 5.7:

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 |                                  0 |
|    2 |                                 0 |                                  0 |
|   11 |                                 1 |                                  1 |
+------+-----------------------------------+------------------------------------+
3 rows in set (0.00 sec)```