[MDEV-7029] AVG() without group by on non matching row returns NULL Created: 2014-11-05  Updated: 2014-11-08  Resolved: 2014-11-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: jocelyn fournier Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Hi,

I wonder if it's a bug or a feature, but I find this behaviour quite disturbing.
If there's no join and if AVG() is used without GROUP BY and with a WHERE clause on a non matching row, it still returns a result.

e.g. :

MariaDB [(none)]> SELECT AVG(a), b FROM (SELECT 1 as a, 1 as b) a WHERE b=10;
 
+--------+---+
 
| AVG(a) | b |
 
+--------+---+
 
|   NULL | NULL |
 
+--------+---+

However, if a GROUP BY is added, no rows are returned :

SELECT AVG(a), b FROM (SELECT 1 as a, 1 as b) a WHERE b=10 GROUP BY a;

Empty set (0.00 sec)

Thanks and regards,
Jocelyn Fournier

 Comments   
Comment by Sergei Golubchik [ 2014-11-08 ]

Quoting the standard (ISO/IEC 9075-2:2003, 4.15 Data analysis operations):

If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the
null value.

That explains that NULL is correct. Even more:

MariaDB [test]> SELECT AVG(a), MAX(a), COUNT(a), SUM(a) FROM (SELECT 1 as a, 1 as b) a WHERE b=10;
 
+--------+--------+----------+--------+
 
| AVG(a) | MAX(a) | COUNT(a) | SUM(a) |
 
+--------+--------+----------+--------+
 
|   NULL |   NULL |        0 |   NULL |
 
+--------+--------+----------+--------+
 
1 row in set (0.00 sec)

But if you add GROUP BY you should get one row per group, as there are no groups you get no rows.

Generated at Thu Feb 08 07:16:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.