How I found a SQL problem

Today I solved a SQL problem that had been ocurring intermittingly for a few days.  One of the problems is I don't have access to the production database where the problem is occuring and there isn't a test platform to diagnose the problem.

The problem was that a stored proc that I wrote was blowing up with a duplicate error. Note to self make sure you handle this case on Insert statements.

The cause is from unexpected data. We have an events table that links a date to an event_id. But this table can have duplicate entries in it. I didn't expect this case to happen. It didn't make sense to have unique ids pointing to the same date.

This bug was noticed previously but the change wasn't made to all occurances of the problem. I changed it in one place and then another as each process didn't work.

Mental note: always look for all occurances of a bug you find.

The problem is that I had no idea where the issue was happening or what was causing it. With help from a co-worker I took the stored proc that was running and converted it to inline query which I was able to run.

Some of the changes I had to make
1. Convert inserts and deletes to table to inserts to a temp table
2. Create the temp table
3. Add Go statements to make sure the Cursor Declare statements only statements
4. copy Declare statements that are no longer active due to the Go inserted
5. Remove create proc type statements.

Now that the output was going to the temp table that had no duplicate restrictions, I could see that the duplicate records and realized it was cause by the duplicate events.

To get around the issue I coded the following SQL:

-- Create events temp table
select valuation_date,max(backtest_event_id) backtest_event_id
into #events
from backtest_event
group by valuation_date

This code would get the last event_id for each date.

I then substituted wherever the backtest_event was used with #events.

blog comments powered by Disqus