HI Richard > As mentioned in my initial email, the goal of this patch is not to > handle every possible case, but rather only to handle the basic form > where both sides of NOT IN are provably non-nullable. This keeps the > code complexity to a minimum, and I believe this would cover the most > common use cases in real world. Agree +1 ,The current path already covers common scenarios and is no less comprehensive than other databases.I'm already quite pleased that it can be merged. Having tested a certain widely used open-source database, I found it unable to process the following query: `SELECT * FROM join1 WHERE id NOT IN (SELECT id FROM join2 WHERE id IS NOT NULL);` Note that join2 allows null values for id.
Thanks On Thu, Feb 5, 2026 at 2:09 PM Richard Guo <[email protected]> wrote: > On Wed, Feb 4, 2026 at 11:59 PM David Geier <[email protected]> wrote: > > If the sub-select can yield NULLs, the rewrite can be fixed by adding an > > OR t2.c1 IS NULL clause, such as: > > > > SELECT t1.c1 FROM t1 WHERE > > NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c1 OR t2.c1 IS NULL) > > I'm not sure if this rewrite results in a better plan. The OR clause > would force a nested loop join, which could be much slower than a > hashed-subplan plan. > > > If the outer expression can yield NULLs, the rewrite can be fixed by > > adding a t1.c1 IS NOT NULL clause, such as: > > > > SELECT t1.c1 FROM T1 WHERE > > t1.c1 IS NOT NULL AND > > NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c1) > > This rewrite doesn't seem correct to me. If t2 is empty, you would > incorrectly lose the NULL rows from t1 in the final result. > > > What's our today's take on doing more involved transformations inside > > the planner to support such cases? It would greatly open up the scope of > > the optimization. > > As mentioned in my initial email, the goal of this patch is not to > handle every possible case, but rather only to handle the basic form > where both sides of NOT IN are provably non-nullable. This keeps the > code complexity to a minimum, and I believe this would cover the most > common use cases in real world. > > - Richard > > >
