From: | Vik Fearing <vik(at)postgresfriends(dot)org> |
---|---|
To: | Marek Malevič <marek(at)malevicovi(dot)net>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Broken type checking for empty subqueries |
Date: | 2023-09-28 11:28:35 |
Message-ID: | 42808eee-9a68-cfbd-924c-db895d13f9c0@postgresfriends.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2023-09-28 12:41:11 | Re: Broken type checking for empty subqueries |
Previous Message | David Rowley | 2023-09-28 10:33:14 | Re: Missed TidRangePath in the print_path switch |