Re: IGNORE/RESPECT NULLS can be specified for (prokind == 'f'). - Mailing list pgsql-hackers

> While reviewing the row pattern recognition,
> I noticed that the IGNORE NULLS and RESPECT NULLS clauses can be specified for
> normal functions (prokind == 'f').
> 
> create or replace function dummy2(int) returns bigint AS $$ BEGIN
> RETURN $1; END$$ STABLE LANGUAGE plpgsql;
> select dummy2(1) IGNORE NULLS;
> select dummy2(1) RESPECT NULLS;
> 
> This seems unexpected?

Yes. Other than true window functions are not allowed to use the null
treatment clause per spec.

> Also https://git.postgresql.org/cgit/postgresql.git/commit/?id=25a30bbd4235a49c854036c84fe90f2bc5a87652
> Obviously, RESPECT NULLS and IGNORE NULLS can appear in SELECT query,
> since they are dedicated keywords, shouldn't they be mentioned in
> select.sgml?

No, we should fix the code. See attached patch.

To fix this, ParseFuncOrColumn() now checks whether other than window
functions are used with the null treatment clause. If so, error out.

Also remove the unnecessary test for "aggregate functions do not
accept RESPECT/IGNORE NULLS" because it is now checked in the
early-stage new check. The window regression test expected file is
changed accordingly.

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
From 668f79937d19084ff93ea7b49810db443d2de148 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Tue, 16 Jun 2026 20:51:59 +0900
Subject: [PATCH v1] Fix to not allow null treatment to non window functions.

The null treatment clause (RESPECT NULLS/IGNORE NULLS) are only
allowed to window functions per spec. Previously the check was only
applied to aggregates in window clause. Other type of functions were
allowed to use the clause, which was plain wrong.

To fix this, ParseFuncOrColumn() now checks whether other than window
functions are used with the null treatment clause. If so, error out.

Also remove the unnecessary test for "aggregate functions do not
accept RESPECT/IGNORE NULLS" because it is now checked in the
early-stage new check. The window regression test expected file is
changed accordingly.

Discussion: https://postgr.es/m/CACJufxFnm%2BAj2Jyhyd58PtW8e1vTZDKimkZE%2BMashCPSDKw56Q%40mail.gmail.com
---
 src/backend/parser/parse_func.c      | 16 +++++++++-------
 src/test/regress/expected/window.out |  4 ++--
 2 files changed, 11 insertions(+), 9 deletions(-)

diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 2e4cc1de50d..6717ddc6881 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -353,6 +353,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
                      parser_errposition(pstate, location)));
     }
 
+    /*
+     * NULL TREATEMENT is only allowed for window functions per spec.
+     */
+    if (fdresult != FUNCDETAIL_WINDOWFUNC && ignore_nulls != NO_NULLTREATMENT)
+        ereport(ERROR,
+                errcode(ERRCODE_WRONG_OBJECT_TYPE),
+                errmsg("Only window functions accept RESPECT/IGNORE NULLS"),
+                parser_errposition(pstate, location));
+
     /*
      * So far so good, so do some fdresult-type-specific processing.
      */
@@ -519,13 +528,6 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
                          errmsg("%s is not an ordered-set aggregate, so it cannot have WITHIN GROUP",
                                 NameListToString(funcname)),
                          parser_errposition(pstate, location)));
-
-            /* It also can't treat nulls as a window function */
-            if (ignore_nulls != NO_NULLTREATMENT)
-                ereport(ERROR,
-                        (errcode(ERRCODE_WRONG_OBJECT_TYPE),
-                         errmsg("aggregate functions do not accept RESPECT/IGNORE NULLS"),
-                         parser_errposition(pstate, location)));
         }
     }
     else if (fdresult == FUNCDETAIL_WINDOWFUNC)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index de0e14a686e..891b0153004 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5764,11 +5764,11 @@ SELECT sum(orbit) OVER () FROM planets; -- succeeds
 (10 rows)
 
 SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
-ERROR:  aggregate functions do not accept RESPECT/IGNORE NULLS
+ERROR:  Only window functions accept RESPECT/IGNORE NULLS
 LINE 1: SELECT sum(orbit) RESPECT NULLS OVER () FROM planets;
                ^
 SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
-ERROR:  aggregate functions do not accept RESPECT/IGNORE NULLS
+ERROR:  Only window functions accept RESPECT/IGNORE NULLS
 LINE 1: SELECT sum(orbit) IGNORE NULLS OVER () FROM planets;
                ^
 SELECT row_number() OVER () FROM planets; -- succeeds
-- 
2.43.0