Обсуждение: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

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

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)

"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

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.

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.

David J.

Στις 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.
>
>             regards, tom lane
>
>

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.
>>
>> -- 
>> Achilleas Mantzios
>> DBA, Analyst, IT Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>
>

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::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.

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.

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 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::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.

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.

-- 
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
doany 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