BUG: Discrepency between documentation and output for outer merge on index when left and right indices match and are unique

[wence-]

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({"key": [2, 3, 1]})
right = pd.DataFrame({"key": [2, 3, 1]})
lefti = left.set_index("key")
righti = right.set_index("key")

for sort in [False, True]:
    result_key = left.merge(right, on="key", how="outer", sort=sort)
    result_index = lefti.merge(righti, left_index=True, right_index=True, how="outer", sort=sort).reset_index()

    print(f"{sort=!s:<5} {result_key.equals(result_index)=!s:<5}")

# sort=False result_key.equals(result_index)=False
# sort=True  result_key.equals(result_index)=False

Issue Description

Since #54611, merging dataframes produces a result that almost always matches the documented behaviour. The only exception appears to be an outer merge on an index when the left and right indexes are equal and unique. In this case, irrespective of sort=True/False, the returned result has the order of the input index.

Expected Behavior

Since there isn't a carve-out in the documentation for this specific corner-case, I would expect that pd.merge(..., how="outer") always sorts the output lexicographically according to the join key(s).

Installed Versions

INSTALLED VERSIONS
------------------
commit              : b864b8eb54605124827e6af84aa7fbc816cfc1e0
python              : 3.10.12.final.0
python-bits         : 64
OS                  : Linux
OS-release          : 6.2.0-36-generic
Version             : #37~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Mon Oct  9 15:34:04 UTC 2
machine             : x86_64
processor           : x86_64
byteorder           : little
LC_ALL              : None
LANG                : en_GB.UTF-8
LOCALE              : en_GB.UTF-8

pandas              : 2.1.0.dev0+1414.gb864b8eb54
numpy               : 1.24.4
pytz                : 2023.3
dateutil            : 2.8.2
setuptools          : 67.7.2
pip                 : 23.2.1
Cython              : 3.0.0
pytest              : 7.4.0
hypothesis          : 6.82.2
sphinx              : 6.2.1
blosc               : None
feather             : None
xlsxwriter          : 3.1.2
lxml.etree          : 4.9.3
html5lib            : 1.1
pymysql             : 1.4.6
psycopg2            : 2.9.6
jinja2              : 3.1.2
IPython             : 8.14.0
pandas_datareader   : None
bs4                 : 4.12.2
bottleneck          : 1.3.7
dataframe-api-compat: None
fastparquet         : 2023.7.0
fsspec              : 2023.6.0
gcsfs               : 2023.6.0
matplotlib          : 3.7.2
numba               : 0.57.1
numexpr             : 2.8.4
odfpy               : None
openpyxl            : 3.1.2
pandas_gbq          : None
pyarrow             : 12.0.1
pyreadstat          : 1.2.2
python-calamine     : None
pyxlsb              : 1.0.10
s3fs                : 2023.6.0
scipy               : 1.11.1
sqlalchemy          : 2.0.19
tables              : 3.8.0
tabulate            : 0.9.0
xarray              : 2023.7.0
xlrd                : 2.0.1
zstandard           : 0.19.0
tzdata              : 2023.3
qtpy                : 2.4.0
pyqt5               : None

[mroeschke]

cc @lukemanley if interested in following up here

[lukemanley]

So this behavior exists in Index.union and is documented there which is why we see it in outer joins/merges on indexes:

import pandas as pd

# inputs are equal -> no sort
left = pd.Index([1, 3, 2])
right = pd.Index([1, 3, 2])
result = left.union(right)
print(result)  # Index([1, 3, 2], dtype='int64')

# inputs are different -> does sort
left = pd.Index([1, 3, 2])
right = pd.Index([3, 2, 1])
result = left.union(right)
print(result)  # Index([1, 2, 3], dtype='int64')

I agree it feels inconsistent and its also annoying that merging on columns vs indexes produces a different default sort behavior in this case.

@mroeschke - do you think the exising behavior should be documented do you think outer joins/merges should sort by default regardless of whether the indexes are equal?

[wence-]

That behaviour is handled by special-case code in Index.union I think:

        elif not len(other) or self.equals(other):
            # NB: whether this (and the `if not len(self)` check below) come before
            #  or after the dtype equality check above affects the returned dtype
            result = self._get_reconciled_name_object(other)
            if sort is True:
                return result.sort_values()
            return result

And in any case does obey sort=True:

import pandas as pd

# inputs are equal -> no sort
left = pd.Index([1, 3, 2])
right = pd.Index([1, 3, 2])
result = left.union(right, sort=True)
print(result)  # Index([1, 2, 3], dtype='int64')

Personally, I think adding another carve-out in the documentation for outer joins is the wrong approach (as is having carve-outs in Index setops). Rationale: all of these special-cases make it very hard to write generic analysis code. If I know that there are no guarantees on the output ordering, then I can unilaterally take steps to fix that (if I need to). Conversely if there are always guarantees on the output ordering, I can take advantage of that (for example by using searchsorted or similar downstream if I need to). The current behaviour is neither one thing nor the other, and so whether or not I get ordered output is data-dependent (rather than algorithm-dependent). If I want to exploited ordering, this leaves me with the choice to either just post-hoc sort anyway (potentially expensive and unnecessary) or encode all of the corner-cases in my analysis code where I need to sort (avoiding sorts when they are already done, but difficult to keep up-to-date and ensure that I have covered all bases).

Moreover, this adds to the cognitive load when using the library since there are no simple patterns.

[mroeschke]

do you think the exising behavior should be documented do you think outer joins/merges should sort by default regardless of whether the indexes are equal?

I think for these merge case we should follow the current merge documentation and sort the keys lexicographically.

The Index.union behavior has a sort=None default that may or may not sort depending on circumstance which is also not ideal. I forgot why a sort=None was introduces, maybe to transition to a different behavior, but eventually it would be great if we didn't have special cases there as well.

[wence-]

I forgot why a sort=None was introduces, maybe to transition to a different behavior, but eventually it would be great if we didn't have special cases there as well

See discussion in #25007, #24959, and linked issues/PRs.

If I read through, the Index setops seem to have originally (almost accidentally) gained a sort=True default as a result of #22811 which fixed #20760.