Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7009

DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.14.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      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.

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              lukas.eder Lukas Eder
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: