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
