Simon Riggs <simon@2ndquadrant.com> writes:
> On Tue, 2008-02-26 at 10:34 -0500, Tom Lane wrote:
> and add COALESCE(id2,id1) to it as well I think.
>> No, because those two expressions are not equivalent. (Hmm ... squint
>> ... but full merge join is pretty much symmetric, so it's not clear
>> why it should matter which side is left or right. Maybe COALESCE
>> isn't
>> exactly the right concept with which to describe the merged variable?)
> It is, in this case only, since when id2 is not null id2 == id1.
> So in this case its OK to express a symmetric relationship as a left
> handed function.
Well, it gives the right answer, but it fails to capture the property
that the expression is really symmetric. Which is something we need
to capture here so that the planner doesn't think that x mergejoin y
and y mergejoin x produce different output orderings. I think I had
done the COALESCE hack to avoid putting very much effort into FULL
JOIN, but it might be time to put in some more work, if we really
care about improving this.
regards, tom lane