mysql - Get rows product (multiplication)

Question

SO,

The problem

I have an issue with rows multiplication. In SQL, there is a SUM() function which calculates sum for some field for set of rows. I want to get multiplication, i.e. for table

+------+
| data |
+------+
|    2 |
|   -1 |
|    3 |
+------+

that will be 2*(-1)*3 = -6 as a result. I'm using DOUBLE data type for storing my data values.

My approach

From school math it is known that log(A x B) = log(A) + log(B) - so that could be used to created desired expression like:

SELECT
  IF(COUNT(IF(SIGN(`col`)=0,1,NULL)),0,
    IF(COUNT(IF(SIGN(`col`)<0,1,NULL))%2,-1,1)
    *
    EXP(SUM(LN(ABS(`col`))))) as product
FROM `test`;

-here you see weakness of this method - since log(X) is undefined when X<=0 - I need to count negative signs before calculating whole expression. Sample data and query for this is given in this fiddle. Another weakness is that we need to find if there is 0 among column values (Since it is a sample, in real situation I'm going to select product for some subset of table rows with some condition(s) - i.e. I can not simply remove 0-s from my table, because result zero product is a valid and expected result for some rows subsets)

Specifics

And now, finally, my question main part: how to handle situation when we have expression like: X*Y*Z and here X < MAXF, Y<MAXF, but X*Y>MAXF and X*Y*Z<MAXF - so we have possible data type overflow (here MAXF is limit for double MySQL data type). The sample is here. Query above works well, but can I always be sure that it will handle that properly? I.e. may be there is another case with overflow issue when some sub-products causing overflow, but entire product is ok (without overflow).

Or may be there is another way to find rows product? Also, in table there possibly be millions of records (-1.1<X<=1.1 mainly, but probably with values such as 100 or 1000 - i.e. high enough to overflow DOUBLE if multiplied with certain quantity if we have an issue that I've described above) - may be calculating via log will be slow?

Answer 1

I guess this would work...

SELECT IF(MOD(COUNT(data < 0),2)=1
        , EXP(SUM(LOG(data)))*-1
        , EXP(SUM(LOG(data))))
          x 
  FROM my_table;