As you know, streams.html is a specification. We do not claim that it is all implemented.
Did you do a search of the existing tests? JdbcTest.testWinAgg2 features windows that have a variety of bounds, and produces the correct results. There are also tests in winagg.iq. I suspect that the “constants” field of Window is not output as part of the explain for Window (or LogicalWindow). The $2 and $3 refer to those hidden constants. Julian > On Nov 2, 2016, at 10:53 AM, Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com> > wrote: > > Hello, > > Can someone inform me if we can define the bounds of sliding windows with > OVER in Calcite at this moment? I am trying to define sliding windows > according to the examples given in https://calcite.apache > .org/docs/stream.html and I keep getting wrong results. Some examples and > the plans (I use ProjectToWindowRUle) they generate are : > 1) > "select s.orders.productid, SUM(units) over pr " + "from > s.orders " + "window pr as (ORDER BY productid ROWS BETWEEN 5 PRECEDING AND > 10 FOLLOWING)" > > ==> > LogicalProject(productid=[$0], EXPR$1=[CASE(>($2, 0), CAST($3):INTEGER, > null)]) LogicalWindow(window#0=[window(partition {} order by [0] rows > between $2 PRECEDING and $3 FOLLOWING aggs [COUNT($1), $SUM0($1)])]) > LogicalProject(productid=[$1], units=[$2]) LogicalTableScan(table=[[s, > orders]]) > > in which, the numbers I have used are "converted" to columns. > > 2) > "select s.orders.productid, SUM(units) over (ORDER BY productid ROWS > BETWEEN 5 PRECEDING AND 10 FOLLOWING) " + "from s.orders " > > For this I get the same plan as before. > > 3)The same goes for RANGE : > > "select s.orders.productid, SUM(units) over (ORDER BY productid > RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING) " > + "from s.orders " > > 4) > "select s.orders.productid, SUM(units) over (ORDER BY productid > RANGE 3600 PRECEDING) " > + "from s.orders " > > ==> > LogicalProject(productid=[$0], EXPR$1=[CASE(>($2, 0), CAST($3):INTEGER, > null)]) > LogicalWindow(window#0=[window(partition {} order by [0] range between $2 > PRECEDING and CURRENT ROW aggs [COUNT($1), $SUM0($1)])]) > LogicalProject(productid=[$1], units=[$2]) > LogicalTableScan(table=[[s, orders]]) > > Can someone provide me a working example? > > Thanks, > George > > 2016-10-18 20:33 GMT+03:00 Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com>: > >> Also this query in Calcite : >> >> "select * from (" >> + "select s.orders.productid , avg(units) OVER (product >> ROWS BETWEEN 10 PRECEDING and 5 FOLLOWING)" + " as m10, " >> + "AVG(units) OVER (product RANGE INTERVAL '7' DAY >> PRECEDING) AS d7 " >> + "from s.orders " >> + " WINDOW product AS (PARTITION BY productId)) " >> + "where m10>d7 " >> ); >> >> gives me after optimization the following plan, that doesn't have any >> window boundaries : >> >> LogicalFilter(condition=[>($1, $2)]) >> LogicalProject(productid=[$0], m10=[CAST(/($1, $2)):INTEGER NOT NULL], >> d7=[CAST(/($3, $4)):INTEGER NOT NULL]) >> LogicalProject(productid=[$0], $1=[$2], $2=[$3], $3=[$4], $4=[$5]) >> LogicalWindow(window#0=[window(partition {0} order by [] rows >> between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1), >> COUNT($1)])], window#1=[window(partition {0} order by [] range between >> UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1), COUNT($1)])]) >> LogicalProject(productid=[$1], units=[$2]) >> LogicalTableScan(table=[[s, orders]]) >> >> 2016-10-18 20:23 GMT+03:00 Γιώργος Θεοδωράκης <giwrgosrth...@gmail.com>: >> >>> Hi, >>> >>> I was wondering if there is any possible way to define windows with SQl >>> in Calcite for queries that don't have an aggregate function? For example, >>> I want to define the queries from Linear Road Benchmark of the STREAM >>> project (http://infolab.stanford.edu/stream/cql-benchmark.html): >>> >>> 1) >>> SELECT DISTINCT car_id >>> FROM CarSegStr [RANGE 30 SECONDS]; >>> >>> 2) >>> SELECT car_id, exp_way, dir, seg >>> FROM CarSegStr [PARTITION BY car_id ROWS 1], CurActiveCars >>> WHERE CarSegStr.car_id = CurActiveCars.car_id; >>> >>> Thank you in advance, >>> George. >>> >> >>