From 3396138a6de329fa7b5e0dda79219b4ae82622dc Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 1 Mar 2019 17:57:20 -0500 Subject: [PATCH] Check we don't misoptimize a NOT IN where the subquery returns no rows. Future-proofing against a common mistake in attempts to optimize NOT IN. We don't have such an optimization right now, but attempts to do so are in the works, and some of 'em are buggy. Add a regression test case covering the point. David Rowley Discussion: https://postgr.es/m/CAKJS1f90E9agVZryVyUpbHQbjTt5ExqS2Fsodmt5_A7E_cEyVA@mail.gmail.com --- src/test/regress/expected/subselect.out | 13 +++++++++++++ src/test/regress/sql/subselect.sql | 9 +++++++++ 2 files changed, 22 insertions(+) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index cc3f5f3737..fe5fc64480 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -830,6 +830,19 @@ explain (verbose, costs off) One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) (8 rows) +-- +-- Check we don't misoptimize a NOT IN where the subquery returns no rows. +-- +create temp table notinouter (a int); +create temp table notininner (b int not null); +insert into notinouter values (null), (1); +select * from notinouter where a not in (select b from notininner); + a +--- + + 1 +(2 rows) + -- -- Check we behave sanely in corner case of empty SELECT list (bug #8648) -- diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 8bca1f5d55..b5931ee700 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -466,6 +466,15 @@ explain (verbose, costs off) select x, x from (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; +-- +-- Check we don't misoptimize a NOT IN where the subquery returns no rows. +-- +create temp table notinouter (a int); +create temp table notininner (b int not null); +insert into notinouter values (null), (1); + +select * from notinouter where a not in (select b from notininner); + -- -- Check we behave sanely in corner case of empty SELECT list (bug #8648) -- -- 2.40.0