[
https://issues.apache.org/jira/browse/CALCITE-4865?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17558799#comment-17558799
]
Jing Zhang commented on CALCITE-4865:
-------------------------------------
Hi, [~julianhyde] , thanks for your suggestion.
{quote}The main missing thing is productization. Change the description of this
case to indicate WHAT are polymorphic table functions (or rather, what makes a
table function polymorphic), WHY I would want to use one, and give some simple
examples. Also the [table
functions|https://calcite.apache.org/docs/adapter.html#table-functions-and-table-macros]
doc needs to be significantly expanded.
{quote}
I agree.
I have updated the description of the parent JIRA
([CALCITE-4864|https://issues.apache.org/jira/browse/CALCITE-4864]) of this
subtask to describe the points you mentioned.
Besides, I create a
subtask[CALCITE-5198|https://issues.apache.org/jira/browse/CALCITE-5198] to add
PTF in [ table
functions|https://calcite.apache.org/docs/adapter.html#table-functions-and-table-macros]
documentation, including :
* What are polymorphic table functions
* When do we want to use polymorphic table functions
* Give some simple examples of polymorphic table functions
* For PTF author, how to implement a user-defined polymorphic table function
* For query author, how to use PTF function in queries
{quote}I'm still confused about row semantics vs set semantics, and I still
dislike the word 'semantics' for this aspect of behavior
{quote}
Row semantics and set semantics are correspond to {{{}{}}}[ROW SEMANTICS and
TABLE
SEMANTICS|https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-optimization-and-tuning.html#GUID-A496E727-7A54-4F6E-9E8E-82DF7558063E]{{{}{}}}
in oracle. I found oracle also use 'semantics' in its PTF document. I admit
that 'semantics' is really difficult for users to understand, but currently SQL
standard and oracle's user doc both use this word, so I think 'semantics' is
acceptable.
!image-2022-06-26-00-25-14-422.png|width=909,height=141!
{quote}If so, why is it a property of the function in Oracle, but a property of
the parameter in your implementation?
{quote}
Yes, In oracle, row semantics and table semantics are property of the PTF
instead of the property of input table parameter.s It is because Oracle’s
implementation, be aware, not everything from the standard is implemented
exactly. As long as Oracle allows only for one input table, they can also be
used to classify the functions themselves, but if this restriction will
eventually be lifted, this won’t be possible anymore.
SQL standard allows for multiple generic input table parameters having their
own semantic, and these properties are the properties of the input table
parameters.
> Extends Table function to support Polymorphic Table Function
> ------------------------------------------------------------
>
> Key: CALCITE-4865
> URL: https://issues.apache.org/jira/browse/CALCITE-4865
> Project: Calcite
> Issue Type: Sub-task
> Components: core
> Reporter: Jing Zhang
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2021-12-07-11-04-28-206.png,
> image-2022-06-26-00-25-14-422.png
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> This Jira aims to extend existed Table function in order to support
> Polymorphic Table Function, including:
> * Introduces generic input table parameter which has 3 characteristics:
> ## Input tables have either row semantics or set semantics)
> ### Row semantics means that the the result of the PTF is determined only by
> looking at the current row
> ### Set semantics means that the result of the PTF can be determined by
> looking at the current row and some state “summarized” from previously
> processed rows.
> ## The second characteristic, which applies only to input tables with set
> semantics, is whether the PTF can generate a result row even if the input
> table is empty.
> ## The third characteristic is whether the input table supports pass-through
> columns or not. Pass-through columns is a mechanism enabling the PTF to copy
> every column of an input row into columns of an output row.
> * Extends `SqlFunction` to support generic input table parameter
> * Adds validator for table function
> ## only input table parameter with set semantics could be partitioned or
> ordered
> ## At most has one input table parameter with row semantics
> * Updates `Parser.jj` to allow partition by clause and order by clause
> follow after input table parameter of table function
> * Adds test
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
