From: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
---|---|
To: | david(dot)g(dot)johnston(at)gmail(dot)com |
Cc: | jacob(dot)champion(at)enterprisedb(dot)com, vik(at)postgresfriends(dot)org, pgsql-hackers(at)postgresql(dot)org, er(at)xs4all(dot)nl, peter(at)eisentraut(dot)org |
Subject: | Re: Row pattern recognition |
Date: | 2024-10-22 10:19:09 |
Message-ID: | 20241022.191909.2043328908414791518.ishii@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2024-10-22 10:26:31 | Re: Make all Perl warnings fatal |
Previous Message | Alexander Lakhin | 2024-10-22 10:00:00 | WaitEventSetWaitBlock() can still hang on Windows due to connection reset |