BUG: merge: order when merging on index not preserved

[chla0089]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

left = pd.DataFrame(index=[2, 3, 0, 1, 2, 3])
right = pd.DataFrame(range(4), index=range(4), columns=["value"])

merged_on_index = pd.merge(left=left, right=right, left_index=True, right_index=True, how="left")

merged_on_column = pd.merge(left=left.reset_index(), right=right.reset_index(), how="left", on="index").set_index("index")

>>> print(merged_on_index)

    value
0   0
1   1
2   2
2   2
3   3
3   3


>>> print(merged_on_column)

    value
2   2
3   3
0   0
1   1
2   2
3   3

Issue Description

When merging two DataFrames on the index, the order of the rows is not preserved as written in the docs ("how: - left: ... preserve key order")

This unexpected behaviour occurs when the index of the DataFrame on which is merged (here left) contains duplicate values (here 2 and 3). The indices of the resulting DataFrame are then sorted according to the the index of right. Hence the order is not preserved.
The resulting index is merged_on_index.index = [0, 1, 2, 2, 3, 3]

If each index value occurs only once, the behaviour of the merge is as expected.

Expected Behavior

The DataFrames merged_on_index and merged_on_column are identical (except of the index name).

Both DataFrames have the same order:
merged_on_index.index == [2, 3, 0, 1, 2, 3] == merged_on_column.index

Installed Versions

INSTALLED VERSIONS

commit : e8093ba
python : 3.8.10.final.0
python-bits : 64
OS : Linux
OS-release : 5.10.102.1-microsoft-standard-WSL2
Version : #1 SMP Wed Mar 2 00:30:59 UTC 2022
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : C.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.4.3
numpy : 1.23.0
pytz : 2022.1
dateutil : 2.8.2
setuptools : 44.0.0
pip : 20.0.2
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.4.0
pandas_datareader: None
bs4 : 4.11.1
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
markupsafe : 2.1.1
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None

[attack68]

If you search, "order on merge", i think you find many similar issues. Will mark as duplicate, and leave open.
Would be helpful if you can link the closest match.

[chla0089]

If you search, "order on merge", i think you find many similar issues. Will mark as duplicate, and leave open. Would be helpful if you can link the closest match.

Hi @attack68
I could not find an identical bug when checking beforehand.
The only open bug that is a bit similar is the following: #33554
There, the issue arises from the merge method "inner". When using "left" on the minimal example posted there, the merge works as expected with the order being preserved despite duplicate entries.

You can also see that the merge works properly when not using the index on the DataFrame merge_on_column. (Added the prints in the description above)
Therefore, I think the reason of the problem might be rooted in using the index. And I could not find another bug describing this issue.