Preston Zaugg wrote:
I would NOT be in favor of this change. As was discussed in the
original post this would be NON-STANDARD behavior. The SQL-99 spec
says that integer math remains an integer.
The only time I would like an integer to return a "real" result is if
that integer is stored in a field of type “real”, then all operations
on that number should return a "real" result.
I understand the reasons for "int"s being stored as an "int" in a
"real" column, but the change I would prefer is for it to act as a
"real" if stored in a "real" column, otherwise it should act as it
currently does.
I agree with Preston. SQLite should follow the SQL standard whenever
possible.
Ralf brought up two separate but related issues in his original post:
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.
The first set of select statements are doing arithmetic using literal
constant values. Each of these has a type, either real or integer.
SQLite is doing the arithmetic using these values according to the
standard. It produces an real (or approximate result) if either argument
is real, and an integer (or exact) result if both arguments are integer.
The semantics of arithmetic are different in many scripting languages,
but those languages are following a different standard. The SQL standard
specifies how this should be done and SQLite is doing it that way now.
It shouldn't be changed.
The second issue is demonstrated by the last last three statements.
CREATE TABLE t (a REAL, b REAL);
INSERT INTO t VALUES (5, 2);
SELECT a / b FROM t;
Here he has explicitly declared the columns a and b to be of type real.
He then stores integer literal values into those columns. This is where
the problem occurs, not during the division in the select statement.
SQL is a typed language. SQLite was originally an untyped implementation
of SQL. In version 3 SQLite was changed to introduce stronger data
typing, while still trying to maintain compatibility with its previous
untyped versions. It does this very well in most cases. SQLite uses
manifest typing, where each data value has its own associated data type
whereas the standard assumes each column has a data type. In a few cases
SQLite bumps into areas where this implementation produces non-standard
behavior. This is one of them.
To produce standard behavior, integer values stored into columns of type
real should be converted to real values.
If SQLite did this, then the select statement would be doing arithmetic
on two real values and it would produce a real result. This is what the
user expects because he explicitly said that these columns should
contain real values. Currently SQLite is giving more weight to the fact
that he didn't put a decimal point on the literal values than it is
giving to the fact that he explicitly said the columns will hold real data.
This change would only affect columns which are explicitly typed as
real. Other columns that are untyped would continue to be able to hold
any type of value as they do now. In all likelihood, any users that are
explicitly declaring the data type of a column will be intending to use
it to hold data of that type, and will only insert data of that type (or
values hey expect to be converted to that type as in this case). Others
who are using the typeless feature of SQLite will probably not declared
a column data type and will get the same behavior they have now.
Similar arguments can be applied to values inserted into columns that
are declared to be type integer. SQLite should probably convert real
valued data inserted into integer columns into integer values. This
would ensure that the sum of an integer column is always an integer for
example.
It would also eliminate the situation we have now where real values
stored into integer columns are accepted without complaint except when
the integer column is also a primary key. In this case we get a
"datatype mismatch" error for the primary key column but not for other
columns with the same declared type. Only the last of the following
inserts causes an error.
CREATE TABLE t (a INTEGER, b INTEGER, PRIMARY KEY(a));
INSERT INTO t VALUES (5, 2);
INSERT INTO t VALUES (6, 2.1);
INSERT INTO t VALUES (7.1, 2);
I think this is change should also be made to ensure that explicitly
declared column types are honored by SQLite.
I would also like to thank DRH for taking the time to request and
evaluate input from the group before making this decision and changing
SQLite's behavior.
Dennis Cote
