Re: SqlValidator can't prohibit the data type of ORDER BY with RANGE prober.
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.
> On May 14, 2018, at 12:26 AM, John Fang <hustfxj@xxxxxxxxx> 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.
> John Fang