SQL is hard, but it's a lot harder when you're "clever". This tale highlights an… interesting solution to code reuse. --Remy
Many years ago, Valts and his spouse both worked for the same company. The company had an ERP system that started its life as a product back when ERP systems were novel and new. It was written in Delphi, and it was huge- so huge that the company needed to hack the Delphi linker to handle its size.
Well, their company got swallowed up by Initech, and after the buyout, things changed. Valts left, and a few years later so did his spouse. But they kept in touch with their colleagues, so it was over dinner that former co-worker Viktorija related the recent disaster she had stumbled across.
Viktorija's day started when she needed to modify one of the "Business Objects". This product started its life well before ORMs were a common tool, and someone had taken to inventing it in house, wrapping around Delphi's TDataSet object and plenty of hard-coded SQL strings. What she needed to modify was just a minor validation rule, which she quickly changed and tested, and was quite happy with.
While making the change, she also spotted a hard-coded SQL string that was just… badly formatted. Since it made the query harder to read, and since she was right there, Viktorija added some spaces and breaks and generally made the formatting readable. She committed her changes and went on to the next task for the day.
The CI jobs failed on her commit. But they failed in a completely unrelated module, which nothing she had changed should impact. Nothing about a changed validation rule even applied to that module, and the errors were about database access- nothing in Viktorija's change should impact database access…
… well, aside from modifying a query.
Viktorija revisited her changes, and noticed that there was a "getter" function to retrieve the value of the query string. She had assumed that was just for debugging purposes, but when she CTRL+Fed through the broken module, she saw that it was fetching that string. And then it was mangling that string.
At some point, someone said, "Code reuse is always good, and since SQL queries are code, I'll reuse it!" So they fetched the query string from the Business Object Viktorija had changed, and then did a series of operations like "delete the substring from character 160-173" and "insert this substring at position 57."
By altering the whitespace and formatting, Viktorija had broken that code. It was easy enough to make the tests pass- all Viktorija had to do was revert that part of the change. Unpicking the string mangling and trying to make the system not a disaster was a much larger challenge.
Upon learning of this story, Valts and his partner were quite happy to have left- and felt that Viktorija should go home with an extra bottle of wine after dinner.
[Advertisement]
Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!
This post originally appeared on The Daily WTF.