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
