Re: Row pattern recognition

> On Monday, October 21, 2024, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>>
>> I wonder how "PREV(col + 1)" is different from "PREV(col) + 1".
>> Currently my RPR implementation does not allow PREV(col + 1). If
>> "PREV(col + 1)" is different from "PREV(col) + 1", it maybe worthwhile
>> to implement "PREV(col + 1)".
>>
>
> Interesting feature that I’m now just seeing.
>
> The expression PREV(column_name) produces a value output taken from the
> given named column in the preceding frame row. It doesn’t make any sense
> to me to attempt to add the integer 1 to an identifier that is being used
> as a value input to a “function”. It would also seem quite odd if “+ 1”
> had something to do with row selection as opposed to simply being an
> operator “+(column_name%type, integer)” expression.

According to the ISO/IEC 9075-2:2016, 6.26 <row pattern navigation
operation> (I don't have access to SQL 2023) PREV (and NEXT) is
defined as:

<row pattern navigation: physical> ::=
<prev or next> <left paren> <value expression> [ <comma> <physical offset> ] <right paren>

(Besides <row pattern navigation: physical>, there are <row pattern
navigation: logical> and <row pattern navigation: compound> but I
ignore them here).

So PREV's first argument is a value expression (VE). VE shall contain
at least one row pattern column reference. <set function
specification>, <window function specification> or <row patter
navigation operation> are not permitted.

From this, I don't see any reason PREV(column_name + 1) is prohibited
unless I miss something.

I think even PREV(column_name1 + column_name2) is possible. I see
similar example in ISO/IEC 19075-5:2021, 5.6.2 "PREV and NEXT".

> Maybe RPR is defining something special here I haven't yet picked up on, in
> which case just ignore this. But if I read: “UP as price > prev(price +
> 1)” in the opening example it would be quite non-intuitive to reason out
> the meaning. “Price > prev(price) + 1” would mean my current row is at
> least one (e.g. dollar per share) more than the value of the previous
> period.

Acording to ISO/IEC 9075-2:2016 "4.21.2 Row pattern navigation operations",

<row pattern navigation operation> evaluates a <value expression> VE
in a row NR, which may be different than current row CR.

From this I think PREV(col + 1) should be interpreted as:

1. go to the previous row.
2. evaluate "col + 1" at the current row (that was previous row).
3. return the result.

If my understanding is correct, prev(price + 1) has the same meaning
as prev(price) + 1.

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