On 11/04/2019 00:28, Joshua Thomas Wise wrote:
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;
Suggestion: "Don't Do That", use database purely as a storage medium.
If the Integer values you want to store are greater than the 64bit
values accepted by SQLite then store them as BLOBs.
If the mathematical manipulations you wish to apply in your queries are
beyond the scope of the built-in functions, then just return the stored
values to your external programming environment and manipulate them there.
You would seem to be working in an edge case environment, in which case
it is your responsibility to make the adjustments.
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 <jklow...@schemamania.org> wrote:
On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise <joshuathomasw...@gmail.com> 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
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
--
Regards
John McMahon
li...@jspect.fastmail.fm
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
