Ralf Junker wrote:
In risk of asking the obvious, I wonder if the following division should be
considered correct:
| Query | Result Value | Result Type | OK?
-----------------------------------------------------------
1 | SELECT 5 / 2; | 2 | SQLITE_INTEGER | No?
2 | SELECT 5.0 / 2; | 2.5 | SQLITE_FLOAT | Yes
3 | SELECT 5 / 2.0; | 2.5 | SQLITE_FLOAT | Yes
4 | SELECT 5.0 / 2.0; | 2.5 | SQLITE_FLOAT | Yes
The query in question is Query 1. Is the returned integer result correct or
should it not better return the 2.5 float value instead?
I understand that this probably boils down to integer arithmetics, but the
decimals dropping can cause severe rounding errors if SQLite stores an integer
number without further warning like in:
CREATE TABLE t (a REAL, b REAL);
INSERT INTO t VALUES (5, 2);
Then the query
SELECT a / b FROM t;
returns wrong results, even though both colums are declared as REAL.
In my opinion, any division which can not be represented as an integer should
return a float value.
What do you think?
Regards,
Ralf
Ralf,
The following is from section 6.26 <numeric value expression> of the
SQL:1999 standard.
1) If the declared type of both operands of a dyadic arithmetic operator
is exact numeric, then the
declared type of the result is exact numeric, with precision and scale
determined as follows:
a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is
implementation-defined, and the
scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is
implementation-defined, and the scale is S1+
S2.
d) The precision and scale of the result of division is
implementation-defined.
2) If the declared type of either operand of a dyadic arithmetic
operator is approximate numeric,
then the declared type of the result is approximate numeric. The
precision of the result is
implementation-defined.
As you can see, the result of exact (integer) division is also exact
(integer) with implementation defined precision and scale. The result
of an expression containing approximate (floating point) values is
approximate (floating point). So SQLite is conforming to the SQL standard.
The problem is that in SQLite, individual rows can store a column value
using a data type that is different than the column was declared to
hold. Expressions using these values will then be done using different
math (i.e. integer vs float) for some rows.
This exposes a difference between SQLite and other database engines with
strict data typing. I suspect that your example will behave differently
on mySQL, Oracle, et. al. than it does on SQLite. With strict typing
these engines will always store the values using the declared type and
always use the type of math associated with the declared types.
Effectively 5 and 2 are converted to 5.0 and 2.0 when they are stored
into the row. So the same operation is performed for all rows. This
isn't true for SQLite.
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.
Note, this change will have the side effect of making database files
slightly larger, and may slow down operations with untyped columns
because they default to numeric affinity. All integer values stored in
these columns will be converted to REAL values and all math will be done
with floating point (which may require library calls for some embedded
applications without an FPU). However all this can be avoided by simply
declaring these column to be integer typed. This will set the affinity
correctly, so no conversions are done. It probably makes sense to
declare the type of columns explicitly for increased performance (much
like the integer primary key declaration) as long as the untyped version
still produces the correct results.
Dennis Cote
