Re: Broken type checking for empty subqueries

On Fri, 29 Sept 2023 at 01:41, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> 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.

I got curious if this would be difficult or not and came up with the
attached attempt-to-do-it-to-see-if-it-works grade patch.

On a very quick test, it does seem to work.

Patched:
postgres=# explain verbose select '123a'::varchar::bigint where false;
-- previously failed
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: ('123a'::cstring)::bigint
One-Time Filter: false
(3 rows)

postgres=# explain verbose select '123'::varchar::bigint where false;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '123'::bigint
One-Time Filter: false
(3 rows)

Master:
postgres=# explain verbose select '123a'::varchar::bigint where false;
ERROR: invalid input syntax for type bigint: "123a"
postgres=# explain verbose select '123'::varchar::bigint where false;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '123'::bigint
One-Time Filter: false
(3 rows)

I didn't really follow the soft error development work done in
d9f7f5d32 very closely. I'm assuming any extension that has not
updated its type input function(s) to use errsave/ereturn instead of
elog/ereport will just continue to fail and there's nothing really we
can do about that. Maybe that's not really a problem as constant
folding would raise errors today for invalid inputs for the given type
anyway. It would at least work nicer for all our built-in types that
did have their input functions modified to suppress invalid inputs
when there's an ErrorSaveContext.

Should we consider doing something like this?

David