On Tue, 15 Mar 2016 01:02:17 +0100
"Cezary H. Noweta" <chn at poczta.onet.pl> wrote:
> 2nd row: why REALs can have trailing spaces, while INTEGERs cannot?
> 3rd row: why REALs can have trailing trash, while INTEGERs cannot?
I think we know now that string->integer conversion is pathologically
broken for inputs that cannot be represented as a 64-bit integer.
Appending '0' to any such string produces a different, illogical
result, too.
> While fixing, I spotted a problem mentioned by you:
>
> > 2) sqlite> select cast('100000000000000000000' as int);
...
> > 7766279631452241920
>
> It would be:
>
> 1. left as is, i.e. CAST(manydigitstext AS INTEGER) ==
> MAX(TEXT2INT(manydigitstext)%2^64,LARGEST_INT64) --- Clemens Ladisch'
> opinion;
That is not acceptable IMO. Conversions should be reversible,
including string->integer->string. Conversions should also be
equivalent, and no one is claiming '100000000000000000000' == 2^64.
> 2. CAST(manydigitstext AS INTEGER) == {SMALLEST_INT64,LARGEST_INT64}
> --- your opinion as I understood you well;
Yes, if the string cannot be represented as an integer, CAST should
raise a range error. That spares the application from applying the
same test in an ad hoc and inconsistent way.
To my way of thinking, SQLite's handling of giant integers per se
is an edge case. Because such huge numbers don't normally arise, the
non-error path (inputs in bounds) almost always produces correct
results. The reason to have CAST raise a range error when the output
would be invalid is to guard against erroneous inputs creating spurious
outputs that, when used in further computation, produce inexplicable
results. It's better for the user and programmer to detect the error
early -- nearest the cause, where it can be diagnosed -- than to
produce a bogus answer and be forced into a manual recapitulation of the
processing.
--jkl
