column_property with func has static column alias

[Lukas Siemon]

I have an issue with column_property and using database specific functions. It seems like the alias is not created correctly in the query.

I'm obtaining data from a model and an alias of the model. When I use "func.hex" (or any "func" function), the column alias is named "hex" (or what the function is called) for both models and this causes a collision: InvalidRequestError: Ambiguous column name 'hex(CAST(label.id AS VARCHAR) || :param_1)' in result set! try 'use_labels' option on select statement.

However, when I cast the column_property as a string (see commented section in test case) this works as expected. Is this a bug or a limitation? What would be the best way to work around this?

The test case and generated query below. I would have expected the returned columns to be named "hex_1" and "hex_2".

SELECT
   hex(CAST(label.id AS VARCHAR) || :param_1) AS hex,
   hex(CAST(label_1.id AS VARCHAR) || :param_2) AS hex   
FROM
   label 
JOIN
   link 
      ON link.id = label.link_id 
JOIN
   label AS label_1 
      ON label_1.id = link.label_id

Test case:

import unittest
from sqlalchemy import (Column, Integer, String, func, ForeignKey, cast)
from sqlalchemy.orm import (column_property, relationship, aliased)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


class TestColumnPropertyStaticName(unittest.TestCase):

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

        class Label(db.Model):
            __tablename__ = 'label'
            id = Column(Integer, primary_key=True)
            hex = column_property(func.hex(func.cast(id, String) + 'data'))
            # =============================
            # ====== this would work ======
            # hex = column_property(
            #     cast(func.hex(func.cast(id, String) + 'data'), String))
            # =============================
            link_id = Column(Integer, ForeignKey('link.id'))
            link = relationship("Link", foreign_keys=link_id)

        class Link(db.Model):
            __tablename__ = 'link'
            id = Column(Integer, primary_key=True)
            label_id = Column(Integer, ForeignKey('label.id'))
            label = relationship("Label", foreign_keys=label_id)

        db.drop_all()
        db.create_all()

        self.Label = Label
        self.Link = Link
        self.db = db

    def test_dynamic_bindparam(self):
        # saving the mirrored linkage
        label = self.Label()
        link = self.Link()
        label.link = link
        self.db.session.add(label)
        self.db.session.commit()
        link.label_id = label.id
        self.db.session.commit()

        # generate the query
        query = self.Label.query
        query = query.join(self.Link, self.Link.id == self.Label.link_id)
        alias = aliased(self.Label)
        query = query.join(alias, alias.id == self.Link.label_id)
        query = query.with_entities(self.Label.hex, alias.hex)
        print query

        data = query.one()
        print data

[Mike Bayer]

Here's an easier way to show that:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(Integer)
    hex_ = column_property(func.hex(data))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(data=123))

s.commit()

a1 = aliased(A)
q = s.query(A.hex_, a1.hex_)
print q.all()

it passes in 1.1 since the duplicate names no longer matter (see http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#positional-matching-is-trusted-over-name-based-matching-for-core-orm-sql-constructs). The "func.foo" basically is a named element and because of that it isn't given an anonymous label. You can give it one by saying column_property(func.hex(col).label(None)).

[Mike Bayer]

• Anonymous labeling is applied to a :attr:.func construct that is passed to :func:.column_property, so that if the same attribute is referred to as a column expression twice the names are de-duped, thus avoiding "ambiguous column" errors. Previously, the .label(None) would need to be applied in order for the name to be de-anonymized. fixes #3663

→ <<cset 0210695bd97e>>

[Mike Bayer]

• Anonymous labeling is applied to a :attr:.func construct that is passed to :func:.column_property, so that if the same attribute is referred to as a column expression twice the names are de-duped, thus avoiding "ambiguous column" errors. Previously, the .label(None) would need to be applied in order for the name to be de-anonymized. fixes #3663

(cherry picked from commit 0210695bd97e76f58d8781b69337816501482fb0)

→ <<cset aac2cb5007ce>>

[Lukas Siemon]

As always, very impressed with your responsiveness! Thanks for solving this so fast!