[
https://issues.apache.org/jira/browse/CALCITE-4337?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17427454#comment-17427454
]
JING ZHANG commented on CALCITE-4337:
-------------------------------------
[~julianhyde] Sorry for late response because we were on a vocation holiday.
Thanks for great suggestions and professional questions. I spent some time to
read SQL standard again to collect more information about the questions. Please
correct me if something is wrong.
# I totally agree with you to introduce PTF as general feature. PTF is the
evolution of the table functions since ANSI SQL 2016. They have the following
different features compare with normal Table function.
## PTF may have generic table parameters which row type are not declared at
design time.
## PTF returns a table whose row type is not declared at design time, it may
depend on the function arguments.
# Strictly speaking, Row semantic/Set semantic is indeed a property of
parameter. SQL standard allows for multiple generic input table parameters
having their own semantic. However, some database vendors have weakened this
restriction. For example Oracle (18.0.1) allows only one table parameter for
PTF，so they can also be used to classify the PTF themselves.
# Whether an input table parameter of PTF is Row semantic or Set semantic is
deterministic. The input tables with set semantics may be partitioned on one or
more columns. An input table with at least one partitioning column is said to
be partitioned (even if, in fact, the partitioning column has only one value).
An input table with no partitioning column is said to be broadcast.
# Input tables with row semantics may not be partitioned. It is reasonable to
throw exception if input tables with row semantics follow by `PARTITION BY`.
Inputs tables with set semantics could be partitioned, however, it could also
valid without `PARTITION BY`.
> Support PARTITION BY clause in table function call
> --------------------------------------------------
>
> Key: CALCITE-4337
> URL: https://issues.apache.org/jira/browse/CALCITE-4337
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Affects Versions: 1.26.0
> Reporter: Danny Chen
> Assignee: Danny Chen
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2021-09-30-11-04-33-474.png,
> image-2021-09-30-11-05-12-400.png, image-2021-09-30-11-05-42-033.png
>
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> An example from the SQL standard 2016 Polymorphic Table Functions:
> {code:sql}
> SELECT W.wstart, W.wend, OI.customer, SUM(OI.price)
> FROM TABLE(SESSION(
> data => TABLE(order_item) AS OI PARTITION BY customer,
> timecol => DESCRIPTOR(order_time),
> timeout => INTERVAL '10' MINUTE)) W
> GROUP BY 1, 2, 3
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
