MySQL support fun, multiplication

There was a question how to do an aggregate multiplication in MySQL. MySQL does not provide such functionality, so we were looking at various workarounds.

We discussed UDF interface that allows to construct custom aggregates, also did look at @a:=@a*field hack, and how different initializers have results wrapped differently.

Then Scott killed our discussion with this simple query:

select exp(sum(log(c)))

Of course, thats nice mathematical approach to solve the multiplication issue with just SUM() at hands, but while we were still in awe, Scott explained it with this wit:

The Great Flood is over, and as the animals are departing 3×3, Noah is blessing each, saying “Go forth, be fruitful and multiply.” Two snakes come down the ramp and say to Noah, “We can’t. We’re adders.”

Noah groans and says, “That’s the worst pun I’ve heard in 40 days and nights. Go sit in that pile of sticks until I can deal with you!”

After mucking out the ark, Noah returns to the sticks and lifts them up, to find baby snakes everywhere. “What happened?” he asks. “I thought you were having problems!”

The snakes reply, “Even adders can multiply with logs.”

:-)

3 thoughts on “MySQL support fun, multiplication”

  1. Story is good. The approach is documented in Joe Celko’s “SQL for Smarties”.
    There’s also a theoretical PROD() aggregate function that is not actually implemented in most database servers.

    I coded it once for MySQL server but it had some test fails that I couldn’t track down with the spare time I had available. I still have the diff here though (just checked&found, was for MySQL 4.1 in 2003/2004). I can see if I can add it into 5.1 without fuss, and otherwise hand it to someone who can look at it in more detail.

Comments are closed.