[BUG] presence of `overrides` breaks ability to update un-hoisted workspace dependencies

Is there an existing issue for this?

[X] I have searched the existing issues

This issue exists in the latest npm version

[X] I am using the latest npm

Current Behavior

If the root package.json defines any overrides, if you attempt to update an un-hoisted dependency by editing the workspace's package.json and then running npm install, the new version doesn't actually get installed.

Critically, package-lock.json does get updated when you do this, which may lead you to believe the new version was installed, but the old version still remains. This version mismatch can be detected by running npm ls (you'll get ELSPROBLEMS).

This has some similarities to #5850 -- in particular see this comment on an earlier issue which describes the same package-lock.json discrepancy -- but the key difference here is that the actual overrides don't matter, this issue is about the (mis)behavior of un-hoisted dependency resolution.

Expected Behavior

Editing a workspace's package.json and running npm install from the root should install the desired version(s) of dependencies, just as it does when you don't have any overrides.

Alternatively, npm install could detect the package.json <-> package-lock.json mismatch and tell the user to instead update the dependency via something like npm install <specifier> --save-exact -w <workspace>

Either solution would be vastly preferable to the current behavior where it appears to install the new version but actually keeps using the old one.

Steps To Reproduce

Given package.json:

{
  "workspaces": [
    "packages/*"
  ],
  "overrides": {
    "doesnt-matter-can-be-anything": "1.2.3"
  }
}

And packages/my-cool-package/package.json:

{}

Run:

npm i tiny-invariant@0.0.2 --save-exact -w my-cool-package (hoisted to node_modules/tiny-invariant)
npm i tiny-invariant@0.0.3 --save-exact -w my-cool-package (un-hoisted to packages/my-cool-package/node_modules/tiny-invariant, seemingly due to #7019)
Edit packages/my-cool-package/package.json and change the tiny-invariant version to 1.3.1
Run npm i

Note this package-lock.json inconsistency:

"packages/my-cool-package": {
  "dependencies": {
    "tiny-invariant": "1.3.1"
  }
},
"packages/my-cool-package/node_modules/tiny-invariant": {
  "version": "0.0.3",
  "resolved": "https://registry.npmjs.org/tiny-invariant/-/tiny-invariant-0.0.3.tgz",
  "integrity": "sha512-SA2YwvDrCITM9fTvHTHRpq9W6L2fBsClbqm3maT5PZux4Z73SPPDYwJMtnoWh6WMgmCkJij/LaOlWiqJqFMK8g=="
}

Note that for steps 1 and 2, you can get the same behavior by editing packages/my-cool-package/package.json and running npm i; this only stops working once the workspace dependency is already un-hoisted (step 3)

Environment

npm: 10.2.4
Node.js: 18.17.1
OS Name: Mac OS Ventura
System Model Name: Macbook Pro

npm config:

registry = "https://registry.npmjs.org/"

npm / cli