We've definitely written lots of tests that felt like net negative, and I think that's part of what burned some devs out on testing. When I joined, the few tests we had were "read a huge JSON file, run it through everything, and assert seemingly random numbers match." Not random, but the logic was so complex that the only sane way to update the tests when code changed was to rerun and copy the new output. (I suppose this is pretty similar to approval testing, which I do find useful for code areas that shouldn't change frequently.)
Similar issue with integration tests mocking huge network requests. Either you assert the request body matches an expected one, and need to update that whenever the signature changes (fairly common). Or you ignore the body, but that feels much less useful of a test.
I agree unit tests are hard to mess up, which is why I mostly gravitate to them. And TDD is fun when I actually do it properly.
Game dev seems like a place where testing is a bit less common due to need for fast iterations and prototyping, not to say it isn't valuable.
I've seen a good talk (I think GDC?) on how the Talos Principle devs developed a tool to replay inputs for acceptance testing. I can't seem to find the talk, but here is a demo of the tool.
The Factorio devs also have some testing discussions in their blog somewhere.