On 4 Apr 2019, at 21:36, James K. Lowden <jklow...@schemamania.org> wrote: > > On Thu, 4 Apr 2019 17:30:29 +0200 > Lifepillar <lifepil...@icloud.com> wrote: > >> On 4 Apr 2019, at 17:15, James K. Lowden <jklow...@schemamania.org> >> wrote: >>> On Wed, 3 Apr 2019 14:30:52 +0200 >>> Lifepillar <lifepil...@icloud.com> wrote: >>>> SQLite3 Decimal is an extension implementing exact decimal >>>> arithmetic for SQLite3. >>> >>> What does divide-by-zero yield? >> >> By default: >> >> sqlite> select decDiv(1,0); >> Error: Division by zero >> >> You have the option to ignore the error, though, in which case you >> get +Inf: >> >> sqlite> delete from decTraps where flag = 'Division by zero'; >> sqlite> select decStr(decDiv(1,0)); >> Infinity >> >> decTraps is a virtual table containing a list of flags which, when >> set by some function, raise an error. The flags are from IEEE 754. > > Nice. Division by zero can be a problem in other DBMSs because, if it > occurs, the query aborts, and you then have to search for the cases and > decide what to do. By flagging and marking them, you make that task > easier. Especially when the query runs for a long time.
Thanks for the positive feedback! >> NULLs are avoided where other results make sense. For instance: >> >> sqlite> create table T (n blob); >> sqlite> select decStr(decSum(n)) from T; -- Sum of an empty set of >> sqlite> values >> 0 >> sqlite> select decStr(decAvg(n)) from T; -- Avg. of empty set of >> sqlite> values >> NaN > > Here, as you may know, you're in close touch with SQL's ambivalent > ambiguity with NULL. Should the sum of nulls be 0? Should the count > be 0? Just one example of how SQL is a fossil from the 1980s. The examples above use an empty table, so no NULLs are present and there is no ambiguity. In the presence of NULLs I have decided to go with SQL: decimal aggregate functions simply ignore them and other functions, such as decAdd() (addition), return NULL if any of their arguments is NULL (which is consistent with SQL behaviour for + and similar operators). This is essentially a pragmatic choice, as the semantics of NULLs is unspecified and ambiguous. And I don’t think that it matters much, after all: if you care about exact arithmetic (e.g., for accounting), you should definitely avoid NULLs, at least in any numeric fields. Life. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
