From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Vik Fearing <vik(at)postgresfriends(dot)org>, Marek Malevič <marek(at)malevicovi(dot)net>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: Broken type checking for empty subqueries |
Date: | 2023-09-29 07:52:23 |
Message-ID: | CAApHDvpUf5Z2RXMyZ5hi=mS_vQKGcepbQREDmAtXi0O+v+9kzA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On Fri, 29 Sept 2023 at 10:53, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yeah. I suppose we could start trying to expand the use of soft
> error reporting, but it'd be a herculean, probably multi-year
> task to get to where even "most cases" would be covered.
I had a go at trying to categorise the reports we've received over the
years about this. My search was just limited to the search term
"constant folding"
It looks like of the 9 below, the input function soft errors would
cover #0 (this report), #1 and #4. Division by 0 covers another 2 (#5
and #8). So we could fix "most cases" if we added soft errors to
arithmetic.
Also, from looking at [1], there has been some interest in the past to
stop these surprising const-folding errors.
0. This thread.
Casting issue. text can't be parsed by type's input function.
effectively:
# explain select '$maybeNumber'::bigint where false;
ERROR: invalid input syntax for type bigint: "$maybeNumber"
1. Bug 17637 https://www.postgresql.org/message-id/17637-5904e3fdee533c7f%40postgresql.org
Casting issue in case statement
with tmp as (select 1::int8 id, '123.4'::text v)
select t.id,
case m.mapped_to when 1 then v::float8 else null end,
case m.mapped_to when 2 then v::bool else null end
from tmp t
join tmap m on m.id = t.id;
ERROR: invalid input syntax for type boolean: "123.4"
immutable function raising an exception
SELECT raise_exception_immutable('foo') WHERE false;
ERROR: foo
3. Bug 16545 https://www.postgresql.org/message-id/16545-affff840bc4e72ca%40postgresql.org
Complaint about coalesce evaluating arguments after the first non-NULL value.
# SELECT coalesce((SELECT 'ONE'),
(SELECT 'TWO'
WHERE '123' ~
((xpath('/tag/text()','<tag>[</tag>'))[1]::TEXT)
)
);
ERROR: invalid regular expression: brackets [] not balanced
ereport in RE_compile_and_cache().
4. https://www.postgresql.org/message-id/C0FDEC5E-0E01-4FAB-A7A6-3FAC1F94B51E%40gmail.com
Appears an error from the t7.value::numeric case below.
-- CASE WHEN t9.is_internal_namespace = true
-- AND t9.code = 'STORAGE_POSITION.STORAGE_RACK_ROW'
-- AND (t10.code = 'INTEGER' OR t10.code = 'REAL')
-- THEN t7.value::numeric = 1
-- ELSE false
-- END
not much further details.
division by zero.
test=> CREATE FUNCTION zero() RETURNS integer IMMUTABLE LANGUAGE SQL
AS 'SELECT 0';
CREATE FUNCTION
test=> SELECT CASE WHEN (SELECT zero()) = 0 THEN -1 ELSE 1/zero() END;
ERROR: division by zero
Some procedural language error.
A very old one. Unsure if it relates to casting or a bug that was fixed.
8. https://www.postgresql.org/message-id/20020414165222.914FB475451%40postgresql.org
A very old one from 2002
SELECT
CASE
WHEN 1 = 2 THEN 1 / 0
WHEN 1 = 1 THEN 1.0
END;
ERROR: floating point exception! The last floating point operation
either exceeded legal ranges or was a divide by zero
Stopped as these reports are getting very old and less valuable.
David
[1] https://www.postgresql.org/message-id/265964.1595523454%40sss.pgh.pa.us
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2023-09-29 14:31:55 | BUG #18141: sorry, too many clients error occurring very frequently |
Previous Message | Michael Paquier | 2023-09-29 06:50:42 | Re: pg_rewind: ERROR: could not fetch remote file "global/pg_control": ERROR: permission denied |