Repository: pandas-dev/pandas
Pull request: Test: IntervalIndex.astype("category") doesn't preserve exact interval dtype in categories
- [ ] closes #38316
- [ ] [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
- [ ] 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.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Problem statement:
BUG: IntervalIndex.astype("category") doesn't preserve exact interval dtype in categories
Somewhere in the conversion, before factorizing, we convert the interval array/index to a object-dtype numpy array of Interval objects, and so afterwards *infer* the IntervalDtype again when creating the categories.

Example consequence is that if you have uint64 intervals, they get inferred as int64 afterwards:

```python
In [29]: index = pd.IntervalIndex.from_breaks(np.arange(5, dtype="uint64"))

In [30]: index
Out[30]: 
IntervalIndex([(0, 1], (1, 2], (2, 3], (3, 4]],
              closed='right',
              dtype='interval[uint64]')  # <---- unsigned ints

In [31]: pd.CategoricalIndex(index)
Out[31]: CategoricalIndex([(0, 1], (1, 2], (2, 3], (3, 4]], categories=[(0, 1], (1, 2], (2, 3], (3, 4]], ordered=False, dtype='category')

In [33]: pd.CategoricalIndex(index).dtype.categories
Out[33]: 
IntervalIndex([(0, 1], (1, 2], (2, 3], (3, 4]],
              closed='right',
              dtype='interval[int64]')  # <---- no longer uint64
```

Hints from issue discussion:
Hi! I can reproduce the issue, could I look into this issue? 
I have confirmed in the latest main branch that this is not an issue anymore since both output the same 
`IntervalIndex([(0, 1], (1, 2], (2, 3], (3, 4]], dtype='interval[uint64, right]')
`

Commit message: add test

Patch to review:
diff --git a/pandas/core/frame.py b/pandas/core/frame.py
index 4302b14da6418..aba7e1993a370 100644
--- a/pandas/core/frame.py
+++ b/pandas/core/frame.py
@@ -174,6 +174,7 @@
     DatetimeIndex,
     Index,
     PeriodIndex,
+    RangeIndex,
     default_index,
     ensure_index,
     ensure_index_from_sequences,
@@ -2283,7 +2284,7 @@ def maybe_reorder(
             result_index = None
             if len(arrays) == 0 and index is None and length == 0:
                 # for backward compat use an object Index instead of RangeIndex
-                result_index = Index([])
+                result_index = RangeIndex(start=0, stop=0, step=1)
 
             arrays, arr_columns = reorder_arrays(arrays, arr_columns, columns, length)
             return arrays, arr_columns, result_index
diff --git a/pandas/tests/arrays/categorical/test_dtypes.py b/pandas/tests/arrays/categorical/test_dtypes.py
index 5acb62c5ace49..e46c169a4477d 100644
--- a/pandas/tests/arrays/categorical/test_dtypes.py
+++ b/pandas/tests/arrays/categorical/test_dtypes.py
@@ -1,3 +1,4 @@
+import numpy as np
 import pytest
 
 from pandas.core.dtypes.dtypes import CategoricalDtype
@@ -6,6 +7,7 @@
     Categorical,
     CategoricalIndex,
     Index,
+    IntervalIndex,
     Series,
     Timestamp,
 )
@@ -134,3 +136,13 @@ def test_iter_python_types_datetime(self):
         cat = Categorical([Timestamp("2017-01-01"), Timestamp("2017-01-02")])
         assert isinstance(list(cat)[0], Timestamp)
         assert isinstance(cat.tolist()[0], Timestamp)
+
+    def test_interval_index_types_category(self):
+        # GH 38316
+        index = IntervalIndex.from_breaks(np.arange(5, dtype="uint64"))
+        categoricalIndex = CategoricalIndex(index)
+
+        result_dtype = categoricalIndex.dtype.categories.dtype.subtype
+        expected_type = "uint64"
+
+        assert result_dtype == expected_type
diff --git a/pandas/tests/frame/constructors/test_from_records.py b/pandas/tests/frame/constructors/test_from_records.py
index c6d54e28ca1c8..d5322b96c29a0 100644
--- a/pandas/tests/frame/constructors/test_from_records.py
+++ b/pandas/tests/frame/constructors/test_from_records.py
@@ -396,9 +396,10 @@ def test_from_records_misc_brokenness(self):
         # GH#2179
 
         data = {1: ["foo"], 2: ["bar"]}
+        index = RangeIndex(start=0, stop=0, step=1)
 
         result = DataFrame.from_records(data, columns=["a", "b"])
-        exp = DataFrame(data, columns=["a", "b"])
+        exp = DataFrame(data, columns=["a", "b"], index=index)
         tm.assert_frame_equal(result, exp)
 
         # overlap in index/index_names
@@ -432,12 +433,14 @@ def test_from_records_misc_brokenness(self):
 
     def test_from_records_empty(self):
         # GH#3562
+        index = RangeIndex(start=0, stop=0, step=1)
+
         result = DataFrame.from_records([], columns=["a", "b", "c"])
-        expected = DataFrame(columns=["a", "b", "c"])
+        expected = DataFrame(columns=["a", "b", "c"], index=index)
         tm.assert_frame_equal(result, expected)
 
         result = DataFrame.from_records([], columns=["a", "b", "b"])
-        expected = DataFrame(columns=["a", "b", "b"])
+        expected = DataFrame(columns=["a", "b", "b"], index=index)
         tm.assert_frame_equal(result, expected)
 
     def test_from_records_empty_with_nonempty_fields_gh3682(self):
diff --git a/pandas/tests/io/test_sql.py b/pandas/tests/io/test_sql.py
index c2c47672b190d..7d237ddf311f1 100644
--- a/pandas/tests/io/test_sql.py
+++ b/pandas/tests/io/test_sql.py
@@ -2972,3 +2972,21 @@ def test_if_exists(self):
             (5, "E"),
         ]
         self.drop_table(table_name)
+
+    def test_index_consistency_df_and_sql_df(self):
+        # GH48193
+        cur = self.conn.cursor()
+
+        cur.execute("CREATE TABLE data (id int, val real)")
+        cur.execute("INSERT INTO data VALUES (1, 150.0), (2, 160.0)")
+        self.conn.commit()
+
+        result = pd.read_sql("SELECT * FROM data", self.conn)
+        expected = DataFrame({"id": [1, 2], "val": [150.0, 160.0]})
+        tm.assert_frame_equal(result, expected)
+
+        result = pd.read_sql("SELECT * FROM data WHERE (1 = 0)", self.conn).astype(
+            float
+        )
+        expected = DataFrame({"id": [], "val": []})
+        tm.assert_frame_equal(result, expected)


Code review comments:
diff_hunk: @@ -2283,7 +2284,7 @@ def maybe_reorder(
             result_index = None
             if len(arrays) == 0 and index is None and length == 0:
                 # for backward compat use an object Index instead of RangeIndex
-                result_index = Index([])
+                result_index = RangeIndex(start=0, stop=0, step=1)
path: pandas/core/frame.py
text: @user1:
This is not part of this pr, correct?

@author:
yes. My mistake. I will revert back.