Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNivk-0010vK-2j for pgsql-hackers@arkaria.postgresql.org; Fri, 15 May 2026 03:09:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNivj-00G0Be-1H for pgsql-hackers@arkaria.postgresql.org; Fri, 15 May 2026 03:09:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNivi-00G0BV-34 for pgsql-hackers@lists.postgresql.org; Fri, 15 May 2026 03:09:39 +0000 Received: from mail-pl1-x62f.google.com ([2607:f8b0:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wNivg-00000000gOt-1cSj for pgsql-hackers@postgresql.org; Fri, 15 May 2026 03:09:38 +0000 Received: by mail-pl1-x62f.google.com with SMTP id d9443c01a7336-2b7d3ecc10dso85674485ad.2 for ; Thu, 14 May 2026 20:09:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1778814574; x=1779419374; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=r05/HbunghxcithAw3W01cgUGgT8dy0nPdE/AHJroiw=; b=ED7vq0lgOoxrvHbOxjtciLaAs8Ho92aK8BdqLhajkTMO7DEepFBK2tvdFFMWgwYdXN zd/jnnyZwEMyGJS35Mn29CS2gcC+f35kzIPk849Q41aFNVmeOm3rQSeP727HztZe2+V2 ek1gwUrmrXroC7Nd0B3WwfR7BjedwDyY51VivmXRW0eeF4szguiTrKApvVJJehIT+5GK ponl45UszXklRlg4soFbxyP43Eq7WT/AT4Q5uccnsZlGxDXNM7NKuZ9lp/6bd4eFVJ92 9n7ayERduWWJ9MSn0T7Aka+AzQ9NJxDIefWSE7PkmM7hpTayZAXsVXm2+Q8IiQgdCP+r e0Mg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778814574; x=1779419374; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=r05/HbunghxcithAw3W01cgUGgT8dy0nPdE/AHJroiw=; b=SmIaCcAxyHl9iFw/lrSCovif/yk3mK70l8Apr+XUnHcQSYs3kr6XOh0Ep5r+wVpSQT rAJ9Xs0iTKpjuatc7NKMrFpfhhcp+J/Tc6c3BmVu/eqMFIOq/QLu5xPm5ndf9r7vt7b0 i1E1oNOt1SlC/8XWh1gS2DiEjai2OuxMC0ocky3YUcR4j7cJlG38BOfgYnifEpBJicYE mdiZ0otMtw/0pS5Pk/XxamdXPJC04nW6F4EyJuMtNJcV5jcvv1377QHhU6kQvrQo9LIR ZLZ5fxeVZ8Ibqm7WX8pFg95pl8eGgi7BDACiSAUY77LkYvFF2k/mVHTk17XL10p1EO9p 1QkA== X-Gm-Message-State: AOJu0YwVQ5DWWzo8N8UAwgBjvRDWhornWE1T64UYhQc+gMiF+maV84bS DRjQhqbI/EVUInMV8fFp4gheJJiONhRYe2Yu66DePJf7SQCfSfKO2o4r X-Gm-Gg: Acq92OFX76seFCOJog7HCyJKtQM6ZjUUKltOtY3pWiDAptl8H8wKkButcq4l/KEdGP3 YQc1xYZAKTxZWtu8pgVSrfeHXmaJjFx8VuuJP6lIAoefpDW3HluUaKj8y9OclIQmHSR9jGOFuuP /tutAor4yNDFoB9PpNxKOBzCm4zF4yC1XrKwiiSvr01j39adhbP8W0OE2UUWqyZAqIgAAh524mY K41FL2kb7ZZ9JW1NI68ComcGRGR06KZoNrbGaUSiiCnE2F9G2ie87Fl6QtFtQCKaHy0jGtRJny8 Yh3gKLN6Ud25jbJCfUvmECWcH4euLdFmL14uLy9AkVEamsRoF8G10hsqQ+B1DIW4v1sW6W0n4S0 72hh3n+TY5dWSymVf/UYGG02c1KyXrSV7K+BFurKX8AkPtpOXbbdmF9IJJZQgO13VBKKawUSqME NLCyfHU8ffNPIyN+qj5fRMABgvYLWmSVRshuNVsaLLng== X-Received: by 2002:a17:902:f54c:b0:2b2:6df1:1112 with SMTP id d9443c01a7336-2bd7e923ea3mr23848905ad.40.1778814573840; Thu, 14 May 2026 20:09:33 -0700 (PDT) Received: from smtpclient.apple ([45.32.121.103]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-2bd5bd5f2e7sm39664875ad.5.2026.05.14.20.09.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 14 May 2026 20:09:32 -0700 (PDT) From: Chao Li Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_B5B60395-2733-44F6-BC47-603081EE27DE" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Re: Should IGNORE NULLS cache nullness for volatile arguments? Date: Fri, 15 May 2026 11:08:54 +0800 In-Reply-To: <20260514.215622.2237422742023167912.ishii@postgresql.org> Cc: pgsql-hackers@postgresql.org, ojford@gmail.com To: Tatsuo Ishii References: <42B42506-6972-4266-8422-FB73E61D9DA7@gmail.com> <20260514.165323.1913944269609196803.ishii@postgresql.org> <20260514.215622.2237422742023167912.ishii@postgresql.org> X-Mailer: Apple Mail (2.3864.400.21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_B5B60395-2733-44F6-BC47-603081EE27DE Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On May 14, 2026, at 20:56, Tatsuo Ishii wrote: >=20 > Hi Chao, >=20 >>> The attached patch makes a small change in that direction. It only = uses the IGNORE NULLS nullness cache when the argument is safe to reuse. = For non-cacheable arguments, the nullness is treated as unknown and the = argument is evaluated again. >>>=20 >>> See the attached patch for details. >>=20 >> I will look into the patches. >=20 > @@ -3454,7 +3455,10 @@ ignorenulls_getfuncarginframe(WindowObject = winobj, int argno, > if (isout) > *isout =3D false; >=20 > - v =3D get_notnull_info(winobj, abs_pos, argno); > + if (winobj->notnull_info_cacheable[argno]) >=20 > What about moving this if statement inside get_notnull_info() so that > the caller does not care about this argno is cacheable or not? >=20 > + /* record the row status if it is safe to reuse */ > + if (winobj->notnull_info_cacheable[argno]) > + put_notnull_info(winobj, abs_pos, argno, *isnull); >=20 > Similary, we can move "if (winobj->notnull_info_cacheable[argno])" = inside put_notnull_info(). >=20 Yep, good idea. Addressed in attached v2. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_B5B60395-2733-44F6-BC47-603081EE27DE Content-Disposition: attachment; filename=v2-0001-Fix-IGNORE-NULLS-nullness-cache-for-volatile-wind.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Fix-IGNORE-NULLS-nullness-cache-for-volatile-wind.patch" Content-Transfer-Encoding: quoted-printable =46rom=200d25ea218666a0e76950cbb03517a014d6b3abac=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Thu,=2014=20May=202026=2011:59:34=20+0800=0ASubject:=20[PATCH=20= v2]=20Fix=20IGNORE=20NULLS=20nullness=20cache=20for=20volatile=20window=0A= =20arguments=0A=0AThe=20IGNORE=20NULLS=20implementation=20caches=20= whether=20a=20window=20function=20argument=0Aevaluated=20to=20NULL=20or=20= NOT=20NULL=20for=20a=20given=20partition=20row.=20=20That=20is=20safe=20= for=0Aordinary=20expressions,=20but=20not=20for=20volatile=20= expressions,=20where=20evaluating=20the=0Asame=20argument=20on=20the=20= same=20row=20can=20produce=20a=20different=20NULL/NOT=20NULL=20result=0A= later.=0A=0AThis=20could=20produce=20wrong=20results=20in=20two=20ways.=20= =20A=20row=20previously=20cached=20as=0ANULL=20could=20be=20skipped=20= even=20though=20a=20later=20evaluation=20would=20return=20NOT=20NULL.=0A= Conversely,=20a=20row=20cached=20as=20NOT=20NULL=20could=20be=20chosen=20= as=20the=20target=20row,=20then=0Are-evaluated=20to=20fetch=20the=20= actual=20value=20and=20return=20NULL.=0A=0AMake=20the=20nullness=20cache=20= conditional=20per=20argument.=20=20Do=20not=20use=20it=20for=0Aarguments=20= containing=20volatile=20functions=20or=20subplans,=20following=20the=20= same=0Aconservative=20approach=20used=20for=20moving=20window=20= aggregates.=20=20Also=20avoid=0Are-evaluating=20non-cacheable=20= partition=20arguments=20after=20the=20scan=20has=20already=0Afound=20the=20= target=20row.=0A=0AAdd=20regression=20tests=20covering=20volatile=20= arguments=20and=20subplan=20arguments=20with=0AIGNORE=20NULLS.=0A=0A= Author:=20Chao=20Li=20=0AReviewed-by:=20Tatsuo=20Ishii=20= =0ADiscussion:=20= https://postgr.es/m/42B42506-6972-4266-8422-FB73E61D9DA7@gmail.com=0A---=0A= =20src/backend/executor/nodeWindowAgg.c=20|=2044=20+++++++++++++++----=0A= =20src/test/regress/expected/window.out=20|=2066=20= ++++++++++++++++++++++++++++=0A=20src/test/regress/sql/window.sql=20=20=20= =20=20=20|=2028=20++++++++++++=0A=203=20files=20changed,=20130=20= insertions(+),=208=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/executor/nodeWindowAgg.c=20= b/src/backend/executor/nodeWindowAgg.c=0Aindex=20= 5cc39bd9086..f1c524d00df=20100644=0A---=20= a/src/backend/executor/nodeWindowAgg.c=0A+++=20= b/src/backend/executor/nodeWindowAgg.c=0A@@=20-76,6=20+76,7=20@@=20= typedef=20struct=20WindowObjectData=0A=20=09int64=09=20=20=20= *num_notnull_info;=09/*=20track=20size=20(number=20of=20tuples=20in=0A=20= =09=09=09=09=09=09=09=09=09=20*=20partition)=20of=20the=20notnull_info=20= array=0A=20=09=09=09=09=09=09=09=09=09=20*=20for=20each=20func=20args=20= */=0A+=09bool=09=20=20=20*notnull_info_cacheable;=20/*=20can=20we=20= cache=20notnull_info?=20*/=0A=20=0A=20=09/*=0A=20=09=20*=20Null=20= treatment=20options.=20One=20of:=20NO_NULLTREATMENT,=20= PARSER_IGNORE_NULLS,=0A@@=20-3518,8=20+3519,23=20@@=20= init_notnull_info(WindowObject=20winobj,=20WindowStatePerFunc=20= perfuncstate)=0A=20=0A=20=09if=20(winobj->ignore_nulls=20=3D=3D=20= PARSER_IGNORE_NULLS)=0A=20=09{=0A+=09=09int=09=09=09argno=20=3D=200;=0A+=09= =09ListCell=20=20=20*lc;=0A+=0A=20=09=09winobj->notnull_info=20=3D=20= palloc0_array(uint8=20*,=20numargs);=0A=20=09=09winobj->num_notnull_info=20= =3D=20palloc0_array(int64,=20numargs);=0A+=09=09= winobj->notnull_info_cacheable=20=3D=20palloc_array(bool,=20numargs);=0A= +=0A+=09=09foreach(lc,=20perfuncstate->wfunc->args)=0A+=09=09{=0A+=09=09=09= Node=09=20=20=20*arg=20=3D=20(Node=20*)=20lfirst(lc);=0A+=0A+=09=09=09= winobj->notnull_info_cacheable[argno]=20=3D=0A+=09=09=09=09= !contain_volatile_functions(arg)=20&&=0A+=09=09=09=09= !contain_subplans(arg);=0A+=0A+=09=09=09argno++;=0A+=09=09}=0A=20=09}=0A=20= }=0A=20=0A@@=20-3580,6=20+3596,9=20@@=20get_notnull_info(WindowObject=20= winobj,=20int64=20pos,=20int=20argno)=0A=20=09uint8=09=09mb;=0A=20=09= int64=09=09bpos;=0A=20=0A+=09if=20= (!winobj->notnull_info_cacheable[argno])=0A+=09=09return=20NN_UNKNOWN;=0A= +=0A=20=09grow_notnull_info(winobj,=20pos,=20argno);=0A=20=09bpos=20=3D=20= NN_POS_TO_BYTES(pos);=0A=20=09mbp=20=3D=20winobj->notnull_info[argno];=0A= @@=20-3603,6=20+3622,9=20@@=20put_notnull_info(WindowObject=20winobj,=20= int64=20pos,=20int=20argno,=20bool=20isnull)=0A=20=09uint8=09=09val=20=3D=20= isnull=20?=20NN_NULL=20:=20NN_NOTNULL;=0A=20=09int=09=09=09shift;=0A=20=0A= +=09if=20(!winobj->notnull_info_cacheable[argno])=0A+=09=09return;=0A+=0A= =20=09grow_notnull_info(winobj,=20pos,=20argno);=0A=20=09bpos=20=3D=20= NN_POS_TO_BYTES(pos);=0A=20=09mbp=20=3D=20winobj->notnull_info[argno];=0A= @@=20-3812,6=20+3834,7=20@@=20WinGetFuncArgInPartition(WindowObject=20= winobj,=20int=20argno,=0A=20=09int=09=09=09notnull_relpos;=0A=20=09int=09= =09=09forward;=0A=20=09bool=09=09myisout;=0A+=09bool=09=09got_datum;=0A=20= =0A=20=09Assert(WindowObjectIsValid(winobj));=0A=20=09winstate=20=3D=20= winobj->winstate;=0A@@=20-3860,6=20+3883,7=20@@=20= WinGetFuncArgInPartition(WindowObject=20winobj,=20int=20argno,=0A=20=09= notnull_relpos=20=3D=20abs(relpos);=0A=20=09forward=20=3D=20relpos=20>=20= 0=20?=201=20:=20-1;=0A=20=09myisout=20=3D=20false;=0A+=09got_datum=20=3D=20= false;=0A=20=09datum=20=3D=200;=0A=20=0A=20=09/*=0A@@=20-3905,25=20= +3929,29=20@@=20WinGetFuncArgInPartition(WindowObject=20winobj,=20int=20= argno,=0A=20=09=09{=0A=20=09=09=09/*=0A=20=09=09=09=20*=20NOT=20NULL=20= info=20does=20not=20exist=20yet.=20=20Get=20tuple=20and=20evaluate=20= func=0A-=09=09=09=20*=20arg=20in=20partition.=20We=20ignore=20the=20= return=20value=20from=0A-=09=09=09=20*=20gettuple_eval_partition=20= because=20we=20are=20just=20interested=20in=0A-=09=09=09=20*=20whether=20= we=20are=20inside=20or=20outside=20of=20partition,=20NULL=20or=20NOT=0A-=09= =09=09=20*=20NULL.=0A+=09=09=09=20*=20arg=20in=20partition.=20Keep=20the=20= return=20value=20in=20case=20this=20row=20is=20the=0A+=09=09=09=20*=20= target;=20re-evaluating=20a=20volatile=20argument=20could=20give=20a=0A+=09= =09=09=20*=20different=20nullness=20status.=0A=20=09=09=09=20*/=0A-=09=09= =09(void)=20gettuple_eval_partition(winobj,=20argno,=0A-=09=09=09=09=09=09= =09=09=09=09=20=20=20abs_pos,=20isnull,=20&myisout);=0A+=09=09=09datum=20= =3D=20gettuple_eval_partition(winobj,=20argno,=0A+=09=09=09=09=09=09=09=09= =09=09=09abs_pos,=20isnull,=20&myisout);=0A=20=09=09=09if=20(myisout)=09=09= /*=20out=20of=20partition?=20*/=0A=20=09=09=09=09break;=0A=20=09=09=09if=20= (!*isnull)=0A+=09=09=09{=0A=20=09=09=09=09notnull_offset++;=0A+=09=09=09=09= if=20(notnull_offset=20>=3D=20notnull_relpos)=0A+=09=09=09=09=09= got_datum=20=3D=20true;=0A+=09=09=09}=0A=20=09=09=09/*=20record=20the=20= row=20status=20*/=0A=20=09=09=09put_notnull_info(winobj,=20abs_pos,=20= argno,=20*isnull);=0A=20=09=09}=0A=20=09}=20while=20(notnull_offset=20<=20= notnull_relpos);=0A=20=0A=20=09/*=20get=20tuple=20and=20evaluate=20func=20= arg=20in=20partition=20*/=0A-=09datum=20=3D=20= gettuple_eval_partition(winobj,=20argno,=0A-=09=09=09=09=09=09=09=09=09= abs_pos,=20isnull,=20&myisout);=0A+=09if=20(!got_datum)=0A+=09=09datum=20= =3D=20gettuple_eval_partition(winobj,=20argno,=0A+=09=09=09=09=09=09=09=09= =09=09abs_pos,=20isnull,=20&myisout);=0A=20=09if=20(!myisout=20&&=20= set_mark)=0A=20=09=09WinSetMarkPosition(winobj,=20mark_pos);=0A=20=09if=20= (isout)=0Adiff=20--git=20a/src/test/regress/expected/window.out=20= b/src/test/regress/expected/window.out=0Aindex=20= e6aac27a2a9..de0e14a686e=20100644=0A---=20= a/src/test/regress/expected/window.out=0A+++=20= b/src/test/regress/expected/window.out=0A@@=20-5964,6=20+5964,72=20@@=20= WINDOW=20w=20AS=20(ORDER=20BY=20x=20ROWS=20BETWEEN=202=20PRECEDING=20AND=20= 2=20FOLLOWING);=0A=20=205=20|=20=20=20=20=20=20=20=20=204=0A=20(5=20= rows)=0A=20=0A+--=20volatile=20arguments=20cannot=20use=20the=20IGNORE=20= NULLS=20nullness=20cache=0A+CREATE=20TEMPORARY=20SEQUENCE=20= null_treatment_seq;=0A+CREATE=20FUNCTION=20pg_temp.volatile_null(i=20= int)=20RETURNS=20int=0A+LANGUAGE=20sql=20VOLATILE=20AS=0A+$$=0A+=20=20= SELECT=20CASE=20WHEN=20nextval('null_treatment_seq')=20%=202=20=3D=200=20= THEN=20i=20ELSE=20NULL=20END;=0A+$$;=0A+SELECT=20x,=0A+=20=20=20=20=20=20= =20first_value(pg_temp.volatile_null(x))=20IGNORE=20NULLS=0A+=20=20=20=20= =20=20=20=20=20OVER=20(ORDER=20BY=20x=20ROWS=20BETWEEN=20UNBOUNDED=20= PRECEDING=20AND=20CURRENT=20ROW)=0A+FROM=20generate_series(1,5)=20g(x);=0A= +=20x=20|=20first_value=20=0A+---+-------------=0A+=201=20|=20=20=20=20=20= =20=20=20=20=20=20=20=0A+=202=20|=20=20=20=20=20=20=20=20=20=20=201=0A+=20= 3=20|=20=20=20=20=20=20=20=20=20=20=202=0A+=204=20|=20=20=20=20=20=20=20=20= =20=20=202=0A+=205=20|=20=20=20=20=20=20=20=20=20=20=202=0A+(5=20rows)=0A= +=0A+SELECT=20last_value=20FROM=20null_treatment_seq;=0A+=20last_value=20= =0A+------------=0A+=20=20=20=20=20=20=20=20=20=208=0A+(1=20row)=0A+=0A= +ALTER=20SEQUENCE=20null_treatment_seq=20RESTART=20WITH=201;=0A+SELECT=20= x,=0A+=20=20=20=20=20=20=20lead(pg_temp.volatile_null(x),=201)=20IGNORE=20= NULLS=20OVER=20(ORDER=20BY=20x)=0A+FROM=20generate_series(1,5)=20g(x);=0A= +=20x=20|=20lead=20=0A+---+------=0A+=201=20|=20=20=20=203=0A+=202=20|=20= =20=20=204=0A+=203=20|=20=20=20=205=0A+=204=20|=20=20=20=20=20=0A+=205=20= |=20=20=20=20=20=0A+(5=20rows)=0A+=0A+SELECT=20last_value=20FROM=20= null_treatment_seq;=0A+=20last_value=20=0A+------------=0A+=20=20=20=20=20= =20=20=20=20=207=0A+(1=20row)=0A+=0A+ALTER=20SEQUENCE=20= null_treatment_seq=20RESTART=20WITH=201;=0A+SELECT=20x,=0A+=20=20=20=20=20= =20=20first_value((SELECT=20CASE=20WHEN=20nextval('null_treatment_seq')=20= %=202=20=3D=200=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20THEN=20x=20ELSE=20NULL=20END))=20= IGNORE=20NULLS=0A+=20=20=20=20=20=20=20=20=20OVER=20(ORDER=20BY=20x=20= ROWS=20BETWEEN=20UNBOUNDED=20PRECEDING=20AND=20CURRENT=20ROW)=0A+FROM=20= generate_series(1,5)=20g(x);=0A+=20x=20|=20first_value=20=0A= +---+-------------=0A+=201=20|=20=20=20=20=20=20=20=20=20=20=20=20=0A+=20= 2=20|=20=20=20=20=20=20=20=20=20=20=201=0A+=203=20|=20=20=20=20=20=20=20=20= =20=20=202=0A+=204=20|=20=20=20=20=20=20=20=20=20=20=202=0A+=205=20|=20=20= =20=20=20=20=20=20=20=20=202=0A+(5=20rows)=0A+=0A+SELECT=20last_value=20= FROM=20null_treatment_seq;=0A+=20last_value=20=0A+------------=0A+=20=20=20= =20=20=20=20=20=20=208=0A+(1=20row)=0A+=0A=20--cleanup=0A=20DROP=20TABLE=20= planets=20CASCADE;=0A=20NOTICE:=20=20drop=20cascades=20to=20view=20= planets_view=0Adiff=20--git=20a/src/test/regress/sql/window.sql=20= b/src/test/regress/sql/window.sql=0Aindex=20305549b104d..17261135dc3=20= 100644=0A---=20a/src/test/regress/sql/window.sql=0A+++=20= b/src/test/regress/sql/window.sql=0A@@=20-2157,5=20+2157,33=20@@=20= SELECT=20x,=0A=20FROM=20generate_series(1,5)=20g(x)=0A=20WINDOW=20w=20AS=20= (ORDER=20BY=20x=20ROWS=20BETWEEN=202=20PRECEDING=20AND=202=20FOLLOWING);=0A= =20=0A+--=20volatile=20arguments=20cannot=20use=20the=20IGNORE=20NULLS=20= nullness=20cache=0A+CREATE=20TEMPORARY=20SEQUENCE=20null_treatment_seq;=0A= +CREATE=20FUNCTION=20pg_temp.volatile_null(i=20int)=20RETURNS=20int=0A= +LANGUAGE=20sql=20VOLATILE=20AS=0A+$$=0A+=20=20SELECT=20CASE=20WHEN=20= nextval('null_treatment_seq')=20%=202=20=3D=200=20THEN=20i=20ELSE=20NULL=20= END;=0A+$$;=0A+=0A+SELECT=20x,=0A+=20=20=20=20=20=20=20= first_value(pg_temp.volatile_null(x))=20IGNORE=20NULLS=0A+=20=20=20=20=20= =20=20=20=20OVER=20(ORDER=20BY=20x=20ROWS=20BETWEEN=20UNBOUNDED=20= PRECEDING=20AND=20CURRENT=20ROW)=0A+FROM=20generate_series(1,5)=20g(x);=0A= +SELECT=20last_value=20FROM=20null_treatment_seq;=0A+=0A+ALTER=20= SEQUENCE=20null_treatment_seq=20RESTART=20WITH=201;=0A+SELECT=20x,=0A+=20= =20=20=20=20=20=20lead(pg_temp.volatile_null(x),=201)=20IGNORE=20NULLS=20= OVER=20(ORDER=20BY=20x)=0A+FROM=20generate_series(1,5)=20g(x);=0A+SELECT=20= last_value=20FROM=20null_treatment_seq;=0A+=0A+ALTER=20SEQUENCE=20= null_treatment_seq=20RESTART=20WITH=201;=0A+SELECT=20x,=0A+=20=20=20=20=20= =20=20first_value((SELECT=20CASE=20WHEN=20nextval('null_treatment_seq')=20= %=202=20=3D=200=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20THEN=20x=20ELSE=20NULL=20END))=20= IGNORE=20NULLS=0A+=20=20=20=20=20=20=20=20=20OVER=20(ORDER=20BY=20x=20= ROWS=20BETWEEN=20UNBOUNDED=20PRECEDING=20AND=20CURRENT=20ROW)=0A+FROM=20= generate_series(1,5)=20g(x);=0A+SELECT=20last_value=20FROM=20= null_treatment_seq;=0A+=0A=20--cleanup=0A=20DROP=20TABLE=20planets=20= CASCADE;=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_B5B60395-2733-44F6-BC47-603081EE27DE--