On Tuesday, 15 March, 2016 07:46, James K Lowden wrote:
> > 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.
Since there is no way to "trap" such errors, out-of-bounds conversions should
return NULL. Just as attempting to divide by zero returns NULL.
> 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.
This would be met by returning NULL. Any operation performed on NULL other
than IS [NOT] NULL results in NULL, so this would carry through to further
computations.
For example, even the operation "select cast(pow(2,65) as integer)" and "select
cast(-pow(2,65) as integer)" should return NULL rather than MAXINT and MININT
respectively.
