Re: Row pattern recognition

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.

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.

Best regards,
Henson