Thanks for the sharing, after some research we found that many DB engines has poor support for nested row type, for example, assumes: my_type is a UDT with schema as (a: int, b: varchar(20)) table T has schema as (f0: my_type, f1: int, f2: varchar(20))
PostgreSQL: select f0.a, max(f1) from T group by f0 -- fails because it recognize the "f0.a" f0 as a table name select (f0).a, max(f1) from T group by f0 -- succeed Oracle: select f0.a, max(f1) from T group by f0 -- fails because group by UDT is not supported In Calcite, there is a fact that the nested row optimize rules are poor supported, such as the project prune, transpose. The nested row nullability is also a topic that needs to be clarified. Currently, when a nested type field is nullable, all it's fields are nullable too, that is not true for Java POJO: User(id: int, name: string) (e.g. its fields have un-boxed type) Pengcheng Liu <pengchengliucr...@gmail.com> 于2020年10月23日周五 下午1:46写道： > Hi, Danny, > I think there is already an explanation in [1], as the author noted: > > The return value of Tumble is a relation that includes all columns of > data as well as > > additional event time columns wstart and wend. It was considered to place > the original > > row in a nested row and wstart and wend in a separate nested row, for > simplicity of > > namespacing, but that would limit these extensions to engines supporting > nested rows. > > [1] > > https://docs.google.com/document/d/138uA7VTpbF84CFrd--cz3YVe0-AQ9ALnsavaSE2JeE4 > > > Danny Chan <danny0...@apache.org> 于2020年10月22日周四 下午7:40写道： > > > Rui Wang, can you help to consult with the author of the window TVF > syntax > > ? We want to hear more suggestions. > > > > cc @Rui Wang > > > > Rui Wang <amaliu...@apache.org> 于2020年10月22日周四 下午12:01写道： > > > > > This seems reasonable to merge window metadata as a column (more > compact > > > format). > > > > > > Maybe Julian can comment whether there was special consideration to > > define > > > window_start and window_end as two separate columns? > > > > > > > > > -Rui > > > > > > On Wed, Oct 21, 2020 at 8:20 PM Danny Chan <danny0...@apache.org> > wrote: > > > > > > > Some of our fellows suggest the window table value functions to > append > > a > > > > structure type field: > > > > > > > > window: ROW(start: timestamp(3), end: timestamp(3)) > > > > > > > > For example if > > > > > > > > table T has schema: (ts: timestamp(3), b: varchar(20)), then > > > > > > > > Tumble(TABLE T, DESCRIPTOR(T.ts), INTERVAL '5' MINUTE) > > > > > > > > has return type > > > > > > > > Row(ts timestamp(3), b varchar(20), window Row(start timestamp(3), > end > > > > timestamp(3))) > > > > > > > > Thus in the aggregate key or the join condition, we can compare the > > > > structure “window” field directly, e.g. > > > > > > > > GROUP BY T.window or ON L.window = R.window > > > > > > > > What do you think about this ? > > > > > > > > Rui Wang <amaliu...@apache.org> 于2020年6月9日周二 上午1:34写道： > > > > > > > > > On Mon, Jun 8, 2020 at 12:04 AM Viliam Durina < > vil...@hazelcast.com> > > > > > wrote: > > > > > > > > > > > > 2. rename the hop_size to emit_frequency. Because that's what > > this > > > > > > argument tries to say: e.g. emit in every x time_unit. > > > > > > > > > > > > It's not about how often you emit. By "emit" I mean the moment > when > > > the > > > > > > rows are sent out. For example, when working with event time and > > the > > > > > input > > > > > > stalls, you might emit much later. Or you can use it for > historical > > > > data > > > > > in > > > > > > which case the actual interval will be much shorter. It's the > time > > > > > interval > > > > > > by which the window "hops". Two subsequent windows will be this > > much > > > > > apart. > > > > > > > > > > > > Regarding the DESCRIPTOR, the PTF is supposed to be implemented > by > > up > > > > to > > > > > 4 > > > > > > member functions: describe, start, fulfill, finish. The > `describe` > > > > > function > > > > > > is supposed to be called during validation. It can validate the > > > > > arguments. > > > > > > In case of TUMBLE, it can check whether the column described by > the > > > > > > descriptor is present in the input table and if it is of a > correct > > > > type. > > > > > It > > > > > > can throw errors which will become validation errors. > > > > > > > > > > Yes. Descriptor has a feature to enable a type checking. If > > considering > > > > it, > > > > > functions will be (argument name not decided yet): > > > > > > > > > > TUMBLE( > > > > > data TABLE, > > > > > time_column DESCRIPTOR(*TIMESTAMP*), > > > > > window_size INTERVAL DAY TO SECOND, > > > > > offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS) > > > > > > > > > > HOP( > > > > > data TABLE, > > > > > time_column DESCRIPTOR(*TIMESTAMP*), > > > > > window_size INTERVAL DAY TO SECOND, > > > > > emit_frequency INTERVAL DAY TO SECOND, > > > > > offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS) > > > > > > > > > > > > > > > > > > > > > > > > > > It also determines > > > > > > the actual output table type. > > > > > > > > > > > > I think it's worthwhile to read the PTF spec before implementing > > > this, > > > > > it's > > > > > > freely available here: > > > > > > > > > > > > > > > > > > > > > > > > > > > https://standards.iso.org/ittf/PubliclyAvailableStandards/c069776_ISO_IEC_TR_19075-7_2017.zip > > > > > > > > > > > > Viliam > > > > > > > > > > > > On Sun, 7 Jun 2020 at 06:23, Rui Wang <amaliu...@apache.org> > > wrote: > > > > > > > > > > > > > Thanks Danny for your summary! > > > > > > > > > > > > > > For question one, I prefer the ordering of parameter in option > > two. > > > > > > > However, for argument names, I think it might be better to > > > > > > > 1. name the window size explicitly by "window_size". > > > > > > > 2. rename the hop_size to emit_frequency. Because that's what > > this > > > > > > > argument tries to say: e.g. emit in every x time_unit. > > > > > > > 3. don't use "table" as the first argument name, because > "table" > > > is a > > > > > > > keyword. So we can replace it by "data" > > > > > > > > > > > > > > so it might be > > > > > > > TUMBLE(data, DESCRIPTOR(time_column), window_size[, offset]) > > > > > > > HOP(data, DESCRIPTOR(time_column), window_size, > emit_frequency[, > > > > > offset]) > > > > > > > > > > > > > > Meanwhile, one thing worths mentioning is, like Viliam always > > > > discussed > > > > > > in > > > > > > > CALCITE-4000[1], we can also finalize the data types for each > > > > > signature: > > > > > > > TUMBLE( > > > > > > > data TABLE, > > > > > > > time_column DESCRIPTOR, > > > > > > > window_size INTERVAL DAY TO SECOND, > > > > > > > offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS) > > > > > > > > > > > > > > HOP( > > > > > > > data TABLE, > > > > > > > time_column DESCRIPTOR, > > > > > > > window_size INTERVAL DAY TO SECOND, > > > > > > > emit_frequency INTERVAL DAY TO SECOND, > > > > > > > offset INTERVAL DAY TO SECOND DEFAULT INTERVAL '0' SECONDS) > > > > > > > > > > > > > > So those intervals have to be INTERVAL DAY TO SECOND, not > > INTERVAL > > > > YEAR > > > > > > TO > > > > > > > MONTH, because year to month varies in seconds. > > > > > > > > > > > > > > > > > > > > > Regarding question two, I tried to dig into codebase and I > think > > it > > > > > could > > > > > > > be a RexCorrelVariable to replace that table_name, so it > becomes > > > > > > > table_function(RexCorrelVariable, DESCRIPTOR($1), > > internal:INTERVAL > > > > > > MINUTE) > > > > > > > > > > > > > > I know RexCorrelVariable might be not designed for this > purpose, > > > but > > > > I > > > > > > > find RexCorrelVariable is really useful because: > > > > > > > 1. It can provide an id, to indicate which input of > > > TableFunctionScan > > > > > > that > > > > > > > this table_function is applied on. Note that TableFunctionScan > > > might > > > > > not > > > > > > > have one input. > > > > > > > 2. It can provide RelDataType, to save the referenced input's > > > schema. > > > > > The > > > > > > > input schema is useful during operator argument type validation > > > (for > > > > > > > DESCRIPTOR) and operator return type inference. > > > > > > > > > > > > > > > > > > > > > [1]: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > https://jira.apache.org/jira/browse/CALCITE-4000?focusedCommentId=17107665&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-17107665 > > > > > > > > > > > > > > > > > > > > > -Rui > > > > > > > > > > > > > > On Thu, Jun 4, 2020 at 6:02 AM Danny Chan < > yuzhao....@gmail.com> > > > > > wrote: > > > > > > > > > > > > > > > Hi, all, in the last 2 releases, Rui Wang has contributed the > > > > window > > > > > > > table > > > > > > > > functions to replace the legacy group window syntax, the idea > > > comes > > > > > > from > > > > > > > > the SQL-2016 polymorphic table functions. But the current > > > > > > implementation > > > > > > > > also got some feedbacks(or confusion), this thread tries to > > have > > > a > > > > > > > > discussion on theses questions that are undecided. > > > > > > > > > > > > > > > > One is about the window function argument names: > > > > > > > > > > > > > > > > === option1 > > > > > > > > TUMBLE(table, DESCRIPTOR(datetime), interval, offset) > > > > > > > > HOP(table, DESCRIPTOR(time_column), slide, size) > > > > > > > > > > > > > > > > === option2 > > > > > > > > TUMBLE(table, DESCRIPTOR(time_column), size, offset) > > > > > > > > HOP(table, DESCRIPTOR(time_column), size, hop_size, offset) > > > > > > > > > > > > > > > > I would prefer for option2 for the reasons pointed out by > > Viliam > > > in > > > > > > > > CALCITE-3737. > > > > > > > > > > > > > > > > > > > > > > > > Another is about how to translate the query > > > > > > > > > > > > > > > > For query, select * from TUMBLE(table, > DESCRIPTOR(time_column), > > > > > > > interval); > > > > > > > > > > > > > > > > Currently our outputs plan is: > > > > > > > > > > > > > > > > LogicalProject(ORDERID=[$0], ROWTIME=[$1], window_start=[$2], > > > > > > > > window_end=[$3]) > > > > > > > > LogicalTableFunctionScan(invocation=[TUMBLE(DESCRIPTOR($1), > > > > > > > > interal:INTERVAL MINUTE)], rowType=[RecordType(INTEGER > ORDERID, > > > > > > > > TIMESTAMP(0) time_column, TIMESTAMP(0) window_start, > > TIMESTAMP(0) > > > > > > > > window_end)]) > > > > > > > > LogicalProject(ORDERID=[$0], ROWTIME=[$1]) > > > > > > > > LogicalTableScan(table=[[CATALOG, SALES, SHIPMENTS]]) > > > > > > > > > > > > > > > > But this seems not right, because the "table table_name" > > argument > > > > is > > > > > > not > > > > > > > a > > > > > > > > relational expression, in CALCITE-3955, Julian has pointed > out > > > that > > > > > we > > > > > > > > should translate the "table" argument as a CURSOR (or TABLE > by > > > > > Viliam), > > > > > > > but > > > > > > > > another question is how to translate the referenced name > > > > > "time_column", > > > > > > > to > > > > > > > > a correlate variable ? which is also confusing because there > is > > > no > > > > > > > > correlation in the plan actually. > > > > > > > > > > > > > > > > Any suggestions are appreciated ! > > > > > > > > > > > > > > > > > > > > > > > > [1] https://issues.apache.org/jira/browse/CALCITE-3955 > > > > > > > > [2] https://issues.apache.org/jira/browse/CALCITE-3737 > > > > > > > > > > > > > > > > Best, > > > > > > > > Danny Chan > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > Viliam Durina > > > > > > Jet Developer > > > > > > hazelcast® > > > > > > > > > > > > <https://www.hazelcast.com> 2 W 5th Ave, Ste 300 | San Mateo, > CA > > > > > 94402 | > > > > > > USA > > > > > > +1 (650) 521-5453 <(650)%20521-5453> <(650)%20521-5453> | > > > > hazelcast.com < > > > > > > https://www.hazelcast.com> > > > > > > > > > > > > -- > > > > > > This message contains confidential information and is intended > only > > > for > > > > > > the > > > > > > individuals named. If you are not the named addressee you should > > not > > > > > > disseminate, distribute or copy this e-mail. Please notify the > > sender > > > > > > immediately by e-mail if you have received this e-mail by mistake > > and > > > > > > delete this e-mail from your system. E-mail transmission cannot > be > > > > > > guaranteed to be secure or error-free as information could be > > > > > intercepted, > > > > > > corrupted, lost, destroyed, arrive late or incomplete, or contain > > > > > viruses. > > > > > > The sender therefore does not accept liability for any errors or > > > > > omissions > > > > > > in the contents of this message, which arise as a result of > e-mail > > > > > > transmission. If verification is required, please request a > > hard-copy > > > > > > version. -Hazelcast > > > > > > > > > > > > > > > > > > > > >
