- The :func:`.type_coerce` construct is now a fully fledged Core

expression element which is late-evaluated at compile time.  Previously,
the function was only a conversion function which would handle different
expression inputs by returning either a :class:`.Label` of a column-oriented
expression or a copy of a given :class:`.BindParameter` object,
which in particular prevented the operation from being logically
maintained when an ORM-level expression transformation would convert
a column to a bound parameter (e.g. for lazy loading).
fixes #3531

3 files changed, 140 insertions, 2 deletions

diff --git a/test/orm/test_lazy_relations.py b/test/orm/test_lazy_relations.py
index ea39753b4..706f49d6f 100644
--- a/test/orm/test_lazy_relations.py
+++ b/test/orm/test_lazy_relations.py
@@ -1073,3 +1073,75 @@ class RefersToSelfLazyLoadInterferenceTest(fixtures.MappedTest):
         session.query(B).options(
             sa.orm.joinedload('parent').joinedload('zc')).all()
 
+
+class TypeCoerceTest(fixtures.MappedTest, testing.AssertsExecutionResults,):
+    """ORM-level test for [ticket:3531]"""
+
+    class StringAsInt(TypeDecorator):
+        impl = String
+
+        def column_expression(self, col):
+            return sa.cast(col, Integer)
+
+        def bind_expression(self, col):
+            return sa.cast(col, String)
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table(
+            'person', metadata,
+            Column("id", cls.StringAsInt, primary_key=True),
+        )
+        Table(
+            "pets", metadata,
+            Column("id", Integer, primary_key=True),
+            Column("person_id", cls.StringAsInt()),
+        )
+
+    @classmethod
+    def setup_classes(cls):
+        class Person(cls.Basic):
+            pass
+
+        class Pet(cls.Basic):
+            pass
+
+    @classmethod
+    def setup_mappers(cls):
+        mapper(cls.classes.Person, cls.tables.person, properties=dict(
+            pets=relationship(
+                cls.classes.Pet, primaryjoin=(
+                    orm.foreign(cls.tables.pets.c.person_id) ==
+                    sa.cast(
+                        sa.type_coerce(cls.tables.person.c.id, Integer),
+                        Integer
+                    )
+                )
+            )
+        ))
+
+        mapper(cls.classes.Pet, cls.tables.pets)
+
+    def test_lazyload_singlecast(self):
+        Person = self.classes.Person
+        Pet = self.classes.Pet
+
+        s = Session()
+        s.add_all([
+            Person(id=5), Pet(id=1, person_id=5)
+        ])
+        s.commit()
+
+        p1 = s.query(Person).first()
+
+        with self.sql_execution_asserter() as asserter:
+            p1.pets
+
+        asserter.assert_(
+            CompiledSQL(
+                "SELECT pets.id AS pets_id, CAST(pets.person_id AS INTEGER) "
+                "AS pets_person_id FROM pets "
+                "WHERE pets.person_id = CAST(:param_1 AS INTEGER)",
+                [{'param_1': 5}]
+            )
+        )
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 7ff7d68af..c957b2f8a 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -3484,13 +3484,15 @@ class ResultMapTest(fixtures.TestBase):
         tc = type_coerce(t.c.a, String)
         stmt = select([t.c.a, l1, tc])
         comp = stmt.compile()
-        tc_anon_label = comp._create_result_map()['a_1'][1][0]
+        tc_anon_label = comp._create_result_map()['anon_1'][1][0]
         eq_(
             comp._create_result_map(),
             {
                 'a': ('a', (t.c.a, 'a', 'a'), t.c.a.type),
                 'bar': ('bar', (l1, 'bar'), l1.type),
-                'a_1': ('%%(%d a)s' % id(tc), (tc_anon_label, 'a_1'), tc.type),
+                'anon_1': (
+                    '%%(%d anon)s' % id(tc),
+                    (tc_anon_label, 'anon_1', tc), tc.type),
             },
         )
 
diff --git a/test/sql/test_types.py b/test/sql/test_types.py
index 288482392..f1fb611fb 100644
--- a/test/sql/test_types.py
+++ b/test/sql/test_types.py
@@ -12,6 +12,7 @@ from sqlalchemy import (
     BLOB, NCHAR, NVARCHAR, CLOB, TIME, DATE, DATETIME, TIMESTAMP, SMALLINT,
     INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, Array)
 from sqlalchemy.sql import ddl
+from sqlalchemy.sql import visitors
 from sqlalchemy import inspection
 from sqlalchemy import exc, types, util, dialects
 for name in dialects.__all__:
@@ -789,6 +790,68 @@ class TypeCoerceCastTest(fixtures.TablesTest):
             [('BIND_INd1', 'BIND_INd1BIND_OUT')]
         )
 
+    def test_cast_replace_col_w_bind(self):
+        self._test_replace_col_w_bind(cast)
+
+    def test_type_coerce_replace_col_w_bind(self):
+        self._test_replace_col_w_bind(type_coerce)
+
+    def _test_replace_col_w_bind(self, coerce_fn):
+        MyType = self.MyType
+
+        t = self.tables.t
+        t.insert().values(data=coerce_fn('d1', MyType)).execute()
+
+        stmt = select([t.c.data, coerce_fn(t.c.data, MyType)])
+
+        def col_to_bind(col):
+            if col is t.c.data:
+                return bindparam(None, "x", type_=col.type, unique=True)
+            return None
+
+        # ensure we evaulate the expression so that we can see
+        # the clone resets this info
+        stmt.compile()
+
+        new_stmt = visitors.replacement_traverse(stmt, {}, col_to_bind)
+
+        # original statement
+        eq_(
+            testing.db.execute(stmt).fetchall(),
+            [('BIND_INd1', 'BIND_INd1BIND_OUT')]
+        )
+
+        # replaced with binds; CAST can't affect the bound parameter
+        # on the way in here
+        eq_(
+            testing.db.execute(new_stmt).fetchall(),
+            [('x', 'BIND_INxBIND_OUT')] if coerce_fn is type_coerce
+            else [('x', 'xBIND_OUT')]
+        )
+
+    def test_cast_bind(self):
+        self._test_bind(cast)
+
+    def test_type_bind(self):
+        self._test_bind(type_coerce)
+
+    def _test_bind(self, coerce_fn):
+        MyType = self.MyType
+
+        t = self.tables.t
+        t.insert().values(data=coerce_fn('d1', MyType)).execute()
+
+        stmt = select([
+            bindparam(None, "x", String(50), unique=True),
+            coerce_fn(bindparam(None, "x", String(50), unique=True), MyType)
+        ])
+
+        eq_(
+            testing.db.execute(stmt).fetchall(),
+            [('x', 'BIND_INxBIND_OUT')] if coerce_fn is type_coerce
+            else [('x', 'xBIND_OUT')]
+        )
+
     @testing.fails_on(
         "oracle", "ORA-00906: missing left parenthesis - "
         "seems to be CAST(:param AS type)")
@@ -822,6 +885,7 @@ class TypeCoerceCastTest(fixtures.TablesTest):
             [('BIND_INd1BIND_OUT', )])
 
 
+
 class VariantTest(fixtures.TestBase, AssertsCompiledSQL):
 
     def setup(self):