CODEWITHSUNDEEP
mysql
pheromix
pheromix

Reputation: 19317

wrong decimal value after summing

There is a trigger which updates a column of a table after a row is inserted in another table :

CREATE OR REPLACE TRIGGER `tr_apres_insert_mvt_caisse` AFTER INSERT ON `mvt_caisse`
 FOR EACH ROW BEGIN

    DECLARE int_cais_code int;

    SELECT
        cais_code INTO int_cais_code
    FROM
        session_caisse
    WHERE
        cais_sess_id = NEW.cais_sess_id;

   UPDATE  mvt_caisse_histo SET mvth_montant_apres  = avoirMontantFinalParCaisse(int_cais_code) , mvtc_id = NEW.mvtc_id WHERE mvtc_id = 0 ;

END;

Code of the stored function avoirMontantFinalParCaisse :

BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE flt_somme FLOAT DEFAULT 0;
        DECLARE flt_sommeFinal FLOAT DEFAULT 0;
        DECLARE var_type_mvtc_code VARCHAR(5);

        DECLARE curMvtCaisse CURSOR FOR 
                SELECT
                        m.type_mvtc_code,
                        Sum(COALESCE(m.mvtc_montant, 0)) AS somme
                    FROM
                        mvt_caisse AS m
                    RIGHT JOIN session_caisse AS s ON s.cais_sess_id = m.cais_sess_id
                    INNER JOIN caisse AS c ON s.cais_code = c.cais_code
                    WHERE
                        1 = 1
                    AND c.cais_code = int_cais_code
                    GROUP BY
                        m.type_mvtc_code,
                        s.cais_sess_id ;

        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

        OPEN curMvtCaisse; 

        REPEAT
                FETCH curMvtCaisse INTO var_type_mvtc_code, flt_somme;
                IF done = 0 THEN

                        IF var_type_mvtc_code = 'DEC' THEN
                            SET flt_sommeFinal  = flt_sommeFinal  - flt_somme ;
                        ELSEIF var_type_mvtc_code= 'ENC' THEN 
                            SET      flt_sommeFinal  = flt_sommeFinal  + flt_somme ;

                        ELSEIF var_type_mvtc_code = 'REC' THEN 
                                SET  flt_sommeFinal  = flt_sommeFinal  + flt_somme ;

                        ELSEIF var_type_mvtc_code = 'DEG' THEN
                                SET  flt_sommeFinal  = flt_sommeFinal  - flt_somme ;

                        ELSEIF var_type_mvtc_code = 'INT' THEN
                                SET  flt_sommeFinal  = flt_sommeFinal  + flt_somme ;

                        ELSE 
                                SET  flt_sommeFinal  = flt_sommeFinal  + flt_somme ;
                        END IF ;


                END IF;
        UNTIL done
        END REPEAT;
        CLOSE curMvtCaisse; 
  RETURN flt_sommeFinal ;
END

The column mvtc_montant , which is of type decimal(15,2) , has two rows : 1000000.00 and 7331.20

So the sum should be 1007331.20 though I got 1007331.19 ! So what is wrong ?

Upvotes: 0

Views: 44

Answers (1)

spencer7593
spencer7593

Reputation: 108410

If you want a precise decimal calculation, then the FLOAT datatype is what's wrong.

You probably want to use DECIMAL datatype instead.

And this isn't just a MySQL issue. The key is understanding the IEEE floating point implementation. Everywhere.

Reference: http://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html

Upvotes: 1

Related Questions