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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.14.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 26/Sep/18 14:40
• Updated: 01/Oct/18 07:58

Description

In the manual, there is a claim that the maximum precision for the DECIMAL data type is 31:

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

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.