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
