Prohibit pushing subqueries containing window function calculation to

workers.

Allowing window function calculation in workers leads to inconsistent
results because if the input row ordering is not fully deterministic, the
output of window functions might vary across workers.  The fix is to treat
them as parallel-restricted.

In the passing, improve the coding pattern in max_parallel_hazard_walker
so that it has a chain of mutually-exclusive if ... else if ... else if
... else if ... IsA tests.

Reported-by: Marko Tiikkaja
Bug: 15324
Author: Amit Kapila
Reviewed-by: Tom Lane
Backpatch-through: 9.6
Discussion: https://postgr.es/m/CAL9smLAnfPJCDUUG4ckX2iznj53V7VSMsYefzZieN93YxTNOcw@mail.gmail.com

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index fa53b7a8c5050c579fa27d79340a6d9374a8e1f5..82b56902a6c7fe1cc1c86ee9956fc71a6bb3e757 100644 (file)
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1168,12 +1168,26 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
            return true;
    }
 
-   if (IsA(node, NextValueExpr))
+   else if (IsA(node, NextValueExpr))
    {
        if (max_parallel_hazard_test(PROPARALLEL_UNSAFE, context))
            return true;
    }
 
+   /*
+    * Treat window functions as parallel-restricted because we aren't sure
+    * whether the input row ordering is fully deterministic, and the output
+    * of window functions might vary across workers if not.  (In some cases,
+    * like where the window frame orders by a primary key, we could relax
+    * this restriction.  But it doesn't currently seem worth expending extra
+    * effort to do so.)
+    */
+   else if (IsA(node, WindowFunc))
+   {
+       if (max_parallel_hazard_test(PROPARALLEL_RESTRICTED, context))
+           return true;
+   }
+
    /*
     * As a notational convenience for callers, look through RestrictInfo.
     */

diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 562eb2c53801dc693811fa1b2e46fd0b256dab35..9fd86256d2f0da61bb46aabbcc44b2a8b2431c93 100644 (file)
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -573,6 +573,32 @@ explain (costs off)
          Index Cond: (unique1 = 1)
 (5 rows)
 
+-- Window function calculation can't be pushed to workers.
+explain (costs off, verbose)
+  select count(*) from tenk1 a where (unique1, two) in
+    (select unique1, row_number() over() from tenk1 b);
+                                          QUERY PLAN                                          
+----------------------------------------------------------------------------------------------
+ Aggregate
+   Output: count(*)
+   ->  Hash Semi Join
+         Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?))))
+         ->  Gather
+               Output: a.unique1, a.two
+               Workers Planned: 4
+               ->  Parallel Seq Scan on public.tenk1 a
+                     Output: a.unique1, a.two
+         ->  Hash
+               Output: b.unique1, (row_number() OVER (?))
+               ->  WindowAgg
+                     Output: b.unique1, row_number() OVER (?)
+                     ->  Gather
+                           Output: b.unique1
+                           Workers Planned: 4
+                           ->  Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b
+                                 Output: b.unique1
+(18 rows)
+
 -- to increase the parallel query test coverage
 EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
                          QUERY PLAN                          

diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 1d29e9660764088a969fa4d6bfe882afb72106ba..dd9365d7c6e19f989e9d8fa99003a9ebe2ce24f7 100644 (file)
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -204,6 +204,12 @@ set force_parallel_mode=1;
 explain (costs off)
   select stringu1::int2 from tenk1 where unique1 = 1;
 
+-- Window function calculation can't be pushed to workers.
+explain (costs off, verbose)
+  select count(*) from tenk1 a where (unique1, two) in
+    (select unique1, row_number() over() from tenk1 b);
+
+
 -- to increase the parallel query test coverage
 EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;