Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

Tatsuo Ishii <ishii@postgresql.org>

From: Tatsuo Ishii <ishii@postgresql.org>

To: ojford@gmail.com

Cc: krasiyan@gmail.com, tgl@sss.pgh.pa.us, vik@postgresfriends.org, pgsql-hackers@postgresql.org, andrew@tao11.riddles.org.uk, david@fetter.org

Date: 2025-01-28T09:02:32Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix coding style with "else".
- 20628b62e46e 19 (unreleased) landed
Fix multi WinGetFuncArgInFrame/Partition calls with IGNORE NULLS.
- 2d7b247cb414 19 (unreleased) landed
Fix Coverity issue reported in commit 2273fa32bce.
- dd766a441d69 19 (unreleased) landed
Use ereport rather than elog in WinCheckAndInitializeNullTreatment.
- 5f3808646f67 19 (unreleased) landed
Avoid uninitialized-variable warnings from older compilers.
- 71540dcdcb22 19 (unreleased) cited
Fix Coverity issues reported in commit 25a30bbd423.
- 2273fa32bce7 19 (unreleased) landed
Improve EXPLAIN's display of window functions.
- 8b1b342544b6 18.0 cited
Automatically generate node support functions
- 964d01ae90c3 16.0 cited

>> +/*
>> + * ignorenulls_getfuncarginframe
>> + * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
>> + * until we find a value or reach the frame's end.
>> + */
>> +static Datum
>> +ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
>>
>> Do you assume that win_nonnulls is sorted by pos? I think it's
>> necessarily true that pos in win_nonnulls array is sorted. Is that ok?
> 
> Yes it must be sorted on my understanding of the code.

Then the patch has a problem. I ran a query below and examined
win_nonnulls.  It seems it was not sorted out.

SELECT
  x,y,
  nth_value(y,1) IGNORE NULLS OVER w
FROM (VALUES (1,1), (2,2), (3,NULL), (4,4), (5,NULL), (6,6), (7,7)) AS t(x,y)
WINDOW w AS (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW);


(gdb) p *winobj->win_nonnulls @ winobj->nonnulls_len
$8 = {1, 0, 3, 6, 5}

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