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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: avatica-1.10.0, 1.13.0
    • Component/s: None
    • Labels:
      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

            People

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

              Dates

              • Created:
                Updated:
                Resolved: