Floating point literals

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Floating point literals

Eric Reischer
Is there a way to pass binary representations of floating point numbers to
a SQL query?  If sqlite's internal representation of floating point
numbers is 8-byte IEEE doubles, it would be convenient to be able to pass
the literal value of a float or double to the underlying SQL parser
without suffering the quantization that occurs with printf()'ing floating
point values.

One way I've accomplished this in the past with other interfaces is to
interpret a hex value as a binary literal that can be interpreted as a raw
4-byte or 8-byte IEEE floating-point value (either via a union or other
compiler trick).  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.

Can this be done using sqlite3_bind_* interfaces, or do they quantize as
well?  The documentation isn't clear on this.  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.

Thanks in advance.
_______________________________________________
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: Floating point literals

Simon Slavin-3
On 31 Jul 2019, at 10:15pm, Eric Reischer <[hidden email]> wrote:

> Is there a way to pass binary representations of floating point numbers to a SQL query?  If sqlite's internal representation of floating point numbers is 8-byte IEEE doubles, it would be convenient to be able to pass the literal value of a float or double to the underlying SQL parser without suffering the quantization that occurs with printf()'ing floating point values.

Rather than including the values in the text of the query:

    INSERT INTO A (B) VALUES (3.1)

use a parameter marker:

    INSERT INTO A (B) VALUES (?)

and bind the value to that parameter:

<https://sqlite.org/c3ref/bind_blob.html>

int sqlite3_bind_double(sqlite3_stmt*, int, double);

This does not involve any rendering of the value into a string.  However, since the C double is not necessarily a representation of IEEE value, you cannot rely on values being passed entirely unchanged.
_______________________________________________
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: Floating point literals

Igor Tandetnik-2
In reply to this post by Eric Reischer
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Floating point literals

Keith Medcalf
In reply to this post by Eric Reischer

sqlite3_bind_double and sqlite3_column_double will round trip IEEE floating point values EXCEPT for NaN.  NaN will be stored as a NULL.

--
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 Eric Reischer
>Sent: Wednesday, 31 July, 2019 15:15
>To: [hidden email]
>Subject: [sqlite] Floating point literals
>
>Is there a way to pass binary representations of floating point
>numbers to
>a SQL query?  If sqlite's internal representation of floating point
>numbers is 8-byte IEEE doubles, it would be convenient to be able to
>pass
>the literal value of a float or double to the underlying SQL parser
>without suffering the quantization that occurs with printf()'ing
>floating
>point values.
>
>One way I've accomplished this in the past with other interfaces is
>to
>interpret a hex value as a binary literal that can be interpreted as
>a raw
>4-byte or 8-byte IEEE floating-point value (either via a union or
>other
>compiler trick).  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.
>
>Can this be done using sqlite3_bind_* interfaces, or do they quantize
>as
>well?  The documentation isn't clear on this.  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.
>
>Thanks in advance.
>_______________________________________________
>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: Floating point literals

Donald Shepherd
Plus (as Igor noted) -0.0 returns as 0.0.

Regards,
Donald Shepherd.

On Thu, 1 Aug 2019 at 08:41, Keith Medcalf <[hidden email]> wrote:

>
> sqlite3_bind_double and sqlite3_column_double will round trip IEEE
> floating point values EXCEPT for NaN.  NaN will be stored as a NULL.
>
> --
> 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 Eric Reischer
> >Sent: Wednesday, 31 July, 2019 15:15
> >To: [hidden email]
> >Subject: [sqlite] Floating point literals
> >
> >Is there a way to pass binary representations of floating point
> >numbers to
> >a SQL query?  If sqlite's internal representation of floating point
> >numbers is 8-byte IEEE doubles, it would be convenient to be able to
> >pass
> >the literal value of a float or double to the underlying SQL parser
> >without suffering the quantization that occurs with printf()'ing
> >floating
> >point values.
> >
> >One way I've accomplished this in the past with other interfaces is
> >to
> >interpret a hex value as a binary literal that can be interpreted as
> >a raw
> >4-byte or 8-byte IEEE floating-point value (either via a union or
> >other
> >compiler trick).  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.
> >
> >Can this be done using sqlite3_bind_* interfaces, or do they quantize
> >as
> >well?  The documentation isn't clear on this.  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.
> >
> >Thanks in advance.
> >_______________________________________________
> >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: Floating point literals

Keith Medcalf
In reply to this post by Igor Tandetnik-2

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-
>[hidden email]] On Behalf Of Igor Tandetnik
>Sent: Wednesday, 31 July, 2019 15:34
>To: [hidden email]
>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
>[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: Floating point literals

Donald Shepherd
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 <[hidden email]> 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-
> >[hidden email]] On Behalf Of Igor Tandetnik
> >Sent: Wednesday, 31 July, 2019 15:34
> >To: [hidden email]
> >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
> >[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: Floating point literals

Keith Medcalf
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-
>[hidden email]] 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 <[hidden email]>
>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-
>> >[hidden email]] On Behalf Of Igor Tandetnik
>> >Sent: Wednesday, 31 July, 2019 15:34
>> >To: [hidden email]
>> >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
>> >[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



_______________________________________________
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: Floating point literals

Donald Shepherd
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 <[hidden email]> 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-
> >[hidden email]] 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 <[hidden email]>
> >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-
> >> >[hidden email]] On Behalf Of Igor Tandetnik
> >> >Sent: Wednesday, 31 July, 2019 15:34
> >> >To: [hidden email]
> >> >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
> >> >[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
>
>
>
> _______________________________________________
> 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: Floating point literals

Simon Slavin-3
In reply to this post by Keith Medcalf
On 31 Jul 2019, at 11:58pm, Keith Medcalf <[hidden email]> wrote:

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

<https://www.sqlite.org/datatype3.html>

in section 3.2 states "Every table column has a type affinity (one of BLOB, TEXT, INTEGER, REAL, or NUMERIC)"

Do you feel that your demonstration (which I find convincing) agrees or disagrees with that statement ?  You seem to have found a sixth column affinity: none.  Certainly columns declared with no affinity do not behave the same as any of the five documented affinities.

Should the documentation be updated ?

What happens if you add a line to your demo code

     for row in db.execute('select x from x ORDER BY x'): print row

Does -0.0 get sorted before or with 0.0 ?  I'd do it myself but I don't know Python.
_______________________________________________
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: Floating point literals

Keith Medcalf

On Wednesday, 31 July, 2019 17:29, Simon Slavin <[hidden email]> wrote:

>On 31 Jul 2019, at 11:58pm, Keith Medcalf <[hidden email]> wrote:

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

><https://www.sqlite.org/datatype3.html>

>in section 3.2 states "Every table column has a type affinity (one of
>BLOB, TEXT, INTEGER, REAL, or NUMERIC)"

Section 3 states:

(Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.)

>Do you feel that your demonstration (which I find convincing) agrees
>or disagrees with that statement ?  You seem to have found a sixth
>column affinity: none.  Certainly columns declared with no affinity
>do not behave the same as any of the five documented affinities.

It agrees.  Columns declared with no affinity behave as if they had been declared with BLOB infinity and v/v.  That means that they do not attempt to convert whatever is presented to be stored as something else.  What is presented is stored as presented.

A column affinity (other than BLOB) means that for affinity X if the thing being stored looks like X and quacks like X and can be losslessly converted to X, then store X (which includes storing floating values that will fit in an integer as integers in order to save space).  Affinity BLOB means to not be doing that -- store what you is given.

>Should the documentation be updated ?

No.  Though maybe it should be put on the quirks page for those very few things that need to distinguish between -0.0 and +0.0

>What happens if you add a line to your demo code
>
>     for row in db.execute('select x from x ORDER BY x'): print row
>
>Does -0.0 get sorted before or with 0.0 ?  I'd do it myself but I
>don't know Python.

No. -0.0 and 0.0 sort equal, as they should because they are equal.  Mostly.

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




_______________________________________________
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: Floating point literals

Simon Slavin-3
On 1 Aug 2019, at 12:55am, Keith Medcalf <[hidden email]> wrote:

> Columns declared with no affinity behave as if they had been declared with BLOB infinity and v/v.

Okay, so leaving out the affinity just lets it default to BLOB.  That clarifies and simplifies things.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users