SQLite Forum

Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed)
Login
The most reasonably correct answer I can compute is 1.05978960889239021277933445

This is obtained using the Quadmath library to calculate the sin function and accumulate the sum, then print the result (rounded to fit a 80-bit extended IEEE-floating value) to 26 decimal places.

Performing the computation entirely in double precision (MSVC) gives the following result:

```
sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 30000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(1.05978960889239021277933445,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(1.05978960889239021277933445,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 1.0597896088923786095392642   │ 53.0                                           │
└───────────────────────────────┴────────────────────────────────────────────────┘
```

and with MinGW64 GCC where the sin is a double giving double and the sum accumulator is __float128 "rounded even" into a double:

```
sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 30000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(1.05978960889239021277933445,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(1.05978960889239021277933445,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 1.05978960889239104403714     │ -4.0                                           │
└───────────────────────────────┴────────────────────────────────────────────────┘
```

So a lot of precision is lost when doing the sum with insufficient precision.

You have to be careful claiming something is more "precise" than something else, particularly when the computation method may be flawed.

Note also that in the github link the actual value is:  0.03873134997726595606822833

But the MSVC (all double) result is:

```
sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 3000000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(0.03873134997726595606822833,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(0.03873134997726595606822833,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 0.03873134997718130634325461  │ 12199.0                                        │
└───────────────────────────────┴────────────────────────────────────────────────┘
```

which has a huge error due to the lack of a high precision accumulator, but in this case the high precision accumulator is spot on

```
sqlite> WITH cnt(x)
   ...>   AS (
   ...>          SELECT 1
   ...>       UNION ALL
   ...>          SELECT x + 1
   ...>            FROM cnt
   ...>           WHERE x < 3000000
   ...>      )
   ...> select printf('%!.26f', sum(sin(x))), ulps(0.03873134997726595606822833,sum(sin(x)))
   ...>   from cnt;
┌───────────────────────────────┬────────────────────────────────────────────────┐
│ printf('%!.26f', sum(sin(x))) │ ulps(0.03873134997726595606822833,sum(sin(x))) │
├───────────────────────────────┼────────────────────────────────────────────────┤
│ 0.03873134997726595390998838  │ 0.0                                            │
└───────────────────────────────┴────────────────────────────────────────────────┘
```

What is the significance you might ask?  Well, it depends on what the result of the calculation is used for.  If it is for the gap in a spark plug, a variance in the 14th decimal digit is not of significance.  However, if it is calculating the trajectory of an Interconntiental Ballistic Missle, it could be the difference between the destruction of New York -vs- Beijing.

NB:  I have modified the context in func.c for the SUM and TOTAL functions to use a LONGDOUBLE_TYPE for the floating point accumulator.  For GCC I have redefined LONGDOUBLE_TYPE to be __float128 rather than long double.  MSVC completely ignores all requests for extended precision and treats a "long double" as a mere "double" on x64 platforms.  Results will vary depending on compiler and platform.