Re: [sqlite] .DUMP displays floats differently from SELECT

[Cezary H. Noweta]

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

> [...]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Jens Alfke]

> On Jan 22, 2018, at 10:12 PM, Cezary H. Noweta  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Cezary H. Noweta]

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 , 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.Ax2^-5 => 0.050003 is enough for an exact 
conversion. Neighboring numbers are: 1.9x2^-5 => 
0.049996, and 1.Bx2^-5 => 0.050010.


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


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[petern]

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  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.050002775"
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[petern]

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  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.050002775"
>
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Keith Medcalf]

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 , the uncoddling my produce 
a different value of  that that which originally existed, even though it is 
possible that the new value of  may just happen to also coddle to the same 
coddled display as the actual true value of  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 
 no longer coddles to the same "apparent value" as the original .  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-
>boun...@mailinglists.sqlite.org] On Behalf Of Iulian Onofrei
>Sent: Monday, 22 January, 2018 05:01
>To: sqlite-users@mailinglists.sqlite.org
>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 i

Re: [sqlite] .DUMP displays floats differently from SELECT

[Tony Papadimitriou]

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

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .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 <david.raym...@tomtom.com>
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:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Iulian Onofrei
> Sent: Monday, January 22, 2018 7:01 AM
> To: sqlite-users@mailinglists.sqlite.org
> 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.050002775"
> "0.06" -> "0.059997779"
> "0.07" -> "0.070006661"
> "0.08" -> "0.080001665"
> "0.09" -> "0.089996669"
> "0.1" -> "0.1555"
> "0.11" -> "0.1155"
> "0.12" -> "0.11999555"
> "0.13" -> "0.13000444"
> "0.15" -> "0.14999444"
> "0.16" -> "0.16000333"
> "0.17" -> "0.17001221"
> "0.18" -> "0.17999333"
> "0.19" -> "0.19000222"
> "0.21" -> "0.20999222"
> "0.22" -> "0.22000111"
> "0.23" -> "0.23000999"
> "0.24" -> "0.23999111"
> "0.25" -> "0.25"
> "0.26" -> "0.26000888"
> "0.27" -> "0.27001776"
> "0.28" -> "0.28002664"
> "0.29" -> "0.28998001"
> "0.3" -> "0.29998889"
> "0.32" -> "0.32000666"
> "0.33" -> "0.33001554"
> "0.37" -> "0.36999555"
> "0.38" -> "0.38000444"
> "0.4" -> "0.4000222"
> "0.41" -> "0.40997557"
> "0.43" -> "0.42999333"
> "0.44" -> "0.44000222"
> "0.45" -> "0.4500111"
> "0.46" -> "0.46001998"
> "0.49" -> "0.48999111"
> "0.5" -> "0.5"
> "0.51" -> "0.51000888"
> "0.52" -> "0.52001776"
> "0.54" -> "0.54003552"
> "0.56" -> "0.56005329"
> "0.57" -> "0.56995115"
> "0.58" -> "0.57996003"
> "0.6" -> "0.59997779"
> "0.61" -> "0.60998667"
> "0.65" -> "0.6500222"
> "0.67" -> "0.67003996"
> "0.7" -> "0.69995559"
> "0.73" -> "0.72998223"
> "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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Iulian Onofrei
Sent: Monday, January 22, 2018 7:01 AM
To: sqlite-users@mailinglists.sqlite.org
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.050002775"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .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.050002775"
"0.06" -> "0.059997779"
"0.07" -> "0.070006661"
"0.08" -> "0.080001665"
"0.09" -> "0.089996669"
"0.1" -> "0.1555"
"0.11" -> "0.1155"
"0.12" -> "0.11999555"
"0.13" -> "0.13000444"
"0.15" -> "0.14999444"
"0.16" -> "0.16000333"
"0.17" -> "0.17001221"
"0.18" -> "0.17999333"
"0.19" -> "0.19000222"
"0.21" -> "0.20999222"
"0.22" -> "0.22000111"
"0.23" -> "0.23000999"
"0.24" -> "0.23999111"
"0.25" -> "0.25"
"0.26" -> "0.26000888"
"0.27" -> "0.27001776"
"0.28" -> "0.28002664"
"0.29" -> "0.28998001"
"0.3" -> "0.29998889"
"0.32" -> "0.32000666"
"0.33" -> "0.33001554"
"0.37" -> "0.36999555"
"0.38" -> "0.38000444"
"0.4" -> "0.4000222"
"0.41" -> "0.40997557"
"0.43" -> "0.42999333"
"0.44" -> "0.44000222"
"0.45" -> "0.4500111"
"0.46" -> "0.46001998"
"0.49" -> "0.48999111"
"0.5" -> "0.5"
"0.51" -> "0.51000888"
"0.52" -> "0.52001776"
"0.54" -> "0.54003552"
"0.56" -> "0.56005329"
"0.57" -> "0.56995115"
"0.58" -> "0.57996003"
"0.6" -> "0.59997779"
"0.61" -> "0.60998667"
"0.65" -> "0.6500222"
"0.67" -> "0.67003996"
"0.7" -> "0.69995559"
"0.73" -> "0.72998223"
"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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .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.00 sys 0.00

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

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

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.2299822);
COMMIT;

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

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

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

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

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



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] 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.2299822);

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Simon Slavin]

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

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

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Simon Slavin
> Sent: Wednesday, May 03, 2017 12:30 PM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] .DUMP displays floats differently from SELECT
>
>
> On 3 May 2017, at 3:40pm, Scott Robison <sc...@casaderobison.com> wrote:
>
> > On May 3, 2017 8:07 AM, "Tony Papadimitriou" <to...@acm.org> 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 / william.dr...@l3t.com

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 william.dr...@l3t.com. Please 
update your records.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Simon Slavin]

On 3 May 2017, at 3:40pm, Scott Robison  wrote:

> On May 3, 2017 8:07 AM, "Tony Papadimitriou"  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .DUMP displays floats differently from SELECT

[Scott Robison]

On May 3, 2017 8:07 AM, "Tony Papadimitriou"  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] .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.2299822);
COMMIT;

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users