The WHERE clause of a query seems to be polluting subqueries. The WHERE of
the outer select is being pushed into the subquery (query optimizer?), but
because the subquery depend on all its selected rows rows for correct
@variable calculations, moving the outer WHERE inside changes the results.
Is this intended behavior, or a bug?
I'm using H2 1.3.164 (2012-02-03).
The intent is to calculate the time delta between consecutive events (0 and
1), which are interspersed within other events (e.g. 2).
The subquery works on it's own: delta==1 for all rows where var=1 except
the last (at time=12) where delta==2
The intent of the outer select is to select only rows where var=1 and sum
the delta column. The WHERE moving into the subquery causes it to only
select var=1 rows, changing the values of delta. "explain" shows the "event
= 1" being pushed into the subquery, corroborated by the results showing
@start being set to previous event=(1)
drop table if exists foo;
create temporary table foo (time int(10) not null auto_increment, event
int(10), primary key (time));
insert into foo (event) values
(0),(1),(0),(1),(2),(0),(1),(2),(2),(0),(2),(1);
set @start=null;
-- calculate deltas between successive event=(0) and event=(1) events
select time, event, @start as prev, time - @start as delta,
set(@start,time) as now
from foo where event = 0 or event = 1
order by time;
-- corrupts deltas: here they are calculated between successive
event=(1) events only
select * from (
select time, event, @start as prev, time - @start as delta,
set(@start,time) as now
from foo where event = 0 or event = 1
order by time
) x where event = 1;
drop table foo;
I can get the desired results in the above situation using CASE WHEN..., in
the subselect and omitting the outer WHERE, but I thought it worth asking
about the behavior.
Best,
...Chad
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
