BUG: join on column with index
- [x] closes #28243 
- [x] closes #15692
- [x] closes  #17257

 (below can be closed if agree on expected result)
xref #28220
xref #33232
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

Lot's of existing tests were changed, i think there are multiple reported bugs on this.. I might be missing a few others as well.

The changed tests could be broadly classified as:

1. The result contains value in that was not present in the original column. 
2. The index of the merged df was different. According to the docs, when merged on a index it should pass on.
3. ~~The data type of the expected df was object instead of say datetime, or someother~~ 
4. ~~In merge_asof I think since it defaults to left join, it should inherit the index from left df~~ (these are working fine, there was some trouble with local build initially)

One particular test, which I am little puzzled about the expected output is the below. Joining on index on one df, and partial index and partial column in the other df. I was not able to figure out, so skipped for the time
https://github.com/pandas-dev/pandas/blob/f9ff3796329e4bedb4a5477739f5eb8d2e40761d/pandas/tests/reshape/merge/test_merge_index_as_string.py#L166-L189

Most of ~~them~~ the tests are. For the rest I'm not very sure about the expected behaviour.  All the three bugs are kind of similar. 
You're joining / merging two dfs using a column and an index. These are just specific cases I believe.  

EDIT: Even the two xref bugs are similar. But I am not very sure about the expected dfs from those.  This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this. Thanks for the pull request, but it appears to have gone stale. If interested in continuing, please merge in the main branch, address any review comments and/or failing tests, and we can reopen.