Hello,
1. Documentation at ``lang_expr.html#castexpr'' (NUMERIC row) says that:
``Casting a TEXT or BLOB value into NUMERIC first does a forced
conversion into REAL'',
which is inconsistent with ``datatype3.html#affinity'':
``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)''.
The latter description is true for CAST too, as
``sqlite3VdbeMemNumerify'' tries to convert to INTEGER storage in the
first place.
2. ``sqlite3Atoi64'' does not skip trailing spaces which results in:
``SELECT CAST(' 9000000000000000001' AS NUMERIC);'' =>
9000000000000000001, while
``SELECT CAST(' 9000000000000000001 ' AS NUMERIC);'' => 9000000000000000000.
Above case concerns ``INSERT''s into columns with NUMERIC affinity as well.
3. ``sqlite3Atoi64'' is not aware of overflows which results in:
``SELECT CAST('100000000000000000000' AS INTEGER);'' => 7766279631452241920.
This bug could become exploitable. To be consistent with ``the longest
possible prefix of the value that can be interpreted as an integer
number'' it should return 1000000000000000000 rather then
9223372036854775807, as '.1e2' gives 0 --- not 10. I'm considering if it
would be more flexible if a TEXT=>INTEGER conversion gone through an
intermediate REAL (if required) and resulted in MIN/MAX INT64 --- would
not be? .1e2 => 10, '.1e2' => 0 --- is it a non-consequence?
4. I'm not sure if I'm thinking right. However, REAL/INTEGER selection
on ,,you are not a perfect int so be a real'' basis is a gross
exaggeration. For example:
``SELECT CAST('9000000000000000001x' AS NUMERIC);'' => 9000000000000000000.
Additional return status for ``sqlite3Atoi64'', which means ``I have
found no perfect INTEGER, however trying a REAL would not give better
results'', and which is recognized by ``sqlite3VdbeMemNumerify'' and a
few other places should resolve this problem.
I'm a new, thus not so skilled in SQLite, so in a few hours I will
provide code corrections (pts 2 and 4, as I have no idea on how to deal
with the 3rd --- MIN/MAX INT64, or MIN/MAX INT64 + TEXT=>REAL=>INTEGER
or simply 1000000000000000000; in the 3rd case, ``sqlite3Atoi64'' must
behave differently depending on a context, and in the 2st case, an
implementation of additional behavior is required).
-- best regards
Cezary H. Noweta
