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

[Kevin Lewis]

Roy Lyseng wrote:
> 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.

I am willing to accept the MYSQL extension of returning NULL for set functions like (MIN,
MAX, SUM and AVG) if the result make no sense, while the regular queries return 'Empty
set'.  But the following is inconsistent for the result of MIN(a).  If MAX(a) and AVG(a)
and SUM(a) each return NULL, then MIN(a) should return NULL also.

mysql> CREATE TABLE t2 (a int(11), key k2 (a));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t2 VALUES (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT MIN(a) FROM t2 WHERE a > NULL;
+--------+
| MIN(a) |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT MAX(a) FROM t2 WHERE a > NULL;
+--------+
| MAX(a) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> SELECT SUM(a) FROM t2 WHERE a > NULL;
+--------+
| SUM(a) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> SELECT AVG(a) FROM t2 WHERE a > NULL;
+--------+
| AVG(a) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(a) FROM t2 WHERE a > NULL;
+----------+
| COUNT(a) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT a FROM t2 WHERE a > NULL;
Empty set (0.00 sec)