public inbox for [email protected]
help / color / mirror / Atom feedRe: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18
5+ messages / 3 participants
[nested] [flat]
* 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 <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Damian Lukowski @ 2026-03-12 09:34 UTC (permalink / raw)
To: [email protected]
>> 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] 5+ 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 <[email protected]>
parent: Damian Lukowski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2026-03-12 12:40 UTC (permalink / raw)
To: Damian Lukowski <[email protected]>; +Cc: [email protected] <[email protected]>
On Thursday, March 12, 2026, Damian Lukowski <[email protected]> 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] 5+ 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 <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Damian Lukowski @ 2026-03-12 12:49 UTC (permalink / raw)
To: [email protected] <[email protected]>
> On Thursday, March 12, 2026, Damian Lukowski <[email protected]> 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] 5+ 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 <[email protected]>
parent: Damian Lukowski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2026-03-12 13:29 UTC (permalink / raw)
To: Damian Lukowski <[email protected]>; +Cc: [email protected] <[email protected]>
On Thursday, March 12, 2026, Damian Lukowski <[email protected]> wrote:
> On Thursday, March 12, 2026, Damian Lukowski <[email protected]> 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] 5+ 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 <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2026-03-12 15:46 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Damian Lukowski <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Thursday, March 12, 2026, Damian Lukowski <[email protected]> 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] 5+ messages in thread
end of thread, other threads:[~2026-03-12 15:46 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-12 09:34 Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL functions including subqueries no longer works in 18 Damian Lukowski <[email protected]>
2026-03-12 12:40 ` David G. Johnston <[email protected]>
2026-03-12 12:49 ` Damian Lukowski <[email protected]>
2026-03-12 13:29 ` David G. Johnston <[email protected]>
2026-03-12 15:46 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox