Single table polymorphic count issue

Originally posted at StackOverflow

When using a polymorphic identity and attempting to count the number of records, incorrect SQL is generated. This SQL will count the entire table rather than the items matching the polymorphic discriminator.

Iterating all the results works as expected, I've only experienced this bug using .count().

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Unicode, Integer, create_engine, MetaData, func
from sqlalchemy.orm import scoped_session, sessionmaker

metadata = MetaData()
Base = declarative_base(metadata=metadata)


widgets = Table(
    'widgets', metadata,
    Column('id', Integer, primary_key=True),
    Column('type', Unicode),
    Column('state', Unicode)
)


class Widget(Base):
    __table__ = widgets

    class types(object):
        FOO_WIDGET = 'foo'
        BAR_WIDGET = 'bar'

    __mapper_args__ = {
        'polymorphic_on': widgets.c.type,
    }


class FooWidget(Widget):

    __mapper_args__ = {
        'polymorphic_identity': Widget.types.FOO_WIDGET
    }


db_engine = create_engine('sqlite:///:memory:', echo=True)
Session = scoped_session(sessionmaker())
Session.configure(bind=db_engine)

metadata.create_all(db_engine)

items = Session.query(FooWidget.id).filter_by(
    state='new'
)

print str(items)
print 'i expect the next statement to print something approximating:'
print '''
select count(*) from widgets where type = 'foo' and state = 'new'
'''
print items.count()
# What this actually prints
'''
2014-08-28 09:55:15,055 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM widgets, (SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.state = ?) AS anon_1
WHERE widgets.type IN (?)
'''

Comments (6)