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;
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