Fix NOT IN to anti-join conversion with coerced sub-select outputs Commit 383eb21eb allowed converting "x NOT IN (SELECT y ...)" to an anti-join when neither the outer expressions nor the sub-select's output columns can be NULL, and the operator cannot return NULL for non-null inputs. However, the operator is not necessarily applied to the sub-select's output itself: the parser may wrap the output's Param in a coercion to the operator's input type. If that coercion is a cast function that returns NULL for a non-null input, the comparison yields NULL, so NOT IN must not return true, yet the anti-join keeps the outer row, producing wrong results.
To fix, require each inner operand of the comparison to be the sub-select's output Param, possibly under a RelabelType, which cannot introduce NULLs. This gives up the conversion for some coercions that cannot actually return NULL, such as the built-in cast from integer to numeric, but proving that for a cast function is not straightforward. Back-patch to v19, where the conversion was introduced. Author: BHUVAN CHILUKUTI <[email protected]> Reviewed-by: Richard Guo <[email protected]> Discussion: https://postgr.es/m/pn0p287mb090375874c6d5433282ec9dfa7...@pn0p287mb0903.indp287.prod.outlook.com Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a7d10f6ad9d45ac850878369bfcb90e2eeda4274 Modified Files -------------- src/backend/optimizer/plan/subselect.c | 21 ++++++++++++++++++++- src/test/regress/expected/subselect.out | 18 ++++++++++++++++++ src/test/regress/sql/subselect.sql | 12 ++++++++++++ 3 files changed, 50 insertions(+), 1 deletion(-)
