pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com>
wrote:

ERROR: cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::
numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl,
machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND cept.value='inf' ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)

The column cept.value contains an infinity. I see nothing unusual in any
of these queries given that fact. If you try to cast the infinity to
numeric it will fail. If that doesn’t happen the query won’t fail.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com>
wrote:

ERROR: cannot convert infinity to numeric

The column cept.value contains an infinity. I see nothing unusual in any
of these queries given that fact. If you try to cast the infinity to
numeric it will fail. If that doesn’t happen the query won’t fail.

FWIW, PG 14 and later do support infinity in the numeric type.

regards, tom lane

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.

-- still has problem testing the range

select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl,
machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND  numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()')
@> cept.value::numeric ORDER BY 1;
ERROR:  cannot convert infinity to numeric

SELECT
it.id,
cept.value::numeric AS val,
numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
AS RANGE
FROM
items it,
cept_report cept,
dynacom.vessels vsl,
machdefs md,
cept_reportlimits ceptl wh ere it.id = cept.id
AND md.defid = ceptl.defid
AND it.defid = md.defid
AND it.vslwhid = vsl.id
AND vsl.vslstatus = 'Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval)
AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
@> cept.value::numeric
ORDER BY
1;

So the above fails. In your title when you say there is no infinity that
means the cept.value, ceptl.min_alarm or ceptl.max_alarm fields do not
have any '-infinity' or 'infinity' values, correct?

-- no problem if the query goes into its barrier and the test done outside
with bar as (select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_a
larm::numeric,'()') as range from items it, cept_report cept ,
dynacom.vessels vsl, machdefs md, cept_reportli
mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND
it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs
lstatus='Active' and md.application = 'Critical Equipment Performance
Test' AND cept.systemdate>= (now()-'1 ye
ar'::interval)  ORDER BY 1)
select * from bar where NOT range @> val;
-- good data here

WITH bar AS (
SELECT
it.id,
cept.value::numeric AS val,
numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric,
'()') AS
RANGE
FROM
items it,
cept_report cept,
dynacom.vessels vsl,
machdefs md,
cept_reportli mits ceptl
WHERE
it.id = cept.id
AND md.defid = ceptl.defid
AND it.defid = md.defid
AND it.vslwhid = vsl.id
AND vsl.vs lstatus = 'Active'
AND md.application = 'Critical Equipment Performance Test'
AND cept.systemdate >= (now() - '1 ye
ar'::interval)
ORDER BY
1
)
SELECT
*
FROM
bar
WHERE
NOT RANGE @> val;

This version succeeds, correct? So breaking the range construction into
one step and the @> test into another works. Though I am not sure why
<range> @> changed to NOT <range> @>?

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks David

Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

On Tuesday, July 19, 2022, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:

ERROR:  cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id <http://it.id&gt; ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels
vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id <http://it.id&gt;=cept.id <http://cept.id&gt; AND
md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id
<http://vsl.id&gt; AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value='inf' ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)

The column cept.value contains an infinity.  I see nothing unusual in
any of these queries given that fact.  If you try to cast the infinity
to numeric it will fail.  If that doesn’t happen the query won’t fail.

Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some
subplan uses this filter (and fails). But when this check is applied to
the result, no infinity is found and works correctly.

Στις 19/7/22 17:23, ο/η Tom Lane έγραψε:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com>
wrote:

ERROR: cannot convert infinity to numeric

The column cept.value contains an infinity. I see nothing unusual in any
of these queries given that fact. If you try to cast the infinity to
numeric it will fail. If that doesn’t happen the query won’t fail.

FWIW, PG 14 and later do support infinity in the numeric type.

Yes I noticed that, thank you Tom, I hope we'll be able to upgrade in
the near future.

Thank you Adrian!

Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.

-- still has problem testing the range

select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels
vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND
 numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric,'()') @>
cept.value::numeric ORDER BY 1;
ERROR:  cannot convert infinity to numeric

SELECT
    it.id,
    cept.value::numeric AS val,
    numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
AS RANGE
FROM
    items it,
    cept_report cept,
    dynacom.vessels vsl,
    machdefs md,
    cept_reportlimits ceptl wh ere it.id = cept.id
    AND md.defid = ceptl.defid
    AND it.defid = md.defid
    AND it.vslwhid = vsl.id
    AND vsl.vslstatus = 'Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval)
AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
@> cept.value::numeric
ORDER BY
    1;

So the above fails. In your title when you say there is no infinity
that means the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields
do not have any '-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without
the filter in the WHERE clause including cept.value::numeric) did not
contain any infinity it should also work with the filter in the WHERE
clause. Apparently a subplan executes this conversion in the WHERE
before the other filters. I did not do any analyze to prove this.

-- no problem if the query goes into its barrier and the test done
outside
with bar as (select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_a
larm::numeric,'()') as range from items it, cept_report cept ,
dynacom.vessels vsl, machdefs md, cept_reportli
mits ceptl where it.id=cept.id AND md.defid=ceptl.defid AND
it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vs
lstatus='Active' and md.application = 'Critical Equipment Performance
Test' AND cept.systemdate>= (now()-'1 ye
ar'::interval)  ORDER BY 1)
select * from bar where NOT range @> val;
-- good data here

WITH bar AS (
    SELECT
        it.id,
        cept.value::numeric AS val,
        numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric,
'()') AS
RANGE
    FROM
        items it,
        cept_report cept,
        dynacom.vessels vsl,
        machdefs md,
        cept_reportli mits ceptl
    WHERE
        it.id = cept.id
        AND md.defid = ceptl.defid
        AND it.defid = md.defid
        AND it.vslwhid = vsl.id
        AND vsl.vs lstatus = 'Active'
        AND md.application = 'Critical Equipment Performance Test'
        AND cept.systemdate >= (now() - '1 ye
ar'::interval)
    ORDER BY
        1
)
SELECT
    *
FROM
    bar
WHERE
    NOT RANGE @> val;

This version succeeds, correct? So breaking the range construction
into one step and the @> test into another works. Though I am not sure
why <range> @> changed to NOT <range> @>?

Yes this succeeds. The correct is with the NOT, it does not change the
behavior of the initial query.

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com>
wrote:

Thanks David
Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

On Tuesday, July 19, 2022, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

ERROR: cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id ,cept.value::numeric as val, numrange(ceptl.min_alarm::nume
ric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl,
machdefs md, cept_reportlimits ceptl wh
ere it.id=cept.id AND md.defid=ceptl.defid AND it.defid=md.defid AND
it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND cept.value='inf' ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)

The column cept.value contains an infinity. I see nothing unusual in any
of these queries given that fact. If you try to cast the infinity to
numeric it will fail. If that doesn’t happen the query won’t fail.

Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some
subplan uses this filter (and fails). But when this check is applied to the
result, no infinity is found and works correctly.

That is what it means for SQL to be a declarative language, the order of
execution/evaluation is determined to be efficient and not what is
explicitly written. You do have some control though, but using it also
means you might make things worse.

I think you have issues anyway if you are doing equality checks on what
seems to be a floating point column, regardless of which way you do the
cast.

David J.

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Thank you Adrian!

Actually thank:

https://sqlformat.darold.net/

Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.

AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()')
@> cept.value::numeric
ORDER BY
    1;

So the above fails. In your title when you say there is no infinity
that means the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields
do not have any '-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without
the filter in the WHERE clause including cept.value::numeric) did not
contain any infinity it should also work with the filter in the WHERE
clause. Apparently a subplan executes this conversion in the WHERE
before the other filters. I did not do any analyze to prove this.

Have you tried:

NULLIF(cept.value, 'inf')::numeric

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/19/22 10:32 AM, Adrian Klaver wrote:

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Have you tried:

NULLIF(cept.value, 'inf')::numeric

That was a miss. I originally tested this on Postgres 14 and of course
it worked. Trying it on Postgres 12 got:

select nullif(1.5, 'inf')::numeric;
ERROR: invalid input syntax for type numeric: "inf"
LINE 1: select nullif(1.5, 'inf')::numeric;

Due to this:

https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-NULLIF

"The two arguments must be of comparable types. To be specific, they are
compared exactly as if you had written value1 = value2, so there must be
a suitable = operator available."

So:

1.5::numeric = 'inf'::numeric

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/19/22 10:42 AM, Adrian Klaver wrote:

On 7/19/22 10:32 AM, Adrian Klaver wrote:

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Have you tried:

NULLIF(cept.value, 'inf')::numeric

That was a miss. I originally tested this on Postgres 14 and of course
it worked. Trying it on Postgres 12 got:

select nullif(1.5, 'inf')::numeric;
ERROR:  invalid input syntax for type numeric: "inf"
LINE 1: select nullif(1.5, 'inf')::numeric;

Due to this:

https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-NULLIF

"The two arguments must be of comparable types. To be specific, they are
compared exactly as if you had written value1 = value2, so there must be
a suitable = operator available."

So:

1.5::numeric = 'inf'::numeric

The cheat would be:

select version();
version

-------------------------------------------------------------------------------------
PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.5.0, 64-bit

select nullif('inf'::float, 'inf')::numeric;
nullif
--------
NULL

select nullif(1.5::float, 'inf')::numeric;
nullif
--------
1.5
(1 row)

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

--
Adrian Klaver
adrian.klaver@aklaver.com

On 19/7/22 20:31, David G. Johnston wrote:

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

Thanks David

Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

On Tuesday, July 19, 2022, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

ERROR:  cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id <http://it.id&gt; ,cept.value::numeric as val, numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl, machdefs md, cept_reportlimits ceptl wh
ere it.id <http://it.id&gt;=cept.id <http://cept.id&gt; AND md.defid=ceptl.defid AND it.defid=md.defid AND it.vslwhid=vsl.id <http://vsl.id&gt; AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND cept.systemdate>= (now()-'1 year'::interval
) AND  cept.value='inf' ORDER BY 1;
id | val | range
----+-----+-------
(0 rows)

The column cept.value contains an infinity.  I see nothing unusual in any of these queries given that fact. If you try to cast the infinity to numeric it will fail. If that doesn’t happen the
query won’t fail.

Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some subplan uses this filter (and fails). But when this check is applied to the result, no infinity is found and works correctly.

That is what it means for SQL to be a declarative language, the order of execution/evaluation is determined to be efficient and not what is explicitly written.  You do have some control though, but
using it also means you might make things worse.

I think you have issues anyway if you are doing equality checks on what seems to be a floating point column, regardless of which way you do the cast.

Thank you, will look into it further when I get the time.

David J.

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

On 19/7/22 20:32, Adrian Klaver wrote:

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Thank you Adrian!

Actually thank:

https://sqlformat.darold.net/

Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.

AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') @> cept.value::numeric
ORDER BY
    1;

So the above fails. In your title when you say there is no infinity that means the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields do not have any '-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without the filter in the WHERE clause including cept.value::numeric) did not contain any infinity it should also work with the filter
in the WHERE clause. Apparently a subplan executes this conversion in the WHERE before the other filters. I did not do any analyze to prove this.

Have you tried:

NULLIF(cept.value, 'inf')::numeric

no, cause the CTE version worked. Will keep in mind for similar future problems.

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt