BUG: inconsistent index between plain DataFrame and read_sql DataFrame

[jf2]

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
import sqlite3

conn = sqlite3.connect(":memory:")

cur = conn.cursor()
cur.execute("CREATE TABLE data (id int, val real)")
cur.execute("INSERT INTO data VALUES (1, 150.0), (2, 160.0)")
conn.commit()

full_df = pd.DataFrame({"id": [1, 2], "val": [150.0, 160.0]})
full_sql_df = pd.read_sql("SELECT * FROM data", conn)

empty_df = pd.DataFrame({"id": [], "val": []})
empty_sql_df = pd.read_sql("SELECT * FROM data WHERE (1 = 0)", conn)

pd.testing.assert_frame_equal(full_df, full_sql_df)
pd.testing.assert_frame_equal(empty_df, empty_sql_df)

Issue Description

The empty DataFrame returned from read_sql has a different index type than if one constructs an empty DataFrame directly. When the returned SQL frame is not empty, the index types match.

Expected Behavior

The returned empty SQL DataFrame's index is also a RangeIndex(start=0, end=0, step=1).

Installed Versions

INSTALLED VERSIONS

commit : e8093ba
python : 3.8.6.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19041
machine : AMD64
processor : Intel64 Family 6 Model 165 Stepping 5, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United Kingdom.1252
pandas : 1.4.3
numpy : 1.23.0
pytz : 2022.1
dateutil : 2.8.2
setuptools : 58.2.0
pip : 21.3
Cython : None
pytest : 6.2.5
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
markupsafe : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : 1.4.39
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None

[jf2]

After some stepping through, I believe the error comes from pandas.core.frame.py:2169:

...
  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([])
...

[phofl]

Hi, thanks for your report. This looks to be deliberate. Do you want to check when this was added?

[simonjayhawkins]

added in #43110.

The empty DataFrame returned from read_sql having an object dtype Index goes back to a least 0.25.3 (not checked earlier).

I assume that since @jbrockmendel explicitly added the code for backwards compat that we tested for this.

It maybe possible to treat the inconsistency as a bug and remove the special case, but further investigation (and checking other issues) would be required to see what other changes would result and if the object dtype Index was intentional before #43110 (since this PR just moved that special case).

further investigation and PRs to fix welcome.

[jbrockmendel]

Changing the line mentioned #47608 (comment) to return RangeIndex breaks two tests: test_from_records_empty, test_from_records_misc_brokenness. Looking at the GH issues referenced in those two tests, doesn't look like the type of index was a big motivator. i'd have no objection to deprecating/changing it.