aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2019-03-01 17:57:20 -0500
committerTom Lane <tgl@sss.pgh.pa.us>2019-03-01 17:57:20 -0500
commit3396138a6de329fa7b5e0dda79219b4ae82622dc (patch)
tree8a8a18a2762cc1c3c6634f3318d3af39bc772f3d
parent65ce07e0202f2ef0953be9d085d3e5df7ad353a4 (diff)
downloadpostgresql-3396138a6de329fa7b5e0dda79219b4ae82622dc.tar.gz
postgresql-3396138a6de329fa7b5e0dda79219b4ae82622dc.zip
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
-rw-r--r--src/test/regress/expected/subselect.out13
-rw-r--r--src/test/regress/sql/subselect.sql9
2 files changed, 22 insertions, 0 deletions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index cc3f5f3737d..fe5fc644803 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -831,6 +831,19 @@ explain (verbose, costs off)
(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)
--
create temp table nocolumns();
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 8bca1f5d55e..b5931ee700e 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -467,6 +467,15 @@ explain (verbose, costs off)
(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)
--
create temp table nocolumns();