public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mats Rydberg <[email protected]>
To: [email protected]
Subject: GREATEST/LEAST ignores comparison operator volatility in contain_volatile_functions_walker
Date: Tue, 2 Jun 2026 10:48:32 +0200
Message-ID: <CABu=s9Uz29GRAu-xzHO142jFsWa4xpySUNRT6pxcyQpi3vjvkw@mail.gmail.com> (raw)

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.

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: GREATEST/LEAST ignores comparison operator volatility in contain_volatile_functions_walker
  In-Reply-To: <CABu=s9Uz29GRAu-xzHO142jFsWa4xpySUNRT6pxcyQpi3vjvkw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox