Today, I successfully wrote and deployed my first MySql User Defined Aggregate Function. It's not spectacular, but it is incredibly useful and only took a couple of hours to master the UDF system. The incredible amount of knowledge that I gained was worth the time alone!
The function is a simple function, it multiplies all the values it aggregates. This is similar to the SUM() function, but it multiplies instead of adding.
Interestingly, I found another solution to this problem that did not require as much work. This solution uses the exp(), sum() and log() functions to implement the same behaviour.
My function is used like this:
SELECT PRODUCT() FROM
The alternative solution would be:
SELECT EXP(SUM(LOG())) FROM
The COALESCE() function may be used to replace nulls with the identity element: 1.0 in both cases.
Internally, there is more difference than that shown in the two SQL statements above. The later solution requires two complex floating-point operations: EXP() and LOG(). My aggregate function operates with basic multiplication only.
User Defined Aggregate Functions for MySql are developed as C or C++ Shared Libraries (or objects), compiled externally and then loaded into the database server using a simple SQL statement. This is not complicated in any way. My function was implemented in a single code file (.cpp) and did not even require a header! I have never seen any production code that is closer to academic, algorithmic code – ever!
I did encounter a few gotchas during the development process. The project was not big enough to require GNU autotools, so I just compiled it by hand. To do this, I had to include some MySql headers. The 'locate' command and the '-I' switch to gcc came in handy here. Additionally, I required the '-shared' switch to force GCC to give me a shared object. Here's how I compiled it:
gcc -I /usr/include/mysql -shared -o udf_product.so product.cpp
I deployed it directly into /usr/lib. I cannot say that I am happy with this, because sticking one of my files in a system folder goes against the grain, but it works! (This is a folder that I can guarantee is in ld's search path.)
I loaded the function into the MySql server with the following statement:
CREATE AGGREGATE FUNCTION product RETURNS REAL SONAME 'udf_product.so'
I was disappointed to note that this command would not accept an absolute path to the binary. I am not sure why this is so. Additionally, this function proceeded to play silly-buggers with my library. In order to remove the function, I had to manually delete its registration record from `mysql`.`func` and then restart the MySql Daemon (server) for my actions to take effect. At least once, this “CREATE” statement decided that the function already existed while “DROP FUNCTION” decided that there was no such function to drop!
As far as the code is concerned, it was fairly simple. I forgot to “return 0” at the end of the initialization function and received a very cryptic error about it: “HY000 Can't Initialize Function.” Otherwise, all went well.
