Tom Lane wrote:
Rich Doughty <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
The reason these are different is that the second case constrains only
the last-to-be-joined table, so the full cartesian product of t and h1
has to be formed. If this wasn't what you had in mind, you might be
able to rearrange the order of the LEFT JOINs, but bear in mind that
in general, changing outer-join ordering changes the results. (This
is why the planner won't fix it for you.)
FWIW mysql 4.1 (and i'm no fan at all of mysql) completes both these queries
in approximately 3 seconds.
Does mysql get the correct answer, though? It's hard to see how they do
this fast unless they (a) are playing fast and loose with the semantics,
or (b) have very substantially more analysis logic for OUTER JOIN semantics
than we do. Perhaps mysql 5.x is better about this sort of thing, but
for 4.x I'd definitely find theory (a) more plausible than (b).
i would assume so. i'll re-run my testcase later and verify the results of the
two side-by-side.
The cases that would be interesting are those where rearranging the
outer join order actually does change the correct answer --- it may not
in this particular case, I haven't thought hard about it. It seems
fairly likely to me that they are rearranging the join order here, and
I'm just wondering whether they have the logic needed to verify that
such a transformation is correct.
regards, tom lane
--
- Rich Doughty
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
