why does this query return a row with nulls

Question

compare

SELECT distinct u_id,timestamp as time
FROM my_table;

and

SELECT distinct u_id,max(timestamp) as time
FROM my_table;

When my table has no rows at all (or if I add a where clause that matches no rows):

The former returns an empty results set (which is what I expect) while the later returns a single row that has null as the value for both its fields.

Can someone please explain to me why does the second one acts as it does?

Answer 1

MySQL documentation says

MAX() returns NULL if there were no matching rows.

And if you have no data then it just returns both values as NULL.

If you want the second query return the empty resultset too, then you must filter out the NULL values for example with HAVING clause that you can use with aggregate functions:

SELECT DISTINCT u_id, MAX(timestamp) as time FROM my_table GROUP BY u_id HAVING time IS NOT NULL;

Answer 2

This actual answer to this question is quite complicated to explain, for me anyhow :) Headline points: SQL does not support aggregate operators as found in the relational model, rather merely supports a special case of summarization. Further, because SQL has but one data structure -- the table -- SQL aggregate operator invocations (loosely speaking) must appear as part of some table expression, hence why your second table returns a 'dummy' single row.

For a fuller/better explanation, see SQL and Relational Theory: How to Accurate SQL Code - C. J. Date (2009), section 7.5. Aggregate Operators.