column_property expression + order_by + the usual query limit/subquery thing producing double alias

[Mike Bayer]

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"

    id = sa.Column(sa.Integer, primary_key=True)
    expr = orm.column_property(id > 0)

    children = orm.relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = "child"

    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer, sa.ForeignKey("parent.id"))

    parent = orm.relationship("Parent", back_populates="children")

engine = sa.create_engine("postgres:///test", echo=True)
Base.metadata.create_all(engine)

session = orm.Session(bind=engine)
q = session.query(Parent)
q = q.options(orm.joinedload(Parent.children))
q = q.order_by(Parent.expr)
# Workaround:
#q = q.order_by(sa.case([1)]((Parent.expr,), else_=0))
q = q.limit(10)
q.all()

[Mike Bayer]

here's some more succinct ways of looking at the bug:

from sqlalchemy import *

m = MetaData()

t = Table('t', m, Column('x', Integer))

expr1 = (t.c.x > 1).label(None)
expr2 = expr1._annotate({"foo": "bar"})

print select([expr2](expr1,))
# SELECT t.x > :x_1 AS anon_1, t.x > :x_1 AS anon_1 FROM t

expr1 = (t.c.x > 1)
expr2 = expr1._annotate({"foo": "bar"})
print select([expr2](expr1,))
# SELECT t.x > :x_1 AS anon_1, t.x > :x_1 AS anon_2 FROM t

so both outcomes aren't great, but at least the second one applies a different label. but this is just annotation. the whole point of annotation is that __hash__ stays the same so not sure why the uniquing logic isn't working here.

[Mike Bayer]

ah.

> /Users/classic/dev/sqlalchemy/test.py(20)<module>()
-> print set([expr2](expr1,))
(Pdb) hash(expr1)
269654473
(Pdb) hash(expr2)
269654473
(Pdb) expr1 == expr2
<sqlalchemy.sql.expression.BinaryExpression object at 0x1012e2a10>
(Pdb) bool(expr1 == expr2)
False
(Pdb)

[Mike Bayer]

4950b85e8384869d3f03498c6914afe5aadbf561

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)