Fix nullability check for a sub-select's upper-level Vars When checking whether a sub-select's output columns can produce NULL, so as to decide whether a NOT IN can be converted to an anti-join, query_outputs_are_not_nullable() falls back on find_nonnullable_vars() for targetlist entries that are plain Vars: if the sub-select's own quals prove the Var non-null, the output is non-nullable. But that test compared only varno and varattno, without checking varlevelsup. An outer reference in the targetlist could thus be matched against a Var of the sub-select's own range table that happens to share the same varno and varattno, wrongly proving the output non-nullable and allowing an invalid conversion to an anti-join, which yields wrong answers when the outer reference is NULL.
To fix, restrict the fallback to Vars of the current query level. Author: Rui Zhao <[email protected]> Reviewed-by: Tender Wang <[email protected]> Reviewed-by: Richard Guo <[email protected]> Discussion: https://postgr.es/m/cahwvjhguaffrpmq4j+mcmcm_hc5qot7lzsc9bf9b7bcbmvb...@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0d99579320d075eba5733517463d81a2231e373b Modified Files -------------- src/backend/optimizer/util/clauses.c | 3 ++- src/test/regress/expected/subselect.out | 24 ++++++++++++++++++++++++ src/test/regress/sql/subselect.sql | 15 +++++++++++++++ 3 files changed, 41 insertions(+), 1 deletion(-)
