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 1wNNU8-000lNK-1i for pgsql-hackers@arkaria.postgresql.org; Thu, 14 May 2026 04:15:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNNU5-00Ands-2x for pgsql-hackers@arkaria.postgresql.org; Thu, 14 May 2026 04:15:42 +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 1wNNU5-00Andj-1b for pgsql-hackers@lists.postgresql.org; Thu, 14 May 2026 04:15:41 +0000 Received: from mail-pf1-x434.google.com ([2607:f8b0:4864:20::434]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wNNU1-00000000VRi-0sEK for pgsql-hackers@postgresql.org; Thu, 14 May 2026 04:15:41 +0000 Received: by mail-pf1-x434.google.com with SMTP id d2e1a72fcca58-8353c9f24d2so4202689b3a.3 for ; Wed, 13 May 2026 21:15:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1778732134; x=1779336934; darn=postgresql.org; h=to:cc:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=45VhTuhB4B2gWe1gy9BupJYWtPq0qtMQI9PUiLJkkfA=; b=fSJTleqEDCnJ61nAesbAV/cSPJjn2ElZ0Z3OCOnYpaZEuqC6xMH1McjleBKeCC0nhr DMD977CknwcUlkFCno/V+uR9CFSHl3oUu84bg8OssjJV8CaBH2gbJuZsUYFL3duAe5VL tKerrpxj0Vse7m4q6zdi4Cad+BzELsMhdGg2j1BJOEaHhRieHCygw/ypmKmmOaU2hZnK oTp3mW9K9g8QarCsz15ar0gt9/1s5hy7SxQClfIzbdEJ74X0xc7g781xR7/evJA+WgNg 89eclqRcVtcVawdYKcknyHQSCFPsaPAsoWP8TfsCsYt4XcGqExigwF2RvJiSky7ZnuaL nSaA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778732134; x=1779336934; h=to:cc:date:message-id:subject:mime-version:from:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=45VhTuhB4B2gWe1gy9BupJYWtPq0qtMQI9PUiLJkkfA=; b=oVQaAikbpj1Yh+Ck7Clnvspowy0lKIubKL5fNaHbx1HFvWeo9jiOi1LKTcS4yA9g7x AoOgTWhWRAmKxiDP9uxku37rSnHxO7szgAAciKIeasR/AReBEiMayOWQdFxZebqROYPQ guOkdDyYtv7qQYTBwQC13xdqzL/oRRO2F1BiIgtQdniKoTDy9NqqYiI+n7O9xjWgwO0B Rys0Blk1U73ZYLLSp7y/fxfqC7dZ9MpbL9nuVOtk/5TxOCkESfw33II59/aEt05Bq3Nt DdpOCjWtRwCmrKvGpMjLr6zTEjuzMa2VVo+h9RjxsEZ8tZ2vi5uHdqoxvCuuA13HE2nk FViQ== X-Gm-Message-State: AOJu0YzcnR1UT5ZiPwhipxDYQj+uv//E8RTMZuUnBEWc11T8cQiJEb+p PSb8dDUZrt5mZKHcpOQD2hCHNxIl1+9u4lurYc7Pug2zFElYBwLyONMAFnyenCipFqg= X-Gm-Gg: Acq92OFHw0fkIMiAtQTdpFt0OpZcdHl5rgo//FyP3h7LBfAdMxX5iZ04PGj1YIvCXzS +FrQDh17FKWtMWp0UwoE7UVv/1HSdYFotUkYL8MmFuNiKOVX8Li5EnJcrLqsgSQ+dtNXuRy7vzz +JXf3L43DJGbkKWPI2O6z1h0eg7h2iI8SP/kQhExFcjG9icGfCDcWaAAFxyRrxzGVFL2l4YuS9M T/RovVP2sBybDKYvTemkh5UtaHmF+fIOkdA9UN96ZLUnmTxM7TJvp4RATb42rUoOyLu0ycKvCwU jxJ/q2hmnbDVeeietYT8vnKbF875U5JYZvz5rU/7roYVkR+L910AvDujMv7PJOzaJWyxHUIF3Ux 0oM7oWr80taMoikU/G5qKjKqZSZPwxW6QWj0Ji8Rj3PdFJ1sVuvAiFfr3BG6kv4+nlApKUR0CGa x+GcXSJcyz7d9tCAfPrtmvIuhI2XDaKgs= X-Received: by 2002:a05:6a00:808d:b0:82f:48e:241c with SMTP id d2e1a72fcca58-83f04288a2fmr6021329b3a.23.1778732133762; Wed, 13 May 2026 21:15:33 -0700 (PDT) Received: from smtpclient.apple ([45.32.121.103]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-83f19c5ce1fsm1110718b3a.35.2026.05.13.21.15.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 13 May 2026 21:15:33 -0700 (PDT) From: Chao Li Content-Type: multipart/mixed; boundary="Apple-Mail=_84DC9722-FE2B-4AA2-AE38-05E9B4E84935" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\)) Subject: Should IGNORE NULLS cache nullness for volatile arguments? Message-Id: <42B42506-6972-4266-8422-FB73E61D9DA7@gmail.com> Date: Thu, 14 May 2026 12:14:54 +0800 Cc: Tatsuo Ishii , ojford@gmail.com To: PostgreSQL-development 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=_84DC9722-FE2B-4AA2-AE38-05E9B4E84935 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi, I tested the new IGNORE NULLS support for window functions and noticed = one behavior that looks strange to me. To avoid repeated evaluation, the current code caches whether an = argument value is NULL or NOT NULL. That is fine for stable expressions, = but it looks unsafe for volatile arguments. For example, an argument may = be evaluated as NOT NULL when its nullness is first checked, but when = the value is needed later and the argument is evaluated again, the = result may become NULL. That can lead to surprising results for volatile = functions. I do not have full confidence to call this a bug yet, but I think it is = at least worth discussing. If the value of a NOT NULL argument were also = cached, then I guess this behavior might be acceptable. But with the = current implementation, the argument can be re-evaluated later and = produce the opposite nullness result, which seems wrong to me. 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. See the attached patch for details. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_84DC9722-FE2B-4AA2-AE38-05E9B4E84935 Content-Disposition: attachment; filename=v1-0001-Fix-IGNORE-NULLS-nullness-cache-for-volatile-wind.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v1-0001-Fix-IGNORE-NULLS-nullness-cache-for-volatile-wind.patch" Content-Transfer-Encoding: quoted-printable =46rom=20502082a0fbbc597ed6e8048edbb7ab58e7f4e614=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= v1]=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=0A---=0A=20= src/backend/executor/nodeWindowAgg.c=20|=2060=20= ++++++++++++++++++-------=0A=20src/test/regress/expected/window.out=20|=20= 66=20++++++++++++++++++++++++++++=0A=20src/test/regress/sql/window.sql=20= =20=20=20=20=20|=2028=20++++++++++++=0A=203=20files=20changed,=20139=20= insertions(+),=2015=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/executor/nodeWindowAgg.c=20= b/src/backend/executor/nodeWindowAgg.c=0Aindex=20= 5cc39bd9086..42cc3a1f3c3=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-3454,7=20+3455,10=20@@=20= ignorenulls_getfuncarginframe(WindowObject=20winobj,=20int=20argno,=0A=20= =09=09if=20(isout)=0A=20=09=09=09*isout=20=3D=20false;=0A=20=0A-=09=09v=20= =3D=20get_notnull_info(winobj,=20abs_pos,=20argno);=0A+=09=09if=20= (winobj->notnull_info_cacheable[argno])=0A+=09=09=09v=20=3D=20= get_notnull_info(winobj,=20abs_pos,=20argno);=0A+=09=09else=0A+=09=09=09= v=20=3D=20NN_UNKNOWN;=0A=20=09=09if=20(v=20=3D=3D=20NN_NULL)=09=09/*=20= this=20row=20is=20known=20to=20be=20NULL=20*/=0A=20=09=09=09goto=20= advance;=0A=20=0A@@=20-3471,8=20+3475,9=20@@=20= ignorenulls_getfuncarginframe(WindowObject=20winobj,=20int=20argno,=0A=20= =09=09=09if=20(!*isnull)=0A=20=09=09=09=09notnull_offset++;=0A=20=0A-=09=09= =09/*=20record=20the=20row=20status=20*/=0A-=09=09=09= put_notnull_info(winobj,=20abs_pos,=20argno,=20*isnull);=0A+=09=09=09/*=20= record=20the=20row=20status=20if=20it=20is=20safe=20to=20reuse=20*/=0A+=09= =09=09if=20(winobj->notnull_info_cacheable[argno])=0A+=09=09=09=09= put_notnull_info(winobj,=20abs_pos,=20argno,=20*isnull);=0A=20=09=09}=0A=20= =09=09else=09=09=09=09=09/*=20this=20row=20is=20known=20to=20be=20NOT=20= NULL=20*/=0A=20=09=09{=0A@@=20-3518,8=20+3523,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-3812,6=20+3832,7=20@@=20= WinGetFuncArgInPartition(WindowObject=20winobj,=20int=20argno,=0A=20=09= int=09=09=09notnull_relpos;=0A=20=09int=09=09=09forward;=0A=20=09bool=09=09= myisout;=0A+=09bool=09=09got_datum;=0A=20=0A=20=09= Assert(WindowObjectIsValid(winobj));=0A=20=09winstate=20=3D=20= winobj->winstate;=0A@@=20-3860,6=20+3881,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-3895,8=20= +3917,11=20@@=20WinGetFuncArgInPartition(WindowObject=20winobj,=20int=20= argno,=0A=20=09=09if=20(abs_pos=20<=200)=09=09/*=20clearly=20out=20of=20= partition=20*/=0A=20=09=09=09break;=0A=20=0A-=09=09/*=20check=20NOT=20= NULL=20cached=20info=20*/=0A-=09=09nn_info=20=3D=20= get_notnull_info(winobj,=20abs_pos,=20argno);=0A+=09=09/*=20check=20NOT=20= NULL=20cached=20info=20if=20it=20is=20safe=20to=20reuse=20*/=0A+=09=09if=20= (winobj->notnull_info_cacheable[argno])=0A+=09=09=09nn_info=20=3D=20= get_notnull_info(winobj,=20abs_pos,=20argno);=0A+=09=09else=0A+=09=09=09= nn_info=20=3D=20NN_UNKNOWN;=0A=20=09=09if=20(nn_info=20=3D=3D=20= NN_NOTNULL)=09/*=20this=20row=20is=20known=20to=20be=20NOT=20NULL=20*/=0A= =20=09=09=09notnull_offset++;=0A=20=09=09else=20if=20(nn_info=20=3D=3D=20= NN_NULL)=09/*=20this=20row=20is=20known=20to=20be=20NULL=20*/=0A@@=20= -3905,25=20+3930,30=20@@=20WinGetFuncArgInPartition(WindowObject=20= winobj,=20int=20argno,=0A=20=09=09{=0A=20=09=09=09/*=0A=20=09=09=09=20*=20= NOT=20NULL=20info=20does=20not=20exist=20yet.=20=20Get=20tuple=20and=20= evaluate=20func=0A-=09=09=09=20*=20arg=20in=20partition.=20We=20ignore=20= the=20return=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= /*=20record=20the=20row=20status=20*/=0A-=09=09=09= put_notnull_info(winobj,=20abs_pos,=20argno,=20*isnull);=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+=09=09=09/*=20record=20the=20= row=20status=20if=20it=20is=20safe=20to=20reuse=20*/=0A+=09=09=09if=20= (winobj->notnull_info_cacheable[argno])=0A+=09=09=09=09= put_notnull_info(winobj,=20abs_pos,=20argno,=20*isnull);=0A=20=09=09}=0A=20= =09}=20while=20(notnull_offset=20<=20notnull_relpos);=0A=20=0A=20=09/*=20= get=20tuple=20and=20evaluate=20func=20arg=20in=20partition=20*/=0A-=09= datum=20=3D=20gettuple_eval_partition(winobj,=20argno,=0A-=09=09=09=09=09= =09=09=09=09abs_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= &&=20set_mark)=0A=20=09=09WinSetMarkPosition(winobj,=20mark_pos);=0A=20=09= if=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=_84DC9722-FE2B-4AA2-AE38-05E9B4E84935--