[jira] [Commented] (DERBY-7009) DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-7009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16633138#comment-16633138 ] 

Rick Hillegas commented on DERBY-7009:
--------------------------------------

Thanks for investigating and scripting this behavior, Lukas.

I can see that the current behavior is puzzling. According to the 2016 SQL Standard, part 2, section 5.3 <literal>, syntax rule 22:

{noformat}
The declared type of an <exact numeric literal> ENL is an
implementation-defined exact numeric type whose scale is
the number of <digit>s to the right of the <period>.
There shall be an exact numeric type capable of representing
the value of ENL exactly.
{noformat}

That says to me that Derby is supposed to support very large precisions and scales for DECIMAL/NUMERIC literals.

As a practical matter, databases impose maximum precisions and scales for storage types. I think that is what the Reference Guide is trying to say on the page you cite. Maybe this is a documentation bug and we should clarify that the maximum precision and scale are simply

1) the maximums that can be declared

2) and the maximums that can be used to type table columns.

Note that Derby's DatabaseMetaData.getTypeInfo() says that the maximum precision and scale of DECIMAL/NUMERIC is 31. The javadoc for that method does not make it clear whether this applies only to declared types or whether it constrains implicit types as well. However, in general, the JDBC spec avoids contradicting the SQL Standard.

The following script elaborates on the behavior you are seeing:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

call syscs_util.syscs_register_tool( 'databaseMetaData', true );

--
-- returns expected results
--
--
-- | DECIMAL |31         |0     |31    |
-- | NUMERIC |31         |0     |31    |
--
select type_name,
       precision as max_prec,
       minimum_scale as min_scale,
       maximum_scale as max_scale
from table(getTypeInfo()) t
where type_name = 'DECIMAL' or type_name = 'NUMERIC'
order by type_name;

-- succeeds as expected
values cast (123.45 as decimal(31,2));

--
-- raises reasonable error:
--
-- ERROR 42X48: Value '32' is not a valid precision for DECIMAL.
--
values cast (123.45 as decimal(32,2));

-- all of these succeed:
create view v1(a) as values 123456789012345678901234567890.0123;
create view v2(a) as values 123456789012345678901234567890.01234;
create view v3(a) as values 123456789012345678901234567890.0123456789;
create view v4(a) as values 123456789012345678901234567890.012345678901234567890123456789;

--
-- returns
--
-- 123456789012345678901234567890.012345678901234567890123456789 
--
select * from v4;

--
-- returns
--
-- | V1 | DECIMAL(34,4) |
-- | V2 | DECIMAL(35,5) |
-- | V3 | DECIMAL(40,10) |
-- | V4 | DECIMAL(60,30) |
--
select t.tablename, c.columndatatype
from sys.syscolumns c, sys.systables t
where c.referenceid = t.tableid
and t.tablename like 'V%' and t.tabletype = 'V'
order by t.tablename;

-- raises reasonable error:
--
-- ERROR 42X71: Invalid data type 'DECIMAL(60, 30)' for column 'A'.
--
create table t4 as select * from v4 with no data;
{noformat}

Other than being puzzling, can you think of any harm caused by the behavior you have discovered?

Thanks,
-Rick


> DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
> ------------------------------------------------------------------------------
>
>                 Key: DERBY-7009
>                 URL: https://issues.apache.org/jira/browse/DERBY-7009
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.14.2.0
>            Reporter: Lukas Eder
>            Priority: Major
>
> In the manual, there is a claim that the maximum precision for the DECIMAL data type is 31:
> {quote}The _precision_ must be between 1 and 31. The _scale_ must be less than or equal to the precision.
> {quote}
> [https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
> This can be confirmed via the following failing statements:
> {{CREATE TABLE test (i decimal(33, 3));}}
>  {{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
> However, this statement works:
> {{CREATE VIEW x(a, b) AS}}
> {{select 123456789012345678901234567890.001, 123456789012345678901234567890.001 from SYSIBM.SYSDUMMY1;}}
> And a quick query against the dictionary views shows that the type is definitely DECIMAL(33, 3):
> {{SELECT COLUMNDATATYPE}}
>  {{FROM SYS.SYSCOLUMNS}}
>  {{WHERE COLUMNNAME IN ('A', 'B');}}
> Yielding:
> {{COLUMNDATATYPE         |}}
>  {{-----------------------|}}
>  {{DECIMAL(33,3) NOT NULL |}}
>  {{DECIMAL(33,3) NOT NULL |}}
> For consistency reasons, the latter should fail just like the former two.
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)