So over the last 3 weeks I been tweaking a procedure. In a nutshell we use this procedure to redeem items for points in a frequent shopper type system. However due to the nature of the business, and a wholesale changeover from one system to another system at the beginning of 2015, I had loaded indicating ETL from the previous system as the source for points.
When a new calculation came in from the new system, the available points got set to zero in the ETL record, and a new record got created indicating the calculation engine as the source of the record. This table has one record per rebate program for each customer.
To add to the confusion of all this, a customer can also have been merged with another customer, usually themselves when a bad customer record has been entered somewhere. But, it can also be a merger of two organizations.
And this is where my testing took a turn for the worse.
This is the data as it sat in the stored procedure after some assembling of data by a few other rules.
As it sits above 2100 points are available for redemption. Fairly straightforward, sum of points earned minus sum of points redeemed. Straight forward. This procedure takes a couple of parameters, the two important ones for this is the MemberID and how many points to redeem. The winner MemberID must be the one passed in if there is a merged member, the proc then finds all the MemberIDs and their respective EarnedIDs, assembles the above, then allocates how many points each Earned record should have redeemed.
So simple!
In my unit testing everything worked fine. My fix worked. So I set to grab a few thousand MemberIDs to test the new vs. the old procedure for my regression testing. Everything looked fine, until I came to one of them. They both redeemed the right amount of points total, but the old one picked up that last record for 600 points against MemberID 300, but left 600 of one of the other records. While the new one did not redeem against the record for MemberID 300. They both redeemed the right amount of points according to what I put into the call to the procedure.
I was going back and forth on this for about 2 hours (the data was A LOT more complex than the output above). Not quite grasping what had happened. Until it dawned on me: I'm not passing in the full amount for how many points to redeem!!!!!!!!
So I went back, looked at my test setup and saw it immediately. The function used to find how many points are available for a Member, takes ALL MemberIDs as a table parameter! Not just the winner. When I grabbed MemberIDs to test for, I made sure that the ones I picked where winners if there was merges involved, however I did not call the available points function with all the MemberIDs. So all I got in this case was the 1500 points available for MemberID 100.
Both procs did it right, in that both redeemed 1500 points. All the other tie breakers for which ones to do a partial redemption against where even, so something else in the queries made them pick different records to do redemption against. The new one luckily did it the way I wanted them done, which was part of the changes I did to the proc. But not often I have two version of a procedure, that comes back with different answers, that are both correct.
Lesson learned: Understand your test data. Double check you have the right test. Assume nothing.
