Probably the best solution would be to have the standard implementation
activated by a PRAGMA command. This way, you don't steal functionality
from people who want non-standard implementation and you also don't risk
to break compatibility with existing software over SQLite (you have
backward compatibity).
On Wed, 8 Feb 2006, Dennis Cote wrote:
In Summary, we have 4 database engines that appear to follow the standard,
and 6 that do not.
Standard:
MS SQL 2000
Firebird 1.5
MS SQL 2005
DB2 8.2
Non-standard:
MS Access
PostgreSQL 7.3
PostgreSQL 8.1.2
MySQL 5.0
Informix 7.31
Oracle 10.1
It is also interesting to note that MySQL seems to return different precision
for the two averages for some reason.
I would suggest that SQLite should be modified to comply with the standard
and return an integer value for the average of a column declared as integer.
This will eliminate the inconsistency that Richard note between avg() and
sum()/count(). This would not change the behavior for columns declared are
real, or columns that are untyped.
Do any of you have, or know of, an application that would be adversely
affected if this change is made to SQLite?
Dennis Cote
