diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/optimizer/util/pathnode.c | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/subselect.out | 35 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 9 |
3 files changed, 55 insertions, 0 deletions
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 4e05dcd2463..d129f8d65ea 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1474,6 +1474,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 * match. diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 0f070ef93cd..d85a7170f30 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -740,6 +740,41 @@ select * from int4_tbl where (1 row) -- +-- Check for incorrect optimization when IN subquery contains a SRF +-- +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 +---------------------------------------------------------------------- + Hash Join + Output: o.f1 + Hash Cond: (o.f1 = "ANY_subquery".f1) + -> Seq Scan on public.int4_tbl o + Output: o.f1 + -> Hash + Output: "ANY_subquery".f1, "ANY_subquery".g + -> HashAggregate + Output: "ANY_subquery".f1, "ANY_subquery".g + Group Key: "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) + Group Key: i.f1 + -> Seq Scan on public.int4_tbl i + Output: i.f1 +(18 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) + +-- -- Check that volatile quals aren't pushed down past a DISTINCT: -- nextval() should not be called more than the nominal number of times -- diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index b3fb03c97fb..c3b47734887 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -424,6 +424,15 @@ select * from int4_tbl where (select ten from tenk1 b); -- +-- Check for incorrect optimization when IN subquery contains a SRF +-- +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); + +-- -- Check that volatile quals aren't pushed down past a DISTINCT: -- nextval() should not be called more than the nominal number of times -- |