calcite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jark Wu" <wuchong...@alibaba-inc.com>
Subject [DISCUSS] Support CROSS/OUTER APPLY syntax
Date Tue, 25 Oct 2016 08:18:10 GMT
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