Bug #40674 SELECT ROW = (SELECT sum(outer col) FROM DUAL) ...: incorrect number of rows
Submitted: 12 Nov 2008 19:24 Modified: 14 Apr 2015 12:38
Reporter: Gleb Shchepa Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[12 Nov 2008 19:24] Gleb Shchepa
Description:
SELECT ROW(...) = (SELECT sum function on outer column,... FROM DUAL) always returns result set of 1 row:

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

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT a, ROW(11, 12) = (SELECT MAX(a), 12) FROM t1;
+------+-----------------------------------+
| a    | ROW(11, 12) = (SELECT MAX(a), 12) |
+------+-----------------------------------+
|    1 |                                 0 | 
+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a, ROW(11, 12) = (SELECT MAX(a), 12) FROM t1;
+------+-----------------------------------+
| a    | ROW(11, 12) = (SELECT MAX(a), 12) |
+------+-----------------------------------+
|    1 |                                 0 | 
+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (11);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a, ROW(11, 12) = (SELECT MAX(a), 12) FROM t1;
+------+-----------------------------------+
| a    | ROW(11, 12) = (SELECT MAX(a), 12) |
+------+-----------------------------------+
|    1 |                                 1 | 
+------+-----------------------------------+
1 row in set (0.00 sec)

OTOH if we use an alias of t1.column in the subquery, it returns correct result:

mysql> SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12) FROM t1;
+------+-----------------------------------+
| x    | ROW(11, 12) = (SELECT MAX(x), 12) |
+------+-----------------------------------+
|    1 |                                 0 | 
|    2 |                                 0 | 
|   11 |                                 1 | 
+------+-----------------------------------+
3 rows in set (0.00 sec)

ROW(...) IN subselect is affected too:

mysql> SELECT a, ROW(11, 12) IN (SELECT MAX(a), 12) FROM t1;
+------+------------------------------------+
| a    | ROW(11, 12) IN (SELECT MAX(a), 12) |
+------+------------------------------------+
|    1 |                                  1 | 
+------+------------------------------------+
1 row in set (0.00 sec)

Note: result of IN may be incorrect, see bug #35311.

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);

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

DROP TABLE t1;
[12 Nov 2008 20:23] Miguel Solorzano
Thank you for the bug report. Verified as described.
[1 Jun 2012 6:46] jia liu
I think this can help to find where is the problem:
in MySQL 5.5.19 on general Linux:

root@localhost : liujia 02:44:26> select a as x,(select max(a)) from t1;
+------+-----------------+
| x    | (select max(a)) |
+------+-----------------+
|    1 |              11 |
+------+-----------------+
1 row in set (0.00 sec)

root@localhost : liujia 02:44:31> select a as x,(select max(x)) from t1;
+------+-----------------+
| x    | (select max(x)) |
+------+-----------------+
|    1 |               1 |
|    2 |               2 |
|   11 |              11 |
+------+-----------------+

And the result of min(),sum(),avg() also like this.

How to repeat:(sorry to just copy Gleb Shchepa's work)
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (11);

select a as x,(select max(a)) from t1;
select a as x,(select max(x)) from t1;

DROP TABLE t1;
[1 Jun 2012 6:58] jia liu
Just find that "select a,max(a) from t1;" only return one column too.
[14 Apr 2015 12:38] Roy Lyseng
This is not a bug:

MAX(a) refers to a column from the outer query, so aggregation is in the outer query. Hence, only one row should be returned.

However, the result when referring the alias from the SELECT list is more questionable. But then the whole idea of referring aliases to select list items in a subquery in the select list is also questionable.