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)

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?

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.

I suggest you use this ordering clause,

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

, then read about Type 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.

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

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

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.

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".

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

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.

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 ;)

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.

I see, no problem, have fun ;)

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.

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'
>>>

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.

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

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?

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.)

"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.

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.

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.

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.

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

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.

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);

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.

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.