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, that the 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 snowflake machine 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 CPUs an 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 floating point 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 60s from 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 floating point 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 proper representation. 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
