Re: bzr commit into mysql-5.1-bugteam branch (v.narayanan:3197) Bug#47762

[Roy Lyseng]

Kevin Lewis wrote:

> If
>   SELECT a FROM t2 WHERE a > NULL; fails with Empty set (since a > NULL 
> is meaningless), then     SELECT MIN(a) FROM t2 WHERE a > NULL;
> should also fail.  I don't know if you want to open a new bug for this, 
> or continue working on this with the same bug.  But this is still a 
> problem, I think.

Hi Kevin!

This is actually correct and in compliance with the standard. Think of

SELECT COUNT(*) FROM t2 WHERE a > NULL;

which should return zero if there are no matching rows. The results of the other 
set functions (MIN, MAX, SUM and AVG) are all NULL if there are no matching 
rows, or there are no non-NULL arguments to the functions, and these results can 
actually provide value to the application.

If you run a query with set functions in the SELECT clause, and the query is 
non-grouped (no GROUP BY and no HAVING), the query shall return a single row.

Another story is that the comparison "a > NULL" does not make sense, because the 
comparison is always UNKNOWN. This is a MySQL extension.

Thanks,
Roy