John, what's the full SQL query that you submitted ? On Thu, Jan 3, 2019 at 6:45 AM John Omernik <j...@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 >
