On Sat, 2 Mar 2019 at 05:44, Tom Lane <[email protected]> wrote:
>
> Andres Freund <[email protected]> writes:
> > I've not checked, but could we please make sure these cases are covered
> > in the regression tests today with a single liner?
>
> I'm not sure if the second one is actually a semantics bug or just a
> misoptimization? But yeah, +1 for putting in some simple tests for
> corner cases right now. Anyone want to propose a specific patch?
The second is just reducing the planner's flexibility to produce a
good plan. The first is a bug. Proposed regression test attached.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/src/test/regress/expected/subselect.out
b/src/test/regress/expected/subselect.out
index cc3f5f3737..1b09b3e3fd 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -830,6 +830,20 @@ explain (verbose, costs off)
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
(8 rows)
+--
+-- Check we don't filter NULL outer rows in a NOT IN where the subquery
+-- returns no rows.
+--
+create temp table notinouter (a int);
+create temp table notininner (a int not null);
+insert into notinouter values(null),(1);
+select * from notinouter where a not in(select a 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..48230d4671 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -466,6 +466,16 @@ 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 filter NULL outer rows in a NOT IN where the subquery
+-- returns no rows.
+--
+create temp table notinouter (a int);
+create temp table notininner (a int not null);
+insert into notinouter values(null),(1);
+
+select * from notinouter where a not in(select a from notininner);
+
--
-- Check we behave sanely in corner case of empty SELECT list (bug #8648)
--
