Don't trust NOT NULL on virtual generated columns of partitioned tables Commit 383eb21eb taught the planner to convert a NOT IN sublink to an anti-join when the sub-select's outputs are provably non-nullable. Since the sub-select has not been through any planner processing at that point, a reference to a virtual generated column is still a plain Var, and query_outputs_are_not_nullable() relied on the column's NOT NULL constraint to prove it non-nullable.
That is not safe for a partitioned table. A partition can have a generation expression different from its parent's, and it enforces the NOT NULL constraint against its own expression, whereas a query on the parent computes the column using the parent's expression. So the column can be NULL when read through the parent despite the constraint, and the anti-join would then produce wrong results, as it returns rows that NOT IN should have rejected. To fix, make var_is_nonnullable() ignore the NOT NULL constraint on a virtual generated column of a partitioned table when consulting the catalogs. The other sources of NOT NULL information are only used after virtual generated columns have been expanded, so they are not affected. Author: Shihao Zhong <[email protected]> Reviewed-by: Richard Guo <[email protected]> Discussion: https://postgr.es/m/CAGRkXqR7VXacmRP41UBaM5u4aseeCH5ECVmQgn_YS=L7=xd...@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d8444163d22e576c9f284a55eab7999ba67c4ceb Modified Files -------------- src/backend/optimizer/util/clauses.c | 12 ++++++++++++ src/test/regress/expected/generated_virtual.out | 21 +++++++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 10 ++++++++++ 3 files changed, 43 insertions(+)
