[jira] [Comment Edited] (CALCITE-1472) Support CROSS/OUTER APPLY syntax

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

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

Julian Hyde edited comment on CALCITE-1472 at 10/28/16 3:23 AM:
----------------------------------------------------------------

See https://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html. If you execute the statement {{select * from emp where deptno \!= 10}} and you are not running with conformance=ORACLE_10, the method {{isBangEqualAllowed()}} will return false, and the validator will throw an error. We do this because we want people to use {{<>}} rather than {{!=}} in vanilla Calcite.

So, you'll need to add a new method to SqlConformance for this functionality.


was (Author: julianhyde):
See https://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html. If you execute the statement {{select * from emp where deptno != 10}} and you are not running with conformance=ORACLE_10, the method {{isBangEqualAllowed()}} will return false, and the validator will throw an error. We do this because we want people to use {{<>}} rather than {{!=}} in vanilla Calcite.

So, you'll need to add a new method to SqlConformance for this functionality.

> Support CROSS/OUTER APPLY syntax
> --------------------------------
>
>                 Key: CALCITE-1472
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1472
>             Project: Calcite
>          Issue Type: New Feature
>            Reporter: Jark Wu
>            Assignee: Julian Hyde
>
> The CROSS/OUTER APPLY is very similar to CROSS JOIN and OUTER JOIN.  The difference is that the APPLY operator is used to invoke a table-valued function. This is not a standard SQL syntax , but introduced from MS SQL Server [1]. 
> The APPLY operator can be expressed by Calcite’s LATERAL TABLE . That means the 
> SELECT MyTable.*, t.s FROM MyTable CROSS APPLY split(MyTable.a)) AS t(s)
> corresponds to :
> SELECT MyTable.*, t.s FROM MyTable, LATERAL TABLE(split(MyTable.a)) AS t(s)
> and
> SELECT MyTable.*, t.s FROM MyTable OUTER APPLY split(MyTable.a)) AS t(s)
> corresponds to:
> SELECT MyTable.*, t.s FROM MyTable LEFT JOIN LATERAL TABLE(split(MyTable.a)) AS t(s)  ON TRUE
> The ON TRUE part is necessary for LEFT JOIN, but it's trivial for users. That's why I'm introducing "CROSS/OUTER APPLY" which will simplify the SQL a lot.
> As the APPLY can be expressed by LATERAL, so the only thing we need to touch is the Parser (see [2] and search FLINK to find the modification).  
> [1] https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
> [2] https://github.com/wuchong/flink/blob/60812e51156ec9fa6088154d2f6dea8c1ff9ac17/flink-libraries/flink-table/src/main/codegen/templates/Parser.jj



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)