When you need a feature-packed embedded SQL database, there aren’t many other options to reach for. I’m not suggesting that SQLite3 has a responsibility to satisfy every need just because it has beat out most other competition, but I’m in a situation where either I write every elementary integer operation as a custom function and convince my entire team to ONLY use those functions, or I write my own fork of SQLite3. Of course, option 1 will be much easier, but it’s also very messy and awkward. It would just be nice if SQLite3, being a relational database that seems to take database corruption very seriously, could provide some guarantees about the precision of answers given for basic arithmetic.
> On Apr 10, 2019, at 10:55 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise > <joshuathomasw...@gmail.com <mailto:joshuathomasw...@gmail.com>> 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; > >> 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. > > The answers are correct. SELECT x * y from squares returns the correct > result, as does the bitwise and with -2. > > If you wish to "be able to multiply two 64-bit integers" entirely in the > integer domain with the overflow doing something other than "convert the > arguments into floating point and do the operation in floating point" then > you are completely free to write a function that does precisely and exactly > what you think you want it to do and behave and do precisely that which you > wish. It is so simple to do that during the time taken to read your message > and compose this response I could have written pretty much all the basic > operators written this way, compiled, tested, and moved the code into > production. > > Some languages when multiplying a trail of 47 64-bit integers might return a > 3000 bit integer. Others might explode. Some might cause the universe to > reach heat death. SQLite3 attempts to do what you told it to do by > converting the overflowing operands into floating point, and then using > floating point. > > If you do not like that you are free to either (a) write your own > multiplication function that works the way you think it ought to work, and > use that or (b) use something else that is more akin to your liking. > > I do not like Java. It is long winded, requires a 400" monitor to be able to > see anything at all, and is just about the stupedest hunk of crap that I have > ever seen in my entire life. Since I have no wish to "fix" it, I just use > something more apropos. Mutatis mutandis JavaCripple / Rust / Go / Varnish / > JollyGoodCrap / C# / Cflat and most of the other newfangled hogwash -- I > will stick to C, PL/1, COBOL, FORTRAN and RPG thank-yee-very-much! > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > <mailto:sqlite-users@mailinglists.sqlite.org> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > <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
