MySQL Forums
Forum List  »  Stored Procedures

Re: Stored procedure/function appears non-deterministic (and I'm going crazy)
Posted by: Jay Pipes
Date: November 16, 2005 11:19AM

Few more things I noticed, and sorry, I didn't realize the test cases were in the beginning of your last post!

1) Your return type and the retn variable are different data types
2) Have you tried changing the retn value to 1 and 0 instead of True and False to see if there is any effect?
3) Small thing, but in the procedure, the SELECT CURDATE() INTO testDate; line is redundant, as the next line overwrites the value of testDate anyway.

Additionally, you may find the stored routine easier to maintain and debug if you broke it out into smaller pieces, instead of the large SQL statement currently composing the largest portion of the function. I noticed that you don't JOIN the lv derived table to anything, which means that you are simply retrieving the contents of the vehicle's information from a lookup table based on a lookup from the passed in pVRM parameter. That's fine, because you then issue comparisons against this data in order to "find" rules matching criteria in the returned record.

But, what happens when no vehicle record is returned at all? It is true that the procedure should still work, as no rules records can be joined in the current criteria. However, going forward, what if you or one of your developers added in a WHERE condition similar to:

OR some_rule_condition = some_value

and didn't count the correct number of parentheses, resulting in an overarching OR condition that would always return one or more rows from the rules table. In this case, since the lv derived table is not joined against, it would be difficult to debug, as you wouldn't know whether the function was returning true because of a match on the vehicle's criteria, or returning true because of the OR condition...

Anyway, while I understand your desire to keep the main meat of the function in a single statement, perhaps it might be wiser to split it up, something similar to the following?

CREATE FUNCTION `testVehicleByRule`(pVRM varchar(64), ptestDate date, pRuleID int) RETURNS TINYINT UNSIGNED
BEGIN

DECLARE retn TINYINT UNSIGNED DEFAULT 1;
DECLARE locWeight DOUBLE DEFAULT NULL;
DECLARE locSeats SMALLINT UNSIGNED DEFAULT NULL;
DECLARE locRegDate DATE DEFAULT NULL;
DECLARE locVehTypeID INT UNSIGNED DEFAULT NULL;
DECLARE locNOXEuroClass TINYINT UNSIGNED DEFAULT NULL;
DECLARE locPMEuroClass TINYINT UNSIGNED DEFAULT NULL;
DECLARE found TINYINT UNSIGNED DEFAULT 0;

SELECT COALESCE(ptestDate,CURDATE()) INTO testDate;

SELECT
Weight INTO locWeight
, Seats INTO locSeats
, RegDate INTO locRegDate
, VehicleTypeID INTO locVehTypeID
, NOXEuroClass INTO locNOXEuroClass
, PMEuroClass INTO locNoxEuroClass
, 1 INTO found
FROM myvehicle
INNER JOIN myeuroclass
ON myvehicle.vrm = myeuroclass.vrm
WHERE UPPER(myvehicle.vrm) = UPPER(pVRM);

/* Return now if not found */
IF found = 0 THEN
RETURN 0
END;

IF EXISTS (
SELECT 1
FROM rule r
INNER JOIN ruleprecondition rp
ON r.RulePreConditionID = rp.RulePreConditionID
INNER JOIN ruletestcondition rt
ON r.RuleTestConditionID=rt.RuleTestConditionID
LEFT JOIN Exemption e
ON UPPER(pVRM) = UPPER(e.VRM)
WHERE r.ruleID=pRuleID
AND e.VRM IS NULL
AND (r.StartDate IS NULL OR r.StartDate <= testDate)
AND (r.EndDate IS NULL OR r.EndDate >= testDate)
*if a precondition is not to be tested ie null OR a precondition is passed*/
AND (rp.MinWeight IS NULL OR locWeight >= rp.MinWeight)
AND (rp.MinSeats IS NULL OR locSeats >= rp.MinSeats)
AND (rp.MinAge IS NULL OR ADDDATE(testDate, INTERVAL -rp.MinAge YEAR) >= locRegDate)
AND (rp.VehicleTypeID IS NULL OR locVehTypeID = rp.VehicleTypeID)
/*test if it fails any test conditios*/
/*if any rule fails ie rule 1 fails OR rule 2 fails*/
AND (
r.AlwaysExcluded = true OR
(rt.MinPMEuroClass is not null and locPMEuroClass is not null and locPMEuroClass < rt.MinPMEuroClass) OR
(rt.MinNOXEuroClass IS NOT NULL AND locNOXEuroClass IS NOT NULL AND locNOXEuroClass < rt.MinNOXEuroClass) OR
(rt.MaxAge IS NOT NULL AND locRegDate IS NOT NULL AND ADDDATE(testDate,INTERVAL - rt.MaxAge YEAR) >= locRegDate)
)
THEN
RETURN 1;
ELSE
RETURN 0;
END;

END;;

I haven't tested the above, as I was trying to get back to you quickly, so caution...

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com



Edited 1 time(s). Last edit at 11/16/2005 11:21AM by Jay Pipes.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored procedure/function appears non-deterministic (and I'm going crazy)
2335
November 16, 2005 11:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.