Icon View Incident Report

Serious Serious
Reported By: Kunikazu Okada
Reported On: 4/1/2004
For: Version 4.05 Build 1
# 1634 SQL COUNT() Aggregate Function Returning NULL Instead of 0 When All Column Values Contain NULLs

I've found there's a difference between DBISAM 3.x and DBISAM 4.x in COUNTing NULL values.

DBISAM 3 returns 0 for "SELECT COUNT(NULL) from SomeTable", but DBISAM4 returns NULL.

SELECT Master.Category, Count(Detail.Id)
FROM Master 
  LEFT OUTER JOIN Detail ON (Master.Category = Detail.Category)
  GROUP BY Master.Category

returns a result like this .
Category    Count
        A         1
        B         2
        C      NULL

where DBISAM 3.x's result was as follows:
Category    Count
        A         1
        B         2
        C         0

Comments Comments and Workarounds
The resultant value retrieved is not affected by this result, however the visual display or any code that specifically checks for a NULL instead of just retrieving the value may have issues with this behavior. In addition, the AVG() and STDDEV() functions displayed a similar yet opposite issue - they were returning a 0 when they should have been returning a NULL for the same situation.

Resolution Resolution
Fixed Problem on 4/2/2004 in version 4.06 build 1