[
https://issues.apache.org/jira/browse/CALCITE-4337?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17428144#comment-17428144
]
Viliam Durina commented on CALCITE-4337:
----------------------------------------
My understanding is this: The SQL standard about PTFs introduced two orthogonal
features:
* ability to take and return tables with row type _not_ declared at design
time (the whole feature is named after this)
* ability to parallelize functions with table arguments based on partitioning
The PARTITION BY clause is about the 2nd feature, Calcite already has the 1st
feature. Traditionally, tables were passed into functions as a single value (as
a `List<Tuple>` in Java). `PARTITION BY 0` would implement the traditional
behavior, all rows passed in one set. Partitioning by some expression allows
parallel execution of the function. It also allows stream processing, if
partitioning by some time column. The ROW semantics is a special case of
partitioning where each row is its own partition (as in `PARTITION BY
primaryKey`).
Stating this, we can think of each table argument to a function to be
partitioned in some way.
Second point: in general, you can't partition a collection of rows by two
arbitrary partitioning keys at the same time. These two partitionings have to
be "compatible", i.e. every partition of rows from one partitioning expression
must be contained entirely in a partition of rows from the other partitioning
expression. (Maybe there's a mathematical term for this, Julian might help with
that :)). For example, if one table is partitioned by surname and name (in this
order) and the other table is partitioned only by surname, the partitioning is
compatible. So if a function has multiple table arguments, their partitioning
must be compatible. Note that ROW semantics and the traditional one-value
semantics are compatible with any other partitioning.
And after writing this, third problem came up to my mind: whether the input to
the SESSION function should be partitioned by time first. If it is, the
function will receive a set of rows for each time in a separate set, and then
it cannot evaluate the session end. If it's not partitioned by time, then the
function is supposed to receive the whole set of rows for one key, and that's
not possible in unbounded streams. Intuitively it seems that the table
shouldn't be partitioned by the time column, but the data for each partition
should be streamed into the function. I don't know how to resolve this. But
this discussion should be a part of the second issue about the SESSION function.
> 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)
