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.

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        I don't see a problem in the output above.

        Show
        julianhyde Julian Hyde added a comment - I don't see a problem in the output above.
        Hide
        rusanu Remus Rusanu added a comment -

        CAST('2016-02-26 19:06:00.123456789' as TIMESTAMP yields 2016-02-26 22:31:46.

        Show
        rusanu Remus Rusanu added a comment - CAST('2016-02-26 19:06:00.123456789' as TIMESTAMP yields 2016-02-26 22:31:46 .
        Hide
        julianhyde Julian Hyde added a comment - - edited

        Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/4ba9d1b2.

        Note: That fix is in Avatica. It will only appear in Calcite when Avatica has made release 1.10 and Calcite has upgraded to that release.

        Show
        julianhyde Julian Hyde added a comment - - edited Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/4ba9d1b2 . Note: That fix is in Avatica. It will only appear in Calcite when Avatica has made release 1.10 and Calcite has upgraded to that release.
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release avatica-1.10.0 (2017-05-30).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release avatica-1.10.0 (2017-05-30).
        Show
        julianhyde Julian Hyde added a comment - Fixed in Calcite in http://git-wip-us.apache.org/repos/asf/calcite/commit/915c218b .
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.13.0 (2017-06-26).

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.13.0 (2017-06-26).

          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:

              Development