bind param replacement in join_condition() doesn't work for type_coerce (and why does it make a Label()?)

[Mike Bayer]

type_coerce makes its decision about the thing it is processing when created. if a bindparam, the type is changed. otherwise, a label() is produced.

However this fails for this :

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)==type_coerce(Person.id, Integer)')

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

because we are creating bindparam after the fact - there's no actual TypeCoerce construct here so when we see that Label, we don't know anything that there was a type coerce. We'd need to build some new construct.

[Mike Bayer]

this is related to #3530

[Mike Bayer]

here's the workaround which illustrates the basic idea of the construct, e.g. type_coerce has to be applied at compile time (we can perhaps add this to Label, but then why is type_coerce even using Label??)

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.elements import _clone


class LateTypeCoerce(ColumnElement):
    def __init__(self, element, type_):
        self.element = element
        self.type = type_
        self._proxies = [element]

    def self_group(self, against=None):
        return LateTypeCoerce(self.element.self_group(against=against), self.type)

    def get_children(self, **kwargs):
        return self.element,

    def _copy_internals(self, clone=_clone, **kw):
        self.element = clone(self.element, **kw)

    @property
    def _from_objects(self):
        return self.element._from_objects

    def _make_proxy(self, selectable, **kw):
        return self.element._make_proxy(selectable, **kw)


@compiles(LateTypeCoerce)
def _comp_type_coerce(element, compiler, **kw):
    return compiler.process(type_coerce(element.element, element.type))


# ...

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

    pets = relationship('Pets',
        primaryjoin=lambda: foreign(Pets.person_id) == LateTypeCoerce(Person.id, Integer))

[Mike Bayer]

wow, this wont even work though, at least in this workaround, because now eagerload is broken

[Mike Bayer]

the Label is created because we want to be able to say:

stmt = select([col, type_coerce(col, Integer), type_coerce(col, Numeric)])

and maintain all three of those columns as separate. So the fact that some kind of anonymous label is applied needs to stay. This is the same as with CAST in any case.

[Mike Bayer]

• 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

→ <<cset 24a7241b5ef6>>