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

On Tuesday, July 19, 2022, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
wrote:

> Thanks David
> Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:
>
> On Tuesday, July 19, 2022, Achilleas Mantzios <
> achill(at)matrix(dot)gatewaynet(dot)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.