I present an alternate theory as to the cause of the Icelandic financial crisis. Specifically, the negation and diversion of sub-zeros from database aggregate product calculations.

Just kidding. But, every once in a while there is a hack so circuitous it keeps one up at night. Literally. This is one of them. This hack has found itself in production code in banks around the world with no problems for several years. Until now.

When calculating sliding averages, adjusted duration, volatility, and other performance graphs, it is often necessary to multiply huge sets of values from a database. For example, the product of the daily change in the closing price of a stock over the past ten years. This is known as an aggregate product.

So, you would think that the leading database, Oracle, would handle an aggregate product. Some common aggregate functions include SUM, AVG, COUNT, MAX, MIN, etc. Oracle allows us to add, average, count, even calculate logarithms, but we can not simply multiply the rows in a table. Here’s a simplified solution at one bank, now going the way of the polar bear:

   select
      exp(sum(ln(abs(decode(COLUMN,0,1,COLUMN)))))*
      decode(mod(count(decode(sign(COLUMN),-1,1,null)),2),1,-1,1)*
      nvl(max(decode(COLUMN,0,0,null)),1)

Let’s reconstruct this. Put simply, we add up all the natural logs of each value and reapplying the exponential to the sum. The first line, exp(sum(ln(…))) relies on the fact that:

   e^(ln(x1) + ln(x2) + ... + ln(xn))
      == x1 * x2 * ... * xn, for all x >= 0

But this is only true for positive non-zero numbers. The abs(decode(…)) of the first line, reverses all negatives and ignores all zeros. The next two lines, handle negatives and zeros respectively.

   select
      e^(sum(ln(abs( [ ignore zeros ] ))))*
      ( count( negative numbers ) == even ? 1 : -1 )*
      ( have any zero at all ? 0 : 1 )

The second line multiplies the entire result by negative one if there are an odd number of negative numbers. The third and final line multiplies the entire result by zero if there is any zero.