BUG #17199: Calling stored procedure with stable function as argument results in wrong result

The following bug has been logged on the website:

Bug reference: 17199
Logged by: Alexander Nawratil
Email address: notegihu@confused.at
PostgreSQL version: 13.4
Operating system: Linux
Description:

Hello,

we are facing some inconsistencies in one of our unit tests since the last
upgrade of PostgreSQL to 11.13 and 13.4.
When a function that returns just a row count of a table is marked as STABLE
and is called from a stored procedure as argument, the result of the
function is different than when the function is called beforehand and stored
to a local variable.
It's working fine when the function is marked as VOLATILE, or on PostgreSQL
11.12 / 13.3.

Steps to reproduce:

1. Create an empty table
2. Create a function that is marked as STABLE and returns the row count of
the table (RETURN SELECT COUNT(*) FROM table)
3. Create a procedure that takes an integer parameter and simply outputs it,
e.g. RAISE NOTICE
4. In a single DO block: INSERT a row into the table, then CALL
proc(func()); /* --> output: 0, instead of 1 */
5. Storing the result of func() to a variable x, then CALL proc(x); /* -->
output: 1, as expected */

Example SQL:

CREATE TABLE tbl (col INT4);

CREATE OR REPLACE FUNCTION stable_func()
RETURNS INT4
STABLE AS
$code$
BEGIN
RETURN (SELECT COUNT(1) FROM tbl);
END
$code$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION volatile_func()
RETURNS INT4
VOLATILE AS
$code$
BEGIN
RETURN (SELECT COUNT(1) FROM tbl);
END
$code$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE call_proc(t TEXT, cnt INT4)
AS
$code$
BEGIN
RAISE NOTICE '%', (t || ' -- count: ' || cnt::TEXT);
END
$code$ LANGUAGE plpgsql;

DO
$$
DECLARE
x INT4;
BEGIN
RAISE NOTICE '%', (VERSION());
INSERT INTO tbl(col) VALUES (1);

x := volatile_func();
CALL call_proc('from_var_volatile_func', x);
CALL call_proc('inline_volatile_func', volatile_func());

x := stable_func();
CALL call_proc('from_var_stable_func', x);
CALL call_proc('inline_stable_func', stable_func());
END
$$;

I would expect that all calls output the same row count of 1. However, the
actual value differs for the last call since the upgrade.

Output on Postgres 13.3:
PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
from_var_volatile_func -- count: 1
inline_volatile_func -- count: 1
from_var_stable_func -- count: 1
inline_stable_func -- count: 1

Output on Postgres 13.4:
PostgreSQL 13.4 (Debian 13.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
from_var_volatile_func -- count: 1
inline_volatile_func -- count: 1
from_var_stable_func -- count: 1
inline_stable_func -- count: 0

The same change in behavior seems to be introduced in 12.8, not being
present in 12.7. I ran the code above using official Docker Hub images.

Best regards,
Alexander Nawratil