Thanks for the examples. The PARTITION BY syntax is a clear improvement for the SESSION function and I think we should do it, even though it is breaking.
I’ll make further comments against https://issues.apache.org/jira/browse/CALCITE-4337 <https://issues.apache.org/jira/browse/CALCITE-4337>. > On Sep 29, 2021, at 9:58 PM, JING ZHANG <beyond1...@gmail.com> wrote: > > Hi Julian, > Thanks for your feedback, the suggestion is very helpful. > I've added the discussion content the CALCITE-4337 > <https://issues.apache.org/jira/browse/CALCITE-4337> [1]. I would continue > later discussion in the JIRA case. > About an example of a query before and after the syntax change. I would use > the example in session table function document > <https://calcite.apache.org/docs/reference.html#session> [2]. > Old syntax demo: > >> SELECT * FROM TABLE( SESSION( TABLE orders, DESCRIPTOR(rowtime), >> DESCRIPTOR(product), INTERVAL '20' MINUTE)); -- or with the named params -- >> note: the DATA param must be the first SELECT * FROM TABLE( SESSION( DATA >> => TABLE orders, TIMECOL => DESCRIPTOR(rowtime), KEY => DESCRIPTOR(product >> ), SIZE => INTERVAL '20' MINUTE)); > > > New syntax demo is as follows, the difference is use PARTITION BY clause to > replace KEY DESCRIPTOR. > >> SELECT * FROM TABLE( SESSION( TABLE orders PARTITION BY product, >> DESCRIPTOR(rowtime), INTERVAL '20' MINUTE)); -- or with the named params -- >> note: the DATA param must be the first SELECT * FROM TABLE( SESSION( DATA >> => TABLE orders PARTITION BY product, TIMECOL => DESCRIPTOR(rowtime), SIZE >> => INTERVAL '20' MINUTE)); > > > Best, > JING ZHANG > > Julian Hyde <jhyde.apa...@gmail.com> 于2021年9月30日周四 上午4:55写道： > >> Regarding changes to the syntax of the SESSION table function. I am open >> to this, even though it would be a breaking change. Can you give an example >> of a query before and after the syntax change? >> >> I would like to support the new PARTITIONED BY clause for table functions. >> I encourage you to make the change for table functions in general, before >> and separately from the change to the SESSION function and window functions. >> >> Please ensure that the discussion gets added to the JIRA case. It might be >> best if we continue discussion in the JIRA case. >> >> Julian >> >> >>> On Sep 28, 2021, at 10:28 PM, JING ZHANG <beyond1...@gmail.com> wrote: >>> >>> Hi community, >>> I'm now working on CALCITE-4337 >>> <https://issues.apache.org/jira/browse/CALCITE-4337> [1] which aims to >>> support PARTITION BY clause for table function argument. >>> I've submitted a pull request >>> <https://github.com/apache/calcite/pull/2524> [2], >>> thanks @Danny very much for review. >>> There are two points left which need more discussion. So I fire this >>> discussion in order to get more broader suggestions. >>> 1. SQL standard Polymorphic Table Functions >>> < >> https://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip >>> >>> [3] >>> states: >>> >>>> Input tables have either row semantics or set semantics, as follows: >>>> a) Row semantics means that the the result of the PTF is decided on a >>>> row-by-row basis. As an extreme example, the DBMS could atomize the >> input >>>> table into individual rows, and send each single row to a different >> virtual >>>> processor. >>>> b) Set semantics means that the outcome of the function depends on how >> the >>>> data is partitioned. A partition may not be split across virtual >>>> processors, nor may a virtual processor handle more than one partition. >>> >>> >>> A SESSION window has an input table with set semantics which means it >>> requires a PARTITION BY clause. >>> The new syntax is conflict with current session window table function >>> syntax, please take a look at session table function >>> <https://calcite.apache.org/docs/reference.html#session> [4]. >>> *Could we replace the old syntax directly, or take compatible into >>> consideration.* >>> 2. Based on SQL standard, only input tables with set semantics may be >>> partitioned while input table with row semantics may not be partitioned. >>> *Should we have separate branch in Parser.jj for set semantic input table >>> of table function(Currently, only input table of session window table >>> function has set semantics)*? >>> >>> Any suggestion is appreciated. Thanks in advanced. >>> [1] https://issues.apache.org/jira/browse/CALCITE-4337 >>> [2] https://github.com/apache/calcite/pull/2524 >>> [3] >>> >> https://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip >>> [4] https://calcite.apache.org/docs/reference.html#session >>> >>> Best >>> JING ZHANG >> >>
