Thank you for expanding on your detailed observations.
If you can, please post the long patch at your customary patch site
http://sqlite.chncc.eu/
Also, in the link text, please make note of the exact SQLite version the
patch is for.
The lesson I draw is that unconditionally correct queries must check the
input type for all id range and offset computations.
So unfortunately, if the query depends on id input, it must be wrapped by
inefficient CASE typeof() protection statements.
On the bright side, INT overflow is at least detectable:
.mode line
WITH id AS (SELECT (0x7FFFFFFFFFFFFFFF)id) SELECT
id,typeof(id),id+1,typeof(id+1) FROM id;
-- id = 9223372036854775807
-- typeof(id) = integer
-- id+1 = 9.22337203685478e+18
-- typeof(id+1) = real
Peter
On Thu, Jan 25, 2018 at 12: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
