One use case is Amazon. Currently AWS doesn't charge for data transfer across private IP addresses. So it makes sense that all C* servers should talk across their private IP addresses to ensure that the user isn't charged for data transfer between nodes. Such deployments may also call for accessing Cassandra from a different availability zone (or a different cloud provider) that would require using the public IP. Since the private IP is broadcast to the auto-detect in this case, there will be issues with connecting.
The other scenario is in development environments. Sometimes as a developer I will have the need to connect to a deployed cluster to test assumptions, this again causes the same issue as above.
While technically you can provide all the external hostnames as part of the connection object and it will use those, when I tried it (with the python variant) , I had to wait for every attempted connection to the private ip addresses to timeout before it would continue along the code path. I'm not sure if the Java version of the driver exhibits this behavior or not.
One would expect that if auto-detect is turned off, so will all the features that come along with it like notifications etc. This would mean that the driver will act just as all current thrift-based CQL drivers do today.