Re: [sqlite] Possible bug in storing text values in numeric columns
On 10/13/19, 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. > 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
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 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 > 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] Possible bug in storing text values in numeric columns
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
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 On
>Behalf Of Shawn Wagner
>Sent: Sunday, 13 October, 2019 17:26
>To: SQLite mailing list
>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 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
>> 30, not as the floating point value 30.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
>> 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] Possible bug in storing text values in numeric columns
On October 13, 2019 7:25:50 p.m. EDT, 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. 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
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 wrote: > On 13 Oct 2019, at 10:11pm, Shawn Wagner 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 > 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] Possible bug in storing text values in numeric columns
On 13 Oct 2019, at 10:11pm, Shawn Wagner 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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 > 30, not as the floating point value 30.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 > 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] Possible bug in storing text values in numeric columns
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 30, not as the floating point value 30.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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in storing text values in numeric columns
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 wrote:
> Hi,
>
> On Sun, Oct 13, 2019 at 4:12 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.
> >
> > 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
> > 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] Possible bug in storing text values in numeric columns
Hi,
On Sun, Oct 13, 2019 at 4:12 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.
>
> 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
> 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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
