Hello Jay Sprenkle, >I guess if you're going to use sqlite you're going to have to >force typing explicitly if you're doing math with the sql engine.
Quite right, but even with explicit typing there are cases where SQLite3 can not be instructed to store REAL numbers as REALs: C:\>sqlite3 num.db3 SQLite version 3.2.1 Enter ".help" for instructions sqlite> CREATE TABLE i (a INTEGER, b INTEGER); sqlite> INSERT INTO i VALUES (5, 2); sqlite> SELECT a / b FROM i; 2 sqlite> INSERT INTO i VALUES (5.0, 2.0); sqlite> SELECT a / b FROM i; 2 2 Even though the 2nd insert explicitly types 5.0 and 2.0 as REALs, they are stored as INTEGERs internally and resuls in incorrect calculations (the very last number should be 2.5 and not 2). Summming up: Observations show that SQLite3 behaves inconsistent when storing and/or calculating numbers as INTEGERs or REALs. The point I am personally worried about is not so much the storage side of things but the calculation errors resulting from it. I would like to propose the resolve this inconsistency as follows: 1. If two REALs are divided, the result should be a REAL. This is the current behaviour of SQLite3. 2. If two INTEGERs are divided, the result should be INTEGER only if it can be represented as an INTEGER (i.e. if a % b = 0). 3. If the division of INTEGERs can not be stored as an INTEGER (i.e. if a % b != 0), the result should be returned as a REAL. I do understand that 3. implies a change in numeric type, but in oder to perform calculations with SQL the type change seems unavoidable, at least as long as SQLite3 implements only 2 of the 3 numeric types suggested in this thread. Regards, Ralf
