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(at)gmail(dot)com> wrote:
> In short, it felt to me like fallibility was orthogonal to volatility in that its side effect is only triggered by calling it the first time instead of by calling it multiple times. My thought was of a potential second type of function marker to describe fallibility that the optimizer could possibly leverage to hopefully still enable most types of optimizations while preventing scenarios such as this one. I was imagining that an operation like division could be marked something like "immutable fallible", which would indicate that the function is still allowed to be optimized in any way that an immutable function is except for any optimizations that may cause it to operate on additional input that it would otherwise not normally operate on in an unoptimized query, which could then be used to disallow this bug's optimization where 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