<p dir="auto"><strong>Migrated issue, originally created by Lukas Siemon (<a href="https://github.com/lukas-gitl">@lukas-gitl</a>)</strong></p>
<p dir="auto">I have an issue with column_property and using database specific functions. It seems like the alias is not created correctly in the query.</p>
<p dir="auto">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:<br>
<em>InvalidRequestError: Ambiguous column name 'hex(CAST(label.id AS VARCHAR) || :param_1)' in result set! try 'use_labels' option on select statement.</em></p>
<p dir="auto">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?</p>
<p dir="auto">The test case and generated query below. I would have expected the returned columns to be named "hex_1" and "hex_2".</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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"><pre class="notranslate"><code class="notranslate">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
</code></pre></div>
<p dir="auto">Test case:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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'] = (&quot;sqlite://&quot;)
        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(&quot;Link&quot;, 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(&quot;Label&quot;, 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
"><pre class="notranslate"><code class="notranslate">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

</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">A new mysql driver approaches!</p>
<p dir="auto">I wrote oursql to be a better mysql driver for python, and now I've created this sqlalchemy layer for it. It seems to pass all of the unit tests when running against a mysql 5.1 server.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1613/oursql2.patch">oursql2.patch</a> | <a href="../wiki/imported_issue_attachments/1613/oursql.patch">oursql.patch</a></p>