Sounds like there’s a bug in the Babel parser that it can’t handle IF. Can you please log it.
Julian > On Sep 25, 2021, at 9:28 AM, Florent Martineau <[email protected]> > wrote: > > Thank you very much Julian for your quick and accurate answer ! > > Indeed, the Babel parser factory made me get rid of the error when trying to > parse DATE("2021-01-01"). > Nevertheless, by switching from SqlParserImpl.FACTORY to > SqlBabelParserImpl.FACTORY, I now have an error trying to parse statements > such as IF(condition, then, else). > I made a quick check, for regular Bigquery queries (eg. CURRENT_DATETIME), it > works with both parsers. Nevertheless, when I use Babel, DATE(string) > succeeds and IF(...) fails. When I use the regular parser, DATE(..) fails and > IF(..) succeeds. > Here is the code I used to check: https://snipit.io/public/snippets/57895 > And the logs when running those checks: > https://snipit.io/public/snippets/57896 > > Have a great weekend ! > Florent >> On 2021/09/25 00:38:16, Julian Hyde 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 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" ... ... >>> ... ... >>> ... ... >>> ... * >>> >>> * [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 >> >> > Sent from Mailspring (https://getmailspring.com/), the best free email app > for work
