Re: Row pattern recognition - Mailing list pgsql-hackers

Hi Henson,

> Hi,Tatsuo
> 
> While reviewing the RPR test cases, I noticed that a subquery filter
> on RPR window function results silently returns wrong results.
> 
> For example, given this query:
> 
>   SELECT * FROM (
>       SELECT id, val, COUNT(*) OVER w as cnt
>       FROM rpr_copy
>       WINDOW w AS (
>           ORDER BY id
>           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>           PATTERN (A B?)
>           DEFINE A AS val > 10, B AS val > 20
>       )
>   ) sub
>   WHERE cnt > 0
>   ORDER BY id;
> 
> This should return 2 rows, but returns 0 rows instead.

Thanks for the report!

> The EXPLAIN plan shows that "cnt > 0" is pushed down into the
> WindowAgg node as a Run Condition:
> 
>   WindowAgg
>     Run Condition: (count(*) OVER w > 0)    <-- pushed down
>     ->  Sort
>           ->  Seq Scan on rpr_copy
> 
> I will investigate the cause and work on a fix.

I think there are two ways to solve the issue:

1) Fix the executor
2) Fix the planner

I tried #1 but I don't know how to fix it. The run condition is
already pushed in the window function. Existing code forces to stop
the aggregate evaluation if the run condition is not
satisfied. Without RPR, the optimization is correct because once the
run condition is not satisfied, there's no chance that the subsequence
rows satisfy the condition. But RPR is used, a partition/frame is
divided into multiple reduced frames and each should be evaluated to
the end of the partition/frame.

So, I tried #2 so that the planner does not push down the run
condition.

Attached is the patch.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 90275e25872..b67c35af39a 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2453,6 +2453,17 @@ find_window_run_conditions(Query *subquery, AttrNumber attno,
     wclause = (WindowClause *) list_nth(subquery->windowClause,
                                         wfunc->winref - 1);
 
+    /*
+     * If a DEFINE clause exists, we cannot push down a run condition. In the
+     * case, a window partition (or frame) is divided into multiple reduced
+     * frames and each frame should be evaluated to the end of the partition
+     * (or full frame end). This means we cannot apply the run condition
+     * optimization because it stops evaluation window functions in certain
+     * cases.
+     */
+    if (wclause->defineClause != NIL)
+        return false;
+
     req.type = T_SupportRequestWFuncMonotonic;
     req.window_func = wfunc;
     req.window_clause = wclause;