I have to disagree on the tests not potentially helping here. Finding the right abstraction layer is hard, but there was obviously no integration test that test

I have to disagree on the tests not potentially helping here. Finding the right abstraction layer is hard, but there was obviously no integration test that tested wherever the original query was being constructed and where the output was being used. A single smoke test would have failed the same way their actual infra failed when the change was introduced. Obviously, that's not to say that writing normalized database schemas and formal specification won't reduce the number of problems you will introduce. But people make mistakes anywhere, which could have been the case here with the query even if the DB was in a NF (and it still could have been in their case), or in the formal spec as well. There is no magic bullet for correctness, unfortunately.