On Wednesday, 10 April, 2019 09:06. Joshua Thomas Wise <joshuathomasw...@gmail.com> wrote:
>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. Well, taking a quick peek at the source code it appears that the internal OPCodes for Add/Subtract/Multiply/Divide/Remainder are handled starting at line 1538 in vdbe.c. If the arguments are BOTH integer AND if an error occurs (which would be an integer overflow/underflow) then the implementation jumps ahead to code that converts the arguments to floating-point and does the operations in floating-point. Thereafter of course the type of the contents of the register is changed and different code paths are followed. It would seem to me to be a relatively simple matter to make those "goto fp_math" goto a routine that sets the result register to NULL (for example) or otherwise causes a crash-and-burn. Of course, if one wanted to make this change applicable to all users of the library everywhere it would require extensive testing before such a change could be released. However, for use in your OWN application, I do not think the changes would be that significant -- probably a lot less time than the 30 minutes I spent reading the code. Now if you wanted the "no type escalation" to be handled by a pragma so you can turn it on and off, that will require even more time and to make such changes to the actual released code base would require even more extensive testing. And thinking about how one would want it to work. >> 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! --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
