SQLite Forum

ORDER BY not working for a specific DB/table
Login

ORDER BY not working for a specific DB/table

(1) By alesXala on 2020-05-21 03:10:08 [link]

A database containing just one table with following schema

    CREATE TABLE latiSaltxi (westCCod, lati10, longi);

The following select should result in records sorted first by lati10 and then longi

      SELECT *
      FROM latiSaltxi 
      WHERE lati10 >= 41 AND lati10 <= 41.3 
      ORDER BY lati10, longi

in general it does but not for all values, for example when arriving at lat10 41.2 at some point is not sorted (e.g. -73 < than 129)

      ...
      392|41.1|140.83551
      392|41.1|141.396450589127
      380|41.2|9.37497676257462
      380|41.2|16.6342872848924
      792|41.2|43.2255548289229
      860|41.2|66.7126410609169
      408|41.2|129.727571723508
      840|41.2|-73.970001
      840|41.2|-73.964172
      840|41.2|-73.964172

all values are numeric so it does not help adding +0 to all columns in the query (tried)

But if the filter is set to start with this particular value 
 
       WHERE lati10 >= 41.2 ..

then the result is sorted!

     840|41.2|-73.970001
     840|41.2|-73.964172
     840|41.2|-73.964172
     840|41.2|-73.955566
     840|41.2|-73.955566
     840|41.2|-73.9538569733247
     ...

Dumping the database and building a new one with sqlite3.exe does not help.

This behavior happens with any sqlite version (last tested 3.31.1)
It is very strange, probably I am missing something but right now I cannot figure out what.

I can provide the particular data if needed (~ 1 MB 7zipped)

(2) By Keith Medcalf (kmedcalf) on 2020-05-21 05:13:37 in reply to 1 [link]

Why do you think the values are numeric?

What does
```
 SELECT typeof(WestCCod), WestCCod, typeof(lati10), lati10, typeof(longi), longi
   FROM latiSaltxi 
   WHERE lati10 >= 41 
     AND lati10 <= 41.3 
ORDER BY lati10, longi
;
```
produce?

(3) By Larry Brasfield (LarryBrasfield) on 2020-05-21 05:15:10 in reply to 1 [link]

I suggest you use this ordering clause,

```
order by cast(lati10 as real), cast(longi as real)
```

, then read about [Type Affinity](https://sqlite.org/datatype3.html#affinity).

Just because you see the values as numbers, and therefor believe they should be sorted numerically, does not mean the DB engine is going to act that way. This should become clear after your study of type affinity in SQLite.

(4) By alesXala on 2020-05-21 13:28:00 in reply to 2 [link]

the result of your query: 
all between integer and real, actually lati10 a mixture of both

      integer|392|integer|41|real|140.936371
      integer|392|integer|41|real|140.948578
      integer|392|integer|41|real|140.948578
      integer|392|integer|41|real|140.951355
      integer|392|integer|41|real|141.398491536317
      ...
      integer|392|real|41.1|real|140.819427
      integer|392|real|41.1|real|140.83551
      integer|392|real|41.1|real|141.396450589127
      integer|380|real|41.2|real|9.37497676257462
      integer|380|real|41.2|real|16.6342872848924
      integer|792|real|41.2|real|43.2255548289229
      integer|860|real|41.2|real|66.7126410609169
      integer|408|real|41.2|real|129.727571723508
      integer|840|real|41.2|real|-73.970001
      integer|840|real|41.2|real|-73.964172
      integer|840|real|41.2|real|-73.964172
      integer|840|real|41.2|real|-73.955566

also by examining the dump, alfanumerics are written with quotes which where not found.

on the other side it makes no sense that the order changes depending on the filter. As I've explained when request lati10 >= 41.2 the order is correct.

(5) By alesXala on 2020-05-21 13:33:06 in reply to 3 [link]

same result using 

     ORDER BY cast(lati10 as real), cast(longi as real)

as with 

     ORDER BY lati10+0, longi+0

which was already tried


     380|41.2|9.37497676257462
     380|41.2|16.6342872848924
     792|41.2|43.2255548289229
     860|41.2|66.7126410609169
     408|41.2|129.727571723508
     840|41.2|-73.970001
     840|41.2|-73.964172

(6) By Richard Hipp (drh) on 2020-05-21 14:01:35 in reply to 1 [link]

Can you email a copy of your database file to drh@sqlite.org, please?

Also, tell us exactly what version of SQLite you are using.
Specifically, what is the output of:

~~~~~
   SELECT sqlite_source_id();
~~~~~

(7) By alesXala on 2020-05-21 14:23:32 in reply to 1 [link]

Ok, I found the issue from the values in the dumped database


      BEGIN TRANSACTION;
      CREATE TABLE latiSaltxi (westCCod, lati10, longi);
      INSERT INTO latiSaltxi VALUES(408,41.199999999999995735,129.72757172350807764);
      INSERT INTO latiSaltxi VALUES(408,41.200000000000002843,129.72079500000000962);
      INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.970000999999996338);
      INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.964172000000004913);
      COMMIT;

      SELECT * FROM latisaltxi WHERE lati10 >= 41 ORDER BY lati10, longi;

      408|41.2|129.727571723508
      840|41.2|-73.970001
      840|41.2|-73.964172
      408|41.2|129.720795

      SELECT * FROM latisaltxi WHERE lati10 >= 41.2 ORDER BY lati10, longi;      

      840|41.2|-73.970001
      840|41.2|-73.964172
      408|41.2|129.720795

Actually it was not that the result was correctly ordered but it simply eliminated the weird C real

This bizarre decimals come only in a dump but not in all sqlite queries that can be done, that confuses a lot.

Where do they come from? I only use either

         ROUND(xx, 1)   from sqlite
         or 
         Math.round(xx*10)/10.  from javascript (Rhino)

I would say from sqlite's ROUND, it looks like a typical and nasty C/C++ arithmetic issues.

(8) By alesXala on 2020-05-21 14:36:52 in reply to 6 [link]

Hi, I've seen you question now.

I've tested from 3.8.0.1 (2013) and 3.29.0 and 3.31.1

actually solved, see my explanation

           INSERT INTO latiSaltxi VALUES(408,41.199999999999995735,129.72757172350807764);
           INSERT INTO latiSaltxi VALUES(408,41.200000000000002843,129.72079500000000962);
           INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.970000999999996338);
           INSERT INTO latiSaltxi VALUES(840,41.200000000000002843,-73.964172000000004913);

I don't know if it can be described as correct behavior 
but its really difficult to find. 


      ORDER BY ROUND(lati10, 1), longi

fixes the problem but I don't think is the way to go (as in C or C++ casting absolutely all variables). Anyway from now on I will do it, specially when combining reals in ORDER BY

(9) By alesXala on 2020-05-21 15:05:58 in reply to 6 [link]

Maybe it can be described as a sqlite bug.

I only use sqlite3 command line, I don't know how it would be the real value using library or api calls. But in command line returning 4.2 and using 4.1999999 in the filter condition is not consistent or could be improved.

either returning in command line

     408|41.1999999999|129.727571723508
     840|41.2000000001|-73.970001
     840|41.2000000012|-73.964172

or using for filtering the same rounding as command line does (4.2) 

     840|41.2|-73.970001
     840|41.2|-73.964172
     408|41.2|129.727571723508

(10) By alesXala on 2020-05-21 15:17:28 in reply to 6 [link]

the first to return 4.199999 in command line is not very beautiful (don't like it) and applying some rounding operation could affect the performance of the query in big data sets. No easy solution. 
On the other hand, the work around writing ROUND(xx,1) in the query filter has the same or bigger performance cost as if it were implemented in sqlite.

(11) By Tim Streater (Clothears) on 2020-05-21 16:05:44 in reply to 8 [link]

41.200000000000002843 is the closest to 41.2 that can be represented as a floating point number. I imagine that 41.199999999999995735 is the next lower number.

This sort of problem always arises when trying to compare floating point numbers, nothing to do with SQLite.

(12) By Keith Medcalf (kmedcalf) on 2020-05-21 21:02:37 in reply to 11 [link]

Correct.  The order by and conditions should be rounded to the precision you want to see.  Example:

```
  select westccod, round(lati10, 6) as lati10, round(longi, 6) as longi
    from latiSaltxi 
   where round(lati10, 6) between 41.0 and 41.3 
order by round(lati10, 6), round(longi, 6)
;
```
or, more simply
```
with x(westccod, lati10, longi)
  as (
      select westccod, round(lati10, 6), round(longi, 6)
        from latiSaltxi
     )
  select *
    from x
   where lati10 between 41.0 and 41.3
order by lati10, longi
;
```
or, without the CTE
```
  select *
    from (
          select westccod, round(lati10, 6) as lati10, round(longgi,6) as longi
            from latiSaltxi
         )
   where lati10 between 41.0 and 41.3
order by lati10, longi
;
```

You could also make sure that your entire database is "consistently rounded" by updating it to consistent rounding before running your original query:

```
update latiSaltxi
   set lati10 = round(lati10, 6),
       longi = round(longi, 6)
;
```
before using your original query, choosing the rounding precision as appropriate for you to ensure that all the numbers are "similar representations".

An IEEE floating point number is only an approximation with certain guarantees as to the precision and accuracy of the approximation.  How well something complies with these guarantees (as in the answer contains slop, rather than being exactly rounded as required by the standard) varies considerably from compiler to compiler, runtime to runtime and Operating System to Operating System.  

Strictly speaking the purpose of IEEE floating point is to eliminate variable slop, and that the results obtained on one IEEE compliant system will exactly match the results obtained on another IEEE platform.  How a platform/runtime/compiler/operating system chooses to implement its interpretation of the IEEE standard varies however, leading to varying results (not to mention that the standard itself has bits "open to interpretation" and so different implementers have varying interpretations).

These are issues quite outside of SQLite3 since SQLite3 merely depends on the underlying platform implementation to "do the right thing".

(13) By alesXala on 2020-05-22 01:34:45 in reply to 12 [link]

I don't think you can round the database, at least using sqlite command line

example: create a table with two numbers

      > sqlite3 myroundingtable.db

      SQLite version 3.29.0 2019-07-10 17:32:03
      Enter ".help" for usage hints.
      sqlite> create table mypar (one, second);

insert two rounded numbers

      sqlite> insert into mypar VALUES (round("30.1233198123987303101203",1), round("30.817232123312"));


query them, ok seems to be rounded


      sqlite> select * from mypar;
      30.1|31.0
      sqlite> .q

but, then dumping

      > sqlite3 myroundingtable.db ".dump"
   
      PRAGMA foreign_keys=OFF;
      BEGIN TRANSACTION;
      CREATE TABLE mypar (one, second);
      INSERT INTO mypar VALUES(30.10000000000000142,30.999999999999999999);
      COMMIT;

So, we asked sqlite to round and store two numbers, it says yes I do but it doesn't.

I am not asking for any fix, only pointing that it is an issue

(14) By Keith Medcalf (kmedcalf) on 2020-05-22 04:32:23 in reply to 13 [link]

The nearest schoolboy rounding of 30.1233198123987303101203 to 1 decimal place is 30.1, which has the nearest representable value 30.10000000000000142.  

The nearest representable value of 30.1 is also 30.10000000000000142.

sqlite> select round(30.1233198123987303101203,1) == 30.1;  
1

Similarly for the schoolboy rounding of 30.817232123312 to 0 decimal places is 31.0, and the nearest representable value of 31.0 is 30.999999999999999999.

sqlite> select round(30.817232123312) == 31;  
1  
sqlite> select round(30.817232123312) == 31.0;  
1

Just because the exact rounded representation is not music to your eyes does not mean it is incorrect.  The error in the result is less than 0.5 ULP as required by the IEEE specification.

<https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html> for a technical explanation of **What Every Computer Scientist Should Know About Floating-Point Arithmetic** and why IEEE base-2 floating point works as it does.

(15) By Keith Medcalf (kmedcalf) on 2020-05-22 04:51:20 in reply to 10

Simply create the index using the rounded values rather than the raw values and search/order using that index.  Then the cost of doing the round operation will be paid when you insert/update rather than when you query.

ie:
```
create index r_latiSaltxi on latiSaltxi(round(lati10,6), round(longi,6));
```
done once, and then the query below will execute no round() functions
```
  select WestCCod, round(lati10,6), round(longi,6)
    from latiSaltxi 
   where round(lati10,6) between 41 and 41.3
order by round(lati10,6), round(longi,6);
```

(16) By Tim Streater (Clothears) on 2020-05-22 07:39:06 in reply to 13 [link]

I told you, nothing to do with SQLite and everything to do WITH HOW FLOATING POINT WORKS. You should read the reference that Keith provided.

You can't store 30.1 and 31.0 exactly, because they cannot be represented exactly as floating point numbers.

So this is not an issue at all.

(17) By alesXala on 2020-05-22 08:14:20 in reply to 14 [link]

Hi Keith,

please note that actually the cause of the issue was early identified in (8) and the solution of using round also explained in (9). So at that point, I think it is obvious that I knew about floating point arithmetic and the way to fix it.

discussing further about the goodness or the evilness of floating point arithmethic (by the way if not invented at least used in the first computer Z3 by Konrad Zuse in 1940!, pretty old stuff) is simply floating-pointless ;)

(18) By Rowan Worth (sqweek) on 2020-05-22 08:45:13 in reply to 14 [link]

> the nearest representable value of 31.0 is 30.999999999999999999.

This is pretty misleading. 31.0 can be trivially represented with a binary decimal like:

11111.0000000000000000000

ie. 2⁴ + 2³ + 2² + 2¹ + 2⁰

Which has a clear and precise encoding under IEEE754. At double precision, that is 0x403f000000000000.

Of course that is the same encoding that you would get for 
30.999999999999999999, and to understand why I find it instructive to look at how neighbouring bit patterns are interpreted (decimal approximations courtesy of java.lang.Double):

```
0x403effffffffffff = 30.999999999999996
0x403f000000000000 = 31.0
0x403f000000000001 = 31.000000000000004
```

ie. each distinct floating point value can be thought of as representing a region on the line of real numbers, and any real number is indistingiushable from other numbers in the same region.

I'd wager the majority of floating point engines display 0x403f000000000000 as 31.0 so it's surprising that SQLite generates 30.999999999999999999, but it's not incorrect.

(19) By Keith Medcalf (kmedcalf) on 2020-05-22 09:40:14 in reply to 18 [link]

Vagaries of the compiler and platform.  For exactly the same code, compiled on the same Windows 10 for Workstations running on the same Xeon processor:

Compiled with MinGW GCC 9.1.0 (x32 and x64 identical):
```
SQLite version 3.32.0 2020-05-21 20:32:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table mypar (one, second);
sqlite> insert into mypar VALUES (round(30.1233198123987303101203,1), round(30.817232123312));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mypar (one, second);
INSERT INTO mypar VALUES(30.100000000000001421,31.0);
COMMIT;
sqlite> .quit
```

but with Microsoft (R) C/C++ Optimizing Compiler Version 19.25.28614 for x64:
```
SQLite version 3.32.0 2020-05-21 20:32:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table mypar (one, second);
sqlite> insert into mypar VALUES (round(30.1233198123987303101203,1), round(30.817232123312));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mypar (one, second);
INSERT INTO mypar VALUES(30.100000000000002309,31.000000000000000888);
COMMIT;
sqlite>
```

and for the current 3.8.2 Python for Windows:
```
Python 3.8.2 (tags/v3.8.2:7b3ab59, Feb 25 2020, 23:03:10) [MSC v.1916 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from math import *
>>> '%.18f' % round(30.1233198123987303101203,1)
'30.100000000000001421'
>>> '%.18f' % round(30.817232123312)
'31.000000000000000000'
>>>
```

(20) By Keith Medcalf (kmedcalf) on 2020-05-22 20:30:55 in reply to 19 [link]

Truly fascinating.  I've done some fiddling and here are my findings:
```
SQLite version 3.32.0 2020-05-22 19:31:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table mypar(x,y);
sqlite> insert into mypar VALUES (round(30.1233198123987303101203,1), round(30.817232123312));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mypar(x,y);
INSERT INTO mypar VALUES(30.10000000000000142,30.999999999999999999);
COMMIT;
```
```
SQLite version 3.32.0 2020-05-22 19:31:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table mypar(x,y);
sqlite> insert into mypar VALUES (round(30.1233198123987303101203,1), round(30.817232123312));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mypar(x,y);
INSERT INTO mypar VALUES(30.100000000000001421,31.0);
COMMIT;
```
```
SQLite version 3.32.0 2020-05-22 19:31:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table mypar(x,y);
sqlite> insert into mypar VALUES (round(30.1233198123987303101203,1), round(30.817232123312));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mypar(x,y);
INSERT INTO mypar VALUES(30.100000000000002309,31.000000000000000888);
COMMIT;
```

These are all compiled and run from the same codebase using the same MinGW GCC 9.1.0 x64 compiler on the same Windows 10 for Workstations on the same Xeon processor, the only difference between them is the definition of a `long double (LONGDOUBLE_TYPE)` -- and I had also modified the codebase slightly to ensure that all uses of `long double` used the define `LONGDOUBLE_TYPE`.

In the first case, a `LONGDOUBLE_TYPE` is `long double`, or an 80-bit IEEE extended precision float.  
In the second case, a `LONGDOUBLE_TYPE` is `__float128`, or an 128-bit IEEE float.  
In the third case, a `LONGDOUBLE_TYPE` is `double`, or a standard 64-bit float.

So, the conclusion that I reached is that with the current code, 80-bit extended precision floats do not contain enough bits to produce 100% accurate "exactly rounded" results, and that MSVC seems to not implement extended precision at all and does all computations in 64-bit floating point.

(21) By Keith Medcalf (kmedcalf) on 2020-05-22 21:33:08 in reply to 20 [link]

Note that if you use any `__float128` types in `sqlite3_malloc`'d space, you will need the following to be defined for MinGW GCC on Windows.  This is because GCC on Windows uses the Windows subsystem memory allocator (which returns all allocations 16-byte aligned), but the current code does not recognize this so it makes all allocations 8 bytes bigger so that the first 8 bytes can contain the allocation size and offsets the returned pointer so that it is only 8-byte aligned.

So if you use allocated memory to point to a structure which contains an element requiring 16-byte alignment, the compiler's alignment assumptions will fail and you will coredump with an alignment fault.

The `SQLITE_USE_MALLOC` tells the compiler that the Operating System heap manager is managing the allocation size (so the library does not have to do it) and the `SQLITE_USE_MSIZE` tells the code to use the Windows subsystem API `(_msize)` to get the allocation size rather than the "standard" C library API (which does not work on Windows).

This only applies to `__float128` on heap allocations made directly or indirectly using sqlite3_malloc.  Allocations on the stack or in constant data segments or on directly malloc'd heap will always be properly aligned.


```
#define SQLITE_USE_MALLOC_H 1                                   // Platform has malloc.h
#define SQLITE_USE_MSIZE 1                                      // Platform malloc_usable_size is _msize
```

(22) By Wout Mertens (wmertens) on 2020-05-22 21:35:13 in reply to 20 [link]

Very interesting! However, I’m confused about your “exactly rounded” scenario, aren’t there always base-10 rational numbers that can’t be specified exactly as a base-2 floating point number?

I’d like to find some documented consensus on how to treat currency in binary. Fixed point arithmetic just doesn’t seem right to me, and I’d like to think that even a mere 64 bit floating point number has enough precision to represent all the money in the world. It seems to me that one should convert currency to binary once, and then all arithmetic to be done in binary, only converting it back to currency when showing results.

However, that approach can cause rounding to the nearest significant digit to happen one way or another, depending on which way you do a mathematically identical calculation. What is the best approach? When you calculate e.g. pairs that sum up to a fixed total, make sure to calculate the other half of the pair using the rounded first half?

(23) By Larry Brasfield (LarryBrasfield) on 2020-05-22 21:54:08 in reply to 22 [link]

Re "base-10 rational numbers that can’t be specified exactly as a base-2 floating point number?"  All floating point representations purport to encode non-special values (such as NaNs) as rational numbers. Most of them (counting by usage) can only represent rational numbers with a power of 2 in the denominator. Hence, all negative powers of 10 cannot be represented, as they would have a (maybe repeated) factor of 5 in the denominator.

There have been machines and software that represented "floating point" with the exponent understood to be and treated as a power of 10. They can represent common decimal fractions (such as hundredths) exactly.

For some reason, ever since I stopped picking up pennies laying in public places, (at least the dirty ones), I have been amused at all the falderal about representing cents exactly.  If that really matters, why not just represent cents instead of some multiple considered less "fundamental"?  (Yes, yes, it is important for accounting, somehow. As if that can be exact.)

(24) By Keith Medcalf (kmedcalf) on 2020-05-22 23:38:42 in reply to 23 [link]

"exactly rounded" is a **"term of art"** that means that a calculation is carried out "exactly" and then "rounded" to the precision of the storage format.  The "exact" part may be obtained by using larger precision intermediates or guard digits or (preferably) both.

Traditional IEEE Floating Point operations are carried out in "extended precision with guard bits" and then rounded to fit in the non-extended storage format.  More modern implementations may not meet the requirements of being exactly rounded.

And yes, there is indeed a base 10 floating point format (as well as base 16 and base 8), however, base 2 has the best (ie, lowest) cost and error.

As for money (cents) the issue is not handling the cents (1/100) which is trivial, it is how to handle the fractions of cents when doing multiplication and division.

As an aside, "accounting" is merely a rather simple "inventory control" system, just the thing which is being subject to "inventory control" is money rather than chairs.

There are only two types of "things" in this world -- there is "goop" and there is "stuff".  Goops are made into other goops by putting them in a bucket and mixing with a stick, or taking a goop and separating it into other goops.  Similarly a stuff is made by attaching one stuff to another stuff and getting a new and different stuff, or chopping a stuff into pieces yielding other stuffs.  Sometimes stuffs can be made into goops and goops into stuffs, sometimes not.

(25) By Keith Medcalf (kmedcalf) on 2020-05-23 12:44:16 in reply to 7 [link]

The purpose of the CLI .dump command is to produce a file of SQL commands which, when executed, will produce "the same database content" as the database which was originally .dump'ed.

In the case of double precision floating-point numbers, round tripping the **exact** value requires that 18 digits be used.  Humans are not expected to look at nor care what is contained in the .dump file.

However, when the CLI application "prints" a floating-point number it does so for human consumption.  This means that it only displays a limited precision and "rounds" the result so that it is pretty for the viewer, because if all floating-point values were always printed to their full 18-digits of precision, one would quickly tire of looking at them.  Almost all UI's "pretty print" floating point values rather than the actual value.

(26) By Larry Brasfield (LarryBrasfield) on 2020-05-23 16:37:15 in reply to 24 [link]

Adopting for the moment your goop versus stuff distinction, which I fit into an objects versus substance categorization, I think the concern and issues with cent-exactitude are related to it.  If cents were merely being added, (or multiplied by integers, which is conceptually the same), or subtracted, there would be little difficulty presented for many programmers. It is when money is treated as a non-granular substance, by application of interest rates (often compounded daily) or other very fine-grained or no-grained operations, that arbitrary fractions of the monetary unit are computed and notions of exactitude become a conceptual pitfall. Coupled with the demands of double-entry accounting, where quantities are expected to be exact so that certain errors can be detected, that is a recipe for confusion and woe.

The problem would be better addressed by recognizing distinct money types: one which is always "exact" and granular ("stuff"), and participates in whatever exact accounting scheme is being implemented; and the other allowed to be inexact according the actual precision demanded by real-world concern thresholds, and as fine-grained ("goop") as cost-effective representations (such as 64-bit floating point) support. With such a distinction made, it would become fairly clear where rounding should and should not be done within money processing applications.

Getting back to the current topic, I would ask the OP: Why are you storing quantities with extra bits that you do not consider meaningful enough to display and participate in ordering or grouping? Perhaps rounding to cents should have been done going into the DB, and if so, it would be simpler to represent them exactly rather than lamenting the fact that floating point cannot represent decimal fractions exactly.

(27.1) By Keith Medcalf (kmedcalf) on 2020-05-24 00:52:33 edited from 27.0 in reply to 20 [link]

Further to this I have done some more experimenting comparing between MinGW GCC 9.1.0 x64 and the current MSVC compiler (19.25.28614 it claims in cl.exe /? or 16.5.4 by the Installer) on Windows.  (both generating x64 code).

```
>testmsvc
Sizeof        Float (binary32) =  4, epsilon = 5.9604644775390625e-08
Sizeof       Double (binary64) =  8, epsilon = 1.11022302462515654e-16
Sizeof  Long Double (binary80) =  8, epsilon = 1.11022302462515654e-16

>testgcc
Sizeof        Float (binary32) =  4, epsilon = 5.9604644775390625e-008
Sizeof       Double (binary64) =  8, epsilon = 1.1102230246251565e-016
Sizeof  Long Double (binary80) = 16, epsilon = 5.4210108624275222e-020
Sizeof  __float128 (binary128) = 16, epsilon = 9.6296497219361793e-035
```

Epsilon is computed using the following loop:

```
(float|double|long double|__float128) x, x1;

    x = 1.0;
    while (1)
    {
        x1 = x + 1.0;
        if (x1 <= 1.0)
            break;
        x /= 2.0;
    }
```

which finds the smallest value which when added to 1.0 is still bigger than 1.0.  So the epsilon is the same for binary32 and binary64, but MSVC appears not to support extended precision at all.

(28) By Wout Mertens (wmertens) on 2020-05-24 10:46:44 in reply to 17 [link]

On this forum neé mailinglist we like to flog horses until they are well and truly deceased ;-)

Please ignore us while we frolic in nerdy thoughtscapes.

(29) By alesXala on 2020-05-25 09:10:18 in reply to 28 [link]

I see, no problem, have fun ;)