I don't have experience with H2's ROWNUM() or with H2 query optimization, but the DBMS we use at work optimizes queries by "flattening" complex subquery constructions. This means the outer condition is applied to the inner query directly if possible instead of building a huge transient table just to throw away most rows again, which can give an enormous performance boost, of course.
If H2 does this, too, this would explain the behaviour: The sub query would only create one row in the first place. Of course in situations like this with a ROWNUM(), such an optimization would alter (=break) the semantics of the query. The strategy of the DBMS we work with to avoid this is to not offer a ROWNUM() functionality at all, which of course is a ridiculous solution. The proper solution would be to make optimization "smart" enough to recognize if semantics would be changed, which I could imagine might be incredibly hard to accomplish. Or even smarter to extrapolate the correct ROWNUM() value while still optimizing, which might be impossible, though... Again, no idea if this is really the case here with H2. It's just a possible explanation. Interesting issue. I'm curious what the answer will be. On Dec 30, 10:28 pm, Yves <[email protected]> wrote: > Hi, > > I'm working on an editor that uses H2 as its backend. > The data are accessed by page. > > I'm trying to get the page to which an entry with a given ID belongs. > I can calculate this if I know the row number of the entry. > > But it seems ROWNUM() is behaving strangely. > > To test it, I have a table with a unique entry that has a SEGKEY value > of 55 in a small table. > > When I do: > > SELECT SEGKEY, R FROM (SELECT SEGKEY, ROWNUM() AS R FROM mytable) > WHERE SEGKEY=55 > > I get one record back where the resulting R is 1 > > when I do: > > SELECT SEGKEY, R FROM (SELECT SEGKEY, ROWNUM() AS R FROM mytable) > WHERE MOD(SEGKEY, 55)=0 > > I get one record back where the resulting R is 42 (the correct row > number of the entry with SEGKEY=55 > > I've seen the documentation that states: "...The first row has the row > number 1, and is calculated before ordering and grouping the result > set, but after evaluating index conditions (even when the index > conditions are specified in an outer query)". > > What does it mean exactly? How can i make the first query return the > expected value for R. > To me it seems very illogical that ROWNUM() behaves so differently > depending on what the WHERE clause is. I would expect the inner query > to give back a set of values that does not change when copied into the > main result. > > Any hint would be appreciated. > Thanks, > -yves -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
