Okay, I've got a better test case now.
I created a new database 'test' and stripped down the database and the data to the bare minimum required to consistently reproduce the problem here. The script pasted below is the mysqldump of that database, so please import it all again. Note that the import script works for me when using mysql on the Windows command line (connecting to a remote host, also running Windows XP), but if I try to execute this script in MySQL Query Browser, I get syntax errors; the script is unedited from mysqldump -R.
Then, the following two test cases demonstrate the problem:
>>>> Test statements - execute these to see the problem (run the import script below first) <<<<
-- VRM covered by pre-condition: IN123 -> should fail if rule applies
-- VRM not covered by pre-condition: OUT123 -> should pass always
-- Test one - things work as expected; T5 comes first :
-- Expected sequence is True, True, False, False, False
SELECT IF(testVehicleByRule('OUT123', NULL, 3) = True, "1", "AlwaysExclude applied when pre-condition does not") AS T5,
IF(testVehicleByRule('IN123', '2002-01-01', 1) = True, "1", "Out of date range != pass") AS T1,
IF(testVehicleByRule('IN123', '2002-01-01', 2) = False, "1", "Open EndDate not honoured") AS T2,
IF(testVehicleByRule('IN123', '2002-01-01', 3) = False, "1", "No date range not honoured") AS T3,
IF(testVehicleByRule('IN123', '2002-01-01', 4) = False, "1", "Open StartDate not honoured") AS T4;
-- Test two - things go wrong; T5 comes last;
-- Expected sequence is: True, False, False, False, True
-- Get: True, False, False, False, False
SELECT IF(testVehicleByRule('IN123', '2002-01-01', 1) = True, "1", "Out of date range != pass") AS T1,
IF(testVehicleByRule('IN123', '2002-01-01', 2) = False, "1", "Open EndDate not honoured") AS T2,
IF(testVehicleByRule('IN123', '2002-01-01', 3) = False, "1", "No date range not honoured") AS T3,
IF(testVehicleByRule('IN123', '2002-01-01', 4) = False, "1", "Open StartDate not honoured") AS T4,
IF(testVehicleByRule('OUT123', NULL, 3) = True, "1", "AlwaysExclude applied when pre-condition does not") AS T5;
>>>> The import script, from "mysqldump -R test" <<<<
-- MySQL dump 10.10
--
-- Host: 172.16.64.118 Database: test
-- ------------------------------------------------------
-- Server version 5.0.15-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `exemption`
--
DROP TABLE IF EXISTS `exemption`;
CREATE TABLE `exemption` (
`VRM` varchar(64) NOT NULL,
`ExemptionReasonID` int(10) unsigned default NULL,
PRIMARY KEY (`VRM`),
KEY `Exemption_FKIndex1` (`ExemptionReasonID`),
CONSTRAINT `exemption_ibfk_1` FOREIGN KEY (`ExemptionReasonID`) REFERENCES `exemptionreason` (`ExemptionReasonID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `exemption`
--
/*!40000 ALTER TABLE `exemption` DISABLE KEYS */;
LOCK TABLES `exemption` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `exemption` ENABLE KEYS */;
--
-- Table structure for table `myeuroclass`
--
DROP TABLE IF EXISTS `myeuroclass`;
CREATE TABLE `myeuroclass` (
`VRM` varchar(64) NOT NULL,
`PMEuroClass` tinyint(3) unsigned NOT NULL,
`NOxEuroClass` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`VRM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `myeuroclass`
--
/*!40000 ALTER TABLE `myeuroclass` DISABLE KEYS */;
LOCK TABLES `myeuroclass` WRITE;
INSERT INTO `myeuroclass` VALUES ('IN123',2,2),('OUT123',0,0);
UNLOCK TABLES;
/*!40000 ALTER TABLE `myeuroclass` ENABLE KEYS */;
--
-- Table structure for table `myvehicle`
--
DROP TABLE IF EXISTS `myvehicle`;
CREATE TABLE `myvehicle` (
`VRM` varchar(64) NOT NULL,
`VehicleTypeID` int(10) unsigned default NULL,
`TaxClass` smallint(5) unsigned default NULL,
`Weight` double default NULL,
`Seats` smallint(5) unsigned default NULL,
`RegDate` date default NULL,
`BodyClass` smallint(5) unsigned default '0',
`CountryOfOrigin` char(2) default '' COMMENT 'See
http://www.bcpl.net/~j1m5path/isocodes.html';,
PRIMARY KEY (`VRM`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `myvehicle`
--
/*!40000 ALTER TABLE `myvehicle` DISABLE KEYS */;
LOCK TABLES `myvehicle` WRITE;
INSERT INTO `myvehicle` VALUES ('IN123',1,1,8.47,2,'1996-09-03',23,'UK'),('OUT123',3,34,1.68,98,'1963-10-02',57,'UK');
UNLOCK TABLES;
/*!40000 ALTER TABLE `myvehicle` ENABLE KEYS */;
--
-- Table structure for table `rule`
--
DROP TABLE IF EXISTS `rule`;
CREATE TABLE `rule` (
`RuleID` int(10) unsigned NOT NULL auto_increment,
`RuleTestConditionID` int(10) unsigned NOT NULL,
`RulePreConditionID` int(10) unsigned NOT NULL,
`RuleSetID` int(10) unsigned NOT NULL,
`AlwaysExcluded` tinyint(1) NOT NULL default '0',
`StartDate` date default '0000-00-00',
`EndDate` date default '0000-00-00',
PRIMARY KEY (`RuleID`),
KEY `Rule_FKRuleSet` (`RuleSetID`),
KEY `Rule_FKRulePreCondition` (`RulePreConditionID`),
KEY `Rule_FKRuleTestCondition` (`RuleTestConditionID`),
CONSTRAINT `rule_ibfk_1` FOREIGN KEY (`RuleSetID`) REFERENCES `ruleset` (`RuleSetID`) ON UPDATE CASCADE,
CONSTRAINT `rule_ibfk_2` FOREIGN KEY (`RulePreConditionID`) REFERENCES `ruleprecondition` (`RulePreConditionID`),
CONSTRAINT `rule_ibfk_3` FOREIGN KEY (`RuleTestConditionID`) REFERENCES `ruletestcondition` (`RuleTestConditionID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `rule`
--
/*!40000 ALTER TABLE `rule` DISABLE KEYS */;
LOCK TABLES `rule` WRITE;
INSERT INTO `rule` VALUES (1,1,1,2,1,'2000-01-01','2001-01-01'),(2,1,1,2,1,'2000-01-01',NULL),(3,1,1,2,1,NULL,NULL),(4,1,1,2,1,NULL,'2002-01-01');
UNLOCK TABLES;
/*!40000 ALTER TABLE `rule` ENABLE KEYS */;
--
-- Table structure for table `ruleprecondition`
--
DROP TABLE IF EXISTS `ruleprecondition`;
CREATE TABLE `ruleprecondition` (
`RulePreConditionID` int(10) unsigned NOT NULL auto_increment,
`VehicleTypeID` int(10) unsigned NOT NULL,
`MinWeight` double default NULL,
`MinSeats` smallint(5) unsigned default NULL,
`MinAge` tinyint(3) unsigned default NULL,
PRIMARY KEY (`RulePreConditionID`),
KEY `RulePreCondition_FKVehicleType` (`VehicleTypeID`),
CONSTRAINT `ruleprecondition_ibfk_1` FOREIGN KEY (`VehicleTypeID`) REFERENCES `vehicletype` (`VehicleTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ruleprecondition`
--
/*!40000 ALTER TABLE `ruleprecondition` DISABLE KEYS */;
LOCK TABLES `ruleprecondition` WRITE;
INSERT INTO `ruleprecondition` VALUES (1,1,NULL,NULL,NULL),(2,1,10,NULL,NULL),(3,3,NULL,95,NULL),(4,3,NULL,NULL,30),(5,1,9,NULL,25);
UNLOCK TABLES;
/*!40000 ALTER TABLE `ruleprecondition` ENABLE KEYS */;
--
-- Table structure for table `ruleset`
--
DROP TABLE IF EXISTS `ruleset`;
CREATE TABLE `ruleset` (
`RuleSetID` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) default NULL,
`Enabled` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`RuleSetID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ruleset`
--
/*!40000 ALTER TABLE `ruleset` DISABLE KEYS */;
LOCK TABLES `ruleset` WRITE;
INSERT INTO `ruleset` VALUES (1,'First rule set (inactive)',0),(2,'Second rule set (active)',1);
UNLOCK TABLES;
/*!40000 ALTER TABLE `ruleset` ENABLE KEYS */;
--
-- Table structure for table `ruletestcondition`
--
DROP TABLE IF EXISTS `ruletestcondition`;
CREATE TABLE `ruletestcondition` (
`RuleTestConditionID` int(10) unsigned NOT NULL auto_increment,
`MinPMEuroClass` tinyint(3) unsigned default NULL,
`MinNOxEuroClass` tinyint(3) unsigned default NULL,
`MaxAge` tinyint(3) unsigned default NULL,
PRIMARY KEY (`RuleTestConditionID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ruletestcondition`
--
/*!40000 ALTER TABLE `ruletestcondition` DISABLE KEYS */;
LOCK TABLES `ruletestcondition` WRITE;
INSERT INTO `ruletestcondition` VALUES (1,NULL,NULL,NULL),(2,3,3,NULL),(3,4,3,NULL),(4,NULL,NULL,10),(5,3,3,10);
UNLOCK TABLES;
/*!40000 ALTER TABLE `ruletestcondition` ENABLE KEYS */;
--
-- Table structure for table `vehicletype`
--
DROP TABLE IF EXISTS `vehicletype`;
CREATE TABLE `vehicletype` (
`VehicleTypeID` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL,
PRIMARY KEY (`VehicleTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `vehicletype`
--
/*!40000 ALTER TABLE `vehicletype` DISABLE KEYS */;
LOCK TABLES `vehicletype` WRITE;
INSERT INTO `vehicletype` VALUES (1,'HGV'),(2,'LGV'),(3,'BUS');
UNLOCK TABLES;
/*!40000 ALTER TABLE `vehicletype` ENABLE KEYS */;
--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `testVehicleByRule` */;;
/*!50003 SET SESSION SQL_MODE="NO_AUTO_VALUE_ON_ZERO"*/;;
/*!50003 CREATE FUNCTION `testVehicleByRule`(pVRM varchar(64), ptestDate date, pRuleID int) RETURNS tinyint(1)
BEGIN
DECLARE retn BOOLEAN;
DECLARE testDate DATE;
SELECT True INTO retn;
SELECT CURDATE() INTO testDate;
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 */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;