Re: convert EXSITS to inner join gotcha and bug

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> (On 29 April 2017 at 02:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It looks like in the case that's giving wrong answers, the mergejoin
>> is wrongly getting marked as "Inner Unique". Something's a bit too
>> cheesy about that planner logic --- not sure what, yet.

> Seems related to the unconditional setting of extra.inner_unique to
> true for JOIN_UNIQUE_INNER jointypes in add_paths_to_joinrel()
> Setting this based on the return value of innerrel_is_unique() as done
> with the other join types seems to fix the issue.
> I don't know yet if that's the correct fix. It's pretty late 'round
> this side to be thinking too hard about it.

Yes, I think that's correct. I'd jumped to the conclusion that we could
skip making the test in this case, but this example shows that that's
wrong. The problem is that, in an example like this, create_unique_path
will create a path that's unique-ified for all the join keys of the
semijoin --- but we're considering joining against just a subset of the
semijoin's outer rels, so the inner path is NOT unique for that subset.

We could possibly skip making the test if the outerrel contains
sjinfo->min_lefthand, but I'm not sufficiently excited about shaving
cycles here to take any new risks. Let's just call innerrel_is_unique()
and be done.

Will fix in a bit, once I've managed to create a smaller test case for
the regression tests.

regards, tom lane