To wrap up our best-of year in review, here's yet another case where a simple problem is solved using simple tools, and everything still turns out entirely wrong. Original. And a happy New Year to you all! -- Remy

Sucre blanc cassonade complet rapadura

As we've seen previously, not all government jobs are splashy. Someone has to maintain, for example, the database that keeps track of every legal additive to food so that paranoid hippies can call them liars and insist they all cause cancer and autism. Today's submitter, Cass, had just released an update when users started getting the dreaded blue Internal Error screen—never a good start to the week.

Everything that's added to food is added for a reason; otherwise, why spend money doing it? The additive website allows searching by function, as well as by name. For example, some items might be alternative sweeteners, while others might be coloring agents. The problem of the week turned out to be in the stored procedure related to this particular search, which was intuitively titled prc_AdditiveRegulation_GetResults_NEW. Not to be confused with the entirely unused prc_AdditiveRegulation_GetResults, prc_AdditiveRegulation_GetResults_DEV, or prc_AdditiveRegulation_GetResults_PROD.

As Cass quickly discovered, prc_AdditiveRegulation_GetResults_NEW is a hefty chunk of code. 1044 lines, to be precise, all dedicated to returning a list of additives and their functions. After hours of searching, Cass managed to isolate the portion that was causing the problem:


UPDATE #techfunction
 SET bitNum = TableRank.PowerRank
 FROM (SELECT RowId,TechfunctionId,POWER(2.0,DENSE_RANK() OVER (PARTITION BY TechFunctionId ORDER BY RowId)) AS PowerRank    -- need to use 2.0 instead of 2 to avoid arithmetic overflow error (mark, 6/11/2012)
                         FROM #TechFunction t2) TableRank
 INNER JOIN #techfunction t
         ON t.rowId = TableRank.RowId
         AND t.techfunctionId = TableRank.TechFunctionId

In case it's been a while since you took CompSci 214: Insane Data Algorithms, allow me to translate: this code assigns each function an additive can perform to a specific power of 2. Later, these numbers are added together so that groups of technical functions effectively have a distinct number, allowing them to be used as a bitmask to search for the additives. "Clever" at its finest.

Years later, the number of functions surpassed 32, causing the POWER function to overflow a simple integer. Mark, who worked at least two generations of developers before Cass, must have fixed this by forcing it to use a larger data type by casting the 2 as float type which would then be converted back to a Bigint for storage. (This likely caused a small performance dip, as it can no longer bitshift when calculating powers, but it's still "clever," and that's what really matters.) With Cass' release, the number of additives exceeded 64, making it overflow the Bigint data type as well.

The best part? None of this was necessary. Cass traced every instance where the proc was called: the bitNum was never used, despite being always calculated. She removed it, and the errors cleared up overnight.

[Advertisement] ProGet’s got you covered with security and access controls on your NuGet feeds. Learn more.