git.net

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: SqlValidator can't prohibit the data type of ORDER BY with RANGE prober.


*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 <jhyde@xxxxxxxxxx> 于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 <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.
> >
> > --
> >
> > Regards
> >
> > John Fang
>
>

-- 

Regards

John Fang