It's a huge limitation for many reasonable ways that people want to use Nix. It's a shortcut that Nix has gotten away with, not a superior approach. Look at the machinery mach-nix implements with a cached database of PyPi and how it has to scan across different Nixpkgs revisions just to automatically choose compatible versions of deps when it does its code generation for a clear illustration.
There are lots of source-based package management systems which rely on a mix of implicit versioning based on what happens to be exist under an unversioned name in the monorepo and explicit versioning, Gentoo Portage for example.
There's no reason that we couldn't have either
a. a complement to Nixpkgs which serves as a record of which build recipes have successfully built what versions, and what versions their deps were at
b. richer metadata in Nixpkgs about versions that can be used to support multiple versions of same-named packages in the repository, and a smarter callPackage that uses those version constraints to choose, when necessary, which versions of dependencies to put into scope when calling a package (yes, I know there is ongoing work to remove callPackage, or at least explicit invocations of it)
Hacking things together with nix-index just to get a little automagical code generation where users can specify package versions rather than Nixpkgs commits is a kludge. It's cumbersome and rightly turns some developers off from using Nix. Nixpkgs should be enriched or complemented in order to obviate that approach, and the Nix ecosystem will need tooling that has an actual dependency resolver to work with it— even if the only maintained combination of versions for distribution is pretty much singular, and implicitly encoded in Nixpkgs just as it is today.