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.* *...* 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. 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 sqlite> SELECT CAST('12345678901234567890123.0' AS INTEGER); 4807115922877859019 sqlite> SELECT CAST('12345678901234567890123.0' AS REAL); 1.23456789012346e+22 sqlite> SELECT CAST('12345678901234567890123' AS REAL); 1.23456789012346e+22 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. On Thu, Jan 25, 2018 at 3:36 PM, Cezary H. Noweta <c...@poczta.onet.pl> wrote: > Hello, > > On 2018-01-25 19:54, petern wrote: > >> CREATE TABLE IF NOT EXISTS a (a INTEGER); >> > > INTEGER == NUMERIC in case of column declarations. > > -- Note however, the constant table expression works fine... >> >> SELECT CAST(column1 AS INTEGER) FROM (VALUES >> ('9000000000000000001'),('9000000000000000001 ')); >> -- "CAST(column1 AS INTEGER)" >> -- 9000000000000000001 >> -- 9000000000000000001 >> > > This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' and > returns its result. > > INTEGER/FLOAT handling/recognition is a bit more complicated in other > places, what causes that '9000000000000000001 ' will become 9e18. > > For the same reason ``CAST ('9000000000000000001X' AS INTEGER)'' gives INT > 9000000000000000001, while ``SELECT CAST ('9000000000000000001X' AS > NUMERIC);'' gives FLOAT 9e18. > > Due to a bit disordered treatment of values, my own patch involves many > changes. The ``concise'' patch, which I proposed in my original post, > eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and > FLOATs in some (not all) places. It changes only one line and adds one, and > does not change affinity/type system at all. (As opposed to my ``long'' > version patch). > > 1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a > dangerous number ``1234'' and is checking input text against it; then > ``295147905179352827090'' -- OK, go on -- says my app; then suddenly > ``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234. > > 2. STRING to be INT must be reacting to ``attention'' command immediately > (even small, chaste space at the end will bother); to be FLOAT, it can > carousel from dusk till next dusk all the time. > > There was no noticeable side effects (besides a performance) in old 32bit > INT days, however now, 10 bits of each INT can be going to a vacuum. > > > -- best regards > > Cezary H. Noweta > _______________________________________________ > 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
