Re: Non-deterministic behavior with floating point in parallel mode

On Fri, Feb 3, 2017 at 3:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ruben Buchatskiy <ruben(at)ispras(dot)ru> writes:
>> We have found that in parallel mode result of queries is non-deterministic
>> when the types of the attributes in table are double precision
>> (floating-point).
>
> Yeah ...
>
>> That is because floating-point addition is not necessarily associative.
>
> Right, exactly.
>
>> Is this desirable behavior?
>
> It's not especially the fault of parallelism. Any change in the order in
> which the SUM visits the rows could cause a similar change in the results.
> IOW, you are being overoptimistic about how deterministic this result
> is any of the time.

For example, I just did the following while also running the same
query in another session to provoke synchronize_seqscans (in a
REPEATABLE READ transaction for added absurdity):

tpch=# set max_parallel_workers_per_gather to 0;
SET

tpch=# select sum(l_extendedprice::double precision) from lineitem;
sum
------------------
229577310901.211
(1 row)

tpch=# select sum(l_extendedprice::double precision) from lineitem;
sum
------------------
229577310901.198
(1 row)

--
Thomas Munro
http://www.enterprisedb.com