This is not enough. Because of implicit casting, an integer (a precise value)
could be passed through a series of operations that outputs an integer,
satisfying the check constraint, but it still could’ve been converted to a
floating point (imprecise value) at some intermediate step due to integer
overflow, potentially resulting in an incorrect answer. There’s currently no
way to guarantee that a value will always yield precise results in SQLite3.
Here’s an example:
CREATE TABLE squares (
x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
);
INSERT INTO squares VALUES (1 << 40, 1 << 40);
SELECT x * y & ~1 AS even_numbered_area FROM squares;
In many cases, it’s better for the above SELECT statement to return an error or
NULL, but currently it gives an incorrect answer. Checking its type won’t help
either, because it does indeed return an integer.
> On Apr 9, 2019, at 2:06 PM, James K. Lowden <[email protected]> wrote:
>
> On Mon, 8 Apr 2019 23:08:18 -0400
> Joshua Thomas Wise <[email protected]> wrote:
>
>> I propose there should be a compile-time option to disable all
>> implicit casting done within the SQL virtual machine.
>
> You can use SQLite in a "strict" way: write a CHECK constraint for
> every numerical column.
>
> Just don't do that for tables that are loaded by the .import comand.
> As I reported here not long ago, .import rejects numeric literals.
> Apparently, the value is inserted as a string and rejected, instead of
> being converted to a number first.
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
