pgsql: Fix NOT IN to anti-join conversion with coerced sub-select outpu
pgsql: Fix NOT IN to anti-join conversion with coerced sub-select outpu
От:
Richard Guo <rguo@postgresql.org>
Дата:
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 Reviewed-by: Richard Guo Discussion: https://postgr.es/m/PN0P287MB090375874C6D5433282EC9DFA7642@PN0P287MB0903.INDP287.PROD.OUTLOOK.COM Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/b7eee09eab738e085d6b62d35788f542dd233877 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(-)