On Tue, 16 Sep 2014 01:42:11 +0100 Simon Slavin <slav...@bigfraud.org> 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: > > <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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
