Re: Row pattern recognition

Hi Henson,

> Hi hackers,
>
> When a WINDOW clause is not referenced by any window function, the pattern
> is never matched and the DEFINE expressions are never evaluated.
>
> One consequence is that a DEFINE which would raise a run-time error raises
> nothing when the window is unused. The same window definition behaves
> differently depending only on whether a window function consumes it:
>
> CREATE TABLE t (id int, v int);
> INSERT INTO t VALUES (1, 10), (2, 20), (3, 15);
>
> -- (1) the window IS used (count(*) OVER w): the DEFINE is evaluated
>
> SELECT count(*) OVER w AS cnt
> FROM t
> WINDOW w AS (
> ORDER BY id
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> INITIAL
> PATTERN (A+)
> DEFINE A AS (1 / (v - v)) > 0
> );
> -- ERROR: division by zero
>
> -- (2) the window is NOT used (no window function): same definition
>
> SELECT v
> FROM t
> WINDOW w AS (
> ORDER BY id
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> INITIAL
> PATTERN (A+)
> DEFINE A AS (1 / (v - v)) > 0
> );
> -- v
> -- ----
> -- 10
> -- 20
> -- 15
> -- (no error)
>
> EXPLAIN (VERBOSE, COSTS OFF) of (2) confirms the window, and with it the
> DEFINE expression, are gone entirely:
>
> Seq Scan on public.t
> Output: v, id
>
> (A column-dependent division by zero is used so that the expression does not
> constant-fold and can only error at run time.)
>
> This follows from existing, general planner behavior. A window definition
> that no window function references is removed from the plan by
> select_active_windows(); the planner does this for every window, because a
> window with no consumer produces no output, so sorting or partitioning it
> would be wasted work. Before RPR that reasoning was complete: an ordinary
> window has nothing but its output, so dropping an unused one changes nothing
> observable.
>
> RPR is what turns this into a question. A DEFINE clause is a per-row
> predicate that can have a run-time effect of its own -- here, raising an
> error -- independent of any output the window produces. So the assumption
> behind the optimization, that an unused window has no observable behavior,
> no
> longer holds automatically once a window carries a DEFINE. Whether the
> existing "drop it" optimization should still extend to an RPR window is a
> decision RPR's addition forces, not something the prior behavior settles.
>
> The question is whether the current behavior is what we want:
>
> (a) Keep it. Skipping the pattern matching for a window that produces
> nothing is the natural optimization, and an expression that is never
> evaluated raising no error is normal behavior.
>
> (b) Run the matching anyway, even though there is no output to produce, so
> that DEFINE errors are raised regardless of whether a window function
> consumes the window.
>
> Option (a) is cheaper and consistent with how unevaluated expressions
> behave, but I lean towards (b): a faulty DEFINE should fail consistently
> rather than pass silently just because no window function happens to consume
> the window. I would like to hear what you think.

I think we should follow (a).

From ISO/IEC 9075-2:2016 7.15 <window clause> General Rules:
------------------------------------
1) Let SL be the <select list> of the <query specification> or <select
statement: single row> that immediately contains TE [1].

Case:

a) If SL does not simply contain a <window function>, then the <window
clause> is disregarded, and the result of TE is the result of the last
<from clause>, <where clause>, <group by clause> or <having clause> of
TE.
------------------------------------
[1] TE: Table expression

So I think the standard requires a window clause to be disregarded if
window function is not included in the select list. As DEFINE is a
part of a window clause, it should be disregarded if there's no window
function in the window clause too.

> behave, but I lean towards (b): a faulty DEFINE should fail consistently
> rather than pass silently just because no window function happens to consume
> the window. I would like to hear what you think.

But we already pass faulty window clauses. Example:

-- If window function exists, faulty window clause (invalid frame
-- ending offset) is detected.
PREPARE prep AS
SELECT count(*) OVER w
FROM generate_series(1,5) g(i)
WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND $1 FOLLOWING
);
PREPARE
EXECUTE prep(-1);
psql:prepare.sql:7: ERROR: frame ending offset must not be negative

DEALLOCATE prep;
DEALLOCATE

-- But if window function does not exist, the faulty window clause
-- (invalid frame ending offset) is not detected.
PREPARE prep AS
SELECT i
FROM generate_series(1,5) g(i)
WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND $1 FOLLOWING
);
PREPARE
EXECUTE prep(-1);
i
---
1
2
3
4
5
(5 rows)

I think if we detect faulty DEFINE in the last case , it's not only
against the standard but against our existing behavior.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp