Hello,
On 2018-01-25 22:08, Stephen Chrzanowski wrote:
According to https://www.sqlite.org/datatype3.html
*2. Storage Classes and DatatypesEach value stored in an SQLite database
(or manipulated by the database engine) has one of the following storage
classes: NULL. The value is a NULL value. INTEGER. The value is a
signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the
magnitude of the value. REAL. The value is a floating point value,
stored as an 8-byte IEEE floating point number. TEXT. The value is a
text string, stored using the database encoding (UTF-8, UTF-16BE or
UTF-16LE). BLOB. The value is a blob of data, stored exactly as it was
input.*
*Each column in an SQLite 3 database is assigned one of the following type
affinities:*
- *TEXT*
- *NUMERIC*
- *INTEGER*
- *REAL*
- *BLOB*
*...*
*A column with NUMERIC affinity may contain values using all five storage
classes. 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.*
*...*
3 paragraphs.
My understanding of this is that since you're using strings as integer
literals, its going to use integer calculations, and not go back to using
"REAL" calculations as you'll be losing information. According to
http://en.cppreference.com/w/cpp/language/types if the engine was able to
predetermine that your string (Which looks like an integer, not a REAL)
it'd have more than a just over (1.7 * 10^308) but you're going to lose a
lot of accuracy as the IEEE specs state that there's 15 digit accuracy, and
you're asking for 23. You can't just stuff that many numbers in a 64-bit
number and maintain accuracy, plus, you're looking at data loss since
you're going from a 23 digit number to a maximum of 15. So according to
documentation, this is working as intended. IMO, the query you gave should
FAULT as its an overrun and what you're asking it to do is impossible at a
64-bit integer level.
For me, it looks like an attorney's speech in a court. From 3 paragraphs
going more or less around a problem you draw conclusions which are
contradictory to the explicit documentation's statement
(https://www.sqlite.org/lang_expr.html#castexpr):
INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
prefix of the value that can be interpreted as an integer number is
extracted from the TEXT value and the remainder ignored. [...] If there
is no prefix that can be interpreted as an integer number, the result of
the conversion is 0.''
To test, I ran this:
C:\Users\schrzanowski.YKF>sqlite3
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019
If this is good, then what prefix of ``12345678901234567890123'' could
be interpreted as ``4807115922877859019''?
sqlite> SELECT CAST('12345678901234567890123.0' AS INTEGER);
4807115922877859019
If this is good, then what prefix of ``12345678901234567890123'' could
be interpreted as ``4807115922877859019''?
I'm not a developer of SQLite3 by any stretch of the imagination, so I
can't say for certain whether your patch is going to be accepted or not.
In my view, I've never had to deal with numbers that large, and I suspect
this is going to end up being looked at as an edge case.
Ok, to summarize, in your opinion, (a) ``CAST('...' AS INTEGER) => (not
so) quite random number [NSQRN follows]'' and (b) losing of an info
(900...001 => 900...000, etc.) is good (or, at least better then (a) ...
=> {SMALLEST,LARGEST}_INT64; (b) not losing of an info), because:
(a) It is impossible to express some number '112676...878676878' by a
64bit variable, then
(a.1) CAST('toobignum' AS INTEGER) => [NSQRN]) is ok and conforming to
the doc (``working as intended''). CAST('toobignumstring' AS INTEGER) =>
LARGEST_INT64) is not ok and not conforming to the doc (``not working as
intended''), if so, then report that CAST of string numbers
<'9223372036854775809';'18446744073709551615'> AS INTEGER ``is not
working as intended'', or
(a.2) both ways are ``working as intended'', if so, then I proposed a
patch which requires 5 characters and is making a SQLite behave in a
more (IMHO) reasonable way;
(b) I'm sorry - I haven't got your point here. Appending a trailing
space would lose 10 bits because 'toobignumstring' lose accuracy when
converting to FLOAT?? There is other thing then (a). I did not postulate
to convert '4444444444444444444444444444444444444444444444444444' into
exact INT64 value. I agree with you that it is impossible. Simply, I do
not agree that appending a space to a number which _can_ be fully
represented causes that the number needlessly loses an accuracy, and
have proposed the (second part of the) patch which requires copying of
one line of code with less then 20 chars of change, that's all. (Though
it does not solves the problem in full.)
And at the end: an introduction of such small changes is not worth of
price, because you are not using such big numbers and are classifying
the problem as an edge case? Really, is not it? I do not understand why
are you bringing that argument up. Some kind of argument by an authority?
As you said, probably the problem is an edge one. Probably most people
contending with the problem have found more or less trivial solution. I
have just shared my observations of a bit strange behavior and proposed
a solution. What are you trying to say? That [NSQRN] as a result of
conversion of a 'bignumstring' is better that saturated value MIN/MAX.
That needless loss of info without any benefits is good?
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
