You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Florent Martineau <ft...@gmail.com> on 2021/09/24 20:58:35 UTC
Error parsing DATE("2021-01-01") for BigQuery, using Calcite
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
Re: Error parsing DATE("2021-01-01") for BigQuery, using Calcite
Posted by Florent Martineau <ft...@gmail.com>.
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 <jh...@gmail.com> 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 <ft...@gmail.com> 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
>
>
Re: Error parsing DATE("2021-01-01") for BigQuery, using Calcite
Posted by Julian Hyde <jh...@gmail.com>.
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 <ft...@gmail.com> 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