MySQL Bugs: #50693: Incorrect behaviour for aggregate/min/max functions in subquery with no clause

Bug #50693	Incorrect behaviour for aggregate/min/max functions in subquery with no clause
Submitted:	28 Jan 2010 16:15	Modified:	1 Feb 2010 5:09
Reporter:	bubi bubi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:		OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
On mysql >5.0.27, when selecting a subquery with no FROM clause and MIN/MAX functions, the results are incorrect.

How to repeat:
use test;
create table test1(number INT);
insert into test1 values(10);
select (select min(number)) from test1

It returns 10

Suggested fix:
It should return NULL, like the <5.0.27 versions. Or at least, it should'nt be a valid query

Why do you think that current behavior is wrong? 

MySQL supports SELECT without FROM clause, if only it is clear how to get data it refers to. And this (SELECT min(number)) is a subquery, and thus can refer to any columns of table(s) from outer (main) query:

77-52-24-143:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test1(number INT);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(10);
Query OK, 1 row affected (0.00 sec)

mysql> select (select min(number)) from test1;
+----------------------+
| (select min(number)) |
+----------------------+
|                   10 | 
+----------------------+
1 row in set (0.00 sec)

mysql> explain select (select min(number)) from test1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: test1
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
2 rows in set (0.01 sec)

I think that this behavior is a bug because in more complex queries it will lead to really erroneous results.

Let me show an example.

mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table test (id INT, groupid INT, somevalue INT);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1,1,10),(2,2,20),(3,1,5),(4,2,15);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+---------+-----------+
| id   | groupid | somevalue |
+------+---------+-----------+
|    1 |       1 |        10 |
|    2 |       2 |        20 |
|    3 |       1 |         5 |
|    4 |       2 |        15 |
+------+---------+-----------+
4 rows in set (0.00 sec)

mysql> select (select id from test as t where t.somevalue=min(test.somevalue) and groupid=t.groupid limit 1) as id,min(groupid),somevalue from test group by groupid;
+------+--------------+-----------+
| id   | min(groupid) | somevalue |
+------+--------------+-----------+
|    3 |            1 |        10 |
|    4 |            2 |        20 |
+------+--------------+-----------+
2 rows in set (0.00 sec)

A user designed this query for extrating the min values of groupid. The resultset is strangely correct
I might be wrong, but i think that this "where t.somevalue=min(test.somevalue)" should be at least an illegal construct.
In mysql < 5.0.27, the construct min(test.somevalue) returns null, and i think this should be the correct behaviour... MySQL should not know what is the min value in a non grouped row..
If not in a subquery, a construct like this will  return correctly "illegal use of group function"

btw, look at another strange behaviour from the query from my first post.

mysql> explain extended select(select min(number)) from test1;
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type        | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY            | test1 | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+--------------------+-------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                  |
+-------+------+------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'test.test1.number' of SELECT #2 was resolved in SELECT #1            |
| Note  | 1003 | select (select min('10') AS `min(number)`) AS `(select min(number))` from `test`.`test1` |
+-------+------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

why mysql converted select min(number) in "select min('10')"? If you add another row in table, it will not convert it anymore in this mode.

Again, this is NOT a bug. Read http://dev.mysql.com/doc/refman/5.0/en/correlated-subqueries.html:

"Aggregate functions in correlated subqueries may contain outer references, provided the function contains nothing but outer references, and provided the function is not contained in another function or expression."