MIME-Version: 1.0
From: Mats Rydberg <mats@planetscale.com>
Date: Tue, 2 Jun 2026 10:48:32 +0200
Message-ID: 
 <CABu=s9Uz29GRAu-xzHO142jFsWa4xpySUNRT6pxcyQpi3vjvkw@mail.gmail.com>
Subject: GREATEST/LEAST ignores comparison operator volatility in
 contain_volatile_functions_walker
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dd33110653416089"
Archived-At: 
 <https://www.postgresql.org/message-id/CABu%3Ds9Uz29GRAu-xzHO142jFsWa4xpySUNRT6pxcyQpi3vjvkw%40mail.gmail.com>
Precedence: bulk

--000000000000dd33110653416089
Content-Type: text/plain; charset="UTF-8"

PostgreSQL version: 18.4 (also reproduced on 18.3)
Platform: aarch64-apple-darwin, Apple clang 17

GREATEST and LEAST (MinMaxExpr nodes) are missing from
contain_volatile_functions_walker in src/backend/optimizer/util/clauses.c.
OpExpr nodes are correctly handled -- the walker resolves the oprcode via
set_opfuncid and checks func_volatile. But there is no MinMaxExpr case, so
GREATEST/LEAST always appears function-free to the volatility checker
regardless of the < or > operator's provolatile.

The practical consequence: a GREATEST/LEAST expression whose comparison
operator is STABLE or VOLATILE is incorrectly treated as IMMUTABLE. The
reproducer below demonstrates this via a generated column, which requires
an IMMUTABLE expression. PostgreSQL accepts the column definition when it
should reject it.

-- Reproducer (self-contained, tested on 18.4):

DROP TABLE IF EXISTS t CASCADE;
DROP TYPE IF EXISTS myval CASCADE;

CREATE TYPE myval;

CREATE FUNCTION myval_in(cstring) RETURNS myval
    LANGUAGE internal STRICT IMMUTABLE AS 'textin';
CREATE FUNCTION myval_out(myval) RETURNS cstring
    LANGUAGE internal STRICT IMMUTABLE AS 'textout';

CREATE TYPE myval (INPUT = myval_in, OUTPUT = myval_out, LIKE = text);

-- < is STABLE: result depends on the myval.reverse session GUC
CREATE FUNCTION myval_lt(a myval, b myval) RETURNS boolean
    LANGUAGE sql STABLE STRICT AS $$
        SELECT CASE current_setting('myval.reverse', true)
               WHEN 'on' THEN a::text > b::text
               ELSE            a::text < b::text
               END
    $$;

CREATE OPERATOR < (
    leftarg = myval, rightarg = myval,
    procedure = myval_lt, negator = >=
);

-- btree opclass so GREATEST can resolve a sort operator for myval
CREATE FUNCTION myval_cmp(a myval, b myval) RETURNS integer
    LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT CASE WHEN a::text < b::text THEN -1
                    WHEN a::text > b::text THEN  1
                    ELSE 0 END
    $$;

CREATE OPERATOR CLASS myval_ops DEFAULT FOR TYPE myval USING btree AS
    OPERATOR 1 <,
    FUNCTION 1 myval_cmp(myval, myval);

CREATE TABLE t (x myval, y myval);

-- Control: a direct call to the Stable function is correctly rejected.
-- ALTER TABLE t ADD COLUMN ctrl boolean
--     GENERATED ALWAYS AS (myval_lt(x, y)) STORED;
-- => ERROR: generation expression is not immutable  (correct)

-- Bug: GREATEST uses the same Stable < operator but the missing
-- MinMaxExpr case in contain_volatile_functions_walker means
-- PostgreSQL treats the expression as immutable.
ALTER TABLE t ADD COLUMN z myval
    GENERATED ALWAYS AS (GREATEST(x, y)) STORED;
-- Expected: ERROR: generation expression is not immutable
-- Actual:   ALTER TABLE  (bug: accepted without error)

The fix would be to add a MinMaxExpr case to
contain_volatile_functions_walker that resolves the comparison operator's
oprcode (analogous to the existing OpExpr handling) and checks its
provolatile.

--000000000000dd33110653416089
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">PostgreSQL version: 18.4 (also reproduced on 18.3)<br>Plat=
form: aarch64-apple-darwin, Apple clang 17<br><br>GREATEST and LEAST (MinMa=
xExpr nodes) are missing from contain_volatile_functions_walker in src/back=
end/optimizer/util/clauses.c. OpExpr nodes are correctly handled -- the wal=
ker resolves the oprcode via set_opfuncid and checks func_volatile. But the=
re is no MinMaxExpr case, so GREATEST/LEAST always appears function-free to=
 the volatility checker regardless of the &lt; or &gt; operator&#39;s provo=
latile.<br><br>The practical consequence: a GREATEST/LEAST expression whose=
 comparison operator is STABLE or VOLATILE is incorrectly treated as IMMUTA=
BLE. The reproducer below demonstrates this via a generated column, which r=
equires an IMMUTABLE expression. PostgreSQL accepts the column definition w=
hen it should reject it.<div><br></div><div>-- Reproducer (self-contained, =
tested on 18.4):</div><div><br>DROP TABLE IF EXISTS t CASCADE;<br>DROP TYPE=
 IF EXISTS myval CASCADE;<br><br>CREATE TYPE myval;<br><br>CREATE FUNCTION =
myval_in(cstring) RETURNS myval<br>=C2=A0 =C2=A0 LANGUAGE internal STRICT I=
MMUTABLE AS &#39;textin&#39;;<br>CREATE FUNCTION myval_out(myval) RETURNS c=
string<br>=C2=A0 =C2=A0 LANGUAGE internal STRICT IMMUTABLE AS &#39;textout&=
#39;;<br><br>CREATE TYPE myval (INPUT =3D myval_in, OUTPUT =3D myval_out, L=
IKE =3D text);<br><br>-- &lt; is STABLE: result depends on the myval.revers=
e session GUC<br>CREATE FUNCTION myval_lt(a myval, b myval) RETURNS boolean=
<br>=C2=A0 =C2=A0 LANGUAGE sql STABLE STRICT AS $$<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 SELECT CASE current_setting(&#39;myval.reverse&#39;, true)<br>=C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHEN &#39;on&#39; THEN a::=
text &gt; b::text<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0ELSE =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0a::text &lt; b::text<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0END<br>=C2=A0 =C2=A0=
 $$;<br><br>CREATE OPERATOR &lt; (<br>=C2=A0 =C2=A0 leftarg =3D myval, righ=
targ =3D myval,<br>=C2=A0 =C2=A0 procedure =3D myval_lt, negator =3D &gt;=
=3D<br>);<br><br>-- btree opclass so GREATEST can resolve a sort operator f=
or myval<br>CREATE FUNCTION myval_cmp(a myval, b myval) RETURNS integer<br>=
=C2=A0 =C2=A0 LANGUAGE sql IMMUTABLE STRICT AS $$<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 SELECT CASE WHEN a::text &lt; b::text THEN -1<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN a::text &gt; b::t=
ext THEN =C2=A01<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 ELSE 0 END<br>=C2=A0 =C2=A0 $$;<br><br>CREATE OPERATOR CLASS=
 myval_ops DEFAULT FOR TYPE myval USING btree AS<br>=C2=A0 =C2=A0 OPERATOR =
1 &lt;,<br>=C2=A0 =C2=A0 FUNCTION 1 myval_cmp(myval, myval);<br><br>CREATE =
TABLE t (x myval, y myval);<br><br>-- Control: a direct call to the Stable =
function is correctly rejected.<br>-- ALTER TABLE t ADD COLUMN ctrl boolean=
<br>-- =C2=A0 =C2=A0 GENERATED ALWAYS AS (myval_lt(x, y)) STORED;<br>-- =3D=
&gt; ERROR: generation expression is not immutable =C2=A0(correct)<br><br>-=
- Bug: GREATEST uses the same Stable &lt; operator but the missing<br>-- Mi=
nMaxExpr case in contain_volatile_functions_walker means<br>-- PostgreSQL t=
reats the expression as immutable.<br>ALTER TABLE t ADD COLUMN z myval<br>=
=C2=A0 =C2=A0 GENERATED ALWAYS AS (GREATEST(x, y)) STORED;<br>-- Expected: =
ERROR: generation expression is not immutable<br>-- Actual: =C2=A0 ALTER TA=
BLE =C2=A0(bug: accepted without error)<br><br>The fix would be to add a Mi=
nMaxExpr case to contain_volatile_functions_walker that resolves the compar=
ison operator&#39;s oprcode (analogous to the existing OpExpr handling) and=
 checks its provolatile.<br></div></div>

--000000000000dd33110653416089--