Confirmed.
sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.8
gcc-4.8.4
--FYI some background:
------------------------------
--min 64b signed int:
SELECT CAST(0x8000000000000000 AS INTEGER);
-- "CAST(0x8000000000000000 AS INTEGER)"
-- -9223372036854775808
--max 64b signed int:
SELECT CAST(0x7FFFFFFFFFFFFFFF AS INTEGER);
-- "CAST(0x7FFFFFFFFFFFFFFF AS INTEGER)"
-- 9223372036854775807
-- Note how place counts align with numeric example:
-- 9223372036854775807
-- 9000000000000000001
-- I reproduce problem with spurious CAST due to trailing zero as follows:
-------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS a (a INTEGER);
INSERT INTO a VALUES ('9000000000000000001'),('9000000000000000001 ');
SELECT * FROM a;
-- a
-- 9000000000000000001
-- 9000000000000000000
-- Note however, the constant table expression works fine...
SELECT CAST(column1 AS INTEGER) FROM (VALUES
('9000000000000000001'),('9000000000000000001 '));
-- "CAST(column1 AS INTEGER)"
-- 9000000000000000001
-- 9000000000000000001
-------------------------------------------------------------------------------------------
Peter
On Thu, Jan 25, 2018 at 9:14 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:
> RE the first question, the largest positive 64-bit number is
> 18446744073709551615, or, 18,446,744,073,709,551,615. Your number is
> overloading that value.
> 12,345,678,901,234,567,890,123
> vs
> 18,446,744,073,709,551,615
>
> AFAIK, SQLite uses max of 64-bit integer math, not 128.
>
>
> On Thu, Jan 25, 2018 at 10:04 AM, Cezary H. Noweta <c...@poczta.onet.pl>
> wrote:
>
> > Hello,
> >
> > About year age I reported some strange behavior:
> >
> > 1.
> >
> > 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.''
> >
> > sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
> > 4807115922877859019
> >
> > What prefix of ``12345678901234567890123'' can be interpreted as
> > ``4807115922877859019''?
> >
> > 2.
> >
> > Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
> > not be a problem, if INTs were 32bit. Now, a value is lost:
> >
> > sqlite> CREATE TABLE a (a NUMERIC);
> > sqlite> INSERT INTO a VALUES ('9000000000000000001'),('
> 9000000000000000001
> > ');
> > sqlite> SELECT * FROM a;
> > 9000000000000000001
> > 9000000000000000000
> >
> > 2a.
> >
> > https://www.sqlite.org/lang_expr.html#castexpr:
> >
> > NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
> > conversion into REAL but then further converts the result into INTEGER if
> > and only if the conversion from REAL to INTEGER is lossless and
> > reversible.''
> >
> > Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
> > INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing
> a
> > INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
> >
> > sqlite> SELECT CAST('9000000000000000001' AS NUMERIC);
> > 9000000000000000001
> >
> > However due to pt 2.:
> >
> > sqlite> SELECT CAST('9000000000000000001 ' AS NUMERIC);
> > 9000000000000000000
> >
> > The most concise patch (without, for example ``SELECT
> > CAST('9000000000000000001X' AS NUMERIC);'') contains only two lines:
> > ======
> > --- sqlite-src-3220000/src/util.c 2018-01-23 01:57:26.000000000
> +0100
> > +++ sqlite-src-3220000/src/util.c 2018-01-25 14:22:18.428460300
> +0100
> > @@ -625,6 +625,7 @@
> > zNum += (enc&1);
> > }
> > while( zNum<zEnd && sqlite3Isspace(*zNum) ) zNum+=incr;
> > + while( zNum<zEnd && sqlite3Isspace(zEnd[-incr]) ) zEnd-=incr;
> > if( zNum<zEnd ){
> > if( *zNum=='-' ){
> > neg = 1;
> > @@ -638,7 +639,7 @@
> > for(i=0; &zNum[i]<zEnd && (c=zNum[i])>='0' && c<='9'; i+=incr){
> > u = u*10 + c - '0';
> > }
> > - if( u>LARGEST_INT64 ){
> > + if( 19 < i || u>LARGEST_INT64 ){
> > *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
> > }else if( neg ){
> > *pNum = -(i64)u;
> > ======
> >
> > -- 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
