[Sqlalchemy-tickets] Issue #3530: bind param replacement in join_condition() doesn't work for heterogeneous types (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3530: bind param replacement in join_condition() doesn't work for heterogeneous types
https://bitbucket.org/zzzeek/sqlalchemy/issues/3530/bind-param-replacement-in-join_condition

Mike Bayer:


```
#!python

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

Base = declarative_base()


class CastToIntegerType(TypeDecorator):
    impl = String

    def column_expression(self, col):
        return cast(col, Integer)

    def bind_expression(self,col):
        return cast(col, String)

class Person(Base):
    __tablename__ = 'person'
    id = Column('id_string', CastToIntegerType, primary_key=True)

    pets = relationship('Pets',
        primaryjoin='foreign(Pets.person_id)==Person.id')

class Pets(Base):
    __tablename__ = 'pets'
    id = Column('id', Integer, primary_key=True)
    person_id = Column('person_id', Integer, ForeignKey('person.id_string'), primary_key=True)


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

s = Session(e)
s.add_all([Person(id="5", pets=[Pets(id="1")])])
s.commit()

p1 = s.query(Person).first()
p1.pets

```


generates:


```
#!sql

SELECT pets.id AS pets_id, pets.person_id AS pets_person_id 
FROM pets 
WHERE pets.person_id = CAST(? AS VARCHAR)

```

should generate:


```
#!sql

SELECT pets.id AS pets_id, pets.person_id AS pets_person_id 
FROM pets 
WHERE pets.person_id = ?

```

that is, inside of create_lazy_clause()->col_to_bind() we are taking the type of the column itself when we create the bindparam but not taking into account the thing we're comparing to.  

this would fix that:


```
#!diff

diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 552ce8b..6ac3ac0 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -2782,6 +2782,7 @@ class JoinCondition(object):
     def create_lazy_clause(self, reverse_direction=False):
         binds = util.column_dict()
         equated_columns = util.column_dict()
+        types = util.column_dict()
 
         has_secondary = self.secondaryjoin is not None
 
@@ -2790,12 +2791,15 @@ class JoinCondition(object):
             for l, r in self.local_remote_pairs:
                 lookup[l].append((l, r))
                 equated_columns[r] = l
+                types[l] = r.type
         elif not reverse_direction:
             for l, r in self.local_remote_pairs:
                 equated_columns[r] = l
+                types[l] = r.type
         else:
             for l, r in self.local_remote_pairs:
                 equated_columns[l] = r
+                types[r] = l.type
 
         def col_to_bind(col):
 
@@ -2808,7 +2812,8 @@ class JoinCondition(object):
             ):
                 if col not in binds:
                     binds[col] = sql.bindparam(
-                        None, None, type_=col.type, unique=True)
+                        None, None,
+                        type_=types.get(col, col.type), unique=True)
                 return binds[col]
             return None
 

```