db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@gmail.com>
Subject Re: HashJoinStrategy.feasible
Date Thu, 19 Oct 2006 21:30:08 GMT
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...


View raw message