Bug #4665 Views: NULL value in base table causes SELECT from view to fail
Submitted: 21 Jul 2004 2:28 Modified: 25 Aug 2004 9:02
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.1-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[21 Jul 2004 2:28] Trudy Pelzer 
Description:
If one creates a view (e.g. v1) with an aggregate function of a column of its underlying base table (e.g. t1), and an NULL is inserted into that table, then a subsequent SELECT from the view returns this error message:
ERROR 1111 (HY000): Invalid use of group function

In the example below, a SELECT from the view correctly returns "1" after the first INSERT, but fails after the second INSERT (when the answer should be "2").

How to repeat:
create table t1 (col1 int);
insert into t1 values (1);
create view v1 as select count(*) from t1;
insert into t1 values (null);
select * from v1;
ERROR 1111 (HY000): Invalid use of group function
[23 Jul 2004 3:55] MySQL Verification Team 
Verified against latest bk source.
[24 Aug 2004 19:33] Oleksandr Byelkin 
ChangeSet 
  1.1731 04/08/24 20:29:44 bell@sanja.is.com.ua +3 -0 
  fixed aggregate function processing in VIEWs (BUG#4665)
[25 Aug 2004 9:02] Oleksandr Byelkin 
Thank you for bugreport! Bugfix is pushed into source repository.