[
https://issues.apache.org/jira/browse/CALCITE-1472?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15662182#comment-15662182
]
Julian Hyde commented on CALCITE-1472:
--------------------------------------
Am I correct that SQL Server's supports using table functions anywhere (not
just in {{CROSS}}/{{OUTER APPLY}}) without the {{TABLE}} keyword? If so, yes,
their syntax is simpler, but their validation is more complex, because tables
and table-functions share a namespace and they have to distinguish between
them. For example, if you write {{SELECT * FROM EMP(1, 2)}} it has to deduce
that {{EMP}} is a table function.
I like the idea of supporting SQL Server's more concise syntax, but let's do it
for all table references, not just {{CROSS}}/{{OUTER APPLY}}, and let's add a
{{SqlConformance}} setting so that people can turn that behavior on/off.
> 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)
