DB2 gives this: CREATE TABLE t1(a INTEGER, b REAL) INSERT INTO t1 VALUES(5,5) SELECT a/2, b/2 FROM t1 1 2 ----------- ------------------------ 2 +2.50000000000000E+000
1 record(s) selected. -----Original Message----- From: Rob Lohman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 02, 2005 11:07 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 A quick test here on MSSQL & Oracle: Microsoft SQL 2000 & SQL 2005 (beta): create table MATHTEST ( CINT int null, CDEC decimal null, CDPREC double precision null, CFLOAT float null, CNUM numeric null, CREAL real null ) go insert into MATHTEST (CINT, CDEC, CDPREC, CFLOAT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5); go select CINT/2, CDEC/2, CDPREC/2, CFLOAT/2, CNUM/2, CREAL/2 from MATHTEST go 2 2.500000 2.5 2.5 2.500000 2.5 --------- Oracle 8i2: create table MATHTEST ( CDEC DEC, CDECIMAL DECIMAL, CDPREC DOUBLE PRECISION, CFLOAT FLOAT, CINT INT, CNUM NUMBER, CREAL REAL ); insert into MATHTEST (CDEC, CDECIMAL, CDPREC, CFLOAT, CINT, CNUM, CREAL) values (5, 5, 5, 5, 5, 5, 5); select CDEC/2, CDECIMAL/2, CDPREC/2, CFLOAT/2, CINT/2, CNUM/2, CREAL/2 from MATHTEST; CDEC/2 CDECIMAL/2 CDPREC/2 CFLOAT/2 CINT/2 CNUM/2 CREAL/2 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 2,5 2,5 2,5 2,5 2,5 2,5 2,5 So it seems these two have different opinions on this as well. Rob ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Wednesday, November 02, 2005 4:36 PM Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2 Consider the following SQL: CREATE TABLE t1(a INTEGER, b REAL); INSERT INTO t1 VALUES(5,5); SELECT a/2, b/2 FROM t1; >From the above SQL, SQLite version 3.2.7 and earlier will return 2|2 If my proposed changes for 3.3.0 go in, then the result will be: 2.5|2.5 If I understand what most people are saying, the SQL standard says that the result should be: 2|2.5 Does this correctly summarize the situation? Do other SQL database engines consistently return the 3rd case? Am I alone in thinking that a division operator that does different things depending on the declared datatype of a column is an abomination? Does anybody have a real-world example where any of this will actually make a difference, or is this really just an academic argument? -- D. Richard Hipp <[EMAIL PROTECTED]>