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

Allow custom time unit abbreviations in FLOOR, CEIL, EXTRACT, DATE_PART, DATEADD, DATEDIFF and similar functions

    XMLWordPrintableJSON

Details

    Description

      Babel parser can't parse DATEADD(S, ...). The parser should allow custom time unit abbreviations in FLOOR, CEIL, EXTRACT, DATE_PART, DATEADD, DATEDIFF and similar functions.

      I'm trying to parse and rewrite some SQL dialects including SQL Server which supports some functions that receive HOUR as an argument using. But Calcite's Planner#parse can't handle this kind of SQL.

      FrameworkConfig config = Frameworks.newConfigBuilder().build();
Planner planner = Frameworks.getPlanner(config);
System.out.println(planner.parse("SELECT DATEDIFF(HOUR, NOW(), NOW())"));

      Exception in thread "main" org.apache.calcite.sql.parser.SqlParseException: Incorrect syntax near the keyword 'HOUR' at line 2, column 21.
Was expecting one of:
    "ALL" ...
    "ARRAY" ...
    "CASE" ...
   :
    "GROUPING" ...
    "HOUR" ...
    "HOUR" "(" ...
    "*" ...
    ")" ...
    
    at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:389)
    at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:153)
    at org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:145)
    at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:160)
    at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:185)
    at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:214)
    at org.apache.calcite.tools.Planner.parse(Planner.java:50)

      This issue happens with other date/time keyword-ish ones like YEAR.

      I guess it happens because HOUR and YEAR are reserved keywords.

      Is this an expected behavior? If so, is there any workaround?

      Attachments

        Issue Links

          is duplicated by

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-5222 Support more EXTRACT field values (or allow arbitrary in parse?)

          • Major - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-2496 EXTRACT function: MILLI/MICRO/NANOSECOND parts of a DATE must be zero

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-3218 Syntax error while parsing DATEADD function (which is valid on Redshift)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5155 Custom time frames

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #2807

          Web Link PR 2807

          Activity

            People

              jiajunbernoulli Jiajun Xie
              komamitsu Mitsunori Komatsu
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 10m
                  10m