Re: Broken type checking for empty subqueries
От | David Rowley |
---|---|
Тема | Re: Broken type checking for empty subqueries |
Дата | |
Msg-id | CAApHDvpUf5Z2RXMyZ5hi=mS_vQKGcepbQREDmAtXi0O+v+9kzA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Broken type checking for empty subqueries (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
On Fri, 29 Sept 2023 at 10:53, Tom Lane <tgl@sss.pgh.pa.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" 2. https://www.postgresql.org/message-id/CAAT35tGXUYgjjViNZ5%2B9nFkrOcmgE4ce%2BVvekjgKDy_C38RT2g%40mail.gmail.com 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. 5. https://www.postgresql.org/message-id/A737B7A37273E048B164557ADEF4A58B17BB4EF8%40ntex2010a.host.magwien.gv.at 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 6. https://www.postgresql.org/message-id/flat/11494.1144794560%40sss.pgh.pa.us#68696f2d794cd2d64f9c596782ee8f3a Some procedural language error. 7. https://www.postgresql.org/message-id/flat/815.1049942992%40sss.pgh.pa.us#0874c4dffad1d389fe3f812f18039583 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: pg_rewind: ERROR: could not fetch remote file "global/pg_control": ERROR: permission denied
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #18141: sorry, too many clients error occurring very frequently