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.94.2) (envelope-from ) id 1uy4Go-003c9X-O2 for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Sep 2025 08:09:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uy4Gm-00Ac2L-Ie for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Sep 2025 08:09:05 +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.94.2) (envelope-from ) id 1uy4Gm-00Ac2D-7w for pgsql-hackers@lists.postgresql.org; Mon, 15 Sep 2025 08:09:05 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uy4Gi-000ucq-2V for pgsql-hackers@lists.postgresql.org; Mon, 15 Sep 2025 08:09:04 +0000 Received: by mail-pf1-x42c.google.com with SMTP id d2e1a72fcca58-76e4f2e4c40so3292821b3a.2 for ; Mon, 15 Sep 2025 01:09:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757923740; x=1758528540; darn=lists.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=VKhSblgKNHRm0u49PCfF/4rljzLLp9ekXJUYRI4UU58=; b=jUe6n1RMnXMmmkIrvdsIWJM264Eazk+ze710j3TSmuRdzIAz2DASdWf2wZj6e5qQuo 0j2EIWrnAifiG2Qirz1BOM4jZWXPqvkogJBj8uRuw1LoU3vuMKxWM7kFU6HatA8CbwRm mgaaGf0MrvoTnpWieZoOoQ2qe6uZgpMORF5lo0Jl2chhaTcAZEVyb2rRadCdg2Qyh3TC zc/g3wTyoVWUlB2CRsGJTaCnc0Fx5JdlPWbA3LPDmOa8gmoxnbyRKdOGJ8Nbyyw9akSe U2ZSVdQL+UvqSjtZr5mWviXwptpuT2dEnFaq5Y8Uf0BpH6aNKTbdvAx+RzqQhR/sEmRB 7pRw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757923740; x=1758528540; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=VKhSblgKNHRm0u49PCfF/4rljzLLp9ekXJUYRI4UU58=; b=ppF43KJefvJawLwZD36jGdFmJX4a3zzPyYaoewEfbEnzFaOXJyFhlRkZStIrEx9KWF MBKsiI4XyUXSFHRgaGFSs7x5vEei27JsvG2KXX3xNTP3Z9+8WecGJKFRFgvbB9Pw3hpZ dUhvIT8hZUnMK5bHdnWjwNewkx80lJLEV5vjb4fN77eMZnQJX9jkA1iIvkYZfqi7T/7J ej47d0cdVJvvrn6/9/mvq8KOeq+NNf/hsnVV0kNFvAkEG1rqGCmGbGaRPiNwwMVOBUXE 8noPFG/0KC+p70aAurGBOhVQP5E9maUf0S9k6Td8m2jxYz3pBQyyEbbGebB8+LjvP7RY OfWg== X-Forwarded-Encrypted: i=1; AJvYcCU6mTF9C4H0uDqawRZC1ziNPTVTy4TkPotn/U+zlOe+PBLkh75G+09ZK+PWWlz0+j96tDF1pEDR1zr/pyR+@lists.postgresql.org X-Gm-Message-State: AOJu0YxDQ3+Z2zsrkAiMVxUv5g7bXyDtTMSCD0drQBCXw7UsqTEA/hm1 JOlAQwWlKq/GbEacy+uYAH2DRhEt1zkqzGY7Oj3f+IailNPllqp2cUij X-Gm-Gg: ASbGncv5RVIkXJYpEtLfN79SdIs0yaithYYME2IQbKhz7uy7bypaP4QgkaT6hD66OJ/ HP1NEf+9AOPhlLHltbkNOJ2mqOAmml725k5EBrneE+Iq5CnDQ5XZKLMa0lFXsjnBGyEIzwISGb4 AEBbC+jT5BPUXwwGt5G6ttjIx8eQ99su7TNmNzkOfSinNB8J3rSJ7m0LVPMK4NZ02ktW7IYxpuU +8SJcEZrOaDXoL398IkqlVme5O/KoUbF41CTxh/ATdPk4vMpBMyLSIjtoWEOEAp162TNuBFHi4L c7jpqpE4FMyxH8vOhCQDwGvGvEJX+eg6xF33w0TNDWmXdKjWdtc8alCMe8VBEWQIgvm7JdXhLTk o730fiPrR1klKaMM/r2nfFzp1KiJQixTakOH2+Bnz2QEj9d+6s+DgRw== X-Google-Smtp-Source: AGHT+IGKuncTHpcCDw+QTEJsVWoHE2Wm8Vbi+VlleDGvQrB6SmeGR6GbS+NV+Ec8pgOUHvxhOR7DAA== X-Received: by 2002:a05:6a20:258b:b0:250:c76d:1ccf with SMTP id adf61e73a8af0-26029fa0ca4mr14596883637.1.1757923740202; Mon, 15 Sep 2025 01:09:00 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id 41be03b00d2f7-b54a398d26asm11139174a12.45.2025.09.15.01.08.57 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 15 Sep 2025 01:08:59 -0700 (PDT) From: Chao Li Message-Id: <76F5962C-01AA-4D81-B9C2-70590746EC5C@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_EDAAC502-C57A-4C38-9AB6-9BA87BB66524" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Date: Mon, 15 Sep 2025 16:08:43 +0800 In-Reply-To: <20250912.185316.492168971773927670.ishii@postgresql.org> Cc: ojford@gmail.com, krasiyan@gmail.com, tgl@sss.pgh.pa.us, vik@postgresfriends.org, andrew@tao11.riddles.org.uk, david@fetter.org, pgsql-hackers@lists.postgresql.org To: Tatsuo Ishii References: <20250725.164914.458111718434162385.ishii@postgresql.org> <20250816.183329.1067490243501362986.ishii@postgresql.org> <20250912.185316.492168971773927670.ishii@postgresql.org> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_EDAAC502-C57A-4C38-9AB6-9BA87BB66524 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Overall LGTM. Just a few small comments: > On Sep 12, 2025, at 17:53, Tatsuo Ishii wrote: >=20 >=20 > Comments/suggestions are welcome. > -- > Tatsuo Ishii > SRA OSS K.K. > English: http://www.sraoss.co.jp/index_en/ > Japanese:http://www.sraoss.co.jp >=20 1 - 0001 ``` --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, = List *fargs, bool agg_star =3D (fn ? fn->agg_star : false); bool agg_distinct =3D (fn ? fn->agg_distinct : = false); bool func_variadic =3D (fn ? fn->func_variadic : = false); + int ignore_nulls =3D (fn ? fn->ignore_nulls = : 0); ``` Should we use the constant NO_NULLTREATMENT here for 0? 2 - 0001 ``` --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -579,6 +579,17 @@ typedef struct GroupingFunc * Collation information is irrelevant for the query jumbling, as is = the * internal state information of the node like "winstar" and "winagg". */ + +/* + * Null Treatment options. If specified, initially set to = PARSER_IGNORE_NULLS + * which is then converted to IGNORE_NULLS if the window function = allows the + * null treatment clause. + */ +#define NO_NULLTREATMENT 0 +#define PARSER_IGNORE_NULLS 1 +#define PARSER_RESPECT_NULLS 2 +#define IGNORE_NULLS 3 + typedef struct WindowFunc { Expr xpr; @@ -602,6 +613,8 @@ typedef struct WindowFunc bool winstar pg_node_attr(query_jumble_ignore); /* is function a simple aggregate? */ bool winagg pg_node_attr(query_jumble_ignore); + /* ignore nulls. One of the Null Treatment options */ + int ignore_nulls; ``` Maybe we can use =E2=80=9Cuint8=E2=80=9D type for =E2=80=9Cignore_nulls=E2= =80=9D. Because the previous two are both of type =E2=80=9Cbool=E2=80=9D, = an uint8 will just fit to the padding bytes, so that new field won=E2=80=99= t add extra memory to the structure. 3 - 0004 ``` winobj->markpos =3D -1; winobj->seekpos =3D -1; + + /* reset null map */ + if (perfuncstate->winobj->ignore_nulls =3D=3D = IGNORE_NULLS) + = memset(perfuncstate->winobj->notnull_info, 0, + = NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info)); } ``` Where in =E2=80=9Cif=E2=80=9D and =E2=80=9Cmemset()=E2=80=9D, we can = just use =E2=80=9Cwinobj=E2=80=9D. 4 - 0004 ``` + if (!HeapTupleIsValid(proctup)) + elog(ERROR, "cache lookup failed for function = %u", funcid); + procform =3D (Form_pg_proc) GETSTRUCT(proctup); + elog(ERROR, "function %s does not allow RESPECT/IGNORE = NULLS", + NameStr(procform->proname)); ``` =E2=80=9CProcform=E2=80=9D is assigned but not used. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_EDAAC502-C57A-4C38-9AB6-9BA87BB66524 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Overall LGTM. = Just a few small comments:

On Sep 12, 2025, at 17:53, Tatsuo Ishii = <ishii@postgresql.org> = wrote:


Comments/suggestions are = welcome.
--
Tatsuo Ishii
SRA OSS K.K.
English: = http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
<= br>

1 - = 0001
```
--- = a/src/backend/parser/parse_func.c
+++ = b/src/backend/parser/parse_func.c
@@ -98,6 +98,7 @@ = ParseFuncOrColumn(ParseState *pstate, List *funcname, List = *fargs,
  bool agg_star =3D (fn ? = fn->agg_star : false);
  bool agg_distinct =3D (fn ? = fn->agg_distinct : false);
  bool = func_variadic =3D (fn ? fn->func_variadic : = false);
+ int ignore_nulls =3D = (fn ? fn->ignore_nulls : = 0);
```

Should we use the = constant NO_NULLTREATMENT here for 0?

2 - = 0001
```
--- = a/src/include/nodes/primnodes.h
+++ = b/src/include/nodes/primnodes.h
@@ -579,6 +579,17 @@ typedef = struct GroupingFunc
  * Collation information is = irrelevant for the query jumbling, as is the
  * internal = state information of the node like "winstar" and = "winagg".
  */
+
+/*
+ * = Null Treatment options. If specified, initially set to = PARSER_IGNORE_NULLS
+ * which is then converted to = IGNORE_NULLS if the window function allows the
+ * null = treatment clause.
+ */
+#define NO_NULLTREATMENT = 0
+#define PARSER_IGNORE_NULLS 1
+#define = PARSER_RESPECT_NULLS 2
+#define IGNORE_NULLS = 3
+
 typedef struct = WindowFunc
 {
  Expr = xpr;
@@ -602,6 +613,8 @@ typedef struct = WindowFunc
  bool winstar = pg_node_attr(query_jumble_ignore);
  /* is = function a simple aggregate? */
  bool = winagg pg_node_attr(query_jumble_ignore);
+ /* ignore = nulls. One of the Null Treatment options */
+ int = ignore_nulls;
```

Maybe = we can use =E2=80=9Cuint8=E2=80=9D type for =E2=80=9Cignore_nulls=E2=80=9D= . Because the previous two are both of type =E2=80=9Cbool=E2=80=9D, an = uint8 will just fit to the padding bytes, so that new field won=E2=80=99t = add extra memory to the structure.

3 - = 0004
```
  = winobj->markpos =3D -1;
  = winobj->seekpos =3D -1;
+
+ = /* reset null map */
+ if = (perfuncstate->winobj->ignore_nulls =3D=3D = IGNORE_NULLS)
+ = memset(perfuncstate->winobj->notnull_info, = 0,
+ =   = NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
=   = }
```
Where in =E2=80=9Cif=E2=80=9D and = =E2=80=9Cmemset()=E2=80=9D, we can just use = =E2=80=9Cwinobj=E2=80=9D.

4 - = 0004
```
+ if = (!HeapTupleIsValid(proctup))
+ elog(ERROR, = "cache lookup failed for function %u", funcid);
+ = procform =3D (Form_pg_proc) GETSTRUCT(proctup);
+ = elog(ERROR, "function %s does not allow RESPECT/IGNORE = NULLS",
+ = NameStr(procform->proname));
```

=E2=80=9CProcform=E2=80=9D is assigned but not = used.

Best regards,
--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_EDAAC502-C57A-4C38-9AB6-9BA87BB66524--