- committing a commit hash from a foreign repository in a text file (.gitmodules), along with
- some convenience tooling to check out that repository's tree at that commit in a subdirectory.
It is useful exactly when you want both of those things at once. Wanting the first is common enough, it's wanting the second that's more rare.
For example, you wouldn't submodule a Rust dependency, because you get what you want by committing the hash to a text file (Cargo.toml), and tooling to check it out in a subdirectory of your project gets you nothing.
I think the error the Git project made with submodules is trying to make them transparent, i.e., allowing you to use Git inside the submodule checkout. This is basically never a good idea; it is understandable that people get confused trying it (the thing they are trying to do is inherently confusing; if you have already been traumatized by submodules, imagine doing it with Cargo.toml or similar - it would be a mess!).
You probably don't want submodules. But they're useful, and not at all broken or poorly specified.