db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Decimal values larger than (31,0)...should this be an error?
Date Thu, 26 Apr 2007 18:01:43 GMT
The Derby reference guide says for decimal data types:

   The precision must be between 1 and 31. The scale must be less than
   or equal to the precision.

And indeed, if I try to create a table with precision of 32 or more, I get an error:

ij> connect 'mydb;create=true';
ij> create table oops (d decimal(32, 0));
ERROR 42X48: Value '32' is not a valid precision for DECIMAL.

But it is nonetheless possible to create a decimal value in Derby that has a 
precision larger than 32.  Esp. via a VALUES statement:

-- Following constant has 44 digits...
ij> values 12345678901234567890123456789012345678901234;
1
---------------------------------------------
12345678901234567890123456789012345678901234

1 row selected

Should the above VALUES clause throw an error, or is this intentional (and 
correct) behavior?  If it's intentional and correct, then I think this causes 
problems for the new (to 10.3) CREATE TABLE AS <subquery> WITH NO DATA 
functionality.  For example, since the above VALUES clause executes without 
error, the following statement suceeds in Derby 10.3:

ij> create table invalid_t (d) as
   values 12345678901234567890123456789012345678901234 with no data;
0 rows inserted/updated/deleted

If we describe the new table, we can see that the precision is set to 44:

ij> describe invalid_t;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
D                   |DECIMAL  |0   |10  |*44*  |NULL      |NULL      |NO

And we are now allowed to insert values with precision 44 (or less) into that table:

ij> insert into invalid_t values 12345678901234567890123456789012345678901234;
1 row inserted/updated/deleted

Further, we can correctly select the decimal column from that table without 
error or loss of precision:

ij> select d from invalid_t;
D
---------------------------------------------
12345678901234567890123456789012345678901234

1 row selected

But if we use the column in a numeric expression, we get an error saying that 
the resultant value is out of range:

ij> select d * 1 from invalid_t;
1
--------------------------------
ERROR 22003: The resulting value is outside the range for the data type 
DECIMAL/NUMERIC(31,0).

ij> select d / 2 from invalid_t;
1
--------------------------------
ERROR 22003: The resulting value is outside the range for the data type 
DECIMAL/NUMERIC(31,0).

ij> select d + 2 from invalid_t;
1
--------------------------------
ERROR 22003: The resulting value is outside the range for the data type 
DECIMAL/NUMERIC(31,0).

ij> select d - 1 from invalid_t;
1
--------------------------------
ERROR 22003: The resulting value is outside the range for the data type 
DECIMAL/NUMERIC(31,0).

Note that if the expression returns a decimal with precision less than 32, then 
all is okay:

ij> select d / 234567898765432 from invalid_t;
1
--------------------------------
52631579027700853983831737804

1 row selected

If we run dblook against the database the column does indeed have a precision of 44:

 > java org.apache.derby.tools.dblook -d jdbc:derby:mydb -t invalid_t -verbose

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "APP"."INVALID_T" ("D" DECIMAL(44,0) NOT NULL);

But as expected, trying to execute the generated DDL will lead to an error:

ij> connect 'diffdb;create=true';
ij> CREATE TABLE "APP"."INVALID_T" ("D" DECIMAL(44,0) NOT NULL);
ERROR 42X48: Value '44' is not a valid precision for DECIMAL.

I'm pretty sure this warrants a Jira, but I'm not real sure where the bug is. 
Should the VALUES clause be throwing an error?  Or should the CREATE TABLE AS 
... WITH NO DATA functionality be responsible for checking the column 
definition?  Or is the problem someplace else entirely?

Army


Mime
View raw message