Quantcast

.DUMP displays floats differently from SELECT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

.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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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
|  
Report Content as Inappropriate

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