On Mon, 14 Mar 2016 13:25:09 +0100
Clemens Ladisch <clemens at ladisch.de> wrote:
> > that ``SELECT CAST(col AS INTEGER);'' should return (not so) random
> > result set, and receiving any INTEGER should mean that a source
> > string could have trillion or more possible values?
>
> The documentation does not specify how the textual representation of
> an out-of-range integer is to be converted, so anything SQLite does
> can be considered correct.
While I'm sympathetic to the GIGO notion that invalid input (a domain
error, in this case) is bound to produce unpredictable output, you have
to admit there's some inconsistency here. I would argue the
inconsistency is harmful, and would be better thought of as a
constraint that needs to be enforced. Consider:
1) sqlite> select cast('100000000000000000000' as float);
cast('100000000000000000000' as float)
--------------------------------------
1.0e+20
2) sqlite> select cast('100000000000000000000' as int);
cast('100000000000000000000' as int)
------------------------------------
7766279631452241920
3) sqlite> select 100000000000000000000;
100000000000000000000
---------------------
1.0e+20
4) sqlite> select 10000000000 * 10000000000 ;
10000000000 * 10000000000
-------------------------
1.0e+20
5) sqlite> select cast(10000000000 * 10000000000 as int) ;
cast(10000000000 * 10000000000 as int)
--------------------------------------
9223372036854775807
6) sqlite> select cast(100000000000000000000 as text);
cast(100000000000000000000 as text)
-----------------------------------
1.0e+20
7) sqlite> select cast(cast(100000000000000000000 as text) as
int);
cast(cast(100000000000000000000 as text) as int)
------------------------------------------------
1
Before you say, "don't do that", keep in mind that sum() could produce
the same integer-overflow result, as could string concatenation. If
SQLite produces erroneous results, and places the burden of error
detection -- errors that orginate in the data -- on the application.
It's hard to justify any of the above. Integer arithmetic produces
floating point if the result can't be represented as an integer?
That's dubious enough. When forced to be an integer, it's 2^63. The
application could reasonable expect that integer arithmetic results in
an integer (as it normally does); when the double is returned instead,
it will still call sqlite3_column_int with its implicit cast. No
error, just MAX_INT.
In 7, we convert an integer to text and back to int, and get 1.
I understand why, mechanically. But I would say any form of
T -> TEXT -> T
conversion should be lossless, for any type T, given sufficient width
of TEXT.
The problems could be avoided by observing simple but strict rules:
1. Integer arithmetic produces integer result. Error on overflow.
2. CAST raises a range error if the value would be outside the range
of the target type.
3. Implicit conversion by e.g. sqlite3_column_* adhere to the same
rule as CAST.
--jkl
