A B (JIRA) wrote:
I think the condition may need to be narrowed down even further: EXISTS
subqueries _are_ allowed to be flattened from a WHERE clause _if_ that
subquery's WHERE clause does not itself contain another subquery.
Correct - the updated patch I'm working on attempts to skip flattening
only in this scenario as per the javadoc in derby-3301-1.diff, and still
flatten EXISTS subqueries without a subquery in the where clause. I
didn't check patch available due to this :)
I think that has the potential to cause a performance regression for users whose
queries benefit from the EXISTS join optimization today.
Also correct. With derby-3301-1.diff applied, the query takes noticeably
more time to execute. But that is reasonable because it disables
flattening of the nested exist where clause all together.
The patch as posted does not apply to the current trunk,
My apologies. I'll reattach it later tonight. Probably due to Katheys
changes for DERBY-3257 that wasn't in my sandbox.
If at all possible, I think it'd be better to fix the flattening condition for
the specific
situation of nested subqueries than to completely disable WHERE clause
flattening
for EXISTS subqueries.
That is the intent of the upcoming patch.
On a slightly different note, have you had a chance to run the regression suites
with this change? I'm curious to know if any of the existing tests actually
test
for EXISTS join flattening--and if so, do those tests still pass with the
proposed
change?
I shared your fear, but suites.All actually ran cleanly with the patch
applied.
