I was debugging a query that was giving me some really strange results. Without pasting the query here, basically it does some interesting math to compute the estimated amount of greenhouse gases produced in tons of CO2, N2O, and CH4. So far this is pretty simple, right?
Well, depending on the type of location we have different possible queries and the results are merged with a union and then summed. This is still pretty standard.
Here’s where things got tricky.
The query returned results, we double checked them, and then moved it all to production. Over time there was a need to dynamically select which emissions the users wanted to see, so I changed the query to be nice and dynamic. Unfortunately, as a side effect, I introduced a bug that went completely uncaught because it didn’t actually throw an error.
Basically, my select ended up looking like this (but with real code):
SELECT CO2,
CO2Factor,
N2O,
N2OFactor,
CH4,
CH4Factor
FROM MagicTable
UNION ALL
SELECT CO2,
N2O,
CH4,
CO2Factor,
N2OFactor,
CH4Factor
FROM OtherMagicTable
Can you spot the error?
Basically, since the columns weren’t in the same order, there was something goofy going on in the background and sums weren’t happening correctly and N2O was coming back with a number suspiciously similar to the CO2 number. (If you know anything about greenhouse gas emissions, this should strike you as completley wrong unless I was running a nitrogen factory or something.)
Moral of the story is: Column order can matter.
Unfortunately, I don’t actually understand the why of this and I feel rather bad since one of my primary goals is to understand why errors are occurring before I push fixes into production. In this case, customers had noticed and were complaining. Any ideas in the comments are more than welcome and might just win you a prize. (The prize is my undying thanks.)
