MySQL Bugs: #40674: SELECT ROW = (SELECT sum(outer col) FROM DUAL) ...: incorrect number of rows

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

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;

Thank you for the bug report. Verified as described.

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;

Just find that "select a,max(a) from t1;" only return one column too.

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.