Here's one for the the "seemingly innocuous change that introduces a bug" file. The code I'm sharing is not the actual code where the incident happened, just the same pattern. Here's the TSQL:
In this example, @AnyDateFound would obviously be 0.
I made an update like this:
Most SQL Developers would spot the error right away. But I assumed that if no records met the conditions in the where clause, @AnyDateFound would not be updated to 1, and would remain 0. Not the case.
The problem is that aggregates always return records. There is a maximum, even if the maximum is null. It makes perfect sense when you think about.