adaption of Label breaks further operations because it does not reset the _element attribute

The test case below generates the following query:

SELECT
   venue.id AS venue_id   
FROM
   venue   
WHERE
   EXISTS (
      SELECT
         1   
      FROM
         label,
         venue AS venue_alias2 
      JOIN
         venue_to_label AS venue_to_label_1 
            ON venue_alias2.id = venue_to_label_1.venue_id 
      JOIN
         label AS label_alias 
            ON label_alias.id = venue_to_label_1.label_id   
      WHERE
         venue.id = venue_alias2.id 
         AND (
            label.id = :param_1
         ) IS 1
   )

However I'd expect the subquery to use the aliased Label model, i.e. instead of "label.id = :param_1" it should say "label_alias.id = :param_1".

The problem seems to be with the column_property.

I've tested the issue with SQLAlchemy==1.0.5 Flask==0.10.1 Flask-SQLAlchemy==2.0

Minimal test case:

import unittest
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, aliased, column_property
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
db = SQLAlchemy(app)

Base = declarative_base()

venue_to_label = Table(
    'venue_to_label', db.metadata,
    Column('venue_id', Integer, ForeignKey('venue.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)
label_to_label = Table(
    'label_to_label', db.metadata,
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True),
    Column('label_id', Integer, ForeignKey('label.id'), primary_key=True)
)


class Label(db.Model):
    __tablename__ = 'label'
    id = Column(Integer, primary_key=True, nullable=False)
    is_first = column_property(id == 1)


class Venue(db.Model):
    __tablename__ = 'venue'
    id = Column(Integer, primary_key=True, nullable=False)
    labels = relationship(Label, secondary=venue_to_label)

Base.metadata.drop_all(bind=db.engine)
Base.metadata.create_all(bind=db.engine)


class TestColPropAliasBug(unittest.TestCase):

    def test_column_property_aliased_bug(self):

        query = db.session.query(Venue)

        venue_alias2 = aliased(Venue, name="venue_alias2")
        subquery = db.session.query(venue_alias2)
        label_alias = aliased(venue_alias2.labels, name="label_alias")
        subquery = subquery.join(label_alias, venue_alias2.labels)

        # relate queries
        subquery = subquery.filter(Venue.id == venue_alias2.id)

        # filter_ = label_alias.id.is_(1)  # works as expected
        filter_ = label_alias.is_first.is_(True)  # doesn't work as expected
        subquery = subquery.filter(filter_)
        query = query.filter(subquery.exists())

        print query

Comments (5)