From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sawyer Knoblich <scknoblich@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18399: Query plan optimization results in runtime error when
 hoisting cast from inside subquery
In-reply-to: 
 <CAOfdcWBfDVsYqO=MJxS_D1uvN_woDjX6zD5+jyN=a2hC+pq4fg@mail.gmail.com>
References: <18399-17778030a1971177@postgresql.org>
 <3877106.1710863246@sss.pgh.pa.us>
 <CAOfdcWBfDVsYqO=MJxS_D1uvN_woDjX6zD5+jyN=a2hC+pq4fg@mail.gmail.com>
Comments: In-reply-to Sawyer Knoblich <scknoblich@gmail.com>
	message dated "Tue, 19 Mar 2024 16:11:58 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4085749.1710895021.1@sss.pgh.pa.us>
Date: Tue, 19 Mar 2024 20:37:01 -0400
Message-ID: <4085750.1710895021@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4085750.1710895021%40sss.pgh.pa.us>
Precedence: bulk

Sawyer Knoblich <scknoblich@gmail.com> writes:
> To make sure I'm understanding correctly, are you suggesting that this is
> not a bug? The mention of "the cast to integer is presumed to be
> side-effect-free, which isn't true if you're working with data where it
> could throw errors" to me heavily implies that this is a faulty assumption
> on behalf of the optimizer about what behaviors certain parts of the query
> may have, but I didn't get the impression that this is being considered an
> optimizer bug and is instead something that just needs to be worked around
> or avoided (although if I'm not interpreting it right then definitely
> please correct me).

It is not an optimizer bug: the optimizer is doing what the function
marking entitles it to.  You could argue that it's a marking bug and
no function/operator that can throw an error should be marked stable
or immutable, because throwing an error is a kind of side-effect.
But that would be a pretty useless answer, as it would nearly destroy
our ability to optimize queries at all, since there's not all that
many functions that can be guaranteed not to throw errors.

In short, this state of affairs is an engineering compromise.
It's not perfect, but it's not easy to see how to do better either.

			regards, tom lane