[jira] [Commented] (CALCITE-4961) Add support for parenthesis-wrapped joined tables

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4961?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17464839#comment-17464839 ] 

Julian Hyde commented on CALCITE-4961:
--------------------------------------

Yes, this is a long-standing problem. The JavaCC fix is very difficult due to grammar ambiguities.

Duplicate of CALCITE-35.

> Add support for parenthesis-wrapped joined tables
> -------------------------------------------------
>
>                 Key: CALCITE-4961
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4961
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Marco Jorge
>            Priority: Major
>
> _(nicetities first - great project and thanks all for the great work)_
> As per [sql-92 grammar|https://ronsavage.github.io/SQL/sql-92.bnf.html], the [joined table|https://ronsavage.github.io/SQL/sql-92.bnf.html#joined%20table] can be wrapped in parenthesis but this is currently not supported in the Calcite grammar. A simple example is `{{{}select * from (table1 join table2 on 1=1){}}}`. The error provided by Calcite is:
> {code:java}
> Non-query expression encountered in illegal context
>     at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
>     at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>     at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
>     at org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
>     at org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:747)
>     ... 20 common frames omitted
> Caused by: java.lang.RuntimeException: parse failed: Non-query expression encountered in illegal context
>     at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:628)
>     at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
>     at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
>     at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
>     at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
>     at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
>     at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
>     ... 22 common frames omitted
> Caused by: org.apache.calcite.sql.parser.SqlParseException: Non-query expression encountered in illegal context
>     at com.outsystems.datafabric.grammar.generated.SqlParserImpl.convertException(SqlParserImpl.java:381)
>     at com.outsystems.datafabric.grammar.generated.SqlParserImpl.normalizeException(SqlParserImpl.java:154)
>     at org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:140)
>     at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:155)
>     at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:180)
>     at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:624)
>     ... 28 common frames omitted
> Caused by: org.apache.calcite.runtime.CalciteException: Non-query expression encountered in illegal context
>     at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>     at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>     at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>     at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
>     at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506)
>     at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:600){code}
> The derivation is:
> {code:java}
> <from clause> = FROM <table reference>
> <from clause> = FROM <joined table>
> <from clause> = FROM <left paren> <joined table> <right paren>
> <from clause> = FROM <left paren> <qualified join> <right paren>
> <from clause> = FROM <left paren> <table reference> [ NATURAL ] [ <join type> ] JOIN <table reference> [ <join specification> ] <right paren>{code}
> I couldn't find a way to extend Calcite's default javacc grammar to support this syntax without having to replicate the whole file.
> It would be great to either add support for this syntax (since Calcite aims to be sql92 compliant) OR provide an extension point for customisation.
>  
> I am not familiar enough with javacc to contribute with a quality solution though.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)