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

Tatsuo Ishii <ishii@postgresql.org>

From: Tatsuo Ishii <ishii@postgresql.org>

To: krasiyan@gmail.com, ojford@gmail.com, tgl@sss.pgh.pa.us, vik@postgresfriends.org, andrew@tao11.riddles.org.uk, david@fetter.org

Cc: pgsql-hackers@postgresql.org

Date: 2025-07-16T04:44:07Z

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

Attachments

(unnamed) (text/plain)
(unnamed) (text/plain)
window.diff (text/x-patch)

Currently the patch set include some regression test additions. I
wanted to expand the test coverage and ended up an idea: generate new
test cases from the existing window function regression test
(window.sql).

Attached "insert_ignore_nulls.sh" script reads window.sql and inserts
"ignore nulls" before "over" clause of each window functions that
accept IGNORE NULLS option. This way, although the generated test
cases do not cover the case where NULL is included, at least covers
all non NULL cases, which is better than nothing, I think.

I replaced the existing window.sql with the modified one, and ran the
regression test. Indeed the test failed because expected file is for
non IGNORE NULLS options. However, the differences should be just for
SQL statements, not the output of the SQL statements since the data
set used does not include NULLs. I did an eyeball check the diff and
the result was what I expected.

For those who are interested this test, I attached some files.

insert_ignore_nulls.sh: shell script to insert "ignore nulls"
window.sql: modified regression script by insert_ignore_nulls.sh
window.diff: diff of original window.out and modified window.out

Question is, how could we put this kind of test into core if it worth
the effort? The simplest idea is just adding the modified window.sql
to the end of existing window.sql and update window.out. Thoughts?

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