.DUMP displays floats differently from SELECT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

.DUMP displays floats differently from SELECT

Tony Papadimitriou
While trying to search/replace some text from an SQLite3 dump I noticed that, unfortunately, .DUMP does not produce the exact same numbers as a plain SELECT on the same values.

I know all about expected floating point inaccuracies, but I don’t see why it should matter in this case as we have two different places in the same app (SQLite3) where the same number is ‘displayed’ using whatever default format.  IMO, in both places the same number should display exactly the same (i.e., using the same format), both for consistency and easy verification between dump and actual database.

Below is a sample script and its output that demonstrate the issue (using SQLite 3.18.0 2017-03-28 18:48:43):

SCRIPT:
------------
create table xxx(x);
insert into xxx values(1.23);
select * from xxx;
.dump

OUTPUT:
------------
1.23      
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO xxx VALUES(1.2299999999999999822);
COMMIT;

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Scott Robison-2
On May 3, 2017 8:07 AM, "Tony Papadimitriou" <[hidden email]> wrote:

While trying to search/replace some text from an SQLite3 dump I noticed
that, unfortunately, .DUMP does not produce the exact same numbers as a
plain SELECT on the same values.

I know all about expected floating point inaccuracies, but I don’t see why
it should matter in this case as we have two different places in the same
app (SQLite3) where the same number is ‘displayed’ using whatever default
format.  IMO, in both places the same number should display exactly the
same (i.e., using the same format), both for consistency and easy
verification between dump and actual database.


The select version is output for human readability. The dump version is
output for precision so that every digit goes back into the recreated
database exactly as it came out.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Simon Slavin-3

On 3 May 2017, at 3:40pm, Scott Robison <[hidden email]> wrote:

> On May 3, 2017 8:07 AM, "Tony Papadimitriou" <[hidden email]> wrote:
>
>> While trying to search/replace some text from an SQLite3 dump I noticed
>> that, unfortunately, .DUMP does not produce the exact same numbers as a
>> plain SELECT on the same values.
>
> I know all about expected floating point inaccuracies, but I don’t see why
> it should matter in this case as we have two different places in the same
> app (SQLite3) where the same number is ‘displayed’ using whatever default
> format.  IMO, in both places the same number should display exactly the
> same (i.e., using the same format), both for consistency and easy
> verification between dump and actual database.

That’s a matter of preference.  There is one possible bug here.  If the results of reading the dump file back in produces a different value to the original INSERT, that’s a problem.  So I tried it:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;

Hmm.  Cannot reproduce your problem in my version of the command-line tool.  You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  Or perhaps there’s a difference in a library between my platform (macOS) and yours.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Drago, William @ CSG - NARDA-MITEQ-2
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 03, 2017 12:30 PM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>
> On 3 May 2017, at 3:40pm, Scott Robison <[hidden email]> wrote:
>
> > On May 3, 2017 8:07 AM, "Tony Papadimitriou" <[hidden email]> wrote:
> >
> >> While trying to search/replace some text from an SQLite3 dump I
> >> noticed that, unfortunately, .DUMP does not produce the exact same
> >> numbers as a plain SELECT on the same values.
> >
> > I know all about expected floating point inaccuracies, but I don’t see
> > why it should matter in this case as we have two different places in
> > the same app (SQLite3) where the same number is ‘displayed’ using
> > whatever default format.  IMO, in both places the same number should
> > display exactly the same (i.e., using the same format), both for
> > consistency and easy verification between dump and actual database.
>
> That’s a matter of preference.  There is one possible bug here.  If the results
> of reading the dump file back in produces a different value to the original
> INSERT, that’s a problem.  So I tried it:
>
> SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage hints.
> sqlite> create table xxx(x);
> sqlite> insert into xxx values(1.23);
> sqlite> select * from xxx;
> 1.23
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE xxx(x);
> INSERT INTO "xxx" VALUES(1.23);
> COMMIT;
>
> Hmm.  Cannot reproduce your problem in my version of the command-line
> tool.  You’re using 3.18.0.  I’m using 3.16.0.  Perhaps its a recent change.  Or
> perhaps there’s a difference in a library between my platform (macOS) and
> yours.
>
> Simon.

Nor I with 3.14.1 on Windows 7:

SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table xxx(x);
sqlite> insert into xxx values(1.23);
sqlite> select * from xxx;
1.23
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx(x);
INSERT INTO "xxx" VALUES(1.23);
COMMIT;
sqlite>

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / [hidden email]

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.

Effective immediately my new email address is [hidden email]. Please update your records.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Simon Slavin-3
In reply to this post by Simon Slavin-3
In a previous message I suggest that behaviour under macOS changed between SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);


SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299999999999999822);

It does seem that this is due to a recent change in SQLite source code.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

David Raymond
On the plus side they come out equal, so apart from neatness of the display, all's good.
Interesting how the auto EQP output shows up in the .dump output. But at least it starts with -- so won't harm anything.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table xxx (x primary key);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.004 user 0.000000 sys 0.000000

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE xxx (x primary key);
--EQP-- 0,0,0,SCAN TABLE xxx
INSERT INTO xxx(x) VALUES(1.2299999999999999822);
COMMIT;

sqlite> INSERT INTO xxx(x) VALUES(1.2299999999999999822);
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: UNIQUE constraint failed: xxx.x

sqlite> delete from xxx;
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> INSERT INTO xxx(x) VALUES(1.2299999999999999822);
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> select * from xxx;
--EQP-- 0,0,0,SCAN TABLE xxx
x
1.23
Run Time: real 0.003 user 0.000000 sys 0.000000

sqlite> insert into xxx values (1.23);
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: UNIQUE constraint failed: xxx.x



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Wednesday, May 03, 2017 2:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] .DUMP displays floats differently from SELECT

In a previous message I suggest that behaviour under macOS changed between SQLite 3.16.0 and SQLite 3.18.0.  I then received information from a lurker who uses Windows 7:

SQLite version 3.17.0 2017-02-13 16:02:40
INSERT INTO "xxx" VALUES(1.23);


SQLite version 3.18.0 2017-03-28 18:48:43
INSERT INTO xxx VALUES(1.2299999999999999822);

It does seem that this is due to a recent change in SQLite source code.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Tony Papadimitriou
In reply to this post by Tony Papadimitriou
Apparently, this was an intentional change by this check-in :

[7359fcac] Increase the number of significant digits in floating point literals on ".dump" output from the shell.

I don't know what problem this change actually solved (as in my case it introduced one) but for my own copy I changed %!.20g to %!.16g and everything seems to be OK now without compromising accuracy (I hope).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Clemens Ladisch
Tony Papadimitriou wrote:
> Apparently, this was an intentional change by this check-in :
>
> [7359fcac] Increase the number of significant digits in floating point literals on ".dump" output from the shell.
>
> I don't know what problem this change actually solved

Scott Robinson already mentioned it:
| The dump version is output for precision so that every digit goes back
| into the recreated database exactly as it came out.

(Where "every digit" actually means the binary representation.)


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Iulian Onofrei
Hi,

This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.0" -> "0.0"
"0.05" -> "0.050000000000000002775"
"0.06" -> "0.059999999999999997779"
"0.07" -> "0.070000000000000006661"
"0.08" -> "0.080000000000000001665"
"0.09" -> "0.089999999999999996669"
"0.1" -> "0.10000000000000000555"
"0.11" -> "0.11000000000000000055"
"0.12" -> "0.11999999999999999555"
"0.13" -> "0.13000000000000000444"
"0.15" -> "0.14999999999999999444"
"0.16" -> "0.16000000000000000333"
"0.17" -> "0.17000000000000001221"
"0.18" -> "0.17999999999999999333"
"0.19" -> "0.19000000000000000222"
"0.21" -> "0.20999999999999999222"
"0.22" -> "0.22000000000000000111"
"0.23" -> "0.23000000000000000999"
"0.24" -> "0.23999999999999999111"
"0.25" -> "0.25"
"0.26" -> "0.26000000000000000888"
"0.27" -> "0.27000000000000001776"
"0.28" -> "0.28000000000000002664"
"0.29" -> "0.28999999999999998001"
"0.3" -> "0.29999999999999998889"
"0.32" -> "0.32000000000000000666"
"0.33" -> "0.33000000000000001554"
"0.37" -> "0.36999999999999999555"
"0.38" -> "0.38000000000000000444"
"0.4" -> "0.4000000000000000222"
"0.41" -> "0.40999999999999997557"
"0.43" -> "0.42999999999999999333"
"0.44" -> "0.44000000000000000222"
"0.45" -> "0.4500000000000000111"
"0.46" -> "0.46000000000000001998"
"0.49" -> "0.48999999999999999111"
"0.5" -> "0.5"
"0.51" -> "0.51000000000000000888"
"0.52" -> "0.52000000000000001776"
"0.54" -> "0.54000000000000003552"
"0.56" -> "0.56000000000000005329"
"0.57" -> "0.56999999999999995115"
"0.58" -> "0.57999999999999996003"
"0.6" -> "0.59999999999999997779"
"0.61" -> "0.60999999999999998667"
"0.65" -> "0.6500000000000000222"
"0.67" -> "0.67000000000000003996"
"0.7" -> "0.69999999999999995559"
"0.73" -> "0.72999999999999998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

David Raymond
While maybe different formatting, how is that wrong? Remember, if you've declared the columns to be of type real then they're stored as an 8 byte binary floating point number. There is no exact representation of .05 in binary: so it's giving you all the digits that are stored. See #16 in the FAQ http://www.sqlite.org/faq.html#q16


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Iulian Onofrei
Sent: Monday, January 22, 2018 7:01 AM
To: [hidden email]
Subject: Re: [sqlite] .DUMP displays floats differently from SELECT

Hi,

This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.0" -> "0.0"
"0.05" -> "0.050000000000000002775"
"0.06" -> "0.059999999999999997779"
"0.07" -> "0.070000000000000006661"
"0.08" -> "0.080000000000000001665"
"0.09" -> "0.089999999999999996669"
"0.1" -> "0.10000000000000000555"
"0.11" -> "0.11000000000000000055"
"0.12" -> "0.11999999999999999555"
"0.13" -> "0.13000000000000000444"
"0.15" -> "0.14999999999999999444"
"0.16" -> "0.16000000000000000333"
"0.17" -> "0.17000000000000001221"
"0.18" -> "0.17999999999999999333"
"0.19" -> "0.19000000000000000222"
"0.21" -> "0.20999999999999999222"
"0.22" -> "0.22000000000000000111"
"0.23" -> "0.23000000000000000999"
"0.24" -> "0.23999999999999999111"
"0.25" -> "0.25"
"0.26" -> "0.26000000000000000888"
"0.27" -> "0.27000000000000001776"
"0.28" -> "0.28000000000000002664"
"0.29" -> "0.28999999999999998001"
"0.3" -> "0.29999999999999998889"
"0.32" -> "0.32000000000000000666"
"0.33" -> "0.33000000000000001554"
"0.37" -> "0.36999999999999999555"
"0.38" -> "0.38000000000000000444"
"0.4" -> "0.4000000000000000222"
"0.41" -> "0.40999999999999997557"
"0.43" -> "0.42999999999999999333"
"0.44" -> "0.44000000000000000222"
"0.45" -> "0.4500000000000000111"
"0.46" -> "0.46000000000000001998"
"0.49" -> "0.48999999999999999111"
"0.5" -> "0.5"
"0.51" -> "0.51000000000000000888"
"0.52" -> "0.52000000000000001776"
"0.54" -> "0.54000000000000003552"
"0.56" -> "0.56000000000000005329"
"0.57" -> "0.56999999999999995115"
"0.58" -> "0.57999999999999996003"
"0.6" -> "0.59999999999999997779"
"0.61" -> "0.60999999999999998667"
"0.65" -> "0.6500000000000000222"
"0.67" -> "0.67000000000000003996"
"0.7" -> "0.69999999999999995559"
"0.73" -> "0.72999999999999998223"
"0.75" -> "0.75"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

J Decker
There was work to 'speed up float formatting' which although fast is
apparently inaccurate.
One I Was working on was pretty fast, but didn't format things correctly
because of minor decimals.

On Mon, Jan 22, 2018 at 9:14 AM, David Raymond <[hidden email]>
wrote:

> While maybe different formatting, how is that wrong? Remember, if you've
> declared the columns to be of type real then they're stored as an 8 byte
> binary floating point number. There is no exact representation of .05 in
> binary: so it's giving you all the digits that are stored. See #16 in the
> FAQ http://www.sqlite.org/faq.html#q16
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Iulian Onofrei
> Sent: Monday, January 22, 2018 7:01 AM
> To: [hidden email]
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
> Hi,
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.0" -> "0.0"
> "0.05" -> "0.050000000000000002775"
> "0.06" -> "0.059999999999999997779"
> "0.07" -> "0.070000000000000006661"
> "0.08" -> "0.080000000000000001665"
> "0.09" -> "0.089999999999999996669"
> "0.1" -> "0.10000000000000000555"
> "0.11" -> "0.11000000000000000055"
> "0.12" -> "0.11999999999999999555"
> "0.13" -> "0.13000000000000000444"
> "0.15" -> "0.14999999999999999444"
> "0.16" -> "0.16000000000000000333"
> "0.17" -> "0.17000000000000001221"
> "0.18" -> "0.17999999999999999333"
> "0.19" -> "0.19000000000000000222"
> "0.21" -> "0.20999999999999999222"
> "0.22" -> "0.22000000000000000111"
> "0.23" -> "0.23000000000000000999"
> "0.24" -> "0.23999999999999999111"
> "0.25" -> "0.25"
> "0.26" -> "0.26000000000000000888"
> "0.27" -> "0.27000000000000001776"
> "0.28" -> "0.28000000000000002664"
> "0.29" -> "0.28999999999999998001"
> "0.3" -> "0.29999999999999998889"
> "0.32" -> "0.32000000000000000666"
> "0.33" -> "0.33000000000000001554"
> "0.37" -> "0.36999999999999999555"
> "0.38" -> "0.38000000000000000444"
> "0.4" -> "0.4000000000000000222"
> "0.41" -> "0.40999999999999997557"
> "0.43" -> "0.42999999999999999333"
> "0.44" -> "0.44000000000000000222"
> "0.45" -> "0.4500000000000000111"
> "0.46" -> "0.46000000000000001998"
> "0.49" -> "0.48999999999999999111"
> "0.5" -> "0.5"
> "0.51" -> "0.51000000000000000888"
> "0.52" -> "0.52000000000000001776"
> "0.54" -> "0.54000000000000003552"
> "0.56" -> "0.56000000000000005329"
> "0.57" -> "0.56999999999999995115"
> "0.58" -> "0.57999999999999996003"
> "0.6" -> "0.59999999999999997779"
> "0.61" -> "0.60999999999999998667"
> "0.65" -> "0.6500000000000000222"
> "0.67" -> "0.67000000000000003996"
> "0.7" -> "0.69999999999999995559"
> "0.73" -> "0.72999999999999998223"
> "0.75" -> "0.75"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Tony Papadimitriou
In reply to this post by Iulian Onofrei
I reported this same issue in May 2017
(http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
I too consider this a problem (diffing dumps is one good reason why) but,
unfortunately, this was an intentional change by this check-in:

[7359fcac] Increase the number of significant digits in floating point
literals on ".dump" output from the shell.

Tony
-----Original Message-----
From: Iulian Onofrei

This is clearly a bug, as it outputs incorrect and different output from the
previous versions.

I have a "REAL" column with float values having up to 2 decimals, and using
".dump" with the latest version incorrectly converts them like this:

"0.05" -> "0.050000000000000002775"

I rely on dumps to track changes to some databases, so this breaks it
completely, and I currently had to pin sqlite to an older version which
isn't desired.

Thank you,
iulianOnofrei

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Keith Medcalf
In reply to this post by Iulian Onofrei

When you use a "select" from the shell to output a value converted to text, it outputs the value "doctored up" (coddled) for display to humans rather than display the true (as in actual) floating point value.  This is because "most people" do not understand how computers (binary floating point in particular) work and want "pretty" output that looks like what they typed in rather than the "true and accurate" representation.  

This behaviour (described above) is a bug (in my opinion) because it encourages people to not understand what it is they are doing, and lay blame for their misunderstanding at some door other than their own.  However, it would seem that many (most) user interfaces are "buggy" in that they prefer to molly-coddle rather than be honest and truthful, thus encouraging by its own (rather unfortunate) feedback loop perpetual ignorance by those using the bug ridden products (which is almost everything that uses binary floating point).

Those interested in truth and accuracy realize that the entire binary floating point system is an approximation of base-10 and that (like everything else) should only be coddled at first input and last output, and not coddled anywhere in between.  Intermediate coddling should never be used for any purpose.

That said, however, the purpose of .DUMP is not to present information for the coddling of the user.  It is for the purpose of generating SQL which, when imported back in to an "empty" database, will result in *exactly* what was in the database which was dumped.  This means that the EXACT binary floating point value must be dumped and reloaded, not the coddled user ignorantificated version presented by other interfaces, since it is possible for the coddling to display values which, when reloaded, do *not* result in the same binary value as that which existed before the coddling operation.

That is, depending on the method used to coddle <X>, the uncoddling my produce a different value of <X> that that which originally existed, even though it is possible that the new value of <X> may just happen to also coddle to the same coddled display as the actual true value of <X> before the "round tripping" through the coddling functions.  This "coddling error" may multiply each time the values are successively coddled and uncoddled until the resulting value of <X> no longer coddles to the same "apparent value" as the original <X>.  The "standards" try to minimize the possibility of such errors, however, they still occur and cannot be prevented except by careful use of correctly designed numerical methods.

This is the nature of binary floating point and there have been many pages (probably in the hundreds of thousands or more) written over the last couple of millenia describing the difficulties of, and how to deal with, the problems created by the limited human cognition of arithmetic quantities in various bases, and more lately, on the peculiarities in particular of "binary floating point" representation of base-10 numbers and calculations with them.

Most of these issues had been addressed by the last quarter of the last century, however, there continues to be new people suddenly rediscovering that which was old and ancient news and making much ado out of it (take for example the latest so called meltdown and spectre CPU flaws that were recently "rediscovered" but were known since the 1950's and simply ignored by those hardware manufacturers and software developers affected by them in the here and now).

The long and the short of it is that the output of .DUMP is correct.  It contains the true and accurate binary floating point representation into base-10 and that particular representation does in fact exactly "round trip" through SQLite3 accurately, maintaining 100% true internal binary representation of the values that are stored in the database.  In fact, those representations round trip through almost all other binary-floating point representation converters with 100% binary accuracy.  And this is the goal of the .dump command -- it is for preservation of exact values, not for human consumption.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Iulian Onofrei
>Sent: Monday, 22 January, 2018 05:01
>To: [hidden email]
>Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>Hi,
>
>This is clearly a bug, as it outputs incorrect and different output
>from the
>previous versions.
>
>I have a "REAL" column with float values having up to 2 decimals, and
>using
>".dump" with the latest version incorrectly converts them like this:
>
>"0.0" -> "0.0"
>"0.05" -> "0.050000000000000002775"
>"0.06" -> "0.059999999999999997779"
>"0.07" -> "0.070000000000000006661"
>"0.08" -> "0.080000000000000001665"
>"0.09" -> "0.089999999999999996669"
>"0.1" -> "0.10000000000000000555"
>"0.11" -> "0.11000000000000000055"
>"0.12" -> "0.11999999999999999555"
>"0.13" -> "0.13000000000000000444"
>"0.15" -> "0.14999999999999999444"
>"0.16" -> "0.16000000000000000333"
>"0.17" -> "0.17000000000000001221"
>"0.18" -> "0.17999999999999999333"
>"0.19" -> "0.19000000000000000222"
>"0.21" -> "0.20999999999999999222"
>"0.22" -> "0.22000000000000000111"
>"0.23" -> "0.23000000000000000999"
>"0.24" -> "0.23999999999999999111"
>"0.25" -> "0.25"
>"0.26" -> "0.26000000000000000888"
>"0.27" -> "0.27000000000000001776"
>"0.28" -> "0.28000000000000002664"
>"0.29" -> "0.28999999999999998001"
>"0.3" -> "0.29999999999999998889"
>"0.32" -> "0.32000000000000000666"
>"0.33" -> "0.33000000000000001554"
>"0.37" -> "0.36999999999999999555"
>"0.38" -> "0.38000000000000000444"
>"0.4" -> "0.4000000000000000222"
>"0.41" -> "0.40999999999999997557"
>"0.43" -> "0.42999999999999999333"
>"0.44" -> "0.44000000000000000222"
>"0.45" -> "0.4500000000000000111"
>"0.46" -> "0.46000000000000001998"
>"0.49" -> "0.48999999999999999111"
>"0.5" -> "0.5"
>"0.51" -> "0.51000000000000000888"
>"0.52" -> "0.52000000000000001776"
>"0.54" -> "0.54000000000000003552"
>"0.56" -> "0.56000000000000005329"
>"0.57" -> "0.56999999999999995115"
>"0.58" -> "0.57999999999999996003"
>"0.6" -> "0.59999999999999997779"
>"0.61" -> "0.60999999999999998667"
>"0.65" -> "0.6500000000000000222"
>"0.67" -> "0.67000000000000003996"
>"0.7" -> "0.69999999999999995559"
>"0.73" -> "0.72999999999999998223"
>"0.75" -> "0.75"
>
>I rely on dumps to track changes to some databases, so this breaks it
>completely, and I currently had to pin sqlite to an older version
>which
>isn't desired.
>
>Thank you,
>iulianOnofrei
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

petern
In reply to this post by Tony Papadimitriou
FYI.  There's no need to pin the whole db version back to get original
dbdump formatting.
The original distribution standalone dump is still available in its full
glory here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Peter




On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou <[hidden email]> wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -----Original Message----- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050000000000000002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

petern
In reply to this post by Tony Papadimitriou
FYI. There's no need to pin back the whole db version just to get the old
style dbdump format.
The original dump is available from the distribution as a standalone
program here:

http://www.sqlite.org/src/artifact/819eb33f6ff788a4

--dbdump.c--

** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
** is included so that this routine becomes a command-line utility.  The
** command-line utility takes two or three arguments which are the name
** of the database file, the schema, and optionally the table

Compile and enjoy.
Peter


On Mon, Jan 22, 2018 at 11:46 AM, Tony Papadimitriou <[hidden email]> wrote:

> I reported this same issue in May 2017 (http://mailinglists.sqlite.or
> g/cgi-bin/mailman/private/sqlite-users/2017-May/072714.html)
> I too consider this a problem (diffing dumps is one good reason why) but,
> unfortunately, this was an intentional change by this check-in:
>
> [7359fcac] Increase the number of significant digits in floating point
> literals on ".dump" output from the shell.
>
> Tony
> -----Original Message----- From: Iulian Onofrei
>
> This is clearly a bug, as it outputs incorrect and different output from
> the
> previous versions.
>
> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.05" -> "0.050000000000000002775"
>
> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.
>
> Thank you,
> iulianOnofrei
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Cezary H. Noweta
In reply to this post by Keith Medcalf
Hello,

On 2018-01-22 21:33, Keith Medcalf wrote:
> The long and the short of it is that the output of .DUMP is correct.

I would say that the output is ``too correct''. ``.dump'' displays 20
meaningful digits.

Let's look at the 17 meaningful digits. Mantissa divides every
exponential range (i.e. range <B^n; B^{n+1}>, which is divided linearly)
into 1x2^52 equilengthy segments (binary/double) or 9x10^16 equilengthy
segments (decimal/17). The segment represents a rational number.

Every decimal exponential range overlaps 4 or 5 binary exponential
ranges. Let's consider decimal exponential range <0.1; 1> -- 17 digits
mantissa gives a density of (9x10^16)/(9x10^-1)=10^17 numbers per one.

Let's take the densest binary exponential range which overlaps the above
mentioned decimal one: <1/16;1/8> -- 53 digits binary mantissa gives a
density of (1x2^52)/(1x2^-4)=2^56=7.2...x10^16<10^17 numbers per one.

Let's find a pair of binary/decimal exponential ranges which overlap
each other and a ratio of binary numbers density to decimal numbers
density is highest possible (if greater then one, then same binary
numbers in the range cannot be represented by 17 digits decimal float).

Believe me or not, (for doubles) the ranges are: decimal:
<10^-146;10^-145> -- density: (9x10^16)/(9x10^-146)=10^162 numbers per
one; binary: <2^-486;2^-485> -- density:
(1x2^52)/(1x2^-486)=2^538=8.99..x10^161<10^162 numbers per one. The
ratio < 1. So 17 decimal digits is enough for an exact representation of
IEEE64 float.

I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2...,
however, for 64bit mantissa (long double) it is not enough (to be
represented exactly), for 53bit mantissa it is too many. Besides that,
17 digits + one rounding digit fit in i64, while 20 decimal digits do not.

The error of conversion depends on the width of intermediate floats --
not on the width of a integral part. Further increasing of number of
meaningful digits does nothing. It is important to use ``long double''
for multiplication. Previous (<= 3.21) implementation of ``AtoF()''
would loose 6 bits (40+ multiplications in the worst case), if it was
compiled with a compiler which mapped ``long double'' to ``double''.
Integer had 18/19 digits -- it did not helped.

For example, let's look at the first OP's number: 0.05 -- it is
1.999999999999Ax2^-5 => 0.050000000000000003 is enough for an exact
conversion. Neighboring numbers are: 1.9999999999999x2^-5 =>
0.049999999999999996, and 1.999999999999Bx2^-5 => 0.050000000000000010.

17 digits is also enough for the densest range
(<2^-486;2^-485>n<10^-146;10^-145>):
1.FF99999999999x2^-486 => 1.0002594838824945E-146,
1.FF9999999999Ax2^-486 => 1.0002594838824946E-146,
1.FF9999999999Bx2^-486 => 1.0002594838824947E-146.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Jens Alfke-2


> On Jan 22, 2018, at 10:12 PM, Cezary H. Noweta <[hidden email]> wrote:
>
> I suppose that 20 digits is taken from the fact: 64ln(2)/ln(10)=19.2..., however, for 64bit mantissa (long double) it is not enough (to be represented exactly), for 53bit mantissa it is too many. Besides that, 17 digits + one rounding digit fit in i64, while 20 decimal digits do not.

I agree. A standard `double` value with a 53-bit mantissa is only accurate to 17 decimal digits, so formatting it with more precision than that is unnecessary, and exposes annoying roundoff errors.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: .DUMP displays floats differently from SELECT

Cezary H. Noweta
In reply to this post by Iulian Onofrei
Hello,

On 2018-01-22 13:00, Iulian Onofrei wrote:

> I have a "REAL" column with float values having up to 2 decimals, and using
> ".dump" with the latest version incorrectly converts them like this:
>
> "0.0" -> "0.0"
> "0.05" -> "0.050000000000000002775"
 > [...]

Did you consider an using of INTEGERs as fixed-point numbers? Nearly the
same arithmetic with one additional division by 100 after multiplication
and one additional multiplication by 100 (of dividend) before division.

> I rely on dumps to track changes to some databases, so this breaks it
> completely, and I currently had to pin sqlite to an older version which
> isn't desired.

IMHO, a better and more concise way is to rem out the following line:

p->mode = p->cMode = MODE_Insert;

in ``shell.c'' and to recompile it. Two slashes and the problem has gone.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users