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

I reposted this report a few times with additonal information.

"On the whole, it seems like the best choice is to allow overall failure, since
we're certainly going to try other tours anyway. "

Note that increasing GEQO resolved the issue, so I am not sure what you
mean by letting it fail, it seems like it can be made to work. But maybe I
am misinterpreting your intention.

On Wed, Feb 11, 2015 at 1:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>