Bug #5146 Views: avg of a count of a column won't work

• Submitted: 23 Aug 2004 0:24
• Modified: 27 Aug 2004 8:33
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL version = 5.0.2-alpha-debug
• OS: Linux (SuSE 8.2)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[23 Aug 2004 0:24] Peter Gulutzan

Description:
An aggregate function on an aggregate function won't 
work. MySQL lets me say 
"select avg(col1) from (select count(col1) as col1 from t) as t;" 
The answer is zero if there are no rows in table t. 
The point is, MySQL won't let me do that using a view. 
I get an error return that says: 
Reference 'name' not supported (reference on group function) 
 

How to repeat:
mysql> create table tg (tg_column bigint); 
Query OK, 0 rows affected (1.20 sec) 
 
mysql> create view vg as select count(tg_column) as vg_column from tg; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from vg; 
+-----------+ 
| vg_column | 
+-----------+ 
|         0 | 
+-----------+ 
1 row in set (0.00 sec) 
 
mysql> select avg(vg_column) from vg; 
ERROR 1247 (42S22): Reference 'vg_column' not supported (reference on group function)

[26 Aug 2004 13:36] Oleksandr Byelkin

ChangeSet 
  1.1734 04/08/26 14:34:56 [e-mail] +5 -0 
  fixed updateability VIEW detection (Bug#5146) 
  fixed other Item_ref methods where result_field involved

[27 Aug 2004 8:33] Oleksandr Byelkin

Thank you for bug report!  Bug is fixed, patch is pushed into our source repository.