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.