On Fri, 29 Sept 2023 at 00:28, Vik Fearing <vik@postgresfriends.org> wrote:
> I bisected this back to 4be058fe9ec5e630239b656af21fc083371f30ed, "In
> the planner, replace an empty FROM clause with a dummy RTE."
This just seems to be due to the fact that that commit allowed
FROM-less subqueries to be pulled up and that now allows constant
folding to attempt to perform the cast to BIGINT. An OFFSET 0 in the
subquery would prevent the subquery being pulled up.
After the pullup, and constant folding the WHERE clause, the query becomes:
select '$maybeNumber'::bigint where false;
When we constant-fold the target list, we'll get the error as it'll
try and convert the string to a bigint.
Without that ability, we'd have to do more run-time evaluation in
queries such as:
postgres=# explain verbose select '10' + 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4)
Output: 11
but as you can see, the planner knows the value is 11.
There have been reports about these things before. For example [1].
I've not looked at the code to see if this would be practical or not,
but I wonder if we could reduce these bug reports by using the new
soft error reporting that's now done in the input functions to have
constant folding just silently not do any folding for the expression
if a cast fails. I don't particularly see anything wrong with making
these queries work if they currently fail. I imagine most people who
have had them fail during constant folding have just redesigned or
found some hack to prevent the folding from taking place anyway.
Overall, it wouldn't be great if we had to ensure we keep these sorts
of queries working as they did in previous versions. That would just
prevent us from adding certain optimisations that might be very
useful.
David
[1] https://www.postgresql.org/message-id/17637-5904e3fdee533c7f@postgresql.org