Nested Window Queries

[John Omernik <jo...@omernik.com>]

Is there a limitation on nesting of of Window Queries?  I have a query
where I am using an event stream, and the changing of a value to indicate
an event. (The state goes from disconnected, to charging, to complete, it
reports many times in each of those states, but I am using lag(state, 1)
over (order by event_ts) to find those records where current record is say
complete and previous record was charging to indicate that the state has
changed.

This works well.

Now I am trying to take that result set, and do another window to find the
delta time between when the charging started and when the charging
completed, you would think that a nested query showing the state change
times, and then a select lag(event_ts, 1) over (order by event_ts) would be
able to get me both the current time (when the charging was complete) and
the previous record event_ts (when the charging started as outputted by the
subquery). However, I am getting a verbose confusing error that perhaps we
can help figure out...

The first part is:

SYSTEM ERROR: CannotPlanException: Node
[rel#92358:Subset#11.LOGICAL.ANY([]).[3]] could not be implemented;
planner state:

Root: rel#92358:Subset#11.LOGICAL.ANY([]).[3]
Original rel:


The rest is pages of information related to the query, the data etc. I
can send that separately if you are interested.


I guess, before sending all of that, are nested window queries just
not alloweable?


If that is the case, what are some alternative approaches?


Thanks,

John

Re: Nested Window Queries

[Aman Sinha <am...@gmail.com>]

John,   what's the full SQL query that you submitted ?

On Thu, Jan 3, 2019 at 6:45 AM John Omernik <jo...@omernik.com> wrote:

> Is there a limitation on nesting of of Window Queries?  I have a query
> where I am using an event stream, and the changing of a value to indicate
> an event. (The state goes from disconnected, to charging, to complete, it
> reports many times in each of those states, but I am using lag(state, 1)
> over (order by event_ts) to find those records where current record is say
> complete and previous record was charging to indicate that the state has
> changed.
>
> This works well.
>
> Now I am trying to take that result set, and do another window to find the
> delta time between when the charging started and when the charging
> completed, you would think that a nested query showing the state change
> times, and then a select lag(event_ts, 1) over (order by event_ts) would be
> able to get me both the current time (when the charging was complete) and
> the previous record event_ts (when the charging started as outputted by the
> subquery). However, I am getting a verbose confusing error that perhaps we
> can help figure out...
>
> The first part is:
>
> SYSTEM ERROR: CannotPlanException: Node
> [rel#92358:Subset#11.LOGICAL.ANY([]).[3]] could not be implemented;
> planner state:
>
> Root: rel#92358:Subset#11.LOGICAL.ANY([]).[3]
> Original rel:
>
>
> The rest is pages of information related to the query, the data etc. I
> can send that separately if you are interested.
>
>
> I guess, before sending all of that, are nested window queries just
> not alloweable?
>
>
> If that is the case, what are some alternative approaches?
>
>
> Thanks,
>
> John
>