1.0.x regression involving aliases and synonyms

This issue seems remarkably similar to ~~#3445~~ but I've verified it still exists in 1.0.6 (and does not exist in 0.9.9).

In the below test case, generating the query without an explicit select_from causes the query to be generated without the label for the first table. (The problem goes away if you explicitly specify select_from or if you stop using the public_id synonym column and use the username column directly.)

from sqlalchemy import (
    Column, Integer, String, Boolean, DateTime,
    ForeignKey)
from sqlalchemy.orm import Session, aliased, Bundle, relationship, synonym
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class AuthUser(Base):
    __tablename__ = 'auth_user'
    id = Column('user_ptr_id', Integer, primary_key=True, nullable=False)
    username = Column(String(32), nullable=False)
    public_id = synonym('username')  # Please use this field in preference to username

    first_name = Column(String(length=30), nullable=False)
    last_name = Column(String(length=30), nullable=False)

    email = Column(String(length=75), nullable=False)

    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)

    company = relationship('Company', backref="axial_user", uselist=False)


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True, nullable=False)
    public_id = Column(String(32), unique=True, nullable=False)
    name = Column(String(length=255), nullable=False)
    company_type = Column(String(length=5))
    last_activity_date = Column(DateTime, nullable=False)


class License(Base):
    __tablename__ = 'license'
    public_id = Column(String(32), primary_key=True)
    is_active = Column(Boolean, nullable=False, server_default="FALSE")
    company_public_id = Column(String(32), nullable=False, index=True)


def query(db_session, explicit):
    AU = aliased(AuthUser)
    CMP = aliased(Company)
    LIC = aliased(License)

    USER = Bundle('user',
                  AU.public_id,
                  AU.first_name,
                  AU.last_name,
                  AU.email,
                  )
    COMP = Bundle('comp',
                  CMP.public_id,
                  CMP.name,
                  CMP.company_type,
                  CMP.last_activity_date,
                  LIC.is_active,
                  )

    sql = db_session.query(USER, COMP)
    if explicit:
        sql = sql.select_from(AU)
    sql = sql.join(CMP, CMP.id == AU.company_id)
    sql = sql.join(LIC, LIC.company_public_id == CMP.public_id)

    return sql


s = Session()

print query(s, False)
print "-----"
print query(s, True)

The relevant bits are in the FROM clause:

FROM auth_user JOIN company AS company_1 ON ...

vs

FROM auth_user AS auth_user_1 JOIN company AS company_1 ON ...

Comments (3)