Re: [sqlite] Divide by 0 not giving error

[Markus Schaber]

Hi,

Von: 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. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | 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.

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

Re: [sqlite] Divide by 0 not giving error

[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. 


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

Re: [sqlite] Divide by 0 not giving error

[James K. Lowden]

On Tue, 16 Sep 2014 08:59:31 +0200
Jean-Christophe Deschamps  wrote:

> 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



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

Re: [sqlite] Divide by 0 not giving error

[James K. Lowden]

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

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

> SQL Server does it too unless SET ARITHABORT is ON:
> 
> 

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 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Jean-Christophe Deschamps]

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


Granted.


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.


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

Re: [sqlite] Divide by 0 not giving error

[Clemens Ladisch]

Jean-Christophe Deschamps wrote:
> 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
  >>> import sqlite3
  >>> sqlite3.connect(':memory:').execute('select * from "no such table"')
  Traceback (most recent call last):
File "", line 1, in 
  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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Jean-Christophe Deschamps]

Dear forum,


MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled:



SQL Server does it too unless SET ARITHABORT is ON:



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



> 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.


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.

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

Re: [sqlite] Divide by 0 not giving error

[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. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[Simon Slavin]

On 16 Sep 2014, at 1:23am, James K. Lowden  wrote:

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



SQL Server does it too unless SET ARITHABORT is ON:



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



> 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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[James K. Lowden]

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

> > 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.  

> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[RSmith]

On 2014/09/15 22:13, Simon Slavin wrote:

On 15 Sep 2014, at 8:33pm, Dave Wellman  wrote:


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:


http://www.youtube.com/watch?v=BRRolKTlF6Q

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

http://www.youtube.com/watch?v=8t1TC-5OLdM



Happy viewing!
Ryan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[RSmith]

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

Re: [sqlite] Divide by 0 not giving error

[Simon Slavin]

On 15 Sep 2014, at 8:33pm, Dave Wellman  wrote:

> 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 ?

> 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.

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



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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Divide by 0 not giving error

[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: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
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-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.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


On 15 Sep 2014, at 7:50pm, Dave Wellman <dwell...@ward-analytics.com> wrote:

> 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 ...

> NULLIF(col3,0)

is fine for your purposes.

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

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

Re: [sqlite] Divide by 0 not giving error

[Simon Slavin]

On 15 Sep 2014, at 7:50pm, Dave Wellman  wrote:

> 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 ...

> NULLIF(col3,0)

is fine for your purposes.

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

Re: [sqlite] Divide by 0 not giving error

[Richard Hipp]

On Mon, Sep 15, 2014 at 2:50 PM, Dave Wellman 
wrote:

>
> 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.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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.167

B|2.0|0.333

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: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

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.

 

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