RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thu 10/6/2005 10:14 AM > Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 > <[EMAIL PROTECTED]> wrote: > > Richard, I like to ask you, just to make sure; has the `order by` > > "bug"/issue, as result from automatic float->int conversion, been > > considered by you and if yes, do you plan any changes? > > > > > You have a simple workaround: Just add 0.0 to anything > that you definitely want to be floating point. > > I will continue to trying to figure out a reasonable method > of addressing your concerns. In other databases the behavior in sqlite is the expected behavior. E.g. IBM db2udb, Ingres, Microsoft SQL Server. Oracle is different because of the wierd NUMBER datatype which is used to store both ints and decimal - I don't know about any others. In most databases the datatypes of the operands dictate the result type; which is the sqlite behavior. Chris
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
=?ISO-8859-1?Q?Ren=E9_Tegel?= <[EMAIL PROTECTED]> wrote: > Hi, > > Richard, I like to ask you, just to make sure; has the `order by` > "bug"/issue, as result from automatic float->int conversion, been > considered by you and if yes, do you plan any changes? > I have made note of the issues raised. I do not have a solution to the problem at this point. (The various solutions proposed on this mailing list are all unacceptable for various reasons.) You have a simple workaround: Just add 0.0 to anything that you definitely want to be floating point. I will continue to trying to figure out a reasonable method of addressing your concerns. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Hi, Richard, I like to ask you, just to make sure; has the `order by` "bug"/issue, as result from automatic float->int conversion, been considered by you and if yes, do you plan any changes? regards, rene Ralf Junker wrote: Hello René Tegel, May i add to that that 'order by' also seems involved, see below. This can lead to really unexpected errors... "order by 1.0 * b / c" as workaround solves it btw. Thanks for pointing this out. I am sure we will be able to come up with even more examples where the confusion of INTEGER / REAL numbers leads to unexpected errors. Differating between floating point operator '/' and integer operator 'DIV' like some program languages do would lead to predictable output. Surely one possible solutions, but it very likely does not conform to the SQL standard (but I haven't checked). On the other hand, the sqlite3 column affinity does not result in standard output either for the examples given in this thread. Is there any chance that the problem will be addressed in the forseable future? Do we need to create a ticket to put it on the agenda? Regards, Ralf
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Hello René Tegel, >May i add to that that 'order by' also seems involved, see below. This can >lead to really unexpected errors... "order by 1.0 * b / c" as workaround >solves it btw. Thanks for pointing this out. I am sure we will be able to come up with even more examples where the confusion of INTEGER / REAL numbers leads to unexpected errors. >Differating between floating point operator '/' and integer operator 'DIV' >like some program languages do would lead to predictable output. Surely one possible solutions, but it very likely does not conform to the SQL standard (but I haven't checked). On the other hand, the sqlite3 column affinity does not result in standard output either for the examples given in this thread. Is there any chance that the problem will be addressed in the forseable future? Do we need to create a ticket to put it on the agenda? Regards, Ralf
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Hi, May i add to that that 'order by' also seems involved, see below. This can lead to really unexpected errors... "order by 1.0 * b / c" as workaround solves it btw. Differating between floating point operator '/' and integer operator 'DIV' like some program languages do would lead to predictable output. regards, rene === #select *, b/c, 1.0 * b/c from test2 order by b/c a b c b/c 1.0 * b/c 1 3 4 0 0.75 2 5 6 0 0.833 5 6 7 0 0.857142857142857 6 6 8 0 0.75 7 7 8 0 0.875 3 7 6 1 1.17 4 6 6 1 1.0 8 8 8 1 1.0 9 9 8 1 1.125 10 10 8 1 1.25 11 9 8 1 1.125 12 8 8 1 1.0 13 11 8 1 1.375 14 13 8 1 1.625 15 12 8 1 1.5 #select *, b/c, 1.0 * b/c from test2 order by 1.0*b/c a b c b/c 1.0 * b/c 1 3 4 0 0.75 6 6 8 0 0.75 2 5 6 0 0.833 5 6 7 0 0.857142857142857 7 7 8 0 0.875 4 6 6 1 1.0 8 8 8 1 1.0 12 8 8 1 1.0 9 9 8 1 1.125 11 9 8 1 1.125 3 7 6 1 1.17 10 10 8 1 1.25 13 11 8 1 1.375 15 12 8 1 1.5 14 13 8 1 1.625 Ralf Junker wrote: Hello DRH, 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. Well, understandable. But suppose that's exactly what one wants to do? How to achieve this using the current implementation of sqlite3? On the other hand, I think that the following, currently implemented behaviour of sqlite3 is also incompatible with the way other database engines work. Where they return 2.5 for real type columns, sqlite3 does not: create table t1 (a real, b real); insert into t1 values (5,2); select a / b from t1; 2 So the final question biols down to: How can I reliably guarantee a real type result for divisions on real typed columns even if they happen to contain integers? I cant't believe I always have to use a workaround like this: select 1.0 * a / b from t1; I am sure there must be a better possibility without the extra multiplication! Or is there not? Regards, Ralf
RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Perhaps not a solution, but a workaround: Try coercing the data entering the table into a value recognizable as a real: Create trigger t_t1i after insert on t1 Begin Update t1 set a = 1.0 * a, b = 1.0 * b Where rowid = new.rowid; End; Create trigger t_t1u after update on t1 Begin Update t1 set a = 1.0 * a, b = 1.0 * b Where rowid = new.rowid; End; The table will then hold reals in all cases. Expressions then work without change: Select a/b from t1; Update a set a = a / b; Doug -Original Message- From: Ralf Junker [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 7:12 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ? Hello DRH, >> 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. Well, understandable. But suppose that's exactly what one wants to do? How to achieve this using the current implementation of sqlite3? On the other hand, I think that the following, currently implemented behaviour of sqlite3 is also incompatible with the way other database engines work. Where they return 2.5 for real type columns, sqlite3 does not: create table t1 (a real, b real); insert into t1 values (5,2); select a / b from t1; 2 So the final question biols down to: How can I reliably guarantee a real type result for divisions on real typed columns even if they happen to contain integers? I cant't believe I always have to use a workaround like this: select 1.0 * a / b from t1; I am sure there must be a better possibility without the extra multiplication! Or is there not? Regards, Ralf
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Hello DRH, >> 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. Well, understandable. But suppose that's exactly what one wants to do? How to achieve this using the current implementation of sqlite3? On the other hand, I think that the following, currently implemented behaviour of sqlite3 is also incompatible with the way other database engines work. Where they return 2.5 for real type columns, sqlite3 does not: create table t1 (a real, b real); insert into t1 values (5,2); select a / b from t1; 2 So the final question biols down to: How can I reliably guarantee a real type result for divisions on real typed columns even if they happen to contain integers? I cant't believe I always have to use a workaround like this: select 1.0 * a / b from t1; I am sure there must be a better possibility without the extra multiplication! Or is there not? Regards, Ralf
RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Anybody thought of: t1.a = 5 t1.b = 2 select a / (b * 1.0); I think that would return a real. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 11:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ? Ralf Junker wrote: >>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 ...
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Ralf Junker wrote: 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. I'm sorry I took so long to get back to, I started this reply last night, but had to leave it unfinished until now. To clarify what I said above (since it obviously wasn't clear). The first thing to note is that SQLite applies a data type to individual values, and data type affinities to table columns. These are different things. I meant that your division problem can be fixed in SQLite if SQLite is modified so that it checks the affinity of the column before storing the integer value. In SQLite a column declared as REAL has an affinity of NUMERIC (see http://www.sqlite.org/datatype3.html section 2.1). The problem is that currently columns with NUMERIC affinity can store values of any type, in particular it can store both INTEGER and REAL (see section 1 of the data types documentation). The required change would be to modify SQLite so that it would convert INTEGER values to REAL values when they are stored in a column with NUMERIC affinity. This would make your division work as expected. The integer value supplied to the insert statement would be converted to real and stored as real in the table. When the select statement later retrieves the values to do the division, it will have real values and do floating point path to produce a real result. It has some side effects that others may not like however. Storing a value of 5 into the table would return a value of 5.0 when selected, since SQLite (at least the newest versions) formats REAL values with a decimal point on output. It would also make the database files larger if they were storing integer values in a typeless column or one with a type that produced NUMERIC affinity (again see the data types doc for the rules). If a user really wants to force values to be stored and returned as integers they would simply need to explicitly declare the columns to have an INT type, so they would be assigned an INTEGER affinity. Then no conversion would be done when the values are stored. To me this seems like an optimization for both speed and space that the user can trigger by explicitly specifying the column's data type. 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. This behavior is also documented on the data type page. If the REAL value can be represented exactly as an INTEGER, an INTEGER is stored in columns declared to be of type INT. If not, the REAL value is stored. This seems reasonable to me. It is in some ways the complement to the change I proposed above. If you do this in a standard SQL engine it will silently convert the 5.0 value to an exact value with implementation defined precision. Usually not a problem for 5.0, but I suspect that 5.1 will probably come out of the INTEGER column with the same value as 5.0 does. In SQLite, it will store the 5.1 value as a REAL value even though it is in a column with INTEGER affinity. 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
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
On 9/30/05, Ralf Junker <[EMAIL PROTECTED]> wrote: > > 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). In that case it did what it was instructed to do. The type was integer so it converted. 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. Since it behaves in predictable ways I don't believe inconsistency is a big problem. Inconsistency is the only constant I've found in life! It's only inconsistent on one operation, storing integers in a real type. If you code with a rule of thumb to always insert with something like "insert into t values( round(x) );" then you will have no problems. I would like to propose the resolve this inconsistency as follows: > > That would make sqlite math inconsistent with math as performed by computer languages. I don't see that as an improvement. --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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 18-19
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
Ralf Junker <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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.5000 | 2.5000 | 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
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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 <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org 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.5000 (1 row) devl=# select 5 / 2.0; ?column? 2.5000 (1 row) devl=# select version(); version PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 (1 row)
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
> 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, Alexandermailto:[EMAIL PROTECTED]
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
On 9/29/05, Ralf Junker <[EMAIL PROTECTED]> 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.
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
On 9/29/05, Ralf Junker <[EMAIL PROTECTED]> 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.
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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
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.5000 (1 row) devl=# select 5 / 2.0; ?column? 2.5000 (1 row) devl=# select version(); version PostgreSQL 8.0.3 on i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 (1 row)
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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 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
Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
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 <[EMAIL PROTECTED]> 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? >
