While fixing another bug in var_is_nonnullable(), I noticed $subject. The NOTNULL_SOURCE_SYSCACHE code path (newly added for the NOT IN to anti-join transformation) checks pg_attribute.attnotnull, which can be true even for invalid (NOT VALID) NOT NULL constraints.
The consequence is that query_outputs_are_not_nullable() could wrongly conclude that a subquery's output is non-nullable, causing NOT IN to be incorrectly converted to an anti-join.
The attached fix checks the attnullability field in the relation's tuple descriptor instead, which correctly distinguishes valid from invalid constraints. This is also consistent with what we do in get_relation_notnullatts().
I tested this patch against the current HEAD (155c03ee) and it looks good.
Build & tests: Applies cleanly, compiles without warnings, all 247 regression tests
pass including the new subselect test case. Reproduced the bug before the patch
and verified it is fixed after the patch.
It could be argued that the added table_open/table_close call is a performance concern, but I don't think so:
1. The relation is already locked by the rewriter, so table_open(rte->relid, NoLock) is just a relcache lookup.
2. This code path is only reached when converting NOT IN to an anti-join, and only after the outer side of the test expression has already been proved non-nullable.
3. It is only called for relation RTEs in the subquery.
Thoughts?
Looks like it needs to perform table_open/table_close multiple times depending upon
the number of output columns? I don't see it as a major concern but let others comment.
