Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery

On Wed, 20 Mar 2024 at 16:10, Sawyer Knoblich <scknoblich@gmail.com> wrote:
> In short, it felt to me like fallibility was orthogonal to volatility in that its side effect is only triggered by
callingit the first time instead of by calling it multiple times. My thought was of a potential second type of function
markerto describe fallibility that the optimizer could possibly leverage to hopefully still enable most types of
optimizationswhile preventing scenarios such as this one. I was imagining that an operation like division could be
markedsomething like "immutable fallible", which would indicate that the function is still allowed to be optimized in
anyway that an immutable function is except for any optimizations that may cause it to operate on additional input that
itwould otherwise not normally operate on in an unoptimized query, which could then be used to disallow this bug's
optimizationwhere the values given to the cast are first filtered by the where clause. 

Thanks for thinking about it, but I don't think this would work as you
only have to have > 1 condition that could cause ERRORs for the
optimiser not to know the "correct" order of evaluation.   Aside from
that, just because the evaluation of the expression could fail, it
does not mean that it *will* fail with the given data.  For example,
the addition of two BIGINT values *could* fail due to overflow, but
it's unlikely that it will fail with most numbers that fit into that
type. So restricting the order of evaluation for conditions which
could fail is likely to upset more people than it will please.

There are quite a lot of things which would have to be restricted,
much more than you might think.  Any conditions which could cause an
error would have to be evaluated last in a WHERE clause and that might
result in being unable to use indexes because some other (possibly
unindexed) expression would need to be evaluated first.

As for documentation, I wonder if it's worth a paragraph in [1] to
mention SQL is a declarative language and mention a few caveats that
could come with that which might catch out people who are used to
procedural languages.

David

[1] https://www.postgresql.org/docs/current/queries-overview.html