Bug #5365 MAX fuction from integer column from empty set returns null insted...

• Submitted: 2 Sep 2004 9:01
• Modified: 2 Sep 2004 11:42
• Reporter: Jan Vitasek
• Status: Not a Bug
• Category: MySQL Server: ISAM storage engine
• Severity: S2 (Serious)
• Version: 4.0.18 - nt
• OS: Windows (windows XP)
• CPU Architecture: Any

[2 Sep 2004 9:01] Jan Vitasek

Description:
MAX function returns "NULL" instead of "empty result set" where maximum is finding from empty row set.

This behaviour is in other functions like MIN, AVG,....
This behaviour contradicts RELATION ALGEBRA I think.

How to repeat:
create table x (id integer);
select MAX(id) from x;

Suggested fix:
select MAX(id) from x;

Query above return:
Qmpty set <0.00 sec>

[2 Sep 2004 11:42] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

"This behaviour contradicts RELATION ALGEBRA I think."

No it doesnt. Your example is not good as you only select MAX() in your query.
Lets try another one:

mysql> select COUNT(*), MAX(id) from x;
+----------+---------+
| COUNT(*) | MAX(id) |
+----------+---------+
|        0 |    NULL |
+----------+---------+
1 row in set (0.00 sec)

I hope now you can see why NULL is the only possible result. This is also according to the SQL standard.

The logic is that Maximum value of an empty set is "Unknown" while "empty set" is different thing.