[
https://issues.apache.org/jira/browse/CALCITE-4337?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17428157#comment-17428157
]
JING ZHANG commented on CALCITE-4337:
-------------------------------------
[~julianhyde] I think it's a good idea to update
`[server/.../config.fmpp|https://github.com/apache/calcite/blob/2916b83712b517430e71c1c22042e484ee900589/server/src/main/codegen/config.fmpp#L72]
` in order to support define a TableFunction. For example, we could use the
following SQL to define a Table Function named `TopNplus`.
{code:java}
CREATE FUNCTION TopNplus (
Input TABLE NO PASS THROUGH WITH SET SEMANTICS PRUNE WHEN EMPTY,
Howmany INTEGER
) RETURNS TABLE
NOT DETERMINISTIC
READS SQL DATA{code}
The DDL defines the following characteristics:
# Function name
# Function arguments
# Input table characteristics:
## Input table semantics: ROW SEMANTICS or SET SEMANTICS, SET SEMANTICS is
default behavior.
## prunability : The characteristics is required if input table semantics is
SET semantic. PTF can generate a result row on empty input, the table is said
to be "keep when empty". The alternative is called "prune when empty". The
prunability characteristic is not applied for ROW SEMANTICS input table.
## PASS-THROUGH COLUMNS: "Pass-through" columns is a mechanism enabling the
PTF to copy every column of an input row into columns of an output row. The
alternative is "NO PASS THROUGH".
Besides, I still have three questions.
# Should we only define user defined table function based in DDL? Do we need
to define existed builtin window table function (e.g Tumbling Window TVF/HOP
Window TVF/Session Window TVF) in DDL or not?
# I think we should also change query parser in Core module (Parser.jj),
right? A user should write the following SQL query to use the `TopNplus`
function defined in the above example. So we need to update query parser in
order to allow `PARTITION BY` clause and `ORDER BY` clause.
{code:java}
SELECT S.Region, T.*
FROM TABLE ( TopNplus ( Input => TABLE (My.Sales) AS S
PARTITION BY Region
ORDER BY Sales DESC,
Howmany => 3
)
) AS T{code}
# In addition, we also need validate whether PTF query is valid. For example,
throw exception if define `PARTITION BY` clause or `ORDER BY` clause for input
table parameter with ROW SEMANTICS.
What do you think?
> 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)
