pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
8+ messages / 4 participants
[nested] [flat]
* BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-11 16:50 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Bug reporting form @ 2026-03-11 16:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: michael.banck@credativ.de
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-11 17:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-03-11 17:51 UTC (permalink / raw)
To: michael.banck@credativ.de; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 09:34 Damian Lukowski <pgsql-bugs@arcsin.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Damian Lukowski @ 2026-03-12 09:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
>> 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-3...
[3]
https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 12:40 David G. Johnston <david.g.johnston@gmail.com>
parent: Damian Lukowski <pgsql-bugs@arcsin.de>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2026-03-12 12:40 UTC (permalink / raw)
To: Damian Lukowski <pgsql-bugs@arcsin.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 12:49 Damian Lukowski <pgsql-bugs@arcsin.de>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Damian Lukowski @ 2026-03-12 12:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
> 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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 13:29 David G. Johnston <david.g.johnston@gmail.com>
parent: Damian Lukowski <pgsql-bugs@arcsin.de>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2026-03-12 13:29 UTC (permalink / raw)
To: Damian Lukowski <pgsql-bugs@arcsin.de>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2026-03-12 15:46 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Damian Lukowski <pgsql-bugs@arcsin.de>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
@ 2026-03-12 16:03 Damian Lukowski <pgsql-bugs@arcsin.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Damian Lukowski @ 2026-03-12 16:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
> 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';
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-12 16:03 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-11 16:50 BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18 PG Bug reporting form <noreply@postgresql.org>
2026-03-11 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-12 09:34 ` Damian Lukowski <pgsql-bugs@arcsin.de>
2026-03-12 12:40 ` David G. Johnston <david.g.johnston@gmail.com>
2026-03-12 12:49 ` Damian Lukowski <pgsql-bugs@arcsin.de>
2026-03-12 13:29 ` David G. Johnston <david.g.johnston@gmail.com>
2026-03-12 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-12 16:03 ` Damian Lukowski <pgsql-bugs@arcsin.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox