James Synge wrote:
I should earlier have argued here that the use of the terms subquery and correlation in the analysis seems inappropriate.
Yes, I agree. That thought occurred to me while I was writing my last email, but I didn't stop to clarify.
I'm perhaps being nitpicky about terminology, but it is important (to me, at least :-)) to distinguish between standard SQL semantics and implementation limitations/bugs.
Yes, correct terminology is good. Thanks for taking the time to make this clear.
In this case, it doesn't appear that there are any sub-queries, but instead one cross join, two outer joins and a restriction (where clause).
I think the term "subquery" came into the picture because that's what the rest of the DERBY-781 document talks about--*real* subqueries, not just cross joins. But then the example I gave is, as you said, just a cross join--I thought that would make for an easier example. But somewhere in there I started misusing the term "subquery"--sorry. Thanks for keeping me honest here.
Repeating for easy reference your DDL:
<snip DDL, query, and what looks to me like very good and correct query analysis>
Again, I believe that this is not introducing a correlation between tt and the other table express as the results of these two table expressions don't change based on the addition of the where clause.
Yes, I agree. I think you have a correct understanding of how this is all working. My apologies for causing potential confusion with the sloppy terminology.
One interesting thing to note here is that since T0.j=T3.g is not true unless T3.g is NOT NULL, this clause amounts to saying that we don't actually want any of the extra rows that the two outer joins produce (as in those rows T3.g is always NULL). I don't know if Derby includes any such analysis/re-writing, nor do I know how valuable it might be, but it is an interesting thought.
Yes, it is--sounds like the kind of thing that could be useful to code up. As it is, I'm not sure if Derby takes advantage of this type of reasoning or not; I tend to doubt it, but couldn't say with any certainty.
I'm left with the conclusion that Army comes to:
That at least is comforting; it's nice to hear a second opinion on these optimizer issues. Thanks a ton for looking at the code, reading the documents, and sharing your reactions/knowledge...
It does indeed sound as if the predicates/qualifiers are being misapplied. My focus for now is on learning enough to address Derby-47, so I'm not able to dive in to this.
It's great that you've taken the time to understand the pieces that you have. Keep the questions and suggestions (and hopefully contributions!) coming. I for one am grateful for the discussion...
Army
