Re: [sqlite] Option to control implicit casting

[James K. Lowden]

On Thu, 11 Apr 2019 11:35:04 +1000
John McMahon  wrote:

> > SELECT x * y & ~1 AS even_numbered_area FROM squares;
> 
> Suggestion: "Don't Do That", use database purely as a storage medium.

You yourself don't really believe that!  

A disk is a storage medium.  A file is an undifferntiated stream of
bytes.  A SQLite database is much more than that, as I'm sure you'll
agree. Not least, it includes a query language with a passing
resemblance to first order predicate logic and set theory.  

> You would seem to be working in an edge case environment, in which
> case it is your responsibility to make the adjustments.

Actually, the OP was illustrating how implicit conversion to double
yields incorrect results instead of a domain error.  I somehow doubt he
has much use for 10^80 on a regular basis.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[John McMahon]

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  wrote:

On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise  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

Re: [sqlite] Option to control implicit casting

[Warren Young]

On Apr 8, 2019, at 9:08 PM, Joshua Thomas Wise  
wrote:
> 
> there should be a compile-time option to disable all implicit casting done 
> within the SQL virtual machine.

That’d be nice, especially when using SQLite with a strongly- and 
statically-typed programming language and a statically-typed DB interface (e.g. 
ORM), so that the correct mapping is always fixed and known at compile time, so 
the extra flexibility buys that programmer nothing.

Keep in mind that SQLite was born as a Tcl extension, so it shares its 
extremely loose concepts of data typing.

> The option could cause all type-incompatible operations to return NULL

That would overload the already-overloaded keyword NULL with still another 
meaning.  To SQLite, NULL already means:

1. No value given on input.

2. No corresponding value in the “B” table on LEFT JOIN with table “A”.

Then on top of that, there are the libraries that try to map the language’s 
notions of NULL/nullptr/nil, etc. to and from SQL.

Let’s not add still another meaning.

> it could cause these operations to throw hard errors

That’s a much better plan.

> 1. If invoking SUM() would cause integer overflow

…then you’re using the wrong data type.

If you have reason to believe that an idealized implementation of SUM() could 
produce  values of 19 digits or larger given your data, you should not be using 
INTEGER, period.  You might want to be using the recently-announced DECIMAL 
extension instead:

   https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index

> 2. Many built-in string functions will automatically cast BLOBs to TEXTs

You’ll have my sympathy if you can show a reasonable path where a TEXT column 
can have some of its values accidentally or implicitly converted to BLOB.

As far as I’m aware, your stated problem only occurs when you purposefully do 
that.  You have no sympathy from me if you shoot a hole in your foot while 
*aiming* at it. :)

> 3. Declaring a column with INTEGER affinity does not actually force its 
> values to be integers.

That’s only a problem if you don’t give SQLite integers as input, and if it you 
don’t, this falls under one of the oldest laws of computing: “If you lie to the 
computer, it usually finds a way to get its revenge.”
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[Keith Medcalf]

On Wednesday, 10 April, 2019 09:06. Joshua Thomas Wise 
 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 
>wrote:
>>
>>
>> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise
>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 

Re: [sqlite] Option to control implicit casting

[Joshua Thomas Wise]

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  wrote:
> 
> 
> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise 
> 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 
> 
> 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

Re: [sqlite] Option to control implicit casting

[Keith Medcalf]

On Wednesday, 10 April, 2019 08: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;

>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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[Joshua Thomas Wise]

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  wrote:
> 
> On Mon, 8 Apr 2019 23:08:18 -0400
> Joshua Thomas Wise  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

Re: [sqlite] Option to control implicit casting

[James K. Lowden]

On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise  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

Re: [sqlite] Option to control implicit casting

[R Smith]

On 2019/04/09 5:08 AM, Joshua Thomas Wise wrote:

SQLite3 uses manifest typing, which is great and provides a ton of flexibility. 
However, due to implicit casting rules, many operations can accidentally result 
in a different value than what was desired. If programmers don’t guard against 
every possible cause of implicit casting, many error situations are swallowed 
and instead can result in data corruption. I propose there should be a 
compile-time option to disable all implicit casting done within the SQL virtual 
machine. The option could cause all type-incompatible operations to return 
NULL, or it could cause these operations to throw hard errors. Either approach 
would be similarly useful.

Here are some examples of how implicit casting can lead to surprising results:

1. If invoking SUM() would cause integer overflow, a hard error is returned. 
The same things happens with ABS(). However, if integer overflow occurs when 
using the + operator, a REAL value is returned instead.

2. Many built-in string functions will automatically cast BLOBs to TEXTs, but 
those could contain embedded nuls, leading to undefined behavior.

3. Declaring a column with INTEGER affinity does not actually force its values 
to be integers. An integer that is out of range could be stored as a REAL 
value, unexpectedly changing the behavior of functions such as SUM() and ABS().



TLDR: We feel your pain, but it's probably not happening...

We have been forever lobbying for a "STRICT" mode in SQLite (search for 
"strict" in the archives, the hits are legion), something that would 
error out if you shove a string into an Integer declared column, not 
accept double-quotes around non-identifiers, force aggregate queries to 
use aggregate functions, start with Foreign_keys enabled... the list 
goes on. Actually the list doesn't go on that much, there's just a few 
things that break the SQL.


The reason this is regarded as broken is that in most cases in SQL 
(SQLite included) the people here, myself included, would advise you 
along the lines of "Do not try to think for the SQL engine, give the 
query and let it do its thing.' - which is the greatest advice in the 
spirit of RDBMS - but in SQLite's case this specific advice cannot 
always be given, because now in some cases it is up to you to 
specifically HAVE to think for the engine and not trust that it will do 
the thing you intended. You have to write extra code (or CHECK 
constraints) to just check that things are what they seem or have they 
morphed into something else? (like in #1 above).


I'm not sure your No.2 is a valid case - if you make a BLOB column and 
then try to read strings or string-functions from it, you should be 
punished for it. The problem is more if you declare a column as TEXT and 
then shove a BLOB in it, that should error out.


Don't get me wrong, we love the duck typing, we love the flexibility, 
and I for one love to be able to do quick data manipulations with the 
relaxed SQL on offer - but sometimes we are doing mission critical 
applications, or embedded things, these systems have no room for error 
and it's hard to always trust SQLite or design a bunch of code to 
second-guess the SQL in SQLite specifically. A STRICT mode would go such 
a long way to make SQLite 100% formidable.


Then again, it's already the most used DB in the World - it's not like 
they're battling for market share because of this little snag, and the 
problems that STRICT mode would solve are rather well-known and 
documented. As Dominique pointed out, the test harness is one of the 
finest in any industry and it would probably need a LOT of additions to 
test such a mode, so it's not a minor effort to implement.


One can still wish... :)

Cheers!
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[Dominique Devienne]

On Tue, Apr 9, 2019 at 5:08 AM Joshua Thomas Wise <
joshuathomasw...@gmail.com> wrote:

> SQLite3 uses manifest typing, which is great and provides a ton of
> flexibility. However, due to implicit casting rules, many operations can
> accidentally result in a different value than what was desired. If
> programmers don’t guard against every possible cause of implicit casting,
> many error situations are swallowed and instead can result in data
> corruption. I propose there should be a compile-time option to disable all
> implicit casting done within the SQL virtual machine. The option could
> cause all type-incompatible operations to return NULL, or it could cause
> these operations to throw hard errors. Either approach would be similarly
> useful.
>
> Here are some examples of how implicit casting can lead to surprising
> results:
>
> 1. If invoking SUM() would cause integer overflow, a hard error is
> returned. The same things happens with ABS(). However, if integer overflow
> occurs when using the + operator, a REAL value is returned instead.
>
> 2. Many built-in string functions will automatically cast BLOBs to TEXTs,
> but those could contain embedded nuls, leading to undefined behavior.
>
> 3. Declaring a column with INTEGER affinity does not actually force its
> values to be integers. An integer that is out of range could be stored as a
> REAL value, unexpectedly changing the behavior of functions such as SUM()
> and ABS().
>

While I can see some benefits, I'm afraid this is unlikely to happen,
because the testing effort from Richard/Dan/Joe would be large,
to retain their 100% line/branch coverage, with benefits not outweighing
the costs from their POV.

I'm the first one to request or lobby for changes, like optional "usual"
strong enforcement
of types/affinity when storing values into tables, similar to your #3. So
it's not that I don't see
some value in it. But experience tells me this will be an uphill battle I'm
afraid. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users