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

CAST operations on microsecond and nanosecond columns getting down cast to TIMSTAMP(3)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Invalid
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: core
    • Labels:
      None

      Description

      Hello,

      I have the below two issues which require assistance:

      1)

      Calcite is automatically generating TIMSTAMP(3) casts even though we explicitly mention  TIMSTAMP(6) or  TIMSTAMP(9). Please see the below query and generated calcite:

       

      explain calcite select cast(m_9 as timestamp(6)) from test;
      -------------------------------------------------------------
      Explanation
      LogicalProject(EXPR$0=[CAST($19):TIMESTAMP(3)])
       EnumerableTableScan(table=[[mapd, test]])
      
      select cast(m_9 as timestamp(6)) from test limit 2;
      -----------------------------------------------------
      2006-04-26 03:49:04.607
      2006-04-26 03:49:04.607
      
      explain calcite select cast(m_6 as timestamp(9)) from test limit 2;
      ---------------------------------------------------------------------------
      Explanation
      LogicalSort(fetch=[2])
       LogicalProject(EXPR$0=[CAST($18):TIMESTAMP(3)])
       EnumerableTableScan(table=[[mapd, test]])

      I found there was a related issue regarding this and was mentioned that it was fixed in `1.13.0`, we are using `1.16.0` and the problem still persists.

      https://jira.apache.org/jira/browse/CALCITE-1690 

       

      2) 
      I have a column name m_3 which is TIMESTAMP(3). When I am doing comparison operations using `m_3` is get wrong results. I found reason to be calcite actually interpreting it as TIMESTAMP(0) instead of TIMESTAMP(3) and therefore end up generating two casts for the literal.

       

      DDL Statement
      ---------------------------------------------------------------------------------
      CREATE TABLE test (
      m TIMESTAMP(0),
      m_3 TIMESTAMP(3),
      m_6 TIMESTAMP(6),
      m_9 TIMESTAMP(9)))
      
      
      ----------------------------------------------------------------------------------
      explain calcite SELect m_3 from test where m_3 = TIMESTAMP(3) '2014-12-13 22:23:15.323';
      Explanation
      LogicalProject(m_3=[$17])
       LogicalFilter(condition=[=($17, CAST(CAST('2014-12-13 22:23:15.323'):TIMESTAMP(3) NOT NULL):TIMESTAMP(0) NOT NULL)])
       EnumerableTableScan(table=[[mapd, test]])
      
      --------------------------------------------------------------------------------------------
      explain calcite SELect m_3 + 1 = TIMESTAMP(3) '2014-12-13 22:23:15.323' from test;
      Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 16: Cannot apply '+' to arguments of type '<TIMESTAMP(0)> + <INTEGER>'. Supported form(s): '<NUMERIC> + <NUMERIC>'
      '<DATETIME_INTERVAL> + <DATETIME_INTERVAL>'
      '<DATETIME> + <DATETIME_INTERVAL>'
      '<DATETIME_INTERVAL> + <DATETIME>'
      
      

      https://calcite.apache.org/docs/reference.html#scalar-types

      Is there a way to tell calcite that m_3 is TIMESTAMP(3) instead of TIMESTAMP(0) ?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                wamsiv Wamsi Viswanath
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: