Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1664

CAST('<string>' as TIMESTAMP) adds part of sub-second fraction to the value

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • avatica-1.10.0, 1.13.0
    • None
    • None

    Description

      select   
        TIMESTAMP '2016-02-26 19:06:00',
        CAST('2016-02-26 19:06:00' as TIMESTAMP);
       +---------------------+---------------------+
       | EXPR$0              | EXPR$1              |
       +---------------------+---------------------+
       | 2016-02-26 19:06:00 | 2016-02-26 19:06:00 |
       +---------------------+---------------------+
      !ok
      
      select
        TIMESTAMP '2016-02-26 19:06:00.1',
        CAST('2016-02-26 19:06:00.1' as TIMESTAMP),
        TIMESTAMPDIFF(SECOND,
          TIMESTAMP '2016-02-26 19:06:00.1',
          CAST('2016-02-26 19:06:00.1' as TIMESTAMP));
       +---------------------+---------------------+--------+
       | EXPR$0              | EXPR$1              | EXPR$2 |
       +---------------------+---------------------+--------+
       | 2016-02-26 19:06:00 | 2016-02-26 19:06:00 |      0 |
       +---------------------+---------------------+--------+
      !ok
      
      select
        TIMESTAMP '2016-02-26 19:06:00.123456',
        CAST('2016-02-26 19:06:00.123456' as TIMESTAMP),
        TIMESTAMPDIFF(SECOND,
          TIMESTAMP '2016-02-26 19:06:00.123456',
          CAST('2016-02-26 19:06:00.123456' as TIMESTAMP));
       +---------------------+---------------------+--------+
       | EXPR$0              | EXPR$1              | EXPR$2 |
       +---------------------+---------------------+--------+
       | 2016-02-26 19:06:00 | 2016-02-26 19:08:03 |    123 |
       +---------------------+---------------------+--------+
      !ok
      
      select
        TIMESTAMP '2016-02-26 19:06:00.12345678',
        CAST('2016-02-26 19:06:00.12345678' as TIMESTAMP),
        TIMESTAMPDIFF(SECOND,
          TIMESTAMP '2016-02-26 19:06:00.123456789',
          CAST('2016-02-26 19:06:00.123456789' as TIMESTAMP));
       +---------------------+---------------------+--------+
       | EXPR$0              | EXPR$1              | EXPR$2 |
       +---------------------+---------------------+--------+
       | 2016-02-26 19:06:00 | 2016-02-26 22:31:46 | 123456 |
       +---------------------+---------------------+--------+
      !ok
      

      Note how TIMESTAMP <string> parses the value correctly (not sure if the sub-second fraction is parsed though) but CAST adds part of the sub-second fraction as a seconds to the value.

      Attachments

        Activity

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

          People

            julianhyde Julian Hyde
            rusanu Remus Rusanu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment