query.count() + join from parent to joined table subclass produces cartesian product

can be ported to 0.6.7 if the fix is not too intrusive:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Company(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)

class Person(Base):
    __tablename__ = 'p'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('c.id'))
    company = relationship("Company", backref="people")

class Employee(Person):
    __tablename__ = 'e'
    id = Column(Integer, ForeignKey('p.id'), primary_key=True)

e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
s = Session(e)

print "---------------------"
s.query(Company.id, Person).join(Person, Person.company_id==Company.id).count()
"""
SELECT count(1) AS count_1 
FROM (SELECT c.id AS c_id, p.id AS p_id 
FROM c JOIN p ON p.company_id = c.id) AS anon_1
"""  # --> good


print "---------------------"
s.query(Company, Employee).join(Employee, Employee.company_id==Company.id).count()
"""
SELECT count(1) AS count_1 
FROM c JOIN (SELECT p.id AS p_id, p.company_id AS p_company_id, e.id AS e_id 
FROM p JOIN e ON p.id = e.id) AS anon_1 ON anon_1.p_company_id = c.id
""" # --> good


print "---------------------"
s.query(Company.id, Employee).join(Employee, Employee.company_id==Company.id).count()
"""
SELECT count(1) AS count_1 
FROM (SELECT c.id AS c_id, p.id AS p_id 
FROM p, c JOIN (SELECT p.id AS p_id, p.company_id AS p_company_id, e.id AS e_id 
FROM p JOIN e ON p.id = e.id) AS anon_2 ON anon_2.p_company_id = c.id) AS anon_1

"""  # --> boom - 'p' leaked out
Comments (2)
