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

["Lukas Eder (JIRA)" <ji...@apache.org>]

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

Lukas Eder commented on DERBY-7009:
-----------------------------------

{quote}Other than being puzzling, can you think of any harm caused by the behavior you have discovered?
{quote}
Well, there's always some "harm" caused by inconsistencies like these. For instance, what's the point of being able to declare such a constant, and then not use it in any way? E.g., while it is possible to have such a literal:
{code:java}
SELECT 123456789012345678901234567890.012345678901234567890123456789
FROM (VALUES(1)) t(a){code}
I cannot do any arithmetic with it, even the kind of arithmetic that seems should work:
{code:java}
SELECT 123456789012345678901234567890.012345678901234567890123456789 * 1
FROM (VALUES(1)) t(a){code}
This causes:
{quote}SQL Error [30000] [22003]: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,30).
{quote}
I think that for consistency reasons, the data type limitation should apply everywhere, because then it can be expected. If it is applied only in "some cases", then it seems much more annoying. At least, that's my opinion.

> 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)