Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery - Mailing list pgsql-bugs
| From | Sawyer Knoblich |
|---|---|
| Subject | Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery |
| Date | |
| Msg-id | CAOfdcWBfDVsYqO=MJxS_D1uvN_woDjX6zD5+jyN=a2hC+pq4fg@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-bugs |
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@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.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
pgsql-bugs by date: