MySQL bug - decimal comparison

Question

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".

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)

Answer 1

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.