You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Jing Zhang (Jira)" <ji...@apache.org> on 2022/06/25 16:36:00 UTC
[jira] [Comment Edited] (CALCITE-4865) Extends Table function to support Polymorphic Table Function
[ https://issues.apache.org/jira/browse/CALCITE-4865?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17558799#comment-17558799 ]
Jing Zhang edited comment on CALCITE-4865 at 6/25/22 4:35 PM:
--------------------------------------------------------------
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) of this subtask to describe the points you mentioned.
Besides, I create a subtaskCALCITE-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.
was (Author: qingru zhang):
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)