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]https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE. 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]https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0.
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/
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
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
https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0
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.
David J.
Show quoted text
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?
On Thursday, March 12, 2026, Damian Lukowski <pgsql-bugs@arcsin.de> wrote:
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?
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.
David J.
"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