Pages

Monday, March 28, 2011

MySQL div

I have to port one benchmark from MySQL. Test wasn't success because there was a div by zero. A result in MySQL is NULL, PostgreSQL raises a exception. I created new operator, that simulates MySQL's behave:
postgres=# CREATE OR REPLACE FUNCTION my_div(double precision, double precision) 
           RETURNS double precision AS $$ 
             SELECT CASE WHEN $2 = 0 
                              THEN NULL::double precision 
                              ELSE $1::double precision / $2::double precision END; 
           $$ LANGUAGE sql;
CREATE FUNCTION
postgres=# select my_div(2,3);
      my_div       
-------------------
 0.666666666666667
(1 row)

postgres=# select my_div(2,0);
 my_div 
--------
       
(1 row)

postgres=# CREATE OPERATOR // (PROCEDURE = my_div, LEFTARG = double precision, RIGHTARG= double precision);
CREATE OPERATOR
postgres=# SELECT 10//3;
     ?column?     
------------------
 3.33333333333333
(1 row)

postgres=# SELECT 10//0;
 ?column? 
----------
         
(1 row)

2 comments:

  1. Even on MySQL that's wrong. That's why I like to set the sql_mode to ERROR_FOR_DIVISION_BY_ZERO when possible.

    Is it an opensource benchmark?

    ReplyDelete
  2. I cannot to change MySQL application. These tests are single-use non public OLAP tests. But I can say so PostgreSQL is better than MySQL on Amazon Cluster.

    ReplyDelete