Look through wrapper paths when collecting enforced clause serials. get_param_path_clause_serials() handles join paths and Append paths explicitly, and for anything else returns the ppi_serials of the path's ParamPathInfo on the assumption that it is a baserel scan. But Material, Memoize, Projection, Sort, IncrementalSort, Unique, Agg and GroupingSets paths all inherit their subpath's ParamPathInfo, and that assumption fails when the subpath is a join or Append.
This could drop a join clause for good. If a LATERAL UNION ALL subquery references an outer relation in an expression that becomes an EquivalenceClass member, the members get no child version of it, so their parameterized scans cannot enforce the derived clause. The appendrel's ParamPathInfo claims it nonetheless, and once a Materialize sat on the Append, create_nestloop_path() dropped the clause from the join above as already enforced, returning rows that fail it. To fix, make get_param_path_clause_serials() recurse through such wrapper paths, since they enforce no clauses of their own. Also use it in get_memoize_path(), which unsafely read ppi_serials directly for its inner-unique check. To try to clarify what's going on here, introduce a new function get_wrapper_parampathinfo() that pathnode.c should use instead of directly assuming that copying a wrapper path's subpath's param_info is what to do. This is just cosmetic at present but might become less so in future. Back-patch to v16, where the serial-based detection of enforced clauses was introduced. Author: Richard Guo <[email protected]> Co-authored-by: Tom Lane <[email protected]> Reviewed-by: shihao zhong <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> Discussion: https://postgr.es/m/CAMbWs49jVFRZ7oOgMK9zYt7d=SLxpiz=dN=qmu5iv8ts+9t...@mail.gmail.com Backpatch-through: 16 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a8b9a7fe42a56fd29e29ea50d54841919e7caeb9 Modified Files -------------- src/backend/optimizer/path/joinpath.c | 2 +- src/backend/optimizer/util/pathnode.c | 12 +- src/backend/optimizer/util/relnode.c | 212 ++++++++++++++++++++++------------ src/include/nodes/pathnodes.h | 29 ++++- src/include/optimizer/pathnode.h | 1 + src/test/regress/expected/join.out | 37 ++++++ src/test/regress/sql/join.sql | 23 ++++ 7 files changed, 231 insertions(+), 85 deletions(-)
