local_repository / new_local_repository should support paths relative to the current module

Description of the feature request:

Support a way to let local_repository or new_local_repository to specify paths relative to the current MODULE.bazel, not the root of the workspace.

Which category does this issue belong to?

External Dependency

What underlying problem are you trying to solve with this feature?

When paths in local_repository(path=) and new_local_repository(path=) are relative, they are relative to the workspace:

https://bazel.build/rules/lib/repo/local

This doesn't make sense in bzlmod. If a module uses local_repository / new_local_repository rules in its MODULE.bazel, it can no longer be used by other modules; the paths are now interpreted against the root module.

Example / Repro

# mymod/MODULE.bazel
bazel_dep(name = "mydep")
local_path_override(module_name = "mydep", path = "mydep")

# mymod/mydep/MODULE.bazel
module(name = "mydep")

local_repository = use_repo_rule("@bazel_tools//tools/build_defs/repo:local.bzl", "local_repository")
local_repository(
    name = "myrepo",
    path = "myrepo",
)

# mymod/mydep/BUILD.bazel
alias(name = "alias", actual = "@myrepo")

# mymod/mydep/myrepo/BUILD.bazel
filegroup(name = "myrepo")

Then build:

cd mymod
bazel build @mydep//:alias

This gives the error:

Error in fail: The repository's path is "myrepo" (absolute: "<path/to/mymod>/myrepo") but it does not exist or is not a directory.
ERROR: no such package '@@mydep++_repo_rules+myrepo//': The repository's path is "myrepo" (absolute: "<path/to/mymod>/myrepo") but it does not exist or is not a directory.

This is problematic because mydep should be a standalone module. However, because of the local_repository in mydep/MODULE.bazel, @mydep can no longer be used by any module.

Workarounds

1. In this example, mydep is a local_path_override so we could create a symlink. However, this doesn't work if mydep is downloaded from the Internet.
2. The owner of mydep could write its own implementation of local_path_repository() to interpret paths relative to the module root, not the workspace root. Example: https://cs.android.com/android/kernel/superproject/+/common-android-mainline:build/kernel/kleaf/impl/local_repository.bzl . However, this only works if I OWN mydep. If I am the author of mymod that uses mydep from somewhere else, this doesn't work.
3. To achieve 2 when we don't own mydep, we could use a git_override() with a patch argument that changes the local_repository = ... statement.

Real life example I discovered this issue when trying to use google/rootcanal ( https://github.com/google/rootcanal/blob/0a1741ab74af21b1cb2099c5f4c985919f5aa846/MODULE.bazel#L17 ).

Which operating system are you running Bazel on?

8.3.1

What is the output of bazel info release?

No response

If bazel info release returns development version or (@non-git), tell us how you built Bazel.

No response

What's the output of git remote get-url origin; git rev-parse HEAD ?

Have you found anything relevant by searching the web?

No response

Any other information, logs, or outputs that you want to share?

No response