Repository: node
Issue: The ESM resolver does not respect package `main`s for packages imported via relative imports
Labels: esm

### Version

17.1.0

### Platform

Microsoft Windows NT 10.0.19042.0 x64

### Subsystem

_No response_

### What steps will reproduce the bug?

It's possible this is intentional (or at least an unintentional effect of how the esm resolver spec was written/implemented), but it seems like a major departure, so here goes. While I was working on fixing [this issue](https://github.com/microsoft/TypeScript/issues/47848) in TS, I couldn't shake the feeling that something was off about node's behavior.

Suppose I have a (cjs) package named `"pkg"`:
```js
// @filename: /node_modules/pkg/package.json
{
  "name": "pkg",
  "version": "0.0.1",
  "main": "./entrypoint.js"
}
// @filename: /node_modules/entrypoint.js
module.exports = "entrypoint loaded!";
```
which I import from my esm consumer package:
```js
// @filename: /package.json
{
  "type": "module",
  "private": true
}
// @filename: /index.js
import str from "pkg";
```
everything works fine. Likewise, from my cjs consumer package
```js
// @filename: /package.json
{
  "type": "commonjs",
  "private": true
}
// @filename: /index.js
const str = require("pkg");
```
everything works fine. In addition, in my cjs package, I can do a _relative_ import of that `"pkg"` directory:
```js
// @filename: /package.json
{
  "type": "commonjs",
  "private": true
}
// @filename: /index.js
const str = require("./node_modules/pkg");
```
which works fine, however if I try to do the same in an esm package:
```js
// @filename: /package.json
{
  "type": "module",
  "private": true
}
// @filename: /index.js
import str from "./node_modules/pkg";
```
you get an import path error - the package's `main` is ignored entirely! This means that while in cjs, you can happily move packages out of your `node_modules` folder (likely to vendor them) and refer to them by relative paths and they keep working just dandy, to do so breaks any esm consumers (even though the package in question is cjs formatted, and so shouldn't care about esm consumers!).

This seems like a fairly major oversight - esm relative imports being unable to load packages entrypoints outside of `node_modules` which declare themselves to be cjs seems wrong and inconsistent - it's ignoring the declared (or implicit) format of the package the path points to. The package file is still respected for the format for loading any files you write out the whole path to, but is ignored when it comes to calculating the entrypoint.

cc @nodejs/modules 

### How often does it reproduce? Is there a required condition?

_No response_

### What is the expected behavior?

Importing a folder (`import("./a/b")`) that refers to a package (`"./a/b/package.json"` exists) should load that package's entrypoint, as with `require`.

### What do you see instead?

Importing a folder that is a package (`import("./a/b")` where `"./a/b/package.json"` exists) ignored the package's entrypoint, unlike `require`, which respected it.

### Additional information

It's possible this is expected, but it actually makes it really hard to work with packages outside `node_modules` (as when vendor'ing small dependencies), which pre-esm-resolver was actually pretty easy.