MySQL Bugs: #603: strange behaviour with MAX without GROUP BY

Bug #603	strange behaviour with MAX without GROUP BY
Submitted:	6 Jun 2003 4:35	Modified:	6 Jun 2003 7:21
Reporter:	Martijn Korse	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
supose we have a table users with two fields: id and name
this table contains 3 values
1 | marty
2 | martin
3 | martijn

SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM users
will give back 1 row saying that max_id is 3 and min_id is 1

However, if we alter the query a bit to this one:
SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM users WHERE id > 4
it will still give back 1 row, but with NULL as the value for max_id and min_id

This is very annoying and not right imho. It should give back 0 rows. If you're using mysql in combination with a script language like php it can mess up the script since quite frequently you use mysql_num_rows in order to determine what to do next.

A workaround for this problem seems to be to use GROUP BY in the query.
This query:
SELECT MAX(id) AS max_id, MIN(id) AS min_id, '1' FROM users WHERE id>4 GROUP BY '1'
will yield the expected result: 0 Rows

How to repeat:
### DUMP ###
#
# Table structure for table 'users'
#

CREATE TABLE users (
  id tinyint(3) unsigned NOT NULL auto_increment,
  name varchar(20) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
) TYPE=MyISAM;

#
# Dumping data for table 'users'
#

INSERT INTO users VALUES("1", "marty");
INSERT INTO users VALUES("2", "martin");
INSERT INTO users VALUES("3", "martijn");

### END OF DUMP ###

And then try these queries:
SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM users;

SELECT MAX(id) AS max_id, MIN(id) AS min_id FROM users WHERE id > 4;

SELECT MAX(id) AS max_id, MIN(id) AS min_id, '1' FROM users WHERE id>4 GROUP BY '1';

Suggested fix:
Make it work without having to use GROUP BY ;-)

This behavior is as specified by the SQL standard. MIN, MAX and SUM are required to return NULL if all of the rows of the column specified by MIN, MAX or SUM are NULL, or if _no_ rows are returned.

You will find this same behavior in SQL Server, Oracle or any other database that implements these funtions in a way that follows the SQL standard.

> This behavior is as specified by the SQL standard.
My Apologies, i was unaware of that

> MIN, MAX and SUM are required to return NULL if all of the rows of the column
> specified by MIN, MAX or SUM are NULL, or if _no_ rows are returned.

Isn't it a bug then that the last query i gave:

SELECT MAX(id) AS max_id, MIN(id) AS min_id, '1' FROM users WHERE id>4 GROUP BY '1';

doesn't return any rows?
As far as i understand what you just said, it shouldn't be treated any different; I don't see how the GROUP BY changes the logic in such a way that mysql doesn't need to return a row with NULL for both fields