Hi
I have a problem understanding the code to make certain in join are
performed properly. Specifically I have problems understading when
IN_UNIQUE_{INNER,OUTER} is a valid jointype.
Its in joinrels.c:make_join_rel.
Consider this example:
SELECT * FROM a,b WHERE a.id = b.id AND (a.id) IN (SELECT c.id FROM c)
the possible execution trees are {{a,b}, {c}}, {{a,c},{b}} and the code
seems to also permit {{b,c},{a}}. It is the latter I'm having problems with.
When joining {b} and {c} it will fall through and suggest a
IN_UNIQUE_{INNER,OUTER} jointype.
My logic is this: {c} \in {c,b} so it is a valid plan according to the
first check.
We have an issue according to the second check and we haven't done the
work before according to the 3rd and 4th checks.
Since the lefthand of the IN {a} is not in either {b} or {c} we skip the
IN_JOIN{_REVERSE}.
But since one of the relations is equal to the right side {c} of the IN
we determine that IN_UNIQUE_{INNER,OUTER} is a valid jointype.
Now, the next join between {a} and {b,c} is the one I fail to understand
when it can ever happen...
{c} \in {a,b,c} so it is a valid plan according to the first check.
We have an issue according to the second check.
Since we have no trace of the IN's left hand {a} in {b,c} 3rd and 4th
check says we have not done the work?!?
The final checks fail because {c} != {b,c}, thus we determine it is an
invalid plan.
My question is: When is it ever a valid jointype to use
IN_UNIQUE_{INNER,OUTER}? Or am I missing something?
--
Dennis