Hello Dennis & Jay, thanks for your detailed answers. I do understand your arguments and they make good sense for typed DB engines, but for the case of SQLite3 I dare to differ.
>This can be fixed by checking the column affinity for a value when it is >stored. If an integer value is being stored in a column with numeric affinity, >then store the value as a REAL value rather than as an INTEGER value. This >will perform the same conversion that the other engines do, and hence produce >the same result when the division operation is performed later. Unfortunately, this is not true for SQLite3. If you execute the following SQL commands CREATE TABLE t (a REAL, b REAL); INSERT INTO t VALUES (5, 2); both values are stored as INTEGER and not as REAL as one would expect by the column affinity. In fact, this behaviour is intentional and is stated in the help as a feature to minimize storage space for INTEGER numbers. To force storing numbers as REALs SQLite3 requires to use data binding or to rewrite the 2nd command above like this: INSERT INTO t VALUES (5.0, 2.0); In other words, SQLite3 requires REALs to be explicitly written as such. Many users are certainly not aware of this requirement. They expect REALs to be stored as REALs just like other DB-engines do if a column is defined as such. However, SQLite3 behaves differently and this can * result in wrong calculations / different than intended. * lead to data errors when importing SQL scripts from other DBMS. On the other INTEGER side, SQLite3 again behaves differently: CREATE TABLE i (a INTEGER, b INTEGER); INSERT INTO i VALUES (5, 2); INSERT INTO i VALUES (5.0, 2.0); Both INSERTs above store the numbers as INTEGERs, even those explicitly marked as REALs by adding the '.0' decimal. Another problem shows when calculations are not performed on colums with type affinity but just on plain numbers like in SELECT 5 / 2; What exactly is intended? From the findings above, both numbers might be seen as INTEGERs, but if '5.0' is interpreted as an INTEGER above, '5' might just as well be a REAL. Arguing from "common sense" I would prefer 5 / 2 to return 2.5 rather than 2. If the result cannot be represented with exact numeric, I would rather want it to be aproximate only. I can not overcome the feeling that exact numeric is sometimes simply wrong, especially with the whole family of financial calculations. Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' on MySql, SQLServer, and others? Regards, Ralf
