Re: Row pattern recognition

Hi hackers,

This follows the ten-patch increment on top of v53 that I posted for
row pattern recognition (RPR, SQL:2016 R020, CF 4460) in the thread
"Row pattern recognition" [1]. After the ten were done, a code audit
and runs on the tip of RPR-20260930 found three defects in them. Two
make planning fail with an internal error, and one prints a function
body that does not run again. None returns a wrong row. Jian
reported the first one off-list on 09-22, with a patch.

The patch numbers below are those of the increment. I will fold the
three fixes into the patches they belong to in the next posting, each
with a regression test.

1. A column only DEFINE reads is missing below GROUP BY (0004)

DEFINE may read a column that is not in GROUP BY but is functionally
dependent on a grouped primary key. When the node below the aggregate
is a join or a Sort, not a plain scan, that column is not in its
target list and planning fails:

CREATE TABLE g (id int PRIMARY KEY, val int);
CREATE TABLE h (id int);
SELECT g.id, count(*) OVER w FROM g JOIN h ON g.id = h.id
GROUP BY g.id
WINDOW w AS (ORDER BY g.id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+) DEFINE A AS val > 0);
ERROR: variable not found in subplan target list

Without the primary key, val is an error, and it has to stay one. A
column that is not grouped and not functionally dependent on a grouped
one cannot be read after grouping, in a DEFINE clause as in HAVING or
ORDER BY. This is the same query on tables without it, with the caret
under the column in the DEFINE clause:

CREATE TABLE g2 (id int, val int);
CREATE TABLE h2 (id int);
SELECT g2.id, count(*) OVER w FROM g2 JOIN h2 ON g2.id = h2.id
GROUP BY g2.id
WINDOW w AS (ORDER BY g2.id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+) DEFINE A AS val > 0);
ERROR: column "g2.val" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 5: PATTERN (A+) DEFINE A AS val > 0);
^

A column that is not in GROUP BY can be read only if it has a single
value in each group. A primary key identifies one row of its table.
If all its columns are in GROUP BY, every row of a group comes from
the same row of that table, so each other column of the table has one
value per group. So only such a column reaches the planner.

The cause is in 0004, which stopped the parser from planting DEFINE's
Vars in the target list and let the planner find them, as it does for
HAVING. It did so in make_window_input_target() but not in
make_group_input_target(), whose input never mentions what only DEFINE
reads.

Jian raised this off-list on 09-22 with a patch. His example reads
from a plain scan, which hides the failure, but he was right that the
grouping input needs it. The patch is not the right fix: it also
pulls out the columns under a grouped expression, which changes the
output of the rpr_gexp test.

2. A SQL function parameter in DEFINE does not deparse (0002, 0005)

get_parameter() qualifies a named parameter with the function name.
0002 rejects a two-part name in DEFINE. So a function with a standard
body that reads a parameter in DEFINE cannot be dumped and restored:

CREATE TABLE t (id int, v int);
CREATE FUNCTION f(threshold int) RETURNS SETOF bigint
LANGUAGE sql
BEGIN ATOMIC
SELECT count(*) OVER w FROM t
WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+) DEFINE A AS v > threshold);
END;
SELECT pg_get_functiondef('f(int)'::regprocedure);
... DEFINE a AS (v > f.threshold) ...
-- run that text again:
ERROR: qualified expression "f.threshold" is not allowed in DEFINE
clause

pg_dump fails the same way. The same path prints PREV(v,
(f.n)::bigint) and (f.p).field too, and of eight forms I tried only an
unnamed parameter ($1) survives. PL/pgSQL is not affected, since its
body is stored as text.

Dropping the qualifier would bind the name to a column of the same
name and change the query silently. The fix is to print $N for a
parameter while deparsing DEFINE. The same function then deparses as

... DEFINE a AS (v > $1) ...

which runs again and reads the parameter.

3. A function parameter inside PREV() under LATERAL (0001)

The argument of PREV, NEXT, FIRST and LAST is evaluated at another
row, so a value folded into a constant could raise at plan time an
error that execution would never reach. Below, v becomes the constant
1 after pull-up, and 1 / 0 is never evaluated, since the first row has
no previous row. Before 0004 this query failed at plan time:

SELECT count(*) OVER w FROM (VALUES (1)) t(v)
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(v / 0) > 0);
count
-------
0

So 0004 keeps the argument dependent on the row. 0001 does the same
for the merged column of a USING join, which is an expression when the
join is a FULL JOIN, or when one side has to be converted to the
common type, as the left side of the LEFT JOIN below. Before 0001
this one failed with "division by zero":

SELECT count(*) OVER w
FROM (VALUES (1)) a(k) LEFT JOIN (VALUES (1::bigint)) b(k) USING (k)
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A) DEFINE A AS PREV(k / 0) > 0);
count
-------
0

The change in 0001 is a problem in one case, a SQL function that reads
its parameter inside PREV(), called under LATERAL over such a column:

CREATE TABLE s (v int);
INSERT INTO s SELECT generate_series(1, 6);
CREATE TABLE a (k int);
CREATE TABLE b (k bigint);
INSERT INTO a VALUES (1), (2), (-10);
INSERT INTO b VALUES (1), (2), (-10);
CREATE FUNCTION f3(p bigint) RETURNS TABLE(v int, cnt bigint) AS $$
SELECT v, count(*) OVER w FROM s
WINDOW w AS (ORDER BY v
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A B+) DEFINE B AS PREV(v + p) > 0)
$$ LANGUAGE sql STABLE;
SELECT * FROM a FULL JOIN b USING (k), LATERAL f3(k);
ERROR: plan should not reference subplan's variable

The function body names no outer column, only its own parameter, and a
parameter is a value. The same function runs normally when it is not
inlined, and so does one that reads the parameter outside PREV(), so I
think this query should run. ISO/IEC 19075-5 4.18.3 even suggests
this form: pass the values that are prohibited as outer references to
an SQL-invoked routine as arguments. An internal error is a defect
either way.

I am not confident of this reading, though. Should a function
parameter that DEFINE reads count as an outer reference once the
function is inlined? I would like us to settle that together.

A plain LATERAL subquery that names an outer column in DEFINE is a
different case. The standard prohibits outer references in DEFINE
(ISO/IEC 19075-5 4.18.1), and the parser rejects one:

SELECT * FROM a FULL JOIN b USING (k),
LATERAL (SELECT v, count(*) OVER w AS cnt FROM s
WINDOW w AS (ORDER BY v
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A B+)
DEFINE B AS PREV(v + k) > 0)) x;
ERROR: cannot use outer query column in DEFINE clause
LINE 6: DEFINE B AS PREV(v + k) > 0)) x;
^

The merged column has to be an expression for the error to occur: a
FULL JOIN, or a LEFT JOIN whose left side has to be converted to the
common type. The query also has to read the merged column. An INNER
JOIN, a LEFT JOIN whose left side is already the wider type, and a
parameter used outside PREV() as in PREV(v) + p, are fine.

What I checked

I ran each reproduction above on a build identical to the tip of
RPR-20260930. There is no fix for any of the three yet. I do not
know of another case of the same kind, but I found these by reading
and by trying shapes, so I cannot rule one out.

[1]
https://postgr.es/m/CAAAe_zDsYugq506ou49PU+Ok+4Umn5n59Qs5wYofvKyfEpvZJQ@mail.gmail.com

Best regards,
Henson