[Sqlalchemy-tickets] Issue #4294: Group by with joined table inheritance doesn't work with PostgreS

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

New issue 4294: Group by with joined table inheritance doesn't work with PostgreSQL 10.4 and psycopg2==2.7.4
https://bitbucket.org/zzzeek/sqlalchemy/issues/4294/group-by-with-joined-table-inheritance

Eric Atkin:

Python 3.6.5
PostgreSQL 10.4
psycopg2==2.7.4
SQLAlchemy==1.2.7

Hi,

In my example, C has a 1-to-1 relationship to the joined table inheritance model A/B and a 1-to-many relationship to D. As demonstrated below, when I want to query columns from C and A/B (specifically non-primary key columns from A) and aggregates from D, it results in a query that postgres considers an error because the `A.id` column isn't included in the group by clause by `.group_by(B.id)`. I do not get this error with the sqlite engine. Perhaps its planner is smarter. If I manually add `A.id` to the group by clause, it works as expected but this is problematic for two reasons. First, it is boilerplate that is error prone and second and more importantly, if the A/B model is aliased, I have no public API access that I know of to the aliased primary key columns for A (or other superclasses) in order to manually add them. It seems that the postgres engine needs to render the whole chain of primary key columns from a joined table inheritance model into the group by clause. I found this difficult to describe in english prose, so I hope the code below is sufficiently illustrative. Let me know what further clarification may be helpful.

Thanks,
Eric

```
#import sqlparse
from pprint import pprint
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()

class A(Base):
    id = Column(Integer, primary_key=True)
    a_attr = Column(String)
    polymorphic_type = Column(Text, nullable=False)

    __tablename__ = 'a'
    __mapper_args__ = {
        'polymorphic_identity': 'a',
        'polymorphic_on': 'polymorphic_type',
    }

class B(A):
    id = Column(Integer, ForeignKey('a.id'), primary_key=True)
    b_attr = Column(String)

    __tablename__ = 'b'
    __mapper_args__ = {'polymorphic_identity': 'b'}

class C(Base):
    id = Column(Integer, primary_key=True)
    b_id = Column(Integer, ForeignKey('b.id'), nullable=False)

    b = relationship('B')

    __tablename__ = 'c'

class D(Base):
    id = Column(Integer, primary_key=True)
    c_id = Column(Integer, ForeignKey('c.id'), nullable=False)

    c = relationship('C')

    __tablename__ = 'd'

#engine = create_engine('sqlite:///:memory:', echo=True)
engine = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()

count = 10
session.add_all(B(a_attr=f'a{i + 1}', b_attr=f'b{i + 1}') for i in range(count // 5))
session.add_all(C(b_id=i // 5 + 1) for i in range(count))
session.add_all(D(c_id=i + 1) for i in range(count) for j in range(count))
session.flush()

query = (
    session
    .query(C.id, B.id, B.a_attr, B.b_attr, func.count(D.id))
    .join(B)
    .outerjoin(D)
    .group_by(C.id, B.id)
    .order_by(C.id)
)
#print(sqlparse.format(str(query), reindent=True))
pprint(query.all())
session.commit()
```

```
.../venv/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
2018-06-28 20:35:53,426 INFO sqlalchemy.engine.base.Engine select version()
2018-06-28 20:35:53,426 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,427 INFO sqlalchemy.engine.base.Engine select current_schema()
2018-06-28 20:35:53,427 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,427 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-06-28 20:35:53,427 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,428 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-06-28 20:35:53,428 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,428 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2018-06-28 20:35:53,428 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,429 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2018-06-28 20:35:53,429 INFO sqlalchemy.engine.base.Engine {'name': 'a'}
2018-06-28 20:35:53,429 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2018-06-28 20:35:53,429 INFO sqlalchemy.engine.base.Engine {'name': 'b'}
2018-06-28 20:35:53,430 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2018-06-28 20:35:53,430 INFO sqlalchemy.engine.base.Engine {'name': 'c'}
2018-06-28 20:35:53,430 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2018-06-28 20:35:53,430 INFO sqlalchemy.engine.base.Engine {'name': 'd'}
2018-06-28 20:35:53,432 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE a (
        id SERIAL NOT NULL, 
        a_attr VARCHAR, 
        polymorphic_type TEXT NOT NULL, 
        PRIMARY KEY (id)
)

2018-06-28 20:35:53,432 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,435 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-28 20:35:53,436 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE b (
        id INTEGER NOT NULL, 
        b_attr VARCHAR, 
        PRIMARY KEY (id), 
        FOREIGN KEY(id) REFERENCES a (id)
)

2018-06-28 20:35:53,436 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,439 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-28 20:35:53,441 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE c (
        id SERIAL NOT NULL, 
        b_id INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(b_id) REFERENCES b (id)
)

2018-06-28 20:35:53,441 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,444 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-28 20:35:53,446 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE d (
        id SERIAL NOT NULL, 
        c_id INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(c_id) REFERENCES c (id)
)

2018-06-28 20:35:53,446 INFO sqlalchemy.engine.base.Engine {}
2018-06-28 20:35:53,448 INFO sqlalchemy.engine.base.Engine COMMIT
2018-06-28 20:35:53,455 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-06-28 20:35:53,456 INFO sqlalchemy.engine.base.Engine INSERT INTO a (a_attr, polymorphic_type) VALUES (%(a_attr)s, %(polymorphic_type)s) RETURNING a.id
2018-06-28 20:35:53,456 INFO sqlalchemy.engine.base.Engine {'a_attr': 'a1', 'polymorphic_type': 'b'}
2018-06-28 20:35:53,456 INFO sqlalchemy.engine.base.Engine INSERT INTO a (a_attr, polymorphic_type) VALUES (%(a_attr)s, %(polymorphic_type)s) RETURNING a.id
2018-06-28 20:35:53,456 INFO sqlalchemy.engine.base.Engine {'a_attr': 'a2', 'polymorphic_type': 'b'}
2018-06-28 20:35:53,457 INFO sqlalchemy.engine.base.Engine INSERT INTO b (id, b_attr) VALUES (%(id)s, %(b_attr)s)
2018-06-28 20:35:53,457 INFO sqlalchemy.engine.base.Engine ({'id': 1, 'b_attr': 'b1'}, {'id': 2, 'b_attr': 'b2'})
2018-06-28 20:35:53,458 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,458 INFO sqlalchemy.engine.base.Engine {'b_id': 1}
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine {'b_id': 1}
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine {'b_id': 1}
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,459 INFO sqlalchemy.engine.base.Engine {'b_id': 1}
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine {'b_id': 1}
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine {'b_id': 2}
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine {'b_id': 2}
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,460 INFO sqlalchemy.engine.base.Engine {'b_id': 2}
2018-06-28 20:35:53,461 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,461 INFO sqlalchemy.engine.base.Engine {'b_id': 2}
2018-06-28 20:35:53,461 INFO sqlalchemy.engine.base.Engine INSERT INTO c (b_id) VALUES (%(b_id)s) RETURNING c.id
2018-06-28 20:35:53,461 INFO sqlalchemy.engine.base.Engine {'b_id': 2}
2018-06-28 20:35:53,462 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,462 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,462 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,462 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,463 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,464 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,464 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,465 INFO sqlalchemy.engine.base.Engine {'c_id': 1}
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,466 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,467 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine {'c_id': 2}
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,468 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,469 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine {'c_id': 3}
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,470 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,471 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,472 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine {'c_id': 4}
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,473 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,474 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,474 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,474 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,474 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,475 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,475 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,475 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,475 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,476 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,476 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,477 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,477 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,478 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,478 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,479 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,479 INFO sqlalchemy.engine.base.Engine {'c_id': 5}
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,480 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,481 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,482 INFO sqlalchemy.engine.base.Engine {'c_id': 6}
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,483 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,484 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine {'c_id': 7}
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,485 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,486 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine {'c_id': 8}
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,487 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,488 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,489 INFO sqlalchemy.engine.base.Engine {'c_id': 9}
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,490 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,491 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,492 INFO sqlalchemy.engine.base.Engine INSERT INTO d (c_id) VALUES (%(c_id)s) RETURNING d.id
2018-06-28 20:35:53,492 INFO sqlalchemy.engine.base.Engine {'c_id': 10}
2018-06-28 20:35:53,494 INFO sqlalchemy.engine.base.Engine SELECT c.id AS c_id, b.id AS b_id, a.a_attr AS a_a_attr, b.b_attr AS b_b_attr, count(d.id) AS count_1 
FROM c JOIN (a JOIN b ON a.id = b.id) ON b.id = c.b_id LEFT OUTER JOIN d ON c.id = d.c_id GROUP BY c.id, b.id ORDER BY c.id
2018-06-28 20:35:53,494 INFO sqlalchemy.engine.base.Engine {}
Traceback (most recent call last):
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: column "a.a_attr" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.id AS c_id, b.id AS b_id, a.a_attr AS a_a_attr, b.b...
                                           ^

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "group_by_joined_table_inheritance.py", line 61, in <module>
    pprint(query.all())
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2773, in all
    return list(self)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2925, in __iter__
    return self._execute_and_instances(context)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2948, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File ".../venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "a.a_attr" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT c.id AS c_id, b.id AS b_id, a.a_attr AS a_a_attr, b.b...
                                           ^
 [SQL: 'SELECT c.id AS c_id, b.id AS b_id, a.a_attr AS a_a_attr, b.b_attr AS b_b_attr, count(d.id) AS count_1 \nFROM c JOIN (a JOIN b ON a.id = b.id) ON b.id = c.b_id LEFT OUTER JOIN d ON c.id = d.c_id GROUP BY c.id, b.id ORDER BY c.id'] (Background on this error at: http://sqlalche.me/e/f405)
```

[Sqlalchemy-tickets] Issue #4294: Group by with joined table inheritance doesn't work with PostgreS

[Sqlalchemy-tickets] Issue #4294: Group by with joined table inheritance doesn't work with PostgreSQL 10.4 and psycopg2==2.7.4 (zzzeek/sqlalchemy)