On 2014/09/15 20:50, Dave Wellman wrote:
Hi all,
I've found that an sql request that I expected to fail, but it didn't. On
the face of it that is good news but there is a potential downside. I wonder
if my expectation is wrong or if this is a bug which so far hasn't been
caught.
The problem deals with dividing by 0. As far as I can remember, in every
programming language that I have ever used and in all databases that I've
used, if you try and divide by 0 the process will fail with a 'divide by
zero' error. Sqlite doesn't seem to do that, it instead returns NULL.
This is not the first time this issue has come up and the answers are usually the same - NULL is a very good indicator that the math
did not result in a successful calculation, and SQLite has been like this forever, cannot change it now - both valid points btw. but
not really conformist.
I'd like to propose the NULL return as a superior solution though - If I may
illustrate why I think so:
A lot of times I use division in queries, in one example system I need to measure the ratio of cost of material issued vs. bill of
materials cost in some report, a simple (100*A/B) AS 'CostFactor' makes my query work like a charm. Every now and again some system
editor or costing clerk might register a bill of Materials wrongly so that the total cost is 0 or such, an easy fix, but until it is
fixed my queries simply show a NULL in the 'CostFact' Column and in fact, this indicates that there is a problem to whomever is
reading the report, but most importantly, the entire report doesn't fail, and much less forces a rollback or something horrible
until someone sorts out the glitch.
Having said that, the other side of the coin needs consideration too... Sometimes an INSERT query populates new data to a transacted
job registry or such, in here I need the fields to be explicitly correct and fail very hard on a DIV/0 error and force the rollback
with appropriate error message, because financials will be affected the previously harmless error becomes a serious error when
channeled into a monetary value journal. I am however well-aware of this problem, as I imagine any system designer should be, so
when making these queries, I add fail-safes.
The reason I prefer this method is that I have the choice of adding fail-safe code for important queries/functions (which is anyway
only 10% or less of the codebase) and no need to add ludicrous amounts of fail-safes to protect the other 90% quick data views or
reports from not falling over/rolling back every time a zero value appears.
I understand that this assumes I know about the DIV/0 thing and that it does not work the same as the other DBs, but I vote for
documenting it well and keeping it like this, even in upcoming SQLite4.
Regards,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
