Re: Stored procedure/function appears non-deterministic (and I'm going crazy)
Okay, there's quite a bit here, but I really appreciate any help. I will include:
I. The TABLE definitions + some background (see below)
II. The FUNCTION definition of the function that's being inconsistent
III. The script that sets up the test fixture and executes the tests
To give some context:
- We have a table of vehicles (MyVehicle) and a table of Euro certification classes (MyEuroClass; basically just ints that define certain qualities of a vehicle)
- We have a set of tables Rule -> RulePreCondition & RuleTestCondition. Basically, a RulePreCondition defines whether a vehicle is considered by a rule, based on its vehicle type, weight, number of seats etc. Any of these can be NULL to mean "don't care". If a rule is to consider a given vehicle (the pre-condition applies), its RuleTestCondition is applied to see if the vehicle passes the rule. Rules also have a blanket flag "AlwaysExcluded", which, if True, means a vehicle that is covered by a rule's pre-condition always fails that rule.
- The function that is giving us grief is testVehicleByRule(), which takes a VRM (i.e. a number plate), a date (NULL means today; rules have a StartDate and EndDate in which they are applicable), and a RuleID. It does a join that will cause a vehicle to be included in the result set only if (a) it passes all pre-conditions (that is, a given pre-condition value is null, i.e. not applicable, or the vehicle's value for this pre-condition valu is within what the pre-condition specifies) and (b) it fails any test condition. Hence, this join selects IF(COUNT(*)=0, True, False); if any vehicle matched the pre-condition (a) and then failed any rule (b), COUNT(*) would be > 0, and the function would return False, indicating the vehicle failed that rule. (again, note that if a vehicle is not covered by a rule's pre-condition, it automatically passes that rule)
- Rules are grouped in rule-sets, but they are not important for this; we are testing rules directly.
- The test script clears the rule tables and then inserts some values into RuleSet (not important), Rule, RulePreCondition and RuleTestCondition. It then sets up a number of stored procedures that test related aspects of the rule engine. Finally, it calls each of these and inserts the results in tmpTestCondition. A value of 1 means the tests in this procedure were successful; otherwise, a string explaining what went wrong is output. To view the result of a test run, do SELECT * FROM tmpTestCondition.
**The main problem:** testVehicleByRule() returns False when it should be returning True for a vehicle not in the pre-condition (the variable outVRM holds such a vehicle; recall that if a vehicle is not covered by a rule pre-condition, it should pass the rule, hence return True) for rule 3, in testRulePreConditions(). This happens if the call to testRuleDateRanges() comes *before* the call to testRulePreConditions(). If we call testRulePreCondition() first, things work as expected; If we leave out the call to testRuleDateRanges() entirely, things work as expected. If we do the call to testVehicleByRule(outVRM, NULL, 3) manually, it works as expected.
Some speculation and things we've tried:
- It's always been returning False when it should've been returning True, not the other way around
- If we edit testRuleDateRanges() so that it only does the first call to testVehicleByRule(), testRulePreConditions() works as expected always; if we leave any of the other calls in, the same problem as above occurs.
- Wrapping START TRANSACTION; and COMMIT; around the calls to testRuleDateRanges() and testRulePreConditions() has no effect.
Now, I can't send you all the data, but:
- VehicleTypeID 1 = HGV, 2 = LGV, 3 = BUS
The RulePreCondition that is being tested just looks for VehicleTypeID = 1; hence, inVRM (something that should be covered by the pre-condition) would be anything in MyVehicle with VehicleTypeID 1, and outVRM would be anything with VehicleTypeID 3.
Hence, you could probably do:
INSERT INTO VehicleType VALUES (1, "HGV"), (2, "LGV"), (3, "BUS");
INSERT INTO MyVehicle VALUES ("123IN", 1, 1, 10, 5, '2000-01-01', 1, "UK"),
("234IN", 1, 2, 20, 6, '2000-02-02', 2, "UK"),
("345OUT", 3, 3, 30, 7, '2000-03-03', 3, "UK"),
("456OUT", 3, 4, 40, 8, '2000-04-04', 4, "UK");
Note that the only values that matter here are the VRMs and VehicleTypes (first two columns), the rest are not used in the test functions included below.
Okay, so:
I. TABLE DEFINITIONS
-- Vehcle types, e.g. BUS or HGV
CREATE TABLE IF NOT EXISTS VehicleType (
VehicleTypeID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
PRIMARY KEY(VehicleTypeID)
);
-- Main table of vehicles, and information about vehicles' euro classes
CREATE TABLE IF NOT EXISTS MyVehicle (
VRM VARCHAR(64) NOT NULL,
VehicleTypeID INTEGER UNSIGNED NULL,
TaxClass SMALLINT UNSIGNED NULL,
Weight DOUBLE NULL,
Seats SMALLINT UNSIGNED NULL,
RegDate DATE NULL,
BodyClass SMALLINT UNSIGNED NULL,
CountryOfOrigin CHAR(2) NULL,
PRIMARY KEY(VRM),
FOREIGN KEY(VehicleTypeID)
REFERENCES VehicleType(VehicleTypeID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS MyEuroClass (
VRM VARCHAR(64) NOT NULL,
PMEuroClass TINYINT UNSIGNED NOT NULL,
NOxEuroClass TINYINT UNSIGNED NOT NULL,
PRIMARY KEY(VRM)
);
-- Rules with pre-conditions, test-conditions and rule-sets
CREATE TABLE IF NOT EXISTS RuleSet (
RuleSetID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NULL,
Enabled BOOL NOT NULL DEFAULT True,
PRIMARY KEY(RuleSetID)
);
CREATE TABLE IF NOT EXISTS Rule (
RuleID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
RuleTestConditionID INTEGER UNSIGNED NOT NULL,
RulePreConditionID INTEGER UNSIGNED NOT NULL,
RuleSetID INTEGER UNSIGNED NOT NULL,
StartDate DATE NULL,
EndDate DATE NULL,
PRIMARY KEY(RuleID),
INDEX Rule_FKRuleSet(RuleSetID),
INDEX Rule_FKRulePreCondition(RulePreConditionID),
INDEX Rule_FKRuleTestCondition(RuleTestConditionID),
FOREIGN KEY(RuleSetID)
REFERENCES RuleSet(RuleSetID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(RulePreConditionID)
REFERENCES RulePreCondition(RulePreConditionID)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
FOREIGN KEY(RuleTestConditionID)
REFERENCES RuleTestCondition(RuleTestConditionID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS RulePreCondition (
RulePreConditionID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
VehicleTypeID INTEGER UNSIGNED NOT NULL,
MinWeight DOUBLE NULL,
MinSeats SMALLINT UNSIGNED NULL,
MinAge TINYINT UNSIGNED NULL,
PRIMARY KEY(RulePreConditionID),
INDEX RulePreCondition_FKVehicleType(VehicleTypeID),
FOREIGN KEY(VehicleTypeID)
REFERENCES VehicleType(VehicleTypeID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS RuleTestCondition (
RuleTestConditionID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
MinPMEuroClass TINYINT UNSIGNED NULL,
MinNOxEuroClass TINYINT UNSIGNED NULL,
MaxAge TINYINT UNSIGNED NULL,
PRIMARY KEY(RuleTestConditionID)
);
II. STORED PROCEDURES
-- Stored function which tests a rule
DELIMITER //
DROP FUNCTION IF EXISTS testVehicleByRule //
CREATE FUNCTION `testVehicleByRule`(pVRM varchar(64), ptestDate date, pRuleID int) RETURNS BOOLEAN NOT DETERMINISTIC
BEGIN
DECLARE retn BOOLEAN;
DECLARE testDate DATE;
SELECT True INTO retn;
SELECT IFNULL(ptestDate,CURDATE()) into testDate;
SELECT IF(COUNT(*)=0,True,False)
FROM Rule r
INNER JOIN RulePreCondition rp ON r.RulePreConditionID=rp.RulePreConditionID
INNER JOIN RuleTestCondition rt ON r.RuleTestConditionID=rt.RuleTestConditionID,
(SELECT MyVehicle.VRM AS VRM,Weight,Seats,RegDate,VehicleTypeID,NOXEuroClass,PMEuroClass
FROM MyVehicle INNER JOIN MyEuroClass ON MyVehicle.VRM=MyEuroclass.VRM
WHERE UPPER(MyVehicle.VRM) = UPPER(pVRM)) lv
WHERE r.ruleID=pRuleID AND
(UPPER(pVRM) NOT IN (SELECT UPPER(Exemption.VRM) FROM Exemption)) AND
/*test date range*/
(r.StartDate is null or r.StartDate<=testDate) AND
(r.EndDate is null or r.EndDate>=testDate) AND
/*test preconditions*/
/*if a precondition is not to be tested ie null OR a precondition is passed*/
(rp.MinWeight IS NULL OR lv.Weight >= rp.MinWeight) AND
(rp.MinSeats IS NULL OR lv.Seats >= rp.MinSeats) AND
(rp.MinAge IS NULL OR ADDDATE(testDate,INTERVAL -rp.MinAge YEAR) >= lv.RegDate) AND
(rp.VehicleTypeID IS NULL OR lv.VehicleTypeID = rp.VehicleTypeID) AND
/*test if it fails any test conditios*/
/*if any rule fails ie rule 1 fails OR rule 2 fails*/
(
r.AlwaysExcluded = True OR
(rt.MinPMEuroClass IS NOT NULL AND lv.PMEuroClass IS NOT NULL AND lv.PMEuroClass < rt.MinPMEuroClass) OR
(rt.MinNOXEuroClass IS NOT NULL and lv.NOXEuroClass IS NOT NULL AND lv.NOXEuroClass < rt.MinNOXEuroClass) OR
(rt.MaxAge IS NOT NULL AND lv.RegDate IS NOT NULL AND ADDDATE(testDate,INTERVAL -rt.MaxAge YEAR) >= lv.RegDate)
)
INTO retn;
RETURN retn;
END
//
DELIMITER ;
III. TESTING SCRIPT
--
-- Testing rule execution
--
-- WARNING: This may change existing rules!
--
-- Clear rule tables
DELETE FROM Rule;
DELETE FROM RuleTestCondition;
DELETE FROM RulePreCondition;
DELETE FROM RuleSet;
-- Set up active and inactive rule-set
INSERT INTO RuleSet VALUES (1, "First rule set (inactive)", False);
INSERT INTO RuleSet VALUES (2, "Second rule set (active)", True);
-- Add general pre-conditions:
-- All HGV's
-- All HGV's with Weight >= 10t
-- All Busses with Seats >= 95
-- All Busses older than 30 years
-- All HGV's with Weight > 9t, older than 25 years
INSERT INTO RulePreCondition
SELECT 1, (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%HGV%'), NULL, NULL, NULL;
INSERT INTO RulePreCondition
SELECT 2, (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%HGV%'), 10, NULL, NULL;
INSERT INTO RulePreCondition
SELECT 3, (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%Bus%'), NULL, 95, NULL;
INSERT INTO RulePreCondition
SELECT 4, (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%Bus%'), NULL, NULL, 30;
INSERT INTO RulePreCondition
SELECT 5, (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%HGV%'), 9, NULL, 25 ;
-- Add general test conditions
--
-- - Always pass (NULLs all around)
-- - Require PM 3 and NOx 3
-- - Require PM 4 and NOx 3
-- - Younger than 10 years
-- - Younger than 10 years, and require PM 3 and NOx 3
INSERT INTO RuleTestCondition VALUES (1, NULL, NULL, NULL),
(2, 3, 3, NULL),
(3, 4, 3, NULL),
(4, NULL, NULL, 10),
(5, 3, 3, 10);
-- Add rules
INSERT INTO Rule VALUES (1, 1, 1, 2, True, '2000-01-01', '2001-01-01');
INSERT INTO Rule VALUES (2, 1, 1, 2, True, '2000-01-01', NULL);
INSERT INTO Rule VALUES (3, 1, 1, 2, True, NULL, NULL);
INSERT INTO Rule VALUES (4, 1, 1, 2, True, NULL, '2002-01-01');
-- 1. Check that getActiveRuleSet works
DELIMITER //
DROP PROCEDURE IF EXISTS testGetActiveRuleSet //
CREATE PROCEDURE testGetActiveRuleSet(OUT status BOOLEAN) NOT DETERMINISTIC MODIFIES SQL DATA
BEGIN
SELECT IF(getActiveRuleSet()=2, True, False) INTO status;
END
//
-- 2. - Vehicle-type only pre-conditions are applied
-- - All-NULL test-conditions always pass
-- - AlwaysExclude excludes as expected when pre-condition is applied
-- - If date is outside the rule date range, always pass
-- - Dates are open ended (can leave one or the other or both NULL to include that end)
DROP PROCEDURE IF EXISTS testRuleDateRanges //
CREATE PROCEDURE testRuleDateRanges(OUT status VARCHAR(64)) NOT DETERMINISTIC
BEGIN
DECLARE inVRM VARCHAR(64);
SELECT VRM
FROM LezVehicle
WHERE VehicleTypeID IN (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%HGV%')
LIMIT 0, 1
INTO inVRM;
-- Out of date range; Rule should not apply - must pass
SELECT IF(testVehicleByRule(inVRM, '2002-01-01', 1) = True, "1", "Out of date range != pass")
INTO status;
-- In date range (open end): Rule applies, VRM in pre-condition, rule is always exclude, hence fail
SELECT IF(testVehicleByRule(inVRM, '2002-01-01', 2) = False, "1", "Open EndDate not honoured")
INTO status;
-- No date range: Rule applies, VRM in pre-condition, rule is always exclude, hence fail
SELECT IF(testVehicleByRule(inVRM, '2002-01-01', 3) = False, "1", "No date range not honoured")
INTO status;
-- In date range (open start): Rule applies, VRM in pre-condition, rule is always exclude, hence fail
SELECT IF(testVehicleByRule(inVRM, '2002-01-01', 4) = False, "1", "Open StartDate not honoured")
INTO status;
END
//
-- 3. Test pre-conditions
-- - Not matching
-- - Match vehicle type only
-- - Match weight
-- - Match seats
-- - Match age
-- - Match all
DROP PROCEDURE IF EXISTS testRulePreConditions //
CREATE PROCEDURE testRulePreConditions(OUT status VARCHAR(64)) NOT DETERMINISTIC
BEGIN
DECLARE inVRM VARCHAR(64);
DECLARE outVRM VARCHAR(64);
SELECT VRM
FROM LezVehicle
WHERE VehicleTypeID IN (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%HGV%')
LIMIT 0, 1
INTO inVRM;
SELECT VRM
FROM LezVehicle
WHERE VehicleTypeID IN (SELECT VehicleTypeID FROM VehicleType WHERE Name LIKE '%Bus%')
LIMIT 0, 1
INTO outVRM;
-- Pre-condition doesn't apply, should pass (rule is AlwaysExclude)
SELECT IF(testVehicleByRule('26YKO', NULL, 3) = True, "1", "AlwaysExclude applied when pre-condition does not")
INTO status;
END
//
-- 4. Test test-conditions
-- - Weight, in and out
-- - Seats, in and out
-- - Age, in and out
-- 5. Rule-sets
-- - Two matching pre-conditions, one matching test condition => fail
-- - One matching pre-condition, one not matching, same matching test condition ==> pass
-- Run tests.
DELIMITER ;
DROP TABLE IF EXISTS tmpTestResults;
CREATE TABLE tmpTestResults (ran DATETIME,
name VARCHAR(255),
result VARCHAR(255)
);
-- XXX: NO MATTER WHAT I DO (!) if testRuleDateRanges() comes *BEFORE* testRulePreConditions, then
-- testRulePreConditions() fails in the first SELECT (returns False).
CALL testGetActiveRuleSet(@res);
INSERT INTO tmpTestResults VALUES (CURDATE(), "Get active ruleset", @res);
CALL testRulePreConditions(@res);
INSERT INTO tmpTestResults VALUES (CURDATE(), "Pre-condition", @res);
CALL testRuleDateRanges(@res);
INSERT INTO tmpTestResults VALUES (CURDATE(), "Date range", @res);