Обсуждение: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18

The following bug has been logged on the website:

Bug reference:      19428
Logged by:          Michael Banck
Email address:      michael.banck@credativ.de
PostgreSQL version: 18.3
Operating system:   n/a
Description:

A colleague has complained to me that the following (minimal reproducer he
could come up with) worked on pre-18, but no longer does on 18:

SELECT 'hello world' AS arg INTO data;
SELECT NULL AS selector INTO selectors;
CREATE OR REPLACE FUNCTION func(name anyelement)
 RETURNS anyelement
 LANGUAGE sql
 STABLE STRICT
AS $function$
SELECT CASE
    WHEN selector IS NULL THEN $1::text
    ELSE $1::inet::text
END
FROM (SELECT selector FROM selectors)
$function$;
SELECT func(arg) FROM data;

On 17 or earlier, you get:

           func
---------------------------
 selector:foo(hello world)
(1 row)

           func
---------------------------
 selector:bar(hello world)
(1 row)

    func
-------------
 hello world
(1 row)

on 18 you get:

ERROR:  invalid input syntax for type inet: "hello world"
CONTEXT:  SQL function "func" statement 1
STATEMENT:  SELECT func(arg) FROM data;

I bisected this to 0dca5d68d7b. Looks like they depend on lazy evaluation in
CASE via the subquery to check if the second argument is a valid inet, which
no longer works after 0dca5d68d7b. Is that an intended change?

PG Bug reporting form <noreply@postgresql.org> writes:
> A colleague has complained to me that the following (minimal reproducer he
> could come up with) worked on pre-18, but no longer does on 18:
> ...
> SELECT CASE
>     WHEN selector IS NULL THEN $1::text
>     ELSE $1::inet::text
> END
> ...
> I bisected this to 0dca5d68d7b. Looks like they depend on lazy evaluation in
> CASE via the subquery to check if the second argument is a valid inet, which
> no longer works after 0dca5d68d7b. Is that an intended change?

We have never promised to avoid constant-folding within the
subexpressions of a CASE [1].  So it was pure accident that
this example worked before, and I don't think it's a bug that
it doesn't work now.

You could possibly make this logic work reliably by wrapping
the cast-to-inet part in a volatile plpgsql function.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE

A colleague has complained to me that the following (minimal reproducer he
could come up with) worked on pre-18, but no longer does on 18:
...
SELECT CASE    WHEN selector IS NULL THEN $1::text    ELSE $1::inet::text
END
...
I bisected this to 0dca5d68d7b. Looks like they depend on lazy evaluation in
CASE via the subquery to check if the second argument is a valid inet, which
no longer works after 0dca5d68d7b. Is that an intended change?

We have never promised to avoid constant-folding within the
subexpressions of a CASE [1].  So it was pure accident that
this example worked before, and I don't think it's a bug that
it doesn't work now.

For a better understanding, which one is the constant that is being folded? I have found several articles explaining constant folding but their examples are obvious, e.g. `7 + 1` can be folded to `8` [1, 2], or `1 = 1` can be folded to `TRUE` [3].

However, I have not found any articles that resemble this case. Aren't `arg` and `$1` variables? Where is the boundary between constants and non-constants?

[1] https://www.cybertec-postgresql.com/en/how-the-postgresql-query-optimizer-works/
[2] https://www.oreilly.com/library/view/mastering-postgresql-12/9781838988821/b44dc21f-cefa-464a-82e1-391b84fc9f40.xhtml
[3] https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0

We have never promised to avoid constant-folding within the
subexpressions of a CASE [1].  So it was pure accident that
this example worked before, and I don't think it's a bug that
it doesn't work now.

For a better understanding, which one is the constant that is being folded? I have found several articles explaining constant folding but their examples are obvious, e.g. `7 + 1` can be folded to `8` [1, 2], or `1 = 1` can be folded to `TRUE` [3].

However, I have not found any articles that resemble this case. Aren't `arg` and `$1` variables? Where is the boundary between constants and non-constants?

On Thursday, March 12, 2026, Damian Lukowski <pgsql-bugs@arcsin.de> wrote:

We have never promised to avoid constant-folding within the
subexpressions of a CASE [1].  So it was pure accident that
this example worked before, and I don't think it's a bug that
it doesn't work now.

For a better understanding, which one is the constant that is being folded? I have found several articles explaining constant folding but their examples are obvious, e.g. `7 + 1` can be folded to `8` [1, 2], or `1 = 1` can be folded to `TRUE` [3].

However, I have not found any articles that resemble this case. Aren't `arg` and `$1` variables? Where is the boundary between constants and non-constants?

The system is capable of postponing planning until (or performing replanning) after parameter values are known, in which the values they are given are constants.

On Thursday, March 12, 2026, Damian Lukowski <pgsql-bugs@arcsin.de> wrote:

We have never promised to avoid constant-folding within the
subexpressions of a CASE [1].  So it was pure accident that
this example worked before, and I don't think it's a bug that
it doesn't work now.

For a better understanding, which one is the constant that is being folded? I have found several articles explaining constant folding but their examples are obvious, e.g. `7 + 1` can be folded to `8` [1, 2], or `1 = 1` can be folded to `TRUE` [3].

However, I have not found any articles that resemble this case. Aren't `arg` and `$1` variables? Where is the boundary between constants and non-constants?

The system is capable of postponing planning until (or performing replanning) after parameter values are known, in which the values they are given are constants.

The `data` table could have millions of rows. Is the planner invoked for every actual call to `func`, or only once before any pages are read?

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, March 12, 2026, Damian Lukowski <pgsql-bugs@arcsin.de> wrote:
>> The `data` table could have millions of rows. Is the planner invoked for
>> every actual call to `func`, or only once before any pages are read?

> IIRC the first five invocations will get replanned using the parameter as a
> constant; then after either every call will be replanned or none will.

Read up on custom vs. generic plans for some more background on this.

That behavior used to apply only to plpgsql functions, but since v18
SQL-language functions do it too.

            regards, tom lane

Read up on custom vs. generic plans for some more background on this.

Thanks for the hint, the query works again with

set plan_cache_mode to 'force_generic_plan';