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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
