Option to control implicit casting

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Option to control implicit casting

Joshua Thomas Wise
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().


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

Dominique Devienne
On Tue, Apr 9, 2019 at 5:08 AM Joshua Thomas Wise <
[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

R Smith-2
In reply to this post by Joshua Thomas Wise
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

James K. Lowden
In reply to this post by Joshua Thomas Wise
On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>
> On Mon, 8 Apr 2019 23:08:18 -0400
> Joshua Thomas Wise <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

Keith Medcalf

On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:
>
>
> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise <[hidden email] <mailto:[hidden email]>> 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
> [hidden email] <mailto:[hidden email]>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

Keith Medcalf

On Wednesday, 10 April, 2019 09:06. Joshua Thomas Wise <[hidden email]> 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 <[hidden email]>
>wrote:
>>
>>
>> On Wednesday, 10 April, 2019 08:28, Joshua Thomas Wise
><[hidden email] <mailto:[hidden email]>>
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

Warren Young
In reply to this post by Joshua Thomas Wise
On Apr 8, 2019, at 9:08 PM, Joshua Thomas Wise <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

John McMahon-2
In reply to this post by Joshua Thomas Wise


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 <[hidden email]> wrote:
>>
>> On Mon, 8 Apr 2019 23:08:18 -0400
>> Joshua Thomas Wise <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
Regards
    John McMahon
       [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Option to control implicit casting

James K. Lowden
On Thu, 11 Apr 2019 11:35:04 +1000
John McMahon <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users