Bug #3376 AVG(constant) returns constant if no rows in result set

• Submitted: 2 Apr 2004 15:26
• Modified: 29 Apr 2004 19:02
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.2-alpha
• OS: Linux (SuSE 8.2)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[2 Apr 2004 15:26] Peter Gulutzan

Description:
AVG() should return NULL if there are no rows. For example, with an empty set t, "SELECT 
AVG(column1) FROM t" returns NULL. But "SELECT AVG(constant) FROM t" doesn't return 
NULL. 
 

How to repeat:
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.27 sec) 
 
mysql> select avg(s1) from t; 
+---------+ 
| avg(s1) | 
+---------+ 
|    NULL | 
+---------+ 
1 row in set (0.00 sec) 
 
mysql> select avg(5) from t; 
+--------+ 
| avg(5) | 
+--------+ 
| 5.0000 | 
+--------+ 
1 row in set (0.00 sec) 
 
mysql> select sum(5) from t; 
+--------+ 
| sum(5) | 
+--------+ 
|   NULL | 
+--------+ 
1 row in set (0.00 sec)

[9 Apr 2004 2:27] Ramil Kalimullin

ChangeSet
  1.1784 04/04/09 14:12:10 [e-mail] +3 -0
  A fix. (Bug #3376: AVG(constant) returns constant if no rows in result set)

[29 Apr 2004 19:02] Michael Widenius

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix was pushed in 4.0 tree some days ago and fix will be in 4.0.19 and 4.1.2