MySQL Bugs: #12882: min/max inconsistent on empty table

Bug #12882	min/max inconsistent on empty table
Submitted:	30 Aug 2005 17:11	Modified:	23 Sep 2005 20:03
Reporter:	Martin Friebe (Gold Quality Contributor)
Status:	Closed
Category:	Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.14, any	OS:	FreeBSD (freebsd, Windows)
Assigned to:	Timour Katchaounov	Target Version:

Description:
I have checked the documentation on
http://dev.mysql.com/doc/mysql/en/group-by-functions.html

It does not describe, if min/max are expected to return null, or a value, on an empty
set.

The examples below, show that min/max on a constant, return sometime the vqalue, sometime
null.

Should that be consistent?

How to repeat:
create table t1 (a int); #empty table

select max(1) from DUAL;
+--------+
| max(1) |
+--------+
|   NULL |
+--------+
1 row in set (0.01 sec)

select max(1) from t1;
+--------+
| max(1) |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Suggested fix:
- decide, if consitency, is wanted.

According to the SQL-2003 standard (draft):

"If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other
aggregate function is the null value."

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.28 sec)

mysql> select max(1) from t1;
+--------+
| max(1) |
+--------+
|      1 |
+--------+
1 row in set (0.01 sec)

mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

mysql> select max(1);
+--------+
| max(1) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

So, the result of "select max(1) from t1;" in this case is a bug.

I'll update the group-by functions page to indicate what
each function returns when there are no matching rows,
for those descriptions that don't already say.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29842

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29905

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30007

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30132

Fixed in 4.1.15.

Noted in 4.1.15 changelog.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9520