Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> I think the problem results from using non-standard constructs such as
> order by expression, and indeed ordering by columns that don't appear in
> the select list.
No, that's not the problem. I looked into this last night, but hadn't
got round to reporting my findings to the mail list. Richards has
indeed tripped over a plain garden-variety bug, I think. ORDER BY
clauses are set up to represent their targets by hard-coded pointers
to Resdom nodes in the target list, which is an incredibly fragile
representation, and I suspect it is failing to survive the slicing
dicing and mangling of the parsetree that happens during UNION
processing. (The final output plan has Resdoms that are copies of the
originals, and the ORDER BY nodes are not pointing at the same copies as
are in the tlist, so any change to the tlist Resdoms blows the sort...)
We need to change this to be more like GROUP BY is currently done,
with an ID number that's actually stored in the Resdoms so that the
tlist can be copied without breaking it. I'm afraid that means no
fix for 6.5.*, but I will try to fix it soon for 6.6.
> [ example of not using hidden target list entries snipped ]
> What is the difference? The difference is that now GROUP BY (which also
> does internal sorting) knows about that expression and considers it.
Richards' example would still fail if he did that. He might have better
luck using UNION ALL instead of UNION --- I think that the specific
problem is that the DISTINCT implied by UNION requires a sort, which is
not getting merged with the user-given toplevel ORDER BY correctly as a
result of Resdom breakage.
We have had problems in the past with various bits of code failing to
ignore the hidden tlist entries ("resjunk" targets) that are added
to support ORDER BY or GROUP BY values that are not in the given tlist.
I suppose Herouth is suspicious of the feature because he's been burnt
by some of those bugs. They are getting flushed out, though,
gradually...
regards, tom lane
