On 17 Feb 2022, at 8:02, Carsten Klein wrote:
> We have a trigger, that monitors such updates and it reports a changed value, which is not correct. The problem is,
thatthe assignment
>
> SET xyz = 1.56
>
> actually means
>
> SET xyz = 1.559999999
>
> since getting a double value from the string 1.56 yields 1.559999999.
<snip>
> I just wanted to ask whether someone knows something about this or has ever heard about such a behavior. You say, the
snowflakemachine has issues... I don't believe in hardware issues, since it runs in VMware and likely on many different
CPUs.Isn't it more than unlikely that such a constantly occurring error is caused by one faulty CPU (among that many
CPUsan ESX server typically has)?
>
Harking back to my long distant formative years I was taught to never ever rely on equality tests when it came to
floatingpoint values. Nothing has changed in this regard.
If binary exact is part of the requirement then the answer is INTEGER or NUMERIC never FLOAT, REAL, DOUBLE or anything
similar.
So, assuming the trigger function is the only source of this spurious grief one could apply the lesson taught in the
60sfrom the dawn of computing:
EQUALITY = absolute_value(op1 - op2) < epsilon — where op1 & op2 are DOUBLE, and epsilon is smaller than you care
for
Given the intrinsic (standards compliant) uncertainty when converting from absolute (e.g., string representation) to
floatingpoint there will never be value resolving why there are differences.
I suggest using the comparison that is appropriate to the representation of those values or fix the design by using the
properrepresentation.
Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem —
neat,plausible, and wrong.
— H. L. Mencken, 1920