You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Bryan Pendleton <bp...@gmail.com> on 2012/12/19 16:44:34 UTC
Re: Some questions about the Derby
Forwarding to the list.
On 12/19/2012 04:10 AM, 王旭 wrote:
> *Hello Pendleton*
>
> **
>
> *I am a loyal user of derby database from China. I found some problems
> in the course of use, so I need your help urgently.*
>
> **
>
> *Derby Version**：**10.4*
>
> *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?
>
> *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".
>
> Could you tell me whether the cause of problem is inaccurated data from
> a floating-point type double.
>
> *Let me know whether the above problems are the Derby Bug? Are there any
> other similar problems on the version in use? And how should we deal with?*
>
> *Look forward to your reply, thank you!*
>
> From Apollo.Wong
>
答复: 答复: Some questions about the Derby
Posted by 王旭 <wa...@sinosoft.com.cn>.
Hi
I'm sorry for the statement of SQL script (select 350000.0 /1000* RT from CV where age = 20;).
The SQL script we have used is " select 350000.0 * RT/1000 from CV where age = 20;"
But I do not understand why the two execution results of SQL script are inconsistent, and why the above execution results of SQL script appears ".9999999"?
-----邮件原件-----
发件人: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]
发送时间: 2012年12月22日 0:07
收件人: Derby Discussion
抄送: 王旭; 'Knut Anders Hatlen'
主题: Re: 答复: Some questions about the Derby
> In ij, I re-verify these statements and the results the same with you.
> But I use java code using a JDBC connection Derby ,it will appear.
> Can you help me explain the problem?
How are you displaying the result?
Are you using java.text.DecimalFormat?
If you are, what is your format pattern?
thanks,
bryan
Re: 答复: Some questions about the Derby
Posted by Bryan Pendleton <bp...@gmail.com>.
> In ij, I re-verify these statements and the results the same with you.
> But I use java code using a JDBC connection Derby ,it will appear.
> Can you help me explain the problem?
How are you displaying the result?
Are you using java.text.DecimalFormat?
If you are, what is your format pattern?
thanks,
bryan
答复: Some questions about the Derby
Posted by 王旭 <wa...@sinosoft.com.cn>.
Hi Knut
Thank you for your reply , but I still have some problems need your help to answer.
*Question one**：
According to the reference manual:
About divider，the scale expressions: 31 - lp + ls - rs
the precision expressions: lp - ls + rp + max(ls + rp - rs + 1, 4)
For example, the scale of the resulting data type of the following expression is 27:
11.0/1111.33
// 31 - 3 + 1 - 2 = 27
Another, the precision of the resulting data type of the following expression is 14:
11.0/1111.33
//3 - 1 + 6 + Max(1+6-2+1,4) = 14
Why the calculated results of the scale is less than the precision?
I think that the precision (integer digits + scale digits) should be greater than the scale digits.
*Question two**：
In ij, I re-verify these statements and the results the same with you.
But I use java code using a JDBC connection Derby ,it will appear.
Can you help me explain the problem?
-----邮件原件----
发件人: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
发送时间: 2012年12月20日 18:07
收件人: 王旭
抄送: derby-user@db.apache.org
主题: Re: Some questions about the Derby
Bryan Pendleton <bp...@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
Re: Some questions about the Derby
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Bryan Pendleton <bp...@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