Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity - Mailing list pgsql-general

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