> > > It is intended behavior. Depending on the optimization, not all rows are > processed. > > Maybe it is easier to create a Java function that returns a result set, > and use it like a table? > > Shouldn't sub-query results be independent of outer-queries? I understand the optimization and why it is useful - in some cases. However, when SET(@variable, value) is in the selectExpression, the order and number of rows SET(...) is applied to, are critical for correct results. An optimization which filters those sub-queries before their calculations are made, all but guarantees unintended results.
If the sub-query did not use @variable calculations, the optimization would be innocuous, but with the calculations, it changes the results unexpectedly. As comparison, MySQL returns expected results, calculating values on all rows selected within the subquery, then filtering those with the outer query. What if the planner looked for the use of SET(...) in sub-queries before moving a WHERE clause down, and skip the optimization if a SET is used? 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.
