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
