Message-ID: <42808eee-9a68-cfbd-924c-db895d13f9c0@postgresfriends.org>
Date: Thu, 28 Sep 2023 13:28:35 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.15.1
Subject: Re: Broken type checking for empty subqueries
Content-Language: en-US
To: =?UTF-8?Q?Marek_Malevi=c4=8d?= <marek@malevicovi.net>,
 pgsql-bugs@lists.postgresql.org
References: <7e55db68-7169-4e43-363e-947c79ba7922@malevicovi.net>
From: Vik Fearing <vik@postgresfriends.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
In-Reply-To: <7e55db68-7169-4e43-363e-947c79ba7922@malevicovi.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/42808eee-9a68-cfbd-924c-db895d13f9c0%40postgresfriends.org>
Precedence: bulk

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