Content-Type: multipart/alternative;
 boundary="------------xDOoqzgnSxnzZ9WYV0H8m4Gz"
Message-ID: <fac48759-8d95-41c2-a52f-19c6cb0c837a@arcsin.de>
Date: Thu, 12 Mar 2026 10:34:04 +0100
MIME-Version: 1.0
Subject: Re: BUG #19428: Lazy evaluation of type checking in CASE in SQL
 functions including subqueries no longer works in 18
To: pgsql-bugs@lists.postgresql.org
References: <19428-d9ac6c4d84c0bc81@postgresql.org>
 <3708.1773251497@sss.pgh.pa.us>
Content-Language: en-US
From: Damian Lukowski <pgsql-bugs@arcsin.de>
Autocrypt: addr=damian.lukowski@credativ.de; keydata=
 xjMEZ7c2uhYJKwYBBAHaRw8BAQdAceF3EJt2itpxKWUTR6jMvERlO9hfxBqDZ8iTixrhykvN
 LURhbWlhbiBMdWtvd3NraSA8ZGFtaWFuLmx1a293c2tpQGNyZWRhdGl2LmRlPsKQBBMWCAA4
 AhsDBQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAFiEEn2yM4tn2T3ZoWMszfF3Rm/We5T4FAme3
 NxQACgkQfF3Rm/We5T4yBQEAx1pLzviFfdx54XAP5RcEw0cQd3bSWYZbTFPDR1OPxBMA/icc
 vL1ZKvpmMpsSp8zwtRjRtVBd4g+t/rcGzagVVE0OzjgEZ7c2uhIKKwYBBAGXVQEFAQEHQKe8
 LrdoRxv2hpm8b6OqN3iWnI4b4lymHJNc2Q+g6p9JAwEIB8J4BBgWCAAgAhsMFiEEn2yM4tn2
 T3ZoWMszfF3Rm/We5T4FAme3NuoACgkQfF3Rm/We5T4SlgEA9jbDVIs9Zhw4O2d9+FIF6Unm
 nlZRw1p+8UADGtpM5u4BAPEe8Y5UBIdqkf+4Ujx18w4LCcmMV8RfPyAGIPbTG04D
In-Reply-To: <3708.1773251497@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/fac48759-8d95-41c2-a52f-19c6cb0c837a%40arcsin.de>
Precedence: bulk

This is a multi-part message in MIME format.
--------------xDOoqzgnSxnzZ9WYV0H8m4Gz
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

>> 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

--------------xDOoqzgnSxnzZ9WYV0H8m4Gz
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <blockquote type="cite" cite="mid:3708.1773251497@sss.pgh.pa.us">
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">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?
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
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.</pre>
    </blockquote>
    <p>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].</p>
    <p>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?</p>
    <p>[1]
<a class="moz-txt-link-freetext" href="https://www.cybertec-postgresql.com/en/how-the-postgresql-query-optimizer-works/">https://www.cybertec-postgresql.com/en/how-the-postgresql-query-optimizer-works/</a><br>
      [2]
<a class="moz-txt-link-freetext" href="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</a><br>
      [3]
<a class="moz-txt-link-freetext" href="https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0">https://medium.com/@ukhore/deep-dive-postgresql-query-execution-mechanisms-777a79d8caf0</a></p>
  </body>
</html>

--------------xDOoqzgnSxnzZ9WYV0H8m4Gz--