> On Monday, October 21, 2024, Tatsuo Ishii <is...@postgresql.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
