BUG: Partial slicing of unordered datetimeindex inconsistent between providing start and end points

[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
s = pd.Series([1, 2, 3], index=pd.Series(["2001", "2009", "2002"], dtype="datetime64[ns]"))
s.loc["1999":] # KeyError
s.loc[:"1999"] # Empty series

Issue Description

#37819 deprecated (and #49607 removed) support for looking up by slice in an unordered datetime index when the slice bounds are not in the index. This made datetime indexes behave more like other indexes when they are unordered.

My reading of those issues suggests that the intended behaviour is that asking for a slice where either (or both) of the start or end points are not in the index should raise a KeyError. However, it is only when the start point is not in the index that a KeyError is raised.

Expected Behavior

I would anticipate that this is symmetric. cc @phofl who introduced the logic for these deprecations.

Installed Versions

INSTALLED VERSIONS

commit : 310b376
python : 3.10.11.final.0
python-bits : 64
OS : Linux
OS-release : 5.19.0-46-generic
Version : #47~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Jun 21 15:35:31 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+1009.g310b3765f6
numpy : 1.24.3
pytz : 2023.3
dateutil : 2.8.2
setuptools : 67.7.2
pip : 23.1.2
Cython : 0.29.33
pytest : 7.3.2
hypothesis : 6.79.1
sphinx : 6.2.1
blosc : None
feather : None
xlsxwriter : 3.1.2
lxml.etree : 4.9.2
html5lib : 1.1
pymysql : 1.0.3
psycopg2 : 2.9.3
jinja2 : 3.1.2
IPython : 8.14.0
pandas_datareader: None
bs4 : 4.12.2
bottleneck : 1.3.7
brotli :
fastparquet : 2023.4.0
fsspec : 2023.6.0
gcsfs : 2023.6.0
matplotlib : 3.7.1
numba : 0.57.0
numexpr : 2.8.4
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 12.0.0
pyreadstat : 1.2.2
pyxlsb : 1.0.10
s3fs : 2023.6.0
scipy : 1.10.1
snappy :
sqlalchemy : 2.0.16
tables : 3.8.0
tabulate : 0.9.0
xarray : 2023.5.0
xlrd : 2.0.1
zstandard : 0.19.0
tzdata : 2023.3
qtpy : None
pyqt5 : None

[phofl]

Thanks for your report. This certainly looks inconsistent. Investigations are welcome!

[wence-]

What is the intended behaviour? If either endpoint is not in the index, then a keyerror should be returned?

There are, AFAICT, a number of problems with the logic here:

        mask = np.array(True)
        raise_mask = np.array(True)
        if start is not None:
            start_casted = self._maybe_cast_slice_bound(start, "left")
            mask = start_casted <= self
            raise_mask = start_casted == self

        if end is not None:
            end_casted = self._maybe_cast_slice_bound(end, "right")
            mask = (self <= end_casted) & mask
            raise_mask = (end_casted == self) | raise_mask

        if not raise_mask.any():
            raise KeyError(
                "Value based partial slicing on non-monotonic DatetimeIndexes "
                "with non-existing keys is not allowed.",
            )

Let's work through the possible cases:

1. start is None, end is not None (and not in the index)
   raise_mask is initialised to True, but True is not a neutral element for |, so if start is None, then raise_mask always evaluates to True, and its negation is False, so we don't raise.
2. start is not None (and not in the index), end is None
   raise_mask is all False, and its negation is True (so we raise).
3. start is not None (and not in the index), end is not None (and not the index)
   raise_mask is initialised to all False, and ored with another array that is all False, so its negation is True and we raise.
4. start is not None (and not in the index), end is not None and in the index
   raise_mask is all False, is ored with an array with one True value, so any produces True and its negation is False, so we don't raise.
5. start is not None (and in the index), end is not None and not in the index
   raise_mask has at least one True entry, is ored with all False, so any produces True, so we don't raise.

I think one should be checking that both the start and end points are in the index independently, perhaps like so:

diff --git a/pandas/core/indexes/datetimes.py b/pandas/core/indexes/datetimes.py
index 1500bcef5d..95a6e4a9a2 100644
--- a/pandas/core/indexes/datetimes.py
+++ b/pandas/core/indexes/datetimes.py
@@ -666,18 +666,18 @@ class DatetimeIndex(DatetimeTimedeltaMixin):
             return Index.slice_indexer(self, start, end, step)
 
         mask = np.array(True)
-        raise_mask = np.array(True)
+        in_index = True
         if start is not None:
             start_casted = self._maybe_cast_slice_bound(start, "left")
             mask = start_casted <= self
-            raise_mask = start_casted == self
+            in_index &= (start_casted == self).any()
 
         if end is not None:
             end_casted = self._maybe_cast_slice_bound(end, "right")
             mask = (self <= end_casted) & mask
-            raise_mask = (end_casted == self) | raise_mask
+            in_index &= (end_casted == self).any()
 
-        if not raise_mask.any():
+        if not in_index:
             raise KeyError(
                 "Value based partial slicing on non-monotonic DatetimeIndexes "
                 "with non-existing keys is not allowed.",

This might, however, have some unintuitive-seeming behaviour. Since the right slice bound is cast "up", one needs to provide much more precision than it looks like to actually pull something out of the index:

print(s)
# 2001-01-01    1
# 2009-01-01    2
# 2002-01-01    3
# dtype: int64
s.loc[:"2002-01-01"]  # KeyError
s.loc[:"2002-01-01 00:00:00"] # KeyError
s.loc[:"2002-01-01 00:00:00.000000000"] # works

[wence-]

The only failing test with that patch is tests/indexing/test_partial.py::TestStringSlicing::test_slice_irregular_datetime_index_with_nan, but IIUC this is a use-case that should have been explicitly prohibited by #49607