Calcite Engine Support for Oracle SQL89 Outer Join Syntax

["Kimieciki, Igor (ESI)" <ig...@adp.com.INVALID>]

Hello Calcite Development Team!

I'm a software developer working on a project which includes Oracle SQL queries parsing and transformation, and I'm using Calcite's engine to accomplish some of my objectives.

I've been facing some issues regarding the parsing of SQL89 queries, more specifically the ones which use oracles outer join "(+)" syntax.

For instance, when trying to parse the query below into an SqlNode object:

SELECT *
from TAB1, TAB2
where TAB1.key = TAB2.key (+)

I would receive the following exception:

org.apache.calcite.sql.parser.impl.ParseException: Encountered ")" at line 3, column 26.


I could not find any references for this operations on calcite's forums and documentations, so I'm reaching out this mail list to clarify those errors.

Does Calcite currently support SQL89 outer join operations with (+) syntax? if not, is there any recommended workaround to process queries that use it?

Any feedbacks are welcome and thank you for your time.

Best Regards,

Igor Kimieciki

Re: Calcite Engine Support for Oracle SQL89 Outer Join Syntax

[Julian Hyde <jh...@gmail.com>]

Calcite does not support Oracle’s outer join syntax (“(+)”) or, for that matter, SQL Server’s syntax (“(*)”).

Please log a JIRA case, asking for both to be added to the Babel parser. (I don’t want to add it to the Core parser, because it will cause ambiguities.)

I don’t know of a workaround.

Julian




> On May 27, 2021, at 9:50 AM, Kimieciki, Igor (ESI) <ig...@adp.com.INVALID> wrote:
> 
> Hello Calcite Development Team!
> 
> I'm a software developer working on a project which includes Oracle SQL queries parsing and transformation, and I'm using Calcite's engine to accomplish some of my objectives.
> 
> I've been facing some issues regarding the parsing of SQL89 queries, more specifically the ones which use oracles outer join "(+)" syntax.
> 
> For instance, when trying to parse the query below into an SqlNode object:
> 
> SELECT *
> from TAB1, TAB2
> where TAB1.key = TAB2.key (+)
> 
> I would receive the following exception:
> 
> org.apache.calcite.sql.parser.impl.ParseException: Encountered ")" at line 3, column 26.
> 
> 
> I could not find any references for this operations on calcite's forums and documentations, so I'm reaching out this mail list to clarify those errors.
> 
> Does Calcite currently support SQL89 outer join operations with (+) syntax? if not, is there any recommended workaround to process queries that use it?
> 
> Any feedbacks are welcome and thank you for your time.
> 
> Best Regards,
> 
> Igor Kimieciki
> 
>