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

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.14.2.0
    • None
    • SQL
    • 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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Unassigned Unassigned
            lukas.eder Lukas Eder

            Dates

              Created:
              Updated:

              Slack

                Issue deployment