Hi, On Tue, 28 Jul 2026 at 12:57, Ayush Tiwari <[email protected]> wrote:
> Hi, > > On Fri, 19 Jun 2026 at 06:43, Richard Guo <[email protected]> wrote: > >> 383eb21eb teaches the planner to convert "x NOT IN (SELECT ...)" to an >> anti-join when both sides of the comparison are known to be non-null. >> On the outer side we currently get that only from NOT NULL constraints >> and the outer-join-aware-Var infrastructure. >> >> That misses a case: the left-hand column has no NOT NULL constraint, >> but a qual forces it non-null anyway, as in "x IS NOT NULL AND x NOT >> IN (...)" or "x > 0 AND x NOT IN (...)". We leave those as SubPlan >> filters today, even though x clearly can't be NULL where the NOT IN is >> evaluated. >> >> The attached patch proves the left-hand Var non-null from such a qual. >> pull_up_sublinks_jointree_recurse collects the quals at or below the >> NOT IN's jointree node (only those on rels not below the nullable side >> of an outer join, so they really do filter the rows) and hands them to >> convert_ANY_sublink_to_join, which checks them with >> find_nonnullable_vars. >> >> Quals above the NOT IN's node could help in some cases too, but that's >> a separate extension and I've left it as a follow-up. See the details >> in the draft commit message. >> > > Thanks for the patch! > > I did see one issue in this here's a statement for repro: > > CREATE TEMP TABLE outer_t (x int); > CREATE TEMP TABLE inner_t (y int NOT NULL); > > INSERT INTO outer_t VALUES (NULL), (1); > INSERT INTO inner_t VALUES (2); > > SELECT x > FROM outer_t > WHERE NOT (x = ANY('{}'::int[])) > AND x NOT IN (SELECT y FROM inner_t); > > Apart from the above, your patch looked good to me. > I tried the patch on current master after Tom's SAOP fix. I may be missing something, but I think there could be an issue when a NOT IN left-hand expression contains an upper-level Var. For example: CREATE TEMP TABLE outer_t (a int); CREATE TEMP TABLE local_t (a int); CREATE TEMP TABLE inner_t (x int NOT NULL, y int NOT NULL); INSERT INTO outer_t VALUES (NULL), (10); INSERT INTO local_t VALUES (1); INSERT INTO inner_t VALUES (1, 999); SELECT o.a, ARRAY(SELECT l.a FROM local_t l WHERE l.a IS NOT NULL AND (l.a, o.a) NOT IN (SELECT i.x, i.y FROM inner_t i)) AS vals FROM outer_t o ORDER BY o.a NULLS FIRST; With the patch I get {1} for both outer rows, while without the anti-join conversion the NULL row produces {}. It looks like l.a and the upper-level o.a can have the same varno/varattno, despite having different varlevelsup values. Could the mbms_is_member() fallback therefore mistake the proof from l.a IS NOT NULL as applying to o.a? Would it make sense to restrict that fallback to level-zero Vars, for example: if (nonnullable_quals != NIL && IsA(expr, Var) && ((Var *) expr)->varlevelsup == 0) With that additional check, I get the expected result. Regards, Ayush
