Don't absorb child pathkeys into dummy AppendPaths for set-ops. create_append_path() has a rather questionable habit of overriding the caller-supplied pathkeys when it sees that there is a single child path, and applying the child's pathkeys instead. In most cases we can get away with that, but it does not work for set-operation AppendPaths. In set-operation nests, the append's result tlist will contain "varno 0" Vars, which won't match the child's pathkeys, leading to failure in create_plan().
We didn't have this problem before 03d40e4b5 allowed eliding provably-empty child nodes of set-ops; there would never have been a case with only one surviving child node, so create_append_path() would always have accepted the specified NIL pathkeys. As a band-aid fix, force the generated AppendPath's pathkeys to NIL even if create_append_path() did something else, thus restoring the status quo ante. This is demonstrably necessary for two of prepunion.c's three calls; I did it at the third too, although probably the pathkeys would already be NIL there. There have been reasons to want to get rid of the "varno 0" hack for a long time, and this is another one. But that will require significant surgery in prepunion.c, and likely some changes in parsetree representation, so we can't tackle it for v19. Hence, we need a band-aid. Bug: #19742 Reported-by: Junwen AN <[email protected]> Author: Tom Lane <[email protected]> Co-authored-by: shihao zhong <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2509f3c6b529a36ae7f2a53967af296afdc19f78 Modified Files -------------- src/backend/optimizer/prep/prepunion.c | 16 +++++++ src/test/regress/expected/union.out | 80 +++++++++++++++++++++++++++++++++- src/test/regress/sql/union.sql | 29 +++++++++++- 3 files changed, 123 insertions(+), 2 deletions(-)
