MySQL Bugs: #77874: ORDER BY MAX() Returns a row with empty value when no rows exist

Bug #77874	ORDER BY MAX() Returns a row with empty value when no rows exist
Submitted:	29 Jul 2015 15:41	Modified:	6 Mar 2018 17:13
Reporter:	Ian O'Shaughnessy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.28-0	OS:	Ubuntu (Ubuntu 0.12.04.2)
Assigned to:		CPU Architecture:	Any

Description:
When applying MAX() in the ORDER BY of a query that has no group by, or any other grouping function in it, returns a single result even when no results were found by the criteria of the query.

How to repeat:
mysql> SELECT id FROM user WHERE 0=1 ORDER BY MAX(0);
+------+
| id   |
+------+
| NULL |
+------+
1 row in set

mysql> SELECT id FROM `show` WHERE 0=1 ORDER BY NULL;
Empty set

Suggested fix:
When sorting an empty set, no results should be returned.

Hi!

This is an old bug in the version 5.5. Both, latest 5.6 and latest 5.7 return the following error on your query:

RROR 3029 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

Version 5.5 will not be fixed, because its maintenance mode is such that it only receives fixes for the crashing or security bugs.

Not a bug.