calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jark Wu" <wuchong...@alibaba-inc.com>
Subject Re: [DISCUSS] Support CROSS/OUTER APPLY syntax
Date Thu, 27 Oct 2016 07:29:19 GMT
Great! I have logged a JIRA https://issues.apache.org/jira/browse/CALCITE-1472 <https://issues.apache.org/jira/browse/CALCITE-1472>


- Jark Wu 

> 在 2016年10月25日,下午11:54,Julian Hyde <jhyde@apache.org> 写道:
> 
> It seems as if it is syntactic sugar for a feature we already have. So, I’d have no
objections adding this if it was enabled by a SqlConformance setting. (Accompanied by the
appropriate positive and negative tests, and some documentation, of course.)
> 
>> On Oct 25, 2016, at 1:18 AM, Jark Wu <wuchong.wc@alibaba-inc.com> wrote:
>> 
>> Hi all,
>> 
>> Currently we want to implement CROSS APPLY and OUTER APPLY syntax in Flink SQL, and
we hope that it could be supported by Calcite. 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).  
>> 
>> We implement the APPLY operator in Flink SQL by extending Calcite’s Parser.jj,
but it’s not a nice way and not so easy to maintain. So we hope the useful feature could
be supported by Calcite.  And other nicer solutions are welcome.
>> 
>> 
>> [1] https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx <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
<https://github.com/wuchong/flink/blob/60812e51156ec9fa6088154d2f6dea8c1ff9ac17/flink-libraries/flink-table/src/main/codegen/templates/Parser.jj>
>> - Jark Wu 
>> 


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message