Re: BUG #12760: Lateral files with more than 2 laterals

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Looks like an issue where GEQO and LATERAL don't get along. :/

On inspection, it seems like the error check that this is triggering is
just plain wrong.

What's happening is that the geqo pool initialization code
(random_init_pool) is proposing the join order A, C0, B0 (1, 3, 2);
which cannot work in this example because B0 (rel 2) has to be joined to A
before C0 is. Now merge_clump first tries to join A and C0 (1 and 3),
which is legal although it generates only a path parameterized by B0.
Then there is no way to join that to B0 (rel 2) since each side of the
join is wanting to be parameterized by the other.

You could argue that desirable_join should figure out that the 1+3 join
isn't so desirable; but that would still not prevent dead-end joins
like this from being made once gimme_tree starts setting the "force"
parameter. Or you could argue that gimme_tree+merge_clump should be
willing to back off and try another clumping once they realize that what
they're doing is a dead end. That seems pretty complicated.

On the whole, it seems like the best choice is to allow overall failure,
since we're certainly going to try other tours anyway. My recollection is
that this code used to have a failure case, which it handled by returning
DBL_MAX as the estimated cost of the tour. We got rid of that at some
point, essentially assuming that this logic could always succeed at
finding a legal join order --- but considering that it's fundamentally a
heuristic, it doesn't seem too bright to assume that it will *always* find
one.

regards, tom lane