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

Thank you Tom, I appreciate the quick response and the documentation
links/suggested solutions. For context this data model definitely isn't by
choice, it's part of a system that handles third-party data that comes in
with potentially less-than-ideal data models/validation.

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). The linked documentation also seems to imply that using
"materialized" is for generating better query plans and not for ensuring
correctness, and this behavior also manifests when using an inner query
directly.

I did look at https://www.postgresql.org/docs/16/xfunc-volatility.html but
I didn't see any mention of fallibility in there with regards to
optimization, only about side effects such as data mutation or
non-deterministic results. Presumably this cast is being optimized like an
immutable function (similar to division, and I've also reproduced this
behavior with division by zero when the non-zero check is inside an inner
query), but that seems to only look at whether or not a function is
deterministic with respect to its inputs. I would have expected that this
particular optimization additionally needs the requirement that the
operation is guaranteed to succeed on all potential inputs in order for it
to be valid.

Best,
Sawyer Knoblich

On Tue, Mar 19, 2024 at 8:47 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > My expectation was that the query should behave as if the CTE (or
> subquery
> > if I had used that instead) has fully executed before moving on to the
> main
> > query and this behavior breaks that assumption (which may or may not be
> > correct). I attempted to search the docs for anything related to this,
> > either CTE/subquery execution order guarantees or hoisting of casts, but
> I
> > couldn't find anything about it. If this turns out not to a bug and is
> just
> > expected behavior with existing documentation could you please link me to
> > the relevant page for future reference?
>
> You need to materialize the CTE to be certain that it won't be
> intermingled with the calling query:
>
> =# with raw_data(id, value) as (values (1, '1900'), (2, '2100'), (3,
> 'abc')),
> integers as materialized (select value::integer
> from raw_data
> where id in (select id from raw_data where value ~
> '^\d+$'))
> select count(*)
> from integers
> where value < 2000;
> count
> -------
> 1
> (1 row)
>
> See
> https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH
> (last few paras of that sub-section).
>
> The real bottom line here is that 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. My advice is to rethink your
> data model: this sort of EAV approach with inconsistent data
> types in the "same" column just does not play nice with SQL
> optimizers, so it'll cause you ongoing heartburn. If you think
> you're too far down the road for that, consider replacing the
> cast with some non-error-throwing user-defined function,
> about like
>
> case when x ~ '^\d+$' then x::integer else null::integer end
>
> regards, tom lane
>