Thanks, that's an interesting wrinkle that I don't remember being raised in
previous discussions but if known it should be mentioned up front as
many/most use affinities.
Regards,
Donald Shepherd.
On Thu, 1 Aug 2019 at 08:58, Keith Medcalf <kmedc...@dessus.com> wrote:
> False, as it depends on the application of affinity. If you are storing
> the floating point value in a column that does not have an affinity (ie, no
> conversions are performed), then it is stored exactly (except for NaN).
> Application of affinity (ie, real) will cause the -0.0 to be stored as the
> integer 0 and thus the sign will be lost on retrieval (as well as the
> conversion of NaN to NULL).
>
> >>> import apsw
> >>> import math
> >>> db = apsw.Connection('')
> >>> db.execute('create table x(x)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=-0.0)
>
> >>> db.execute('drop table x');
> >>> db.execute('create table x(x real)');
> >>> db.execute('insert into x values (?)', (math.nan,))
> >>> db.execute('insert into x values (?)', (math.inf,))
> >>> db.execute('insert into x values (?)', (-math.inf,))
> >>> db.execute('insert into x values (?)', (0.0,))
> >>> db.execute('insert into x values (?)', (-0.0,))
> >>> for row in db.execute('select x from x'): print row
> ...
> Row(x=None)
> Row(x=inf)
> Row(x=-inf)
> Row(x=0.0)
> Row(x=0.0)
>
> --
> 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 Donald Shepherd
> >Sent: Wednesday, 31 July, 2019 16:50
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Floating point literals
> >
> >That's not correct, verified several times by my own testing and
> >re-verified on the recent discussion about -0.0 on this mailing list.
> >
> >If you store -0.0 as a double, it will be stored as an integer as a
> >space-saving mechanism. That integer is 0. When you retrieve the
> >value as
> >a double it will be 0.0. The sign has been stripped.
> >
> >Regards,
> >Donald Shepherd.
> >
> >On Thu, 1 Aug 2019 at 08:47, Keith Medcalf <kmedc...@dessus.com>
> >wrote:
> >
> >>
> >> The -0.0 is only for conversion to text. Otherwise -0.0 is
> >preserved both
> >> on input and output (including input text conversions). It is only
> >the
> >> conversion of -0.0 TO text that drops the sign. NaN becomes a NULL
> >(ie, a
> >> double is not stored, a NULL value is stored). Everything else is
> >> preserved including Inf and -Inf.
> >>
> >> --
> >> 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 Igor Tandetnik
> >> >Sent: Wednesday, 31 July, 2019 15:34
> >> >To: sqlite-users@mailinglists.sqlite.org
> >> >Subject: Re: [sqlite] Floating point literals
> >> >
> >> >On 7/31/2019 5:15 PM, Eric Reischer wrote:
> >> >> I understand you can *retrieve* a non-quantized value using
> >> >sqlite3_column_double(), but I don't see a way to set one without
> >> >having to printf() the floating point value.
> >> >
> >> >sqlite3_bind_double
> >> >
> >> >> Can this be done using sqlite3_bind_* interfaces, or do they
> >> >quantize as well?
> >> >
> >> >Yes. No; except that I seem to recall it mentioned that NaN is
> >> >treated as SQL NULL, and negative zero is normalized to positive
> >> >zero.
> >> >
> >> >> The goal is to copy the straight 8-byte (or precision-extended
> >4-
> >> >byte) IEEE value into the column into the database (where the
> >column
> >> >is defined as a FLOAT) without having to build a SQL statement
> >that
> >> >has an obscene number of digits in each floating point field.
> >> >
> >> >That's precisely what bound parameters and sqlite3_bind_X
> >functions
> >> >are for.
> >> >--
> >> >Igor Tandetnik
> >> >
> >> >
> >> >_______________________________________________
> >> >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
>
>
>
> _______________________________________________
> 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
