This is the first in a series of articles about bugs I encounter while working[1]. It’s a new thing I’m trying for the blog, along side the Friday Blast series, which is currently at week 4. There’s a lot of bugs out there. But I want to highlight those which are generic enough so that the solution is useful to other people, and fun enough that they’ll be worthwhile to write about.

Today’s bug is related to GETUTCDATE(), the SQL Server function, and how I was using it in a daily scheduled task at StackOverflow. More or less this task looked at active ads campaigns which were more than two weeks past their expiration date. Once found it archived them, which basically meant setting their ArchivedDate field to the current time, and inserting an Archived-type event in an events log. The naive way I was coding this up looked more-or-less like this:

insert into CampaignHistory (CampaignId, Timestamp, HistoryType)
select Id, GETUTCDATE(), 6 /* Archived */
from Campaigns
where ArchivedDate is null
and DATEDIFF(day, EndDate, GETUTCDATE()) > 14;

update Campaigns
set ArchivedDate = GETUTCDATE()
where ArchivedDate is null
and DATEDIFF(day, EndDate, GETUTCDATE()) > 14;

This query is a faithful translation of the English prose from above. It gets executed as given via Dapper, the micro-ORM we use.

I was pretty sure GETUTCDATE() would be the same value across the whole query, since I knew it would be the same inside a single query like the first insert, even though multiple rows would be affected. But it turns out that’s not the case and the values returned in the two queries are different.

What this means is that the set of rows which are produced by the select in the first query, may be different than the one in the update query. More precisely, they’ll be a subset of them, as enough time might pass between their execution, that campaigns which were not two weeks past their end date when the first query was executed, became so when the second query was executed.

So we ended up with a bunch of Campaigns which were archived, yet had no corresponding Archive event. Once detected, and once I figured out this issue, the fix was pretty straightforward. I just had to use a deterministic value for the date used for all comparisons, so the two row-sets were identical. The code simply became:

declare @cutoff datetime = GETUTCDATE();

insert into CampaignHistory (CampaignId, Timestamp, HistoryType)
select Id, @cutoff, 6 /* Archived */
from Campaigns
where ArchivedDate is null
and DATEDIFF(day, EndDate, @cutoff) > 14;

update Campaigns
set ArchivedDate = @cutoff
where ArchivedDate is null
and DATEDIFF(day, EndDate, @cutoff) > 14;

[1] At some point, I’m probably going to post pictures of bigger insects I encounter as an in-joke to this 0 person community.