postgres.git / summary / log / commit / refs
commit 2865d5952d6d89c1006701cb94bc40b146ead880
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Tue Jul 08 18:03:30 2014 +0000
First released in: 9.5.0, 8.4.22, 9.0.18, 9.1.14, 9.2.9, 9.3.5, 9.4.0
Don't assume a subquery's output is unique if there's a SRF in its tlist.
While the x output of "select x from t group by x" can be presumed unique,
this does not hold for "select x, generate_series(1,10) from t group by x",
because we may expand the set-returning function after the grouping step.
(Perhaps that should be re-thought; but considering all the other oddities
involved with SRFs in targetlists, it seems unlikely we'll change it.)
Put a check in query_is_distinct_for() so it's not fooled by such cases.
Back-patch to all supported branches.
David Rowley
src/backend/optimizer/util/pathnode.c | 12 ++++++++++++
src/test/regress/expected/subselect.out | 33 +++++++++++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 11 +++++++++++
3 files changed, 56 insertions(+)
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index e153eb1b64a..623474ddba8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -18,6 +18,7 @@
#include "catalog/pg_operator.h"
#include "miscadmin.h"
+#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/pathnode.h"
@@ -1093,6 +1094,17 @@ query_is_distinct_for(Query *query, List *colnos, List *opids)
Assert(list_length(colnos) == list_length(opids));
+ /*
+ * A set-returning function in the query's targetlist can result in
+ * returning duplicate rows, if the SRF is evaluated after the
+ * de-duplication step; so we play it safe and say "no" if there are any
+ * SRFs. (We could be certain that it's okay if SRFs appear only in the
+ * specified columns, since those must be evaluated before de-duplication;
+ * but it doesn't presently seem worth the complication to check that.)
+ */
+ if (expression_returns_set((Node *) query->targetList))
+ return false;
+
/*
* DISTINCT (including DISTINCT ON) guarantees uniqueness if all the
* columns in the DISTINCT clause appear in colnos and operator semantics
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 9e7816955fb..d31c8dc90a4 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -732,3 +732,36 @@ select * from int4_tbl where
0
(1 row)
+--
+-- Check for incorrect optimization when IN subquery contains a SRF
+--
+set enable_hashjoin to 0;
+explain (verbose, costs off)
+select * from int4_tbl o where (f1, f1) in
+ (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
+ QUERY PLAN
+----------------------------------------------------------------
+ Nested Loop Semi Join
+ Output: o.f1
+ Join Filter: (o.f1 = "ANY_subquery".f1)
+ -> Seq Scan on public.int4_tbl o
+ Output: o.f1
+ -> Materialize
+ Output: "ANY_subquery".f1, "ANY_subquery".g
+ -> Subquery Scan on "ANY_subquery"
+ Output: "ANY_subquery".f1, "ANY_subquery".g
+ Filter: ("ANY_subquery".f1 = "ANY_subquery".g)
+ -> HashAggregate
+ Output: i.f1, (generate_series(1, 2) / 10)
+ -> Seq Scan on public.int4_tbl i
+ Output: i.f1
+(14 rows)
+
+select * from int4_tbl o where (f1, f1) in
+ (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
+ f1
+----
+ 0
+(1 row)
+
+reset enable_hashjoin;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index e85db993d41..eb88f948be5 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -416,3 +416,14 @@ select * from int4_tbl where
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
+
+--
+-- Check for incorrect optimization when IN subquery contains a SRF
+--
+set enable_hashjoin to 0;
+explain (verbose, costs off)
+select * from int4_tbl o where (f1, f1) in
+ (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
+select * from int4_tbl o where (f1, f1) in
+ (select f1, generate_series(1,2) / 10 g from int4_tbl i group by f1);
+reset enable_hashjoin;
[parent: d0091e3c03eb]