Date: Sun, 12 Oct 2025 17:39:59 +0900 (JST)
Message-Id: <20251012.173959.1398633360231077580.ishii@postgresql.org>
To: tgl@sss.pgh.pa.us
Cc: pramsey@cleverelephant.ca, ojford@gmail.com, peter@eisentraut.org,
 li.evan.chao@gmail.com, krasiyan@gmail.com, vik@postgresfriends.org,
 andrew@tao11.riddles.org.uk, david@fetter.org,
 pgsql-hackers@lists.postgresql.org
Subject: Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: <20251011.181103.1209570651155443747.ishii@postgresql.org>
References: <2952409.1760023154@sss.pgh.pa.us>
	<20251011.090731.1451293026979559494.ishii@postgresql.org>
	<20251011.181103.1209570651155443747.ishii@postgresql.org>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Sun_Oct_12_17_39_59_2025_031)--"
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20251012.173959.1398633360231077580.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Sun_Oct_12_17_39_59_2025_031)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> Probably I misunderstood what you said. Now I realize what you are
> suggesting was, throwing an error *only* when a RESPECT/IGNORE NULLS
> option is given and the function did not call
> WinAllowNullTreatmentOption. If the option is not given, no error is
> thrown even if WinAllowNullTreatmentOption is not called. I am okay
> with this direction. I will post a patch for this.

While I was implementing this, I realized that in order to check if
WinAllowNullTreatmentOption had been called or not, it's necessary to
call the window function at least once in mainline execution of
nodeWindowAgg.c (I supposed in eval_windowfunction). I don't like this
because I expected to throw an error *before* calling the window
function.

So I studied your idea:
> Alternatively, you could just drop the entire concept of throwing an
> error for that.

Attached is a patch to implement this. Previously window functions
should call WinCheckAndInitializeNullTreatment with
allowNullTreatment==true if they accept a null treatment
clause. Otherwise, they are called as if null treatment clause is not
specified. With the patch, window functions accept a null treatment
clause as specified without calling
WinCheckAndInitializeNullTreatment.

There's one thing which might be different from what you suggested
is, I want to give window functions a method to stat that they do not
want accept a null treatment clause. For this purpose
WinCheckAndInitializeNullTreatment (with allowNullTreatment==false)
can be called.(Alternatively we could eliminate allowNullTreatment
argument and rename it something like WinDisallowNullTreatmentOption).

Some of built-in window functions that do not accept a null treatment
clause call this in the patch. This way, we do not need to test the
case when the functions are given a null treatment option except just
they throw an error. User defined functions would call the function
for the same purpose as built-in window functions.

> The implementation is entirely
> within nodeWindowAgg.c and does not depend in any way on the
> cooperation of the window function.

I am not sure. For example built-in lead function's behavior (with
IGNORE NULLS option) is defined by the standard. Unlike RESPECT NULLS
case, the expected behavior may not be obvious. According the
standard:

1. If lead's "offset" option is 0, the argument evaluated on current
   row is returned regardless the value is NULL or NOT.

2. Otherwise, returns the value evaluated on a row which is nth NOT NULL.

For me, 2 is obvious but 1 was not so obvious because I thought that
lead() returns only non NULL value (except there's no non null values
or specified offset is out of partition).

Thus lead() calls WinGetFuncArgInPartition with
seektype==WINDOW_SEEK_CURRENT. Thus WinGetFuncArgInPartition with
WINDOW_SEEK_CURRENT is implemented in a way to satisfy the lead()
semantics above. This means if someone tries to implement a new window
function calling WinGetFuncArgInPartition with WINDOW_SEEK_CURRENT,
the function must has the same semantics as lead(). I think there's a
cooperation between nodeWindowAgg.c and window functions.

> +   This option is only allowed for the following functions: <function>lag</function>,
> +   <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
> +   <function>nth_value</function>.
> 
> as this fails to account for the possibility of user-defined window
> functions.  IMO we could drop the error check altogether and rewrite
> the docs along the lines of "Not all window functions pay attention
> to this option.  Of the built-in window functions, only blah blah
> and blah do."

Fixing docs are not included in the patch (yet).

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

----Next_Part(Sun_Oct_12_17_39_59_2025_031)--
Content-Type: Application/Octet-Stream
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="v1-0001-Allow-window-functions-to-accept-a-null-treatment.patch"

RnJvbSA5MjVjNTI3YjRlNzc2ZDQ5MjkyYTBmZDc0N2RjYjE2MzU3NDE4NzAzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBUYXRzdW8gSXNoaWkgPGlzaGlpQHBvc3RncmVzcWwub3JnPgpE
YXRlOiBTdW4sIDEyIE9jdCAyMDI1IDE2OjIxOjQwICswOTAwClN1YmplY3Q6IFtQQVRDSCB2MV0g
QWxsb3cgd2luZG93IGZ1bmN0aW9ucyB0byBhY2NlcHQgYSBudWxsIHRyZWF0bWVudCBjbGF1c2UK
IGJ5IGRlZmF1bHQuCgpQcmV2aW91c2x5IHdpbmRvdyBmdW5jdGlvbnMgKGVpdGhlciBidWlsdC1p
biBvciB1c2VyIGRlZmluZWQpIHNob3VsZApjYWxsIFdpbkNoZWNrQW5kSW5pdGlhbGl6ZU51bGxU
cmVhdG1lbnQgd2l0aCBhbGxvd051bGxUcmVhdG1lbnQ9PXRydWUKaWYgdGhleSBhY2NlcHQgYSBu
dWxsIHRyZWF0bWVudCBjbGF1c2UgKFJFU1BFQ1QgTlVMTFMvSUdOT1JFCk5VTExTKS4gT3RoZXJ3
aXNlLCB0aGV5IGFyZSBjYWxsZWQgYXMgaWYgbnVsbCB0cmVhdG1lbnQgY2xhdXNlIGlzIG5vdApz
cGVjaWZpZWQuCgpUaGlzIGNvbW1pdCBjaGFuZ2VzIHRoZSBiZWhhdmlvciBzbyB0aGF0IHdpbmRv
dyBmdW5jdGlvbnMgYWNjZXB0IGEKbnVsbCB0cmVhdG1lbnQgY2xhdXNlIGFzIHNwZWNpZmllZCB3
aXRob3V0IGNhbGxpbmcKV2luQ2hlY2tBbmRJbml0aWFsaXplTnVsbFRyZWF0bWVudC4KCklmIHdp
bmRvdyBmdW5jdGlvbnMgZG8gbm90IHdhbnQgYWNjZXB0IGEgbnVsbCB0cmVhdG1lbnQgY2xhdXNl
LCBjYWxsCldpbkNoZWNrQW5kSW5pdGlhbGl6ZU51bGxUcmVhdG1lbnQgd2l0aCBhbGxvd051bGxU
cmVhdG1lbnQ9PWZhbHNlLgotLS0KIHNyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVXaW5kb3dBZ2cu
YyB8IDE3ICsrKysrKysrKysrLS0tLS0tCiBzcmMvYmFja2VuZC91dGlscy9hZHQvd2luZG93ZnVu
Y3MuYyAgfCAgNCAtLS0tCiAyIGZpbGVzIGNoYW5nZWQsIDExIGluc2VydGlvbnMoKyksIDEwIGRl
bGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVXaW5kb3dB
Z2cuYyBiL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVXaW5kb3dBZ2cuYwppbmRleCBlNmE1M2Y5
NTM5MS4uYzJlMmNhNjkzNDcgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2V4ZWN1dG9yL25vZGVX
aW5kb3dBZ2cuYworKysgYi9zcmMvYmFja2VuZC9leGVjdXRvci9ub2RlV2luZG93QWdnLmMKQEAg
LTEzMDMsNyArMTMwMyw4IEBAIGJlZ2luX3BhcnRpdGlvbihXaW5kb3dBZ2dTdGF0ZSAqd2luc3Rh
dGUpCiAJCQl3aW5vYmotPnNlZWtwb3MgPSAtMTsKIAogCQkJLyogcmVzZXQgbnVsbCBtYXAgKi8K
LQkJCWlmICh3aW5vYmotPmlnbm9yZV9udWxscyA9PSBJR05PUkVfTlVMTFMpCisJCQlpZiAod2lu
b2JqLT5pZ25vcmVfbnVsbHMgPT0gSUdOT1JFX05VTExTIHx8CisJCQkJd2lub2JqLT5pZ25vcmVf
bnVsbHMgPT0gUEFSU0VSX0lHTk9SRV9OVUxMUykKIAkJCQltZW1zZXQod2lub2JqLT5ub3RudWxs
X2luZm8sIDAsCiAJCQkJCSAgIE5OX1BPU19UT19CWVRFUygKIAkJCQkJCQkJCSAgIHBlcmZ1bmNz
dGF0ZS0+d2lub2JqLT5udW1fbm90bnVsbF9pbmZvKSk7CkBAIC0zNTMwLDggKzM1MzEsMTAgQEAg
cHV0X25vdG51bGxfaW5mbyhXaW5kb3dPYmplY3Qgd2lub2JqLCBpbnQ2NCBwb3MsIGJvb2wgaXNu
dWxsKQogICogV2luQ2hlY2tBbmRJbml0aWFsaXplTnVsbFRyZWF0bWVudAogICoJCUNoZWNrIG51
bGwgdHJlYXRtZW50IGNsYXVzZSBhbmQgc2V0cyBpZ25vcmVfbnVsbHMKICAqCi0gKiBXaW5kb3cg
ZnVuY3Rpb25zIHNob3VsZCBjYWxsIHRoaXMgdG8gY2hlY2sgaWYgdGhleSBhcmUgYmVpbmcgY2Fs
bGVkIHdpdGgKLSAqIGEgbnVsbCB0cmVhdG1lbnQgY2xhdXNlIHdoZW4gdGhleSBkb24ndCBhbGxv
dyBpdCwgb3IgdG8gc2V0IGlnbm9yZV9udWxscy4KKyAqIFdpbmRvdyBmdW5jdGlvbnMgY2FsbCB0
aGlzIGlmIHRoZXkgZG8gbm90IGFjY2VwdCBhIG51bGwgdHJlYXRtZW50IGNsYXVzZQorICogd2l0
aCBhbGxvd051bGxUcmVhdG1lbnQ9PWZhbHNlLiBJdCdzIG5vdCBtYW5kYXRvcnkgYnV0IHRoZXkg
Y2FuIGNhbGwgdGhpcworICogd2l0aCBhbGxvd051bGxUcmVhdG1lbnQ9PXRydWUgdG8gZXhwbGlj
aXRseSBzdGF0IHRoYXQgdGhleSBhY2NlcHQgYSBhIG51bGwKKyAqIHRyZWF0bWVudCBjbGF1c2Uu
CiAgKi8KIHZvaWQKIFdpbkNoZWNrQW5kSW5pdGlhbGl6ZU51bGxUcmVhdG1lbnQoV2luZG93T2Jq
ZWN0IHdpbm9iaiwKQEAgLTM1NTUsNyArMzU1OCw2IEBAIFdpbkNoZWNrQW5kSW5pdGlhbGl6ZU51
bGxUcmVhdG1lbnQoV2luZG93T2JqZWN0IHdpbm9iaiwKIAl9CiAJZWxzZSBpZiAod2lub2JqLT5p
Z25vcmVfbnVsbHMgPT0gUEFSU0VSX0lHTk9SRV9OVUxMUykKIAkJd2lub2JqLT5pZ25vcmVfbnVs
bHMgPSBJR05PUkVfTlVMTFM7Ci0KIH0KIAogLyoKQEAgLTM3MjcsNyArMzcyOSw5IEBAIFdpbkdl
dEZ1bmNBcmdJblBhcnRpdGlvbihXaW5kb3dPYmplY3Qgd2lub2JqLCBpbnQgYXJnbm8sCiAJQXNz
ZXJ0KFdpbmRvd09iamVjdElzVmFsaWQod2lub2JqKSk7CiAJd2luc3RhdGUgPSB3aW5vYmotPndp
bnN0YXRlOwogCi0JbnVsbF90cmVhdG1lbnQgPSAod2lub2JqLT5pZ25vcmVfbnVsbHMgPT0gSUdO
T1JFX05VTExTICYmIHJlbHBvcyAhPSAwKTsKKwludWxsX3RyZWF0bWVudCA9ICgod2lub2JqLT5p
Z25vcmVfbnVsbHMgPT0gSUdOT1JFX05VTExTIHx8CisJCQkJCSAgIHdpbm9iai0+aWdub3JlX251
bGxzID09IFBBUlNFUl9JR05PUkVfTlVMTFMpICYmCisJCQkJCSAgcmVscG9zICE9IDApOwogCiAJ
c3dpdGNoIChzZWVrdHlwZSkKIAl7CkBAIC0zODY2LDcgKzM4NzAsOCBAQCBXaW5HZXRGdW5jQXJn
SW5GcmFtZShXaW5kb3dPYmplY3Qgd2lub2JqLCBpbnQgYXJnbm8sCiAJZWNvbnRleHQgPSB3aW5z
dGF0ZS0+c3MucHMucHNfRXhwckNvbnRleHQ7CiAJc2xvdCA9IHdpbnN0YXRlLT50ZW1wX3Nsb3Rf
MTsKIAotCWlmICh3aW5vYmotPmlnbm9yZV9udWxscyA9PSBJR05PUkVfTlVMTFMpCisJaWYgKHdp
bm9iai0+aWdub3JlX251bGxzID09IElHTk9SRV9OVUxMUyB8fAorCQl3aW5vYmotPmlnbm9yZV9u
dWxscyA9PSBQQVJTRVJfSUdOT1JFX05VTExTKQogCQlyZXR1cm4gaWdub3JlbnVsbHNfZ2V0ZnVu
Y2FyZ2luZnJhbWUod2lub2JqLCBhcmdubywgcmVscG9zLCBzZWVrdHlwZSwKIAkJCQkJCQkJCQkJ
IHNldF9tYXJrLCBpc251bGwsIGlzb3V0KTsKIApkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRp
bHMvYWR0L3dpbmRvd2Z1bmNzLmMgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvd2luZG93ZnVuY3Mu
YwppbmRleCA5NjlmMDJhYTU5Yi4uN2U5MzZhOGJmYmMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k
L3V0aWxzL2FkdC93aW5kb3dmdW5jcy5jCisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC93aW5k
b3dmdW5jcy5jCkBAIC01NDEsNyArNTQxLDYgQEAgbGVhZGxhZ19jb21tb24oRnVuY3Rpb25DYWxs
SW5mbyBmY2luZm8sCiAJYm9vbAkJaXNudWxsOwogCWJvb2wJCWlzb3V0OwogCi0JV2luQ2hlY2tB
bmRJbml0aWFsaXplTnVsbFRyZWF0bWVudCh3aW5vYmosIHRydWUsIGZjaW5mbyk7CiAJaWYgKHdp
dGhvZmZzZXQpCiAJewogCQlvZmZzZXQgPSBEYXR1bUdldEludDMyKFdpbkdldEZ1bmNBcmdDdXJy
ZW50KHdpbm9iaiwgMSwgJmlzbnVsbCkpOwpAQCAtNjU5LDcgKzY1OCw2IEBAIHdpbmRvd19maXJz
dF92YWx1ZShQR19GVU5DVElPTl9BUkdTKQogCURhdHVtCQlyZXN1bHQ7CiAJYm9vbAkJaXNudWxs
OwogCi0JV2luQ2hlY2tBbmRJbml0aWFsaXplTnVsbFRyZWF0bWVudCh3aW5vYmosIHRydWUsIGZj
aW5mbyk7CiAJcmVzdWx0ID0gV2luR2V0RnVuY0FyZ0luRnJhbWUod2lub2JqLCAwLAogCQkJCQkJ
CQkgIDAsIFdJTkRPV19TRUVLX0hFQUQsIHRydWUsCiAJCQkJCQkJCSAgJmlzbnVsbCwgTlVMTCk7
CkBAIC02ODEsNyArNjc5LDYgQEAgd2luZG93X2xhc3RfdmFsdWUoUEdfRlVOQ1RJT05fQVJHUykK
IAlEYXR1bQkJcmVzdWx0OwogCWJvb2wJCWlzbnVsbDsKIAotCVdpbkNoZWNrQW5kSW5pdGlhbGl6
ZU51bGxUcmVhdG1lbnQod2lub2JqLCB0cnVlLCBmY2luZm8pOwogCXJlc3VsdCA9IFdpbkdldEZ1
bmNBcmdJbkZyYW1lKHdpbm9iaiwgMCwKIAkJCQkJCQkJICAwLCBXSU5ET1dfU0VFS19UQUlMLCB0
cnVlLAogCQkJCQkJCQkgICZpc251bGwsIE5VTEwpOwpAQCAtNzA1LDcgKzcwMiw2IEBAIHdpbmRv
d19udGhfdmFsdWUoUEdfRlVOQ1RJT05fQVJHUykKIAlib29sCQlpc251bGw7CiAJaW50MzIJCW50
aDsKIAotCVdpbkNoZWNrQW5kSW5pdGlhbGl6ZU51bGxUcmVhdG1lbnQod2lub2JqLCB0cnVlLCBm
Y2luZm8pOwogCW50aCA9IERhdHVtR2V0SW50MzIoV2luR2V0RnVuY0FyZ0N1cnJlbnQod2lub2Jq
LCAxLCAmaXNudWxsKSk7CiAJaWYgKGlzbnVsbCkKIAkJUEdfUkVUVVJOX05VTEwoKTsKLS0gCjIu
NDMuMAoK

----Next_Part(Sun_Oct_12_17_39_59_2025_031)----