Possible bug in storing text values in numeric columns

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

Possible bug in storing text values in numeric columns

Shawn Wagner
The documentation for a column with NUMERIC affinity says

> When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such
conversion is lossless and reversible.

But consider:

sqlite> create table foo(bar numeric);
sqlite> insert into foo values ('0012');
sqlite> select bar, typeof(bar) from foo;bar         typeof(bar)
----------  -----------12          integer


As you can see, the leading zeros in the original string are gone and it's
been converted to an integer. This seems to violate the "lossless and
reversible" constraint. Shouldn't it be kept as text?
_______________________________________________
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: Possible bug in storing text values in numeric columns

Igor Korot
Hi,

On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner <[hidden email]> wrote:

>
> The documentation for a column with NUMERIC affinity says
>
> > When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.
>
> But consider:
>
> sqlite> create table foo(bar numeric);
> sqlite> insert into foo values ('0012');
> sqlite> select bar, typeof(bar) from foo;bar         typeof(bar)
> ----------  -----------12          integer
>
>
> As you can see, the leading zeros in the original string are gone and it's
> been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?

What version of SQLite do you use?

Thank you.

> _______________________________________________
> 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: Possible bug in storing text values in numeric columns

Shawn Wagner
3.30, though it goes back to at least 3.8.7 (the oldest version I have
available to test with)

On Sun, Oct 13, 2019, 3:03 PM Igor Korot <[hidden email]> wrote:

> Hi,
>
> On Sun, Oct 13, 2019 at 4:12 PM Shawn Wagner <[hidden email]>
> wrote:
> >
> > The documentation for a column with NUMERIC affinity says
> >
> > > When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
> >
> > But consider:
> >
> > sqlite> create table foo(bar numeric);
> > sqlite> insert into foo values ('0012');
> > sqlite> select bar, typeof(bar) from foo;bar         typeof(bar)
> > ----------  -----------12          integer
> >
> >
> > As you can see, the leading zeros in the original string are gone and
> it's
> > been converted to an integer. This seems to violate the "lossless and
> > reversible" constraint. Shouldn't it be kept as text?
>
> What version of SQLite do you use?
>
> Thank you.
>
> > _______________________________________________
> > 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: Possible bug in storing text values in numeric columns

Igor Tandetnik-2
In reply to this post by Shawn Wagner
On 10/13/2019 5:11 PM, Shawn Wagner wrote:
> The documentation for a column with NUMERIC affinity says
>
>> When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.

"Lossless and reversible" here clearly means that the numerical value is preserved, not that the exact text representation is. Thus, a couple paragraphs down in the same article, you'll find

"A string might look like a floating-point literal with a decimal point and/or exponent notation but as long as the value can be expressed as an integer, the NUMERIC affinity will convert it into an integer. Hence, the string '3.0e+5' is stored in a column with NUMERIC affinity as the integer 300000, not as the floating point value 300000.0."

It is clear in this example that '3.0e+5' will in fact get coerced to a number, and that number will *not* in fact be rendered as '3.0e+5' when converted back to text.
--
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: Possible bug in storing text values in numeric columns

Shawn Wagner
I wouldn't call that conversion, or any other, lossless unless it can be
turned back into a string that's character for character identical with the
one that was originally inserted.

On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik <[hidden email]> wrote:

> On 10/13/2019 5:11 PM, Shawn Wagner wrote:
> > The documentation for a column with NUMERIC affinity says
> >
> >> When text data is inserted into a NUMERIC column, the storage class of
> > the text is converted to INTEGER or REAL (in order of preference) if such
> > conversion is lossless and reversible.
>
> "Lossless and reversible" here clearly means that the numerical value is
> preserved, not that the exact text representation is. Thus, a couple
> paragraphs down in the same article, you'll find
>
> "A string might look like a floating-point literal with a decimal point
> and/or exponent notation but as long as the value can be expressed as an
> integer, the NUMERIC affinity will convert it into an integer. Hence, the
> string '3.0e+5' is stored in a column with NUMERIC affinity as the integer
> 300000, not as the floating point value 300000.0."
>
> It is clear in this example that '3.0e+5' will in fact get coerced to a
> number, and that number will *not* in fact be rendered as '3.0e+5' when
> converted back to text.
> --
> 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: Possible bug in storing text values in numeric columns

Simon Slavin-3
In reply to this post by Shawn Wagner
On 13 Oct 2019, at 10:11pm, Shawn Wagner <[hidden email]> wrote:

> As you can see, the leading zeros in the original string are gone and it's been converted to an integer. This seems to violate the "lossless and reversible" constraint. Shouldn't it be kept as text?

You defined the column as 'numeric'.  Had you defined it as 'text' you would get different behaviour.
_______________________________________________
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: Possible bug in storing text values in numeric columns

Shawn Wagner
Yes, I know. (This all comes from someone on stack overflow who used
"string" as a column type and thus ran into this issue because that of
course results in numeric affinity)

On Sun, Oct 13, 2019, 4:27 PM Simon Slavin <[hidden email]> wrote:

> On 13 Oct 2019, at 10:11pm, Shawn Wagner <[hidden email]> wrote:
>
> > As you can see, the leading zeros in the original string are gone and
> it's been converted to an integer. This seems to violate the "lossless and
> reversible" constraint. Shouldn't it be kept as text?
>
> You defined the column as 'numeric'.  Had you defined it as 'text' you
> would get different behaviour.
> _______________________________________________
> 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: Possible bug in storing text values in numeric columns

J. King-3
In reply to this post by Shawn Wagner
On October 13, 2019 7:25:50 p.m. EDT, Shawn Wagner <[hidden email]> wrote:
>I wouldn't call that conversion, or any other, lossless unless it can
>be
>turned back into a string that's character for character identical with
>the
>one that was originally inserted.

It's lossless if you consider the input as the number itself, not as a specific representation of that number.
--
J. King
_______________________________________________
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: Possible bug in storing text values in numeric columns

Keith Medcalf
In reply to this post by Shawn Wagner

sqlite> create table x(x numeric);
sqlite> insert into x values ('0012');
sqlite> select typeof(x), x from x;
integer|12
sqlite> select printf('%04d', x) from x;
0012

Presentation is a user/application problem.  Not a database data problem.

--
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 <[hidden email]> On
>Behalf Of Shawn Wagner
>Sent: Sunday, 13 October, 2019 17:26
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Possible bug in storing text values in numeric
>columns
>
>I wouldn't call that conversion, or any other, lossless unless it can be
>turned back into a string that's character for character identical with
>the
>one that was originally inserted.
>
>On Sun, Oct 13, 2019, 4:10 PM Igor Tandetnik <[hidden email]> wrote:
>
>> On 10/13/2019 5:11 PM, Shawn Wagner wrote:
>> > The documentation for a column with NUMERIC affinity says
>> >
>> >> When text data is inserted into a NUMERIC column, the storage class
>of
>> > the text is converted to INTEGER or REAL (in order of preference) if
>such
>> > conversion is lossless and reversible.
>>
>> "Lossless and reversible" here clearly means that the numerical value
>is
>> preserved, not that the exact text representation is. Thus, a couple
>> paragraphs down in the same article, you'll find
>>
>> "A string might look like a floating-point literal with a decimal point
>> and/or exponent notation but as long as the value can be expressed as
>an
>> integer, the NUMERIC affinity will convert it into an integer. Hence,
>the
>> string '3.0e+5' is stored in a column with NUMERIC affinity as the
>integer
>> 300000, not as the floating point value 300000.0."
>>
>> It is clear in this example that '3.0e+5' will in fact get coerced to a
>> number, and that number will *not* in fact be rendered as '3.0e+5' when
>> converted back to text.
>> --
>> 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: Possible bug in storing text values in numeric columns

Igor Tandetnik-2
In reply to this post by Shawn Wagner
On 10/13/2019 7:25 PM, Shawn Wagner wrote:
> I wouldn't call that conversion, or any other, lossless unless it can be
> turned back into a string that's character for character identical with the
> one that was originally inserted.

If you want the text preserved character for character, store it in a column with TEXT affinity. NUMERIC makes little sense for this requirement.
--
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: Possible bug in storing text values in numeric columns

Shawn Wagner
That's what I told the guy having the original issue to do, yes. That's not
important.

My concern is why a conversion that's only supposed to happen if it's
lossless is in fact happening and causing data loss.

On Sun, Oct 13, 2019, 4:48 PM Igor Tandetnik <[hidden email]> wrote:

> On 10/13/2019 7:25 PM, Shawn Wagner wrote:
> > I wouldn't call that conversion, or any other, lossless unless it can be
> > turned back into a string that's character for character identical with
> the
> > one that was originally inserted.
>
> If you want the text preserved character for character, store it in a
> column with TEXT affinity. NUMERIC makes little sense for this requirement.
> --
> 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: Possible bug in storing text values in numeric columns

Igor Tandetnik-2
On 10/13/2019 8:04 PM, Shawn Wagner wrote:
> That's what I told the guy having the original issue to do, yes. That's not
> important.
>
> My concern is why a conversion that's only supposed to happen if it's
> lossless is in fact happening and causing data loss.

You define the term "lossless" differently than that article does. The conversion is lossless under the article's definition, even while it's not lossless under the definition you insist upon (but which makes no sense for a column of NUMERIC affinity).
--
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: Possible bug in storing text values in numeric columns

Richard Hipp-3
In reply to this post by Shawn Wagner
On 10/13/19, Shawn Wagner <[hidden email]> wrote:
> The documentation for a column with NUMERIC affinity says
>
>> When text data is inserted into a NUMERIC column, the storage class of
> the text is converted to INTEGER or REAL (in order of preference) if such
> conversion is lossless and reversible.
>

Thank you for the bug report.

The term "lossless" in the documentation is certainly very confusing
as it was used. Therefore the documentation has been revised to avoid
using the word "lossless" and to be more precise about when automatic
type conversions occur and when they do not.

I consider this to be a documentation bug, not an SQLite bug.

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: [EXTERNAL] Possible bug in storing text values in numeric columns

Hick Gunter
In reply to this post by Shawn Wagner
You are getting exactly what is documented and exactly what you asked for.

Declaring a column NUMERIC means you intend to store NUMBERS. Leading zeros do not change the value of a number. 0012 == 12 unless you have a convention of interpreting a leading zero as indicating octal base.

If you need to display numbers zero filled on the left, that would be the task of the presentation layer. See also the printf() function.

Lossless and reversible means 15 significant digits. Leading zeros are NOT significant.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Shawn Wagner
Gesendet: Sonntag, 13. Oktober 2019 23:12
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Possible bug in storing text values in numeric columns

The documentation for a column with NUMERIC affinity says

> When text data is inserted into a NUMERIC column, the storage class of
the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible.

But consider:

sqlite> create table foo(bar numeric);
sqlite> insert into foo values ('0012');
sqlite> select bar, typeof(bar) from foo;bar         typeof(bar)
----------  -----------12          integer


As you can see, the leading zeros in the original string are gone and it's been converted to an integer. This seems to violate the "lossless and reversible" constraint. Shouldn't it be kept as text?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users