Hi,
I tried to change the type of the field to TIMESTAMP as you suggested.
Nevertheless, I get a similar error. It seems that when validating the
expression ("INTERVAL '1' HOUR") this gets converted in the SqlWindow class in
the validateFrameBoundary() into a INTERVAL_DAY_TIME and it seems that this is
not supported. It is a bit surprising that it is not converted to TIMESTAMP. I
guess this is part of the error?
Any thoughts to work around?
Best regards,
-----Original Message-----
From: Julian Hyde [mailto:[email protected]]
Sent: Monday, October 24, 2016 5:55 PM
To: [email protected]
Subject: Re: window questions
Usually people use a column of type TIMESTAMP.
I guess TIME makes sense, if you know your stream will stop before midnight. If
you really want to use TIME, please log a JIRA case for it. But things will
work more smoothly if you convert your column to TIMESTAMP.
On Mon, Oct 24, 2016 at 7:16 AM, Radu Tudoran <[email protected]> wrote:
> Hi,
>
> I am trying to parse a simple query over a window. Basically the query
> follows the examples on the website of using the "OVER" clause. Nevertheless
> I have 2 questions about this:
>
>
> 1) if the "time_event" field over which I want to create the window is of
> type TIME, I get an error when parsing the query (see below) that the RANGE
> clause is not acceptable. How is this the case as most of the example on the
> website are like this ?
>
>
> The query example:
> SELECT STREAM user, SUM(value) OVER (ORDER BY time_event RANGE
> INTERVAL '1' HOUR PRECEDING) AS recent_sum FROM data
>
> Exception in thread "main" 16:01:38,146 ERROR
> org.apache.calcite.runtime.CalciteException -
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 85 to
> line 1, column 101: Data type of ORDER BY prohibits use of RANGE clause
> org.apache.calcite.tools.ValidationException:
> org.apache.calcite.runtime.CalciteContextException: From line 1,
> column 85 to line 1, column 101: Data type of ORDER BY prohibits use
> of RANGE clause
>
>
> 2) if I change the type of the field to do the ordering in type long and
> write the same query and replace " RANGE INTERVAL '1' HOUR PRECEDING " with
> "RANGE 3600 PRECEDING " - then I get the query parsed and the logical graph
> looks like the one below. What is surprising is that there is not
> LogicalWindow. I do not understand the logic of the parsing if the inner part
> of "OVER" is not converted to window...then how can it be possible to create
> a rule to map this over some window implementation?
>
> LogicalDelta
> LogicalProject(user=[$3], recent_sum=[CASE(>(COUNT($11) OVER (ORDER BY $10
> RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW), 0), CAST($SUM0($11) OVER
> (ORDER BY $10 RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW)):DOUBLE, null)])
> LogicalTableScan(table=[[data]])
