From: Vik Fearing <vik@postgresfriends.org>
To: Marek Malevič <marek@malevicovi.net>
To: pgsql-bugs@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Broken type checking for empty subqueries
Date: Thu, 28 Sep 2023 13:28:35 +0200
Message-ID: <42808eee-9a68-cfbd-924c-db895d13f9c0@postgresfriends.org> (raw)
In-Reply-To: <7e55db68-7169-4e43-363e-947c79ba7922@malevicovi.net>
References: <7e55db68-7169-4e43-363e-947c79ba7922@malevicovi.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: vik@postgresfriends.org, marek@malevicovi.net, pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us
Subject: Re: Broken type checking for empty subqueries
In-Reply-To: <42808eee-9a68-cfbd-924c-db895d13f9c0@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox