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