Hi Tom,
> I suspect that that commit just allowed reaching some pre-existing
> mistake, but I've not dug into it.
Yes, I think the mistake is older.
The "WHERE false" path is removed, so the UNION ALL has one child
left, the INTERSECT. For an Append with one child,
create_append_path() copies the child's pathkeys. Later
create_append_plan() looks for those sort columns in the Append's
own targetlist. It can't find them, and we get the error.
It can't find them because the SetOp's pathkeys are wrong. A sorted
SetOp reuses the pathkeys of its left input. Here "a = 1" makes the
subquery skip its own sort , so we add a Sort on top of the subquery.
That Sort's pathkeys are built from the subquery's columns, not from
the SetOp's output columns.
There is a second way to get wrong pathkeys, with no SetOp at all.
When the column types differ, recurse_set_operations() adds a
projection but keeps the old pathkeys
(select a from d union select a from d)
union all select a::numeric from d where false;
So I did not fix the SetOp. 0001 makes these one-child Appends drop
the child's pathkeys, which covers both cases. The planner adds a
Sort above if it needs the order. 0002 adds tests for the three
queries.
This is the small fix, meant for 19 and master.
My first try, v1, only fixed the SetOp's pathkeys. It fixed the
reported query but changed many plans, so I think it is too much for
19.
I think a better fix would make the pathkeys right in both places,
so the Append can keep them. I can work on that for master if you
like.
Thanks,
