Thank you very much Julian for your quick and accurate answer ! Indeed, switching to the Babel parser worked !
Have a great day, and thank you once again for the amazing project you've built ! Florent On 2021/09/25 00:38:16, Julian Hyde <[email protected]> wrote: > It’s possible you will also need to use the Babel parser, because DATE is a > reserved keyword and therefore the parser needs to work in a different mode > in order to see it as a function name. I think I made the DATE function work > for Redshift but I’m not sure I did it for BigQuery. > > > > On Sep 24, 2021, at 1:58 PM, Florent Martineau <[email protected]> > > wrote: > > > > Dear all, > > > > Disclaimer: It's the first time I send a message to a mailing list. If it's > > not the right mailing list or if I should use other means (eg. > > Stackoverflow), please do not hesitate to tell me! Also, if you need > > additional pieces of information, I will be glad to provide them! > > > > My problem is the following: I try to parse SQL queries for Big Query > > dialect, and it seems that it fails whenever I use the DATE keyword. > > > > I tried to call Bigquery specific functions such as CURRENT_TIMESTAMP, > > DATE_FROM_UNIX or TIMESTAMP_MICROS, and it works. But it fails when trying > > to do things such as DATE("2021-01-01"). > > > > I'm wondering if it's a bug (either from Apache Calcite's implementation of > > Bigquery's dialect, or more likely from my code), or a feature (Bigquery is > > not supposed to support this kind of syntax ==> the query runs well in > > Bigquery's console so I'm doubtful about this hypothesis). > > > > Also, what's strange is that for the query 'SELECT DATE("2021-01-01")', I > > get an error message telling me that it expects a parenthesis after the > > date, when the parenthesis is actually here. Here is the error message: > > > > > > > > *Error: org.apache.calcite.sql.parser.SqlParseException: Incorrect syntax > > near the keyword 'DATE' at line 1, column 8.Was expecting one of: "ALL" > > ...* > > * [ITEMS OMMITTED]* > > * "(" ...* > > > > * [ITEMS OMMITTED]* > > > > > > > > Here is the full code to reproduce the error: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > *package org.apache.calcite;import org.apache.calcite.config.Lex;import > > org.apache.calcite.sql.SqlNode;import > > org.apache.calcite.sql.parser.SqlParseException;import > > org.apache.calcite.sql.parser.SqlParser;import > > org.apache.calcite.sql.parser.impl.SqlParserImpl;import > > org.apache.calcite.sql.validate.SqlConformanceEnum;import > > org.apache.calcite.tools.FrameworkConfig;import > > org.apache.calcite.tools.Frameworks;import > > org.apache.calcite.tools.Planner;public class Demo { public static void > > main(String[] args) { SqlParser.Config sqlParserConfig = > > SqlParser.config().DEFAULT .withLex(Lex.BIG_QUERY) > > .withConformance(SqlConformanceEnum.BIG_QUERY) > > .withParserFactory(SqlParserImpl.FACTORY); FrameworkConfig > > frameworkConfig = Frameworks.newConfigBuilder() > > .parserConfig(sqlParserConfig) .build(); String[] > > testSqlFragments = { "1+(2*4) as foo", "CURRENT_DATE()", > > "CURRENT_DATETIME()", "DATE_FROM_UNIX(123456)", > > "TIMESTAMP_MICROS(123456)", "UNIX_DATE(DATE '2021-01-01')", > > "DATE '2021-01-01'", "DATE('2021-01-01')", }; for (String > > sqlFragment : testSqlFragments) { try { String query = "SELECT > > " + sqlFragment; System.out.println("Trying to parse : " + query); > > Planner planner = Frameworks.getPlanner(frameworkConfig); > > SqlNode node = planner.parse(query); > > System.out.println("Successfully parsed query: " + node); } catch > > (SqlParseException e) { System.out.println("Error: " + e); } > > } }}* > > > > > > > > > > And here is the output: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > *Trying to parse : SELECT 1+(2*4) as fooSuccessfully parsed query: SELECT 1 > > + 2 * 4 AS `foo`Trying to parse : SELECT CURRENT_DATE()Successfully parsed > > query: SELECT CURRENT_DATE()Trying to parse : SELECT > > CURRENT_DATETIME()Successfully parsed query: SELECT > > `CURRENT_DATETIME`()Trying to parse : SELECT > > DATE_FROM_UNIX(123456)Successfully parsed query: SELECT > > `DATE_FROM_UNIX`(123456)Trying to parse : SELECT > > TIMESTAMP_MICROS(123456)Successfully parsed query: SELECT > > `TIMESTAMP_MICROS`(123456)Trying to parse : SELECT UNIX_DATE(DATE > > '2021-01-01')Error: org.apache.calcite.sql.parser.SqlParseException: > > Incorrect syntax near the keyword 'DATE' at line 1, column 18.Was expecting > > one of: "ALL" ... "CURSOR" ... "DISTINCT" ... "EXISTS" ... > > "NOT" ... "ROW" ... "UNIQUE" ... "WITH" ... "(" ... "+" ... * > > > > * [CUTTING LONG LIST HERE]* > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > * Trying to parse : SELECT DATE '2021-01-01'Error: > > org.apache.calcite.sql.parser.SqlParseException: Incorrect syntax near the > > keyword 'DATE' at line 1, column 8.Was expecting one of: "ALL" ... > > "CURSOR" ... "DISTINCT" ... "EXISTS" ... "NOT" ... "ROW" ... > > "STREAM" ... "UNIQUE" ... "(" ... "+" ... "-" ... "/*+" ... > > "INTERVAL" ... <UNSIGNED_INTEGER_LITERAL> ... > > <DECIMAL_NUMERIC_LITERAL> ... <APPROX_NUMERIC_LITERAL> ... > > <BINARY_STRING_LITERAL> ... <PREFIXED_STRING_LITERAL> ... > > <QUOTED_STRING> ... * > > > > * [CUTTING LONG LIST HERE]* > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > * Trying to parse : SELECT DATE('2021-01-01')Error: > > org.apache.calcite.sql.parser.SqlParseException: Incorrect syntax near the > > keyword 'DATE' at line 1, column 8.Was expecting one of: "ALL" ... > > "CURSOR" ... "DISTINCT" ... "EXISTS" ... "NOT" ... "ROW" ... > > "STREAM" ... "UNIQUE" ... "(" ... "+" ...* > > * [CUTTING LONG LIST HERE]* > > > > > > > > Thanks a lot to anyone reading this far ! If you have any idea why this > > doesn't work and the steps needed to make it work, it would be greatly > > appreciated ! :) > > > > If it's actually a problem in the dialect implementation I would be glad to > > make my first contribution to open source software and try to fix it :) > > > > PS: The work you've done is remarkable ! Well done !! > > > > Have a great day, > > > > Florent > >
