BUG: pd.read_sql() fails on SELECT * self join when handling date columns · Issue #44421 · pandas-dev/pandas (original) (raw)

Reproducible Example

import sqlalchemy as sa import pandas as pd

username = "..." password = "..." host = "..." port = "..." database = "..."

conn_string = sa.engine.url.URL.create( "postgresql", username=username, password=password, host=host, port=port, database=database ) engine = sa.create_engine(conn_string) conn = engine.connect()

sql_query = 'SELECT * FROM "person" AS p1 INNER JOIN "person" AS p2 ON p1.id = p2.id;' df = pd.read_sql(sql_query, conn)

Issue Description

When performing a SQL self-join on a table that includes date columns, pd.read_sql() fails if SELECT * is used with ValueError: shape mismatch: value array of shape (0,1) could not be broadcast to indexing result of shape (1,1):

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/io/sql.py", line 628, in read_sql
    return pandas_sql.read_query(
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/io/sql.py", line 1594, in read_query
    frame = _wrap_result(
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/io/sql.py", line 177, in _wrap_result
    frame = _parse_date_columns(frame, parse_dates)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/io/sql.py", line 158, in _parse_date_columns
    data_frame[col_name] = _handle_date_column(df_col, format=fmt)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/frame.py", line 3612, in __setitem__
    self._set_item(key, value)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/frame.py", line 3797, in _set_item
    self._set_item_mgr(key, value)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/frame.py", line 3756, in _set_item_mgr
    self._iset_item_mgr(loc, value)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/frame.py", line 3746, in _iset_item_mgr
    self._mgr.iset(loc, value)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/internals/managers.py", line 1078, in iset
    blk.set_inplace(blk_locs, value_getitem(val_locs))
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/internals/blocks.py", line 360, in set_inplace
    self.values[locs] = values
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/arrays/datetimelike.py", line 373, in __setitem__
    super().__setitem__(key, value)
  File "/Users/lucas/.virtualenvs/pandas-read-sql-bug/lib/python3.8/site-packages/pandas/core/arrays/_mixins.py", line 183, in __setitem__
    self._ndarray[key] = value
ValueError: shape mismatch: value array of shape (0,1) could not be broadcast to indexing result of shape (1,1)

The tables referenced in the example were created in a PostgreSQL database using the following SQL:

CREATE TABLE person ( id serial constraint person_pkey primary key, created_dt timestamp with time zone );

INSERT INTO person VALUES (1, '2021-01-01T00:00:00Z');

Expected Behavior

I would expect pd.read_sql() to return a DataFrame here with two id columns and two created_dt columns. This is the behaviour I get when creating the table without a primary key:

CREATE TABLE person ( id int not null, created_dt timestamp with time zone );

Installed Versions

INSTALLED VERSIONS

commit : 945c9ed
python : 3.8.2.final.0
python-bits : 64
OS : Darwin
OS-release : 20.6.0
Version : Darwin Kernel Version 20.6.0: Mon Aug 30 06:12:20 PDT 2021; root:xnu-7195.141.6~3/RELEASE_ARM64_T8101
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.3.4
numpy : 1.21.4
pytz : 2021.3
dateutil : 2.8.2
pip : 21.1.1
setuptools : 56.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.9.2 (dt dec pq3 ext lo64)
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 1.4.27
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None