Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Tatsuo Ishii <ishii@sraoss.co.jp>
From: Tatsuo Ishii <ishii@sraoss.co.jp>
To: ojford@gmail.com
Cc: pgsql-hackers@postgresql.org, tgl@sss.pgh.pa.us,
vik@postgresfriends.org, andrew@tao11.riddles.org.uk, david@fetter.org,
krasiyan@gmail.com
Date: 2023-05-06T03:57:25Z
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
- reject_null_treatment.patch (text/x-patch) patch
>> The attached test patch is mostly the same as in the previous patch
>> set, but it doesn't fail on row_number anymore as the main patch
>> only rejects aggregate functions. The test patch also adds a test for
>
>> +SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- succeeds
>
> I think the standard does not allow to specify RESPECT NULLS other
> than lead, lag, first_value, last_value and nth_value. Unless we agree
> that PostgreSQL violates the standard in this regard, you should not
> allow to use RESPECT NULLS for the window functions, expect lead etc.
> and aggregates.
>
> See my patch.
>
>> +/*
>> + * Window function option clauses
>> + */
>> +opt_null_treatment:
>> + RESPECT NULLS_P { $$ = RESPECT_NULLS; }
>> + | IGNORE_P NULLS_P { $$ = IGNORE_NULLS; }
>> + | /*EMPTY*/ { $$ = NULL_TREATMENT_NOT_SET; }
>> + ;
>
> With this, you can check if null treatment clause is used or not in
> each window function.
>
> In my previous patch I did the check in parse/analysis but I think
> it's better to be checked in each window function. This way,
>
> - need not to add a column to pg_proc.
>
> - allow user defined window functions to decide by themselves whether
> they can accept null treatment option.
Attached is the patch to implement this (on top of your patch).
test=# SELECT row_number() RESPECT NULLS OVER () FROM (SELECT 1) AS s;
ERROR: window function row_number cannot have RESPECT NULLS or IGNORE NULLS
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp