Re: Broken type checking for empty subqueries

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"

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