Divide by 0 not giving error

[Dave Wellman]

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.



Here is my test script.



select sqlite_version();



drop table t1;



create table t1

(col1 text not null

,col2 real not null

,col3 real not null);



insert into t1 values('A',1,0);

insert into t1 values('B',2,0);

insert into t1 values('C',3,0);



select * from t1;



select dtl.col1

,dtl.col2

,dtl.col2 / tots.tot_value as col_pct

from t1 as dtl

cross join (select sum(col2) as tot_value from t1) as tots;



select dtl.col1

,dtl.col2

,dtl.col2 / tots.tot_value as col_pct

from t1 as dtl

cross join (select sum(col3) as tot_value from t1) as tots;



select col1,col2 / col3

from t1;



And the output from running this using the sqlite shell program (v3.8.6.0)
is:

3.8.6

A|1.0|0.0

B|2.0|0.0

C|3.0|0.0

A|1.0|0.166666666666667

B|2.0|0.333333333333333

C|3.0|0.5

A|1.0|

B|2.0|

C|3.0|

A|

B|

C|



I also get the same behaviour under 3.8.4.3 and 3.17.6.2.



I've changed my original SQL where I found this to use NULLIF(col3,0) -
which is what I'd normally do if I might encounter this situation in SQL.
My concern is that this is a bug and if it should get fixed in a later build
then any code that I've got which inadvertently relies on this will then
fail.



Should trying to divide by 0 result in an error?



Many thanks,

Dave





Ward Analytics Ltd - information in motion

Tel: [phone]

Fax: [phone]

www: <http://www.ward-analytics.com> http://www.ward-analytics.com



Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

[Richard Hipp]

Post by Dave Wellman
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.

SQLite has returned NULL for division by zero for time out of mind. Maybe
you are right and that was a bad design decision. But it is not something
we can change now, without risk of breaking some fraction of the multiple
millions of applications that use SQLite. Bummer.

[Simon Slavin]

Post by Dave Wellman
Should trying to divide by 0 result in an error?

No. There's no mechanism for reporting a mathematical error in SQL. You can report malformed commands, references to entities (tables, columns, etc.) which don't exist, and complete failure (database corrupt) but you can't report a calculation which failed because of the values found. This means programmers don't have to test their error trapping for an unusual unexpected special case.

In the SQL language, NULL is a special value which means 'unknown' or 'missing'. I would expect to see the answer to anything involving division by zero to be NULL. Your solution ...

Post by Dave Wellman
NULLIF(col3,0)

is fine for your purposes.

Simon.

[Dave Wellman]

Richard,

Thanks for that, at least I know that what I'm seeing is going to continue.

Simon,
I'm really surprised at that. Effectively what this means is that the answer
that Sqlite returns may or may not be the correct result. I realise this may
only be in a single circumstance but that is still what it means. I suppose
we then get into a discussion of what is the 'correct result'. I completely
understand that NULL is unknown, but I've always thought that there is a
difference between unknown and 'error'.

I was always taught in maths that dividing by 0 is not possible (certainly
at school, I don't know what happens if you study maths at degree level),
the closest that I ever got to an answer for that calculation was
'infinity'.

Maybe this is one downside of my working with the same dbms for 20+ years,
I've just got used to the way that it works. I realise that there will be
differences between dbms's, but now that I'm starting to use sqlite I'm
surprised by some of the differences.

Thanks for the info.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: [phone]
Fax: [phone]
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-----Original Message-----
From: sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org
[mailto:sqlite-users-bounces-CzDROfG0BjIdnm+***@public.gmane.org] On Behalf Of Simon Slavin
Sent: 15 September 2014 20:02
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Divide by 0 not giving error

Post by Dave Wellman
Should trying to divide by 0 result in an error?

No. There's no mechanism for reporting a mathematical error in SQL. You
can report malformed commands, references to entities (tables, columns,
etc.) which don't exist, and complete failure (database corrupt) but you
can't report a calculation which failed because of the values found. This
means programmers don't have to test their error trapping for an unusual
unexpected special case.

In the SQL language, NULL is a special value which means 'unknown' or
'missing'. I would expect to see the answer to anything involving division
by zero to be NULL. Your solution ...

Post by Dave Wellman
NULLIF(col3,0)

is fine for your purposes.

Simon.

[Simon Slavin]

Post by Dave Wellman
Simon,
I'm really surprised at that. Effectively what this means is that the answer
that Sqlite returns may or may not be the correct result.

What ? No. It's correct. The answer is not known, and NULL means "I don't know". Given that anything divided by 0 is infinity, and anything divided by itself is 1 what is 0/0 ?

Post by Dave Wellman
I realise this may
only be in a single circumstance but that is still what it means. I suppose
we then get into a discussion of what is the 'correct result'. I completely
understand that NULL is unknown, but I've always thought that there is a
difference between unknown and 'error'.

It is not an error to divide things by zero as long as that's what you meant to do. An error would be to divide by 6 when you meant to divide by zero.

You may be used to thinking the answer is "error" because you see calculators and spreadsheets showing "error" on their display. But that's just your calculator refusing to get into a philosophical discussion about the nature of transfinite numbers. Because it hasn't had enough beer yet.

Post by Dave Wellman
I was always taught in maths that dividing by 0 is not possible (certainly
at school, I don't know what happens if you study maths at degree level),
the closest that I ever got to an answer for that calculation was
'infinity'.

That's because you stopped at school. Ask a mathematician. They didn't stop at school but they still don't have a good answer. It's a way of avoiding the problem. It's as correct to say "Not A Number" or "I don't know". And since SQL conveniently has the NULL value for "unknown" that's what we use. You might find it interesting to read the beginning of this:

<http://en.wikipedia.org/wiki/NaN>

By the way, if you run

SELECT max(x) FROM myTable

and myTable has no rows, what answer would you expect ? I don't mean "What would you expect having read the documentation ?" I'm interested in what your first thought was.

Simon.

[RSmith]

Post by Simon Slavin

Post by Dave Wellman
Simon,
I'm really surprised at that. Effectively what this means is that the answer
that Sqlite returns may or may not be the correct result.

What ? No. It's correct. The answer is not known, and NULL means "I don't know". Given that anything divided by 0 is infinity, and anything divided by itself is 1 what is 0/0 ?

x/0 is of course unknown or, more technically, "undefined" and not simply "infinity" as Simon points out later, and mathematicians
have reasons for this. If anyone has an interest in understanding the real reasons - one of the most succint descriptions of
problems concerning division by zero, 0/0, 0^0, etc. can be found in this youtube video where Dr. James Grime and kie explain some
of the oddities:

And some more interesting information on the History of the number Zero and whether it is even or not - for those interested:

Happy viewing!
Ryan

[James K. Lowden]

On Mon, 15 Sep 2014 21:13:01 +0100

Post by Simon Slavin

I suppose we then get into a discussion of what is the 'correct
result'. I completely understand that NULL is unknown, but I've
always thought that there is a difference between unknown and
'error'.

It is not an error to divide things by zero as long as that's what
you meant to do. An error would be to divide by 6 when you meant to
divide by zero.

Whether or not something "is an error" is a matter of definition.
SQLite defines division by zero to be NULL. It's very unusual in that
regard. It's also unhelpful because the NULL can mask an error in the
data or logic. I hope SQL 4.0 will define it as an error instead.

Post by Simon Slavin
Ask a mathematician. They didn't stop at school but they still don't
have a good answer. It's a way of avoiding the problem.

It's not a problem in any sense. It's known to be undefined.

http://mathworld.wolfram.com/DivisionbyZero.html

Some operations in math are undefined. It's not cop-out; it's an
example of a meaningless expression that the syntax happens to
permit. "Colorless green ideas sleep furiously." George Carlin's line,
"Hand me the piano", might be a better example because "piano" is not
among the things that can be "handed" by most people.

Functions in math are defined on a domain and -- what else? --
*undefined* outside that domain. You can't get the circumference of a
cube or the slope of a curve. You can't multiply nonconformable
matrices. Division by zero stands out because it's so primitive that
the arithmetic unit has to cope with it.

Part of SQLite's success derives from the care it takes with backward
compatibility, and I appreciate "it has always been thus" will
sometimes mean, "thus it will always be". Progress requires change,
though. One useful guideline for introducing "new" errors might be,
are the function's preconditions met? Also known as, "do the inputs
belong to the domain on which the function is defined?"

--jkl

[Simon Slavin]

Post by James K. Lowden
Whether or not something "is an error" is a matter of definition.
SQLite defines division by zero to be NULL. It's very unusual in that
regard.

MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled:

<http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html>

SQL Server does it too unless SET ARITHABORT is ON:

<http://technet.microsoft.com/en-us/library/aa259212(v=sql.80).aspx>

Post by James K. Lowden
It's also unhelpful because the NULL can mask an error in the
data or logic. I hope SQL 4.0 will define it as an error instead.

I would rather it didn't because it's yet another thing to look for and trap in your code, and it's one that most people will not think of most of the time. It expands the test suite. It expands your software.

NULLs propagate harmlessly downstream. You display or print them and they appear as something blank or "NULL" or otherwise weird. If a user finds a field unexpectedly NULL, they can figure out what the source of the problem is and correct the data at their leisure. The rest of the program continues to function.

Errors crash the program. Start it up again and it might just crash again. Or it might crash again some unpredictable but inconvenient time in the future. And as a user you can't put it right because each time you open the window where you can type the correct data in, the program crashes. You need the help of the developer. At 5:30pm the day before The Big Report is due.

Simon.

[Jean-Christophe Deschamps]

Dear forum,

Post by Simon Slavin
<http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html>
<http://technet.microsoft.com/en-us/library/aa259212(v=sql.80).aspx>

Post by James K. Lowden
It's also unhelpful because the NULL can mask an error in the
data or logic. I hope SQL 4.0 will define it as an error instead.

I would rather it didn't because it's yet another thing to look for
and trap in your code, and it's one that most people will not think of
most of the time. It expands the test suite. It expands your software.
NULLs propagate harmlessly downstream. You display or print them and
they appear as something blank or "NULL" or otherwise weird. If a
user finds a field unexpectedly NULL, they can figure out what the
source of the problem is and correct the data at their leisure. The
rest of the program continues to function.

There is another good reason why raising an exception would be a
terrible choice. When SQLite is used as a shared library by some
scripting language, there is /*no*/ possibility to trap exceptions
raised within the library.

This would means that if ever an SQL statement encounters divide by
zero, the application will crash with no way handle the situation
gracefully, nor to locate the source of the problem.

I often see experienced people here completely disregard the contexts
where SQLite is used this way and I find it is a form of myopia (or is
that disdain?). Just because a share of users build applications in
languages like C[++|#], Delphi, Python, Ruby, YouNameIt with SQLite
statically linked or embedded in the language as a standard component,
that shouldn't hide or dismiss different contexts which don't enjoy the
same power.

Returning null may not be the best choice but I don't see that changing
now. Float signed infinity or Nan could be considered, anything but not
an exception.

Post by Simon Slavin
Errors crash the program. Start it up again and it might just crash
again. Or it might crash again some unpredictable but inconvenient
time in the future. And as a user you can't put it right because each
time you open the window where you can type the correct data in, the
program crashes. You need the help of the developer. At 5:30pm the
day before The Big Report is due.

Exactly!


BTW I often read here that "_sqlite3_get_table is deprecated". This
opinion is common but is very dommageable to future uses in contexts
where calling a shared library comes with a significant time penalty.
This API has been there for very long, is well tested and has proven
reliability. Should it be removed anytime in the future, countless
applications written in some scripting languages will have to replace
it by a loop of multiple calls to the shared library, slowing down
applications dramatically (like 20-fold or more).

Why penalize a share of users and not let this API live in the library
forever, and why not offer its counterpart in SQLite v4?

In general I see SQLite developpers take great care for widenning
potential use contexts of SQLite, not limiting it. Some attention is
requested to what some may call "unusual environments", which are
nonetheless many users everyday's reality.

Thank you.

[Clemens Ladisch]

Post by Jean-Christophe Deschamps
There is another good reason why raising an exception would be
a terrible choice. When SQLite is used as a shared library by some
scripting language, there is /*no*/ possibility to trap exceptions
raised within the library.

What the SQL standard calls an "exception" is not necessarily exactly
the same as an exception in other programming languages. It just means
that the entire SQL command is aborted and has no normal result:

$ python

Post by Jean-Christophe Deschamps

import sqlite3
sqlite3.connect(':memory:').execute('select * from "no such table"')

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: no such table
If SQLite were to change the division-by-zero handling, it could be
reported exactly like most other errors, by returning SQLITE_ERROR from
sqlite3_step().


Regards,
Clemens

[Jean-Christophe Deschamps]

Post by Clemens Ladisch
What the SQL standard calls an "exception" is not necessarily exactly
the same as an exception in other programming languages.

Granted.

Post by Clemens Ladisch
If SQLite were to change the division-by-zero handling, it could be
reported exactly like most other errors, by returning SQLITE_ERROR from
sqlite3_step().

It could as well deliver +/- Inf or Nan.

select 15 % 'abc' returns null as well.
Integer overflow could also raise some new SQLite-level error.
SQLite string functions could also raise new errors when invalid
indices are provided, like substr("abc", 456, 17) which doesn't make
any sense.

All in all I don't see any serious enough reason to change behaviors at
this stage.
Complaints posted here about these error situations being kept silent
are rather rare.

[James K. Lowden]

On Tue, 16 Sep 2014 08:59:31 +0200

Post by Jean-Christophe Deschamps
This would means that if ever an SQL statement encounters divide by
zero, the application will crash with no way handle the situation
gracefully, nor to locate the source of the problem.

Seriously, what are you talking about? Why is there "no way to handle"
the error, gracefully otherwise? How do you know there would be no way
to "locate the source of the problem"?

I imagine an error SQLITE_EMATH returned by sqlite4_step. With some
care, perhaps the expression returning zero could be mentioned in the
error text. I can't imagine how that would present a problem.

--jkl

[Jean-Christophe Deschamps]

Post by James K. Lowden

Post by Jean-Christophe Deschamps
This would means that if ever an SQL statement encounters divide by
zero, the application will crash with no way handle the situation
gracefully, nor to locate the source of the problem.

Seriously, what are you talking about? Why is there "no way to handle"
the error, gracefully otherwise? How do you know there would be no way
to "locate the source of the problem"?
I imagine an error SQLITE_EMATH returned by sqlite4_step. With some
care, perhaps the expression returning zero could be mentioned in the
error text. I can't imagine how that would present a problem.

Yes but raising an exception has been mentionned at some point in the
discussion. I was just saying that doing so is pretty different from
returning an error at function-level.

[Markus Schaber]

Hi,

Von: Jean-Christophe Deschamps

Post by James K. Lowden

Post by Jean-Christophe Deschamps
This would means that if ever an SQL statement encounters divide by
zero, the application will crash with no way handle the situation
gracefully, nor to locate the source of the problem.

Seriously, what are you talking about? Why is there "no way to handle"
the error, gracefully otherwise? How do you know there would be no way
to "locate the source of the problem"?
I imagine an error SQLITE_EMATH returned by sqlite4_step. With some
care, perhaps the expression returning zero could be mentioned in the
error text. I can't imagine how that would present a problem.

Yes but raising an exception has been mentionned at some point in the discussion. I was just saying that doing so is pretty different from returning an error at function-level.

An Exception in the SQL sense effectively results in an errorcode returned by an sqlite function.

SQLite is implemented in C, there are no exceptions on the language level it could raise.

Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. [phone] | Fax [phone]

E-Mail: m.schaber-m5RA3sxk/***@public.gmane.org | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.

[James K. Lowden]

On Tue, 16 Sep 2014 01:42:11 +0100

Post by James K. Lowden
Whether or not something "is an error" is a matter of definition.
SQLite defines division by zero to be NULL. It's very unusual in
that regard.

...

PostgreSQL doesn't do it, and that annoys some people

MySQL is a model for what not to do. Postgres has been the model for
SQLite.

<http://technet.microsoft.com/en-us/library/aa259212(v=sql.80).aspx>

That setting has always been ON by default. It's interesting to compare
that page to the current version,

http://msdn.microsoft.com/en-us/library/ms190306.aspx

which advises against turning it off. AFAIK in SQL Server the
only choices are an error message or a warning message. There's
no option for SQLite's silent convert-to-NULL behavior. The
application can achieve that by suppressing the message, of course.

NULLs propagate harmlessly downstream.

FSVO harmlessly. I understand if you want an average of zero things,
maybe NULL is a nice default. If it is, though, you always have the
choice of

case N when 0 then NULL else sum(A)/N end as Mean

The problem is, it's not always harmless. You like it because you can
print them and eyeball them. But in dealing with large datasets
updated by an external process, it is often the case that the
denominator should never be zero. Examples include the Price/Book
ratio for a stock or the current constituency of the S&P 500. With a
large enough compound computation, NULL can be a legitimate result
(because the quotient is added to or multiplied by NULL), but a zero in
the divisor is an error.

Yes, you can check. You can make a separate pass over the data to
ensure none of the divisors are zero. Two ramifications to that
approach:

1. It doubles the work for a rare condition.
2. If it's not done, the NULL will conceal the condition.

You can save #1 by returning the divisor in the output and checking it
in the application. That leaves #2, which is inescapable.

If OTOH the system produces an error for divide by zero, that can be
trapped, or prevented by the above SQL.

Errors crash the program.

Why? Perhaps divide-by-zero presents a difficulty to the programmer
who doesn't account for it. If so, it won't be the last one;
error-handling is a big part of programming.

And therein lies the rub. Errors are a fact of life, and disguising
them is no help. You can't extract a weekday from a non-date; you
can't take the log of a negative. And you can't divide by zero. It's
not special. Zero is invalid input as a divisor, just as much as 13 is
invalid as a month. Errors are errors. Report them, full stop, and be
done with it.

My basic argument is very,very simple: division by zero is an error.
It is not special in any way. If you want SQLite to ignore it, you
have to explain either why it's special, or agree that *all* domain
errors should be converted to NULL.

--jkl

[RSmith]

Post by Dave Wellman
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

[Markus Schaber]

Hi, Dave,

Von: Dave Wellman

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.

AFAIR, there are environments where division by zero for floating points can lead to a +INF or NaN value without an exception.

But you're right insofar as the SQL standard seems to mandate an error in this case.
http://postgresql.1045698.n5.nabble.com/Division-by-zero-td1922266.html



Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. [phone] | Fax [phone]

E-Mail: m.schaber-m5RA3sxk/***@public.gmane.org | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.