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
------
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(-)

FAQ