I think calcite shouldn't prohibit the compound ORDER BY for the window( *UNBOUNDED PRECEDING and current row*).
John Fang <[email protected]> 于2018年5月15日周二 上午11:05写道： > *Thanks for Julian.* > > *I test those query in Oracle for the table test(id int, name varchar(50), > score int).* > > > * For range based vachar type of ORDER BY, those query's behavior is same > with Oracle.* > > *In Oracle:* > query-1: select id, count(1) over (partition by id order by name range > between UNBOUNDED PRECEDING and current row) from test; ----> ok > query-2: select id, count(1) over (partition by id order by name) from > test; ----> ok > query-3: select id, count(1) over (partition by id order by name range between > UNBOUNDED PRECEDING and 0 following) from test; ----> fail("invalid > datatype") > *In Calcite:* > query-1: select id, count(1) over (partition by id order by name range > between UNBOUNDED PRECEDING and current row) from test; ----> ok > query-2: select id, count(1) over (partition by id order by name) from > test; ----> ok > query-3: select id, count(1) over (partition by id order by name range between > UNBOUNDED PRECEDING and 0 following) from test; ----> fail("Data type of > ORDER BY prohibits use of RANGE clause") > > *But for range based compound ORDER BY , those query's behavior is > different from Oracle.* > *In Oracle:* > query-1: select id, count(1) over (partition by id order by id, score > range between > *UNBOUNDED PRECEDING and current row) from test; ----> ok* > query-2: select id, count(1) over (partition by id order by id, score ) > from test; ----> ok > query-3: select id, count(1) over (partition by id order by id, score > between > UNBOUNDED PRECEDING and 0 following) from test; ----> fail("invalid > window aggregation group in the window specification") > *In Calcite:* > query-1: select id, count(1) over (partition by id order by id, score > range between > *UNBOUNDED PRECEDING and current row) from test; ----> fail("RANGE clause > cannot be used with compound ORDER BY clause")* > query-2: select id, count(1) over (partition by id order by id, score ) > from test; ----> ok > query-3: select id, count(1) over (partition by id order by id, score > between > UNBOUNDED PRECEDING and 0 following) from test; ----> fail("RANGE clause > cannot be used with compound ORDER BY clause") > > > > Julian Hyde <[email protected]> 于2018年5月15日周二 上午12:51写道： > >> I still think that the first query is invalid and the second is valid. >> The first query requires not just a range but a distance. You can compute >> the distance between two numeric or timestamp values (by subtracting them) >> but not between two varchar values. The second query requires a “unbounded >> preceding to current row” range but there is no distance in that >> specification. >> >> For further proof (I haven’t checked) I would see what the SQL standard >> says, and what Oracle does. >> >> It is possible that they would use a range “unbounded preceding to >> unbounded following”. In which case we need to comply with the standard - >> but we could make an exception for streaming queries. >> >> Julian >> >> >> > On May 14, 2018, at 12:26 AM, John Fang <[email protected]> wrote: >> > >> > win("window w as (order by ename range ^100^ preceding)") >> > .fails("Data type of ORDER BY prohibits use of RANGE clause"); >> > >> > The query will be prohibited in calcite because the ename's type is >> > varchar(20). But the followed query will be allowed in calcite. >> > >> > win("window w as (partition by ename order by ename)").ok(); >> > >> > The former window still be range window frame because RANGE UNBOUNDED >> > PRECEDING AND CURRENT ROW is used as default for window frame if >> ROWS/RANGE >> > is not specified but ORDER BY is specified. So I think the former query >> > should also be prohibited. >> > >> > -- >> > >> > Regards >> > >> > John Fang >> >> > > -- > > Regards > > John Fang > > > -- Regards John Fang
