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