
12

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


In the C language, which sqlite is written in, performs math this way:
If mixing types the operands are converted to the most precise type and the
operation
evaluated.
=> SELECT 5 / 2;
is: integer operation integer
the most precise type is integer, so it's strictly integer math.
evaluated as integer / integer = integer result
=> SELECT 5 / 2.0;
is: integer operation float
the most precise type is float
evaluated as float / float = float result
It's performing as I expected based on my knowledge of C.
On 9/29/05, Ralf Junker < [hidden email]> 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?
>


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
implementationdefined, and the
scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is
implementationdefined, and the scale is S1+
S2.
d) The precision and scale of the result of division is
implementationdefined.
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
implementationdefined.
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


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 DBengines 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


>Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' on MySql, SQLServer, and others?
>
>
>
devl=# SELECT 5 / 2;
?column?

2
(1 row)
devl=# select 5.0 / 2;
?column?

2.5000000000000000
(1 row)
devl=# select 5 / 2.0;
?column?

2.5000000000000000
(1 row)
devl=# select version();
version

PostgreSQL 8.0.3 on i386portbldfreebsd5.4, compiled by GCC cc (GCC)
3.4.2 [FreeBSD] 20040728
(1 row)


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?
>
>
I am following the rest of this thread, but just wanted to add that the
following may be useful as a workaround for your situation.
SELECT ROUND(5) / 2
2.5
Regards
Lawrence


At 11:07 AM 0600 9/29/05, Dennis Cote wrote:
>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.
You misunderstand what 'exact' means; 'exact' != 'integer', but
rather 'integer is conceptually a subset of exact. An exact value
can be fractional, such as '1.32', and yet not be floating point.
Therefore, if the columns were defined as integers, then it is
reasonable for the result to be an integer; however, if the columns
were defined as exact fractionals, then the result should be an exact
fractional, '2.5'.
When it comes to concept and storage, there are 3 distinct types of
numbers, which the SQL standard gives distinct names:
1. INTEGER  and big/little and sign/unsign variations  An exactly
remembered whole number that can be stored and manipulated compactly
in base2 binary as is native for computers. Any value that will
always be whole is optimally stored this way.
2. DECIMAL(p,s)  An exactly remembered fractional number that is
typically stored in a form akin to text, such as one byte per base10
digit. These can effectively store arbitrarily large numbers of any
length and precision without loss of detail, although doing math with
them may be slower. For example, if you store '2.5' in one, then
'2.5' is actually stored.
3. FLOAT(p)  and double variation  An approximately remembered
number that is stored and manipulated compactly in base2 floating
point. Increasing the precision will only better approximate a
value, but the exact value is lost, though math with these is fast.
For example, if you store '2.5' in one, then either '2.499999999' or
'2.500000001' is actually stored.
SQLite should recognize the above 3 numerical types as being
distinct, and do the correct actions with math involving any of them.
 Darren Duncan


On 9/29/05, Ralf Junker < [hidden email]> wrote:
>
> 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.
>
As per almost every interpreted language standard
5 is an integer.
5.0 is a float.
As you proved with your own example 5.0 is not interpreted as an integer.


On 9/29/05, Ralf Junker < [hidden email]> wrote:
>
> 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.
>
D:\temp\convention>sqlite3 num.db
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> CREATE TABLE t (a REAL, b REAL);
sqlite> INSERT INTO t VALUES (5, 2);
sqlite> select a/b from t;
2
sqlite> insert into t values(5.0,2);
sqlite> select a/b from t;
2
2.5
sqlite>
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.


MySQL 4.0.24standard:
#SELECT 5 /2
5/2
2.50
Dennis Jenkins wrote:
>
>> Just out of curiosity: Maybe someone can report the result of 'SELECT
>> 5 / 2;' on MySql, SQLServer, and others?
>>
>>
>>
> devl=# SELECT 5 / 2;
> ?column?
> 
> 2
> (1 row)
>
> devl=# select 5.0 / 2;
> ?column?
> 
> 2.5000000000000000
> (1 row)
>
> devl=# select 5 / 2.0;
> ?column?
> 
> 2.5000000000000000
> (1 row)
>
> devl=# select version();
> version
> 
>
> PostgreSQL 8.0.3 on i386portbldfreebsd5.4, compiled by GCC cc (GCC)
> 3.4.2 [FreeBSD] 20040728
> (1 row)
>


On 9/29/05, Darren Duncan < [hidden email]> wrote:
>
> At 11:07 AM 0600 9/29/05, Dennis Cote wrote:
> >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.
>
> You misunderstand what 'exact' means; 'exact' != 'integer', but
> rather 'integer is conceptually a subset of exact. An exact value
> can be fractional, such as '1.32', and yet not be floating point.
True mathematically but not in practice. All fractional numbers are
represented as floating point, therefore there are no exact fractional
numbers on any SQL implementations I am aware of. It's possible
to do, but I've never seen it done.


On 9/29/05, Jay Sprenkle < [hidden email]> wrote:
>
> True mathematically but not in practice. All fractional numbers are
> represented as floating point, therefore there are no exact fractional
> numbers on any SQL implementations I am aware of. It's possible
> to do, but I've never seen it done.
>
> Oh, sorry, that's wrong. I haven't seen it done on SQL servers.
I forgot about cobol.


> 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.
I think, it can be fixed by introducing special internal data type:
REAL_STORED_AS_INTEGER
If table column have REAL type, but inserted value is INTEGER, then
inserted type becomes REAL_STORED_AS_INTEGER and binary representation
keep small. When column value is extracted from SQLite or used in
SQL expression, then value dynamically converted to REAL.
That is, the user of database never see values of this type directly.
Such values stored as INTEGER, but can be seen only as REAL.
Best regards,
Alexander mailto: [hidden email]


SQL*Plus: Release 8.1.7.0.0  Production on Thu Sep 29 16:03:55 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0  Production
With the Partitioning option
JServer Release 8.1.6.0.0  Production
SQL> select 5/2 from dual;
5/2

2.5
SQL>
 Original Message 
From: Dennis Jenkins < [hidden email]>
To: [hidden email]
Sent: Thursday, September 29, 2005 13:14:48
Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
>Just out of curiosity: Maybe someone can report the result of 'SELECT 5 / 2;' on MySql, SQLServer, and others?
>
>
>
devl=# SELECT 5 / 2;
?column?

2
(1 row)
devl=# select 5.0 / 2;
?column?

2.5000000000000000
(1 row)
devl=# select 5 / 2.0;
?column?

2.5000000000000000
(1 row)
devl=# select version();
version

PostgreSQL 8.0.3 on i386portbldfreebsd5.4, compiled by GCC cc (GCC)
3.4.2 [FreeBSD] 20040728
(1 row)


On 9/29/05, Darren Duncan < [hidden email]> wrote:
>
> At 11:07 AM 0600 9/29/05, Dennis Cote wrote:
> >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.
>
> You misunderstand what 'exact' means; 'exact' != 'integer', but
> rather 'integer is conceptually a subset of exact. An exact value
> can be fractional, such as '1.32', and yet not be floating point.
Actually I do understand that, but no offence is taken. I was simply
restricting the discussion to the two numeric types thet SQLite implements,
integer and real (or floating point).
Therefore, if the columns were defined as integers, then it is
> reasonable for the result to be an integer; however, if the columns
> were defined as exact fractionals, then the result should be an exact
> fractional, '2.5'.
>
> When it comes to concept and storage, there are 3 distinct types of
> numbers, which the SQL standard gives distinct names:
>
> 1. INTEGER  and big/little and sign/unsign variations  An exactly
> remembered whole number that can be stored and manipulated compactly
> in base2 binary as is native for computers. Any value that will
> always be whole is optimally stored this way.
>
> 2. DECIMAL(p,s)  An exactly remembered fractional number that is
> typically stored in a form akin to text, such as one byte per base10
> digit. These can effectively store arbitrarily large numbers of any
> length and precision without loss of detail, although doing math with
> them may be slower. For example, if you store '2.5' in one, then
> '2.5' is actually stored.
This can also be done with fixed point fractions. This type of scaled
integer with a fractional component are often used by integer DSPs. They
store a value in an integer with a fixed binary point (rather than a
floating binary point as implied by the name floating point). For example, a
32 bit integer can be viewed as a signed 16 bit integer bit and 16 fraction
bits. It has a range of signed values from 32767.999984741 to 32768 with a
resolution of 0.000015259.
These values can also be stored as arbitrary precision integers, which store
multiple integers words to represent the value which can again be
interpreted as a fixed point fraction, or as BCD (binary coded decimal)
values which store 2 digits per byte.
3. FLOAT(p)  and double variation  An approximately remembered
> number that is stored and manipulated compactly in base2 floating
> point. Increasing the precision will only better approximate a
> value, but the exact value is lost, though math with these is fast.
> For example, if you store '2.5' in one, then either '2.499999999' or
> '2.500000001' is actually stored.
Actually, 2.5 can be represented exactly in binary floating point, so your
example is incorrect, but other values cannot, so the principal is valid.
SQLite should recognize the above 3 numerical types as being
> distinct, and do the correct actions with math involving any of them.
>
>  Darren Duncan
>


Thursday, September 29, 2005, 10:12:44 PM, Dennis Cote wrote:
> On 9/29/05, Darren Duncan < [hidden email]> wrote:
>[...]
>>> 2. DECIMAL(p,s)  An exactly remembered fractional number that is
>> typically stored in a form akin to text, such as one byte per base10
>> digit. These can effectively store arbitrarily large numbers of any
>> length and precision without loss of detail, although doing math with
>> them may be slower. For example, if you store '2.5' in one, then
>> '2.5' is actually stored.
> This can also be done with fixed point fractions. This type of scaled
> integer with a fractional component are often used by integer DSPs. They
> store a value in an integer with a fixed binary point (rather than a
> floating binary point as implied by the name floating point). [...]
For those still following this thread, and looking for more examples
of the infinite representations of numbers ;), here is another...
Instead of a binary fixed point, you can use a binary number with an
implicit decimal fixed point. For example, if your implicit decimal
point were 10^2, the number 345 (stored as a binary integer) would
represent the number 3.45. Concretely in C syntax, you'd print numbers
as printf("%d.%02u", x/100, x%100). Addition and subtraction can be
done with the integer operators. Multiplication and division take some
care to avoid loss of range or precision, but abstractly, multiply is
(x * y) / 100 and divide is (x * 100) / y. This is a neat hack to
maintain fixed decimal accuracy with binary integers.
> These values can also be stored as arbitrary precision integers,
> which store multiple integers words to represent the value which can
> again be interpreted as a fixed point fraction, or as BCD (binary
> coded decimal) values which store 2 digits per byte.
Storing numbers as *two* arbitrary precision integers, a numerator and
a denominator, gives you all the exact rationals (at least as big as
your memory allows  reducing the numbers helps). Common Lisp and Scheme
have rationals as well as real, complex, and arbitrary precision
integer numbers. http://mathworld.wolfram.com/RationalNumber.html
Doug Currie
Londonderry, NH


The following table sums up the division findings from other SQL engines:
SELECT 5 / 2  SELECT 5.0 / 2  SELECT 5 / 2.0  SELECT 5.0 / 2.0

SQLite3  2  2.5  2.5  2.5
PostgreSQL  2  2.500000000000  2.500000000000 
SQL*Plus  2.5   
MySql  2.50   
The implementation seems undecided, but I see that the "big players" Oracle SQL*Plus and MySql both do not strip off the decimal digits.
Thanks to all who run some tests and provided the results. Feel free to add other engines or fill in the gaps.
Regards,
Ralf


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


Ralf Junker < [hidden email]> wrote:
>
> 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.
>
create table t1( a integer, b integer);
insert into t1 values(5,2);
update t1 set a=a/b;
If your rule above was in force, this would leave
T1.A holding 2.5, which is incompatible with the
way other database engines work.

D. Richard Hipp < [hidden email]>


Ralf Junker wrote:
> ....
>
> 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:
>
> ....
>
> 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.
>
But this would break much, much code that expects integers to be treated
as integers. This can't be right. Implementation would require, for
every integer calculation, conversion to REAL, performing the
calculation, and checking whether the result is an integer (within some
tolerence). This suggestion won't float.
Gerry


Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles  USDA zone 9b, Sunset 1819

12
