Re: Broken type checking for empty subqueries

On 9/28/23 11:08, Marek Malevič wrote:
> Hello,
> 
> after upgrade from PG11 to PG14.8 the following statement started failing:
> 
> select
>      a::BIGINT
> FROM (
>      SELECT
>          '$maybeNumber'::VARCHAR AS a
>      WHERE NOT '$maybeNumber' LIKE '%maybeNumber'
> ) AS foo;
> 
> Expected (as in PG11): The query should return empty result with one 
> column.
> Actual:  SQL Error [22P02]: ERROR: invalid input syntax for type bigint: 
> "$maybeNumber".
> 
> Where is a workaround that shows the behavior is quite non-stable. 
> Following passes normally while still using the same subquery with one 
> more empty result from another one:
> 
> select
>      a::BIGINT
> FROM (
>      SELECT
>          '$maybeNumber'::VARCHAR AS a
>      WHERE NOT '$maybeNumber' LIKE '%maybeNumber'
>      UNION
>      SELECT '0' AS a WHERE false
> ) AS foo;

I bisected this back to 4be058fe9ec5e630239b656af21fc083371f30ed, "In 
the planner, replace an empty FROM clause with a dummy RTE."
-- 
Vik Fearing