[Sqlalchemy-tickets] Issue #3663: column_property with func has static column alias (zzzeek/sqlalchemy)

[Lukas S. <iss...@bi...>]

New issue 3663: column_property with func has static column alias
https://bitbucket.org/zzzeek/sqlalchemy/issues/3663/column_property-with-func-has-static

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".


```
#!sql

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:
```
#!python
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

```

Responsible: zzzeek