Well, arguably Java had the right idea with their checked/unchecked distinction.
I believe effect types (that are the true generalization of checked exceptions) are a pretty good solution to error handling, though as you also write, certain functions where an "error" case is expected should just use ADTs or so (e.g. parsing a string to an int)
While people love to put this on Java, the idea predates the language by at least two decades, having appeared at least in CLU, Modula-3 and C++, before Java came to be.
Effect types have many good properties, the most important of which being that they provide clear semantics for the composition of multiple different kinds of effects (like exceptions and async).
But I disagree that they are a good solution to "error handling", because a single solution cannot cover all situations. For example, should the indexing function on arrays have an error effect? It'll taint every function that indexes an array even if the function cannot possibly go out of bounds. Same thing with situations like integer overflow, should every function that does integer arithmetic have an error effect?
Unchecked exceptions (or "panics" as the new languages like to call them, rose by any other name and all that) are clearly the superior solution for situations like that, what I like to call "contract violations".
The Elm language does not have effect types, it just uses Result types, and is vehemently against exceptions. The outcome of that is that division by 0 returns 0 because tainting every function that divides two integers with a Result type would be bonkers. Personally I find turning an erroneous situation into a "safe default" that isn't really safe to be a very silly thing to do, specially for a language that proudly claims to have no runtime errors (just logic errors instead I guess?)
At the other end you have Python iterators throwing an exception when they complete, which is one of the weirdest design decisions I've ever seen. How is an iterator reaching the end of the thing it is iterating an exceptional situation?
I believe effect types (that are the true generalization of checked exceptions) are a pretty good solution to error handling, though as you also write, certain functions where an "error" case is expected should just use ADTs or so (e.g. parsing a string to an int)