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
