Stored procedure/function appears non-deterministic (and I'm going crazy)

[Martin Aspeli]

Hi,

We're running 5.0.14 on Windows XP (on a rather old laptop, unfortunately, but this is only development atm), and I'm working with the Query Browser (which, by the way, has visual bugs in its editing buffer that are rather annoying - when editing text, it frequently leaves fragments behind where there should be whitespace) on another XP laptop over a LAN. The database is about 6Gb or so, though the data we're working on is only about 2000 records (the bulk is in another table).

Anyway, we have a fairly complex stored FUNCTION (call it testRule()) that does a number of INNER JOINs and a load of WHERE-clause checks for various parameters. Basically, it's testing parameters against rules, and applies the joins and conditions so that when all the rules pass, a record is not included. Hence, it selects IF(COUNT(*) = 0, True, False) into a variable and returns this variable to indicate whether the record passes the rule or not.

Now, to test this, we've written a script that clears the rules tables and sets up some dummy rules. Then, it defines a number of stored PROCEDUREs (currently 3, call them testA(), testB() and testC()) that each perform a series of SELECTs that call this testRule() function in order to test a given rule against the test data. Thus, testA(), testB() and testC() each test a given aspect of the rule engine by calling the testRule() function several times, and return an overall status True/False indicating if all tests passed. This script hence acts as unit testing framework, allowing us to regression test the method. Or so we'd hoped.

The problem is - the rule-testing function testRule() does not appear to be deterministic! If called on its own in a Query Browser Query Tab, it works as expected. If called within the "unit testing" script in testA(), testB() or testC(), it returns the correct result if testC() is called before testB(), but if we change the order so testB() comes before testC(), the first call to testRules() in testC() gives the opposite of the expected result.

A few points:

- Neither testResult(), nor testA/B/C() modify data in the database. The script *does* DELETE and INSERT data before these functions are called.
- As far as I can tell, nothing is modifying global variables or any such nonsense; all variables that are SELECTed INTO are DECLAREd at the top of the relevant FUNCTION or PROCEDURE.
- There are no calls to START TRANSACTION, COMMIT or ROLLBACK in the script. However, the INSERT/DELETE statements come first, then the prodcedures are defined by DROP PROCEDURE IF EXISTS testA // CREATE PROCEDURE testA ... (which, as I understand, does an implicit COMMIT) and then the CALLs to testA/B/C() and the SELECT that outputs the OUT variables these return to a result set come last.
- If we make testB() have only 1 call to testRule() (it currently has 4), calling testC() after testB() works as expected
- I'm rapidly losing my hair over this

Any pointers would be greatly appreciated. I've spent most of my day trying to debug this, and seem to be going around in great big circles. :-(

Thank you!
Martin