Re: Row pattern recognition

Hi Jian, Tatsuo,

While going over DEFINE clause behavior against the parse tree, I found
another case where a query that is valid on plain PostgreSQL dies with an
internal error under RPR. It takes a window whose ORDER BY and whose
DEFINE read the same composite value, with that value arriving through a
subquery.

Setup:

CREATE TABLE t (a int, b int);
INSERT INTO t SELECT g, g % 4 FROM generate_series(1, 10) g;

Reproduction:

SELECT count(*) OVER w AS c
FROM (SELECT ROW(a, b) AS x FROM t) s
WINDOW w AS (ORDER BY x
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL);
ERROR: variable not found in subplan target list

Control -- dropping the ORDER BY line alone makes it work:

SELECT count(*) OVER w AS c
FROM (SELECT ROW(a, b) AS x FROM t) s
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL PATTERN (P Q+) DEFINE P AS TRUE, Q AS x IS NOT NULL);
c
----
10
0
...
(10 rows)

What happens:

The planner rewrites IS NOT NULL on a ROW(...) into one test per field.
So DEFINE's

x IS NOT NULL

effectively becomes

a IS NOT NULL AND b IS NOT NULL

Ordinarily that is fine: the rewrite is visible early enough that a and
b, which DEFINE will end up reading, are made available in the window
input.

Here, though, x arrives through a subquery. At parse time x is just a
column of that subquery; the fact that it is ROW(a, b) only surfaces
later, when the subquery is pulled up. The rewrite happens only then --
by which point what goes into the window input has already been decided,
and a and b are not in it.

The ORDER BY copy of x, meanwhile, stays whole as ROW(a, b), because a
sort key has to be evaluated once. The split DEFINE side and the unsplit
ORDER BY side now need different things, and planning fails at the end
with the error above. Dropping the ORDER BY lets x be flattened the
ordinary way, so a and b do reach the window input and the query runs.

The fix is to not do that split for a DEFINE clause. I added
EXPRKIND_RPR_DEFINE for the defineClause preprocessing pass, and had that
path call eval_const_expressions_keep_row_nulltest() in place of
eval_const_expressions(). Unsplit, DEFINE's x keeps the same shape as the
ORDER BY side, and a and b are never looked for separately.

All RPR regression suites pass, and I added the reproduction and its
control to rpr_base. The same split reached through PARTITION BY (via a
subquery and via a view) is covered there too.

Patch attached.

My reading is that a DEFINE clause has no reason to be split in the first
place: it never feeds an index lookup, and is evaluated per row inside
the pattern matcher. I'd appreciate a check on whether that holds.

Best regards,
Henson