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

[Roy Lyseng]

Kevin Lewis wrote:
> 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)

You are right, the above result indicates a bug.

Thanks,
Roy
> 
> 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)