db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Some questions about the Derby
Date Thu, 20 Dec 2012 10:06:31 GMT
Bryan Pendleton <bpendleton.derby@gmail.com> writes:

> Forwarding to the list.
> On 12/19/2012 04:10 AM, 王旭 wrote:

>> *Question one**:***
>> 
>> Firstly, I created a table.
>> 
>> CREATE TABLE CV
>> 
>>      (
>> 
>>          AGE INTEGER NOT NULL,
>> 
>>          RT DECIMAL(20,10),
>> 
>>          PRIMARY KEY (AGE)
>> 
>>      )
>> 
>> Then innserted data "insert into CV VALUES (80,10729.5000000000)"
>> 
>> Finally, an error occured when the SQL statement 
>> "select?(95000.0*1000/1000)/1000.0?*?RT?from?CV?where?Age=80?" was 
>> executed, it displayed  "the generated value has gone beyond the data 
>> type of DECIMAL / NUMERIC (31,25)".
>> 
>> Could you tell me how Derby do the precision control and operation on 
>> the data type of DECIMAL when it performs a variety of arithmetic 
>> operations including add, subtract, multiply and divide?

The reference manual explains how numeric types are promoted in
expressions here: http://db.apache.org/derby/docs/10.9/ref/rrefsqlj27767.html

>> 
>> *Question two**:***
>> 
>> Firstly, I created a table.
>> 
>> CREATE TABLE CV
>> 
>>      (
>> 
>>          AGE SMALLINT,
>> 
>>          RT DOUBLE,
>> 
>>          PRIMARY KEY (AGE)
>> 
>>      )
>> 
>> Then innserted data "insert into CV VALUES (20,766.3)"
>> 
>> Finally, I got results of "268204.99999999994" after executing the SQL 
>> statement "select 350000.0/1000 * RT from CV the where age = 20?", but 
>> the actual result should be "268205.0".

When I execute those statements in ij, I see the result you expected:

ij> CREATE TABLE CV (AGE SMALLINT, RT DOUBLE, PRIMARY KEY (AGE));
0 rows inserted/updated/deleted
ij> insert into CV VALUES (20, 766.3);
1 row inserted/updated/deleted
ij> select 350000.0/1000 * RT from CV where age = 20;
1                     
----------------------
268205.0              

1 row selected

>> Could you tell me whether the cause of problem is inaccurated data from 
>> a floating-point type double.

That sounds likely. But it's difficult to tell without knowing exactly
what you did when you ended up with that number.


-- 
Knut Anders

Mime
View raw message