# Problem/Bug: "SELECT 5 / 2;" returns 2 ? Classic List Threaded 38 messages 12
Open this post in threaded view
|

## Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 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
Open this post in threaded view
|

## Re: 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 <[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? >
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker 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
Open this post in threaded view
|

## Re: 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
Open this post in threaded view
|

## Re: 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 i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 (1 row)
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker 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
Open this post in threaded view
|

## the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

 In reply to this post by Dennis Cote 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 mis-understand what 'exact' means; 'exact' != 'integer', but rather 'integer is conceptually a sub-set 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 base-2 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 base-10 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 base-2 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
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker 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.
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker 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.
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Dennis Jenkins MySQL 4.0.24-standard: #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 i386-portbld-freebsd5.4, compiled by GCC cc (GCC) > 3.4.2 [FreeBSD] 20040728 > (1 row) >
Open this post in threaded view
|

## Re: the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

 In reply to this post by Dennis Cote 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 mis-understand what 'exact' means; 'exact' != 'integer', but > rather 'integer is conceptually a sub-set 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.
Open this post in threaded view
|

## Re: the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

 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.
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker > 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]
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Dennis Jenkins 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 i386-portbld-freebsd5.4, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 (1 row)
Open this post in threaded view
|

## Re: the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

 In reply to this post by Dennis Cote 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 mis-understand what 'exact' means; 'exact' != 'integer', but > rather 'integer is conceptually a sub-set 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 base-2 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 base-10 > 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 base-2 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 >
Open this post in threaded view
|

## Re: the 3 numerical types (was Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?)

 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 base-10 >> 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
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Clark Christensen 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
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Jay Sprenkle 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
Open this post in threaded view
|

## Re: Problem/Bug: "SELECT 5 / 2;" returns 2 ?

 In reply to this post by Ralf Junker 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]>