| Bug #56711 | Wrong result with ROW(...) IN(SELECT aggregate,...) | ||
|---|---|---|---|
| Submitted: | 10 Sep 2010 9:41 | Modified: | 5 May 2018 10:40 |
| Reporter: | Gleb Shchepa | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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)
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]
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)
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 | +------+-----------------------------------+------------------------------------+