MySQL bug – decimal comparison

decimalfloating pointMySQL

I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in.

Somehow, MySQL considers that "14.975" is different than "14.975".

enter image description here

If I try taxRate < 14.975 or taxRate = 14.975, it does not enter the condition; but taxRate <> 14.975 and taxRate > 14.975 does.

I have the feeling I'm missing something obvious, but I just can't figure it out.

My test code:

  DECLARE totalTax DECIMAL(20,6) DEFAULT 0;
  DECLARE taxRate DECIMAL(10,3) DEFAULT 0;

  SET @roundMode = 0;
  SET @roundPrecision = 2;

  /* caculate TPS only*/

  CALL calculateTotalTaxOnAmount (200, 4, NULL, totalTax, taxRate);

  IF (totalTax <> 10) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA';
  ELSEIF (taxRate <> 5) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA';
  END IF;

  /* caculate TPS and TVQ */

  CALL calculateTotalTaxOnAmount (100, 4, 87, totalTax, taxRate);

  IF (totalTax <> 14.98) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA_QUEBEC';
  ELSEIF (taxRate <> 14.975) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA_QUEBEC';
  END IF;

My function:

CREATE PROCEDURE calculateTotalTaxOnAmount (
    IN amount DECIMAL(20,6),
    IN countryID INT(10),
    IN stateID INT(10),
    OUT totalTax DECIMAL(20,6),
    OUT totalTaxRate DECIMAL(10,3) 
)
BEGIN
    DECLARE break BOOLEAN DEFAULT FALSE;
    DECLARE taxBehavior INT(10);
    DECLARE taxRate DECIMAL(10,3);

    DECLARE TaxCursor CURSOR FOR
        SELECT (t.`rate` / 100),
            tr.`behavior`
        FROM `ps_tax_rule` tr 
        JOIN `ps_tax` t
            ON tr.`id_tax` = t.`id_tax`
        WHERE tr.`id_country` = countryID 
        AND tr.`id_state` IN (0, stateID) 
        AND t.`active` = 1
        ORDER BY tr.`id_state`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;

    SET totalTax = 0;
    SET totalTaxRate = 0;

    OPEN TaxCursor;

    tax_loop: LOOP
        FETCH TaxCursor INTO 
            taxRate,
            taxBehavior;

        IF (break) THEN

            LEAVE tax_loop;
        END IF;

        IF (taxBehavior = 2) THEN /* cumulative tax */

            SET totalTax = totalTax + (amount + totalTax) * taxRate;

        ELSE /* standalone tax */

            SET totalTax = totalTax + (amount * taxRate);
        END IF;
    END LOOP tax_loop;

    CLOSE TaxCursor;

    IF (totalTax <> 0) THEN

        SET totalTaxRate = totalTax * 100 / amount;

        SET totalTax = roundMoney(totalTax);
    END IF;
END //

Note: If you want to test it locally, replace roundMoney(totalTax) by round(totalTax, 2)

Best Answer

I've made some tests and manage to find some interesting results.

  1. You can use the casting operation to solve the problem:

    SET totalTaxRate = CAST(totalTax * 100 / amount AS DECIMAL(10,3));

  2. You can use the truncate operation to solve the problem:

    SET totalTaxRate = TRUNCATE(totalTax * 100 / amount, 3);

  3. Somehow, using a select after changing the value of the variable seems to also solve the problem...

    SET totalTaxRate = totalTax * 100 / amount; SELECT totalTaxRate;

    I'd rather not use this solution as it returns a recordset.

  4. Using a select into also solve the problem

    SELECT totalTax * 100 / amount INTO totalTaxRate;

Note: You can use the truncate or casting operation either inside or outside the procedure (even both), but I'd suggest to do it inside so you don't have use the truncate/casting operation on the output every time you call the procedure.

I still can't assert for sure the root of the problem but, at least, there are some ways to work around it.

Related Question