Inherit condition doesn't work with a ForeignKeyConstraint on multi column

[Jean-Sébastien Suzanne]

Hi, this is an example

from sqlalchemy import (Column, Integer, String, create_engine,
                        ForeignKeyConstraint)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


#engine = create_engine('sqlite:///memory')
engine = create_engine('postgres:///memory')
Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    id2 = Column(Integer, primary_key=True)


class Test2(Test):
    __tablename__ = 'test2'
    id = Column(Integer, primary_key=True)
    id2 = Column(Integer, primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint([id, id2], [Test.id, Test.id2]),
    )

    __mapper_args__ = {
        'inherit_condition': (id == Test.id and id2 == Test.id2),
    }


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
print(str(session.query(Test2)))

We get a bad result:

#!

SELECT test2.id AS test2_id, test.id AS test_id, test2.id2 AS test2_id2, test.id2 AS test_id2 
FROM test JOIN test2 ON test2.id = test.id

Without the inherit_condition, it is seem ok

#!
SELECT test2.id AS test2_id, test.id AS test_id, test2.id2 AS test2_id2, test.id2 AS test_id2 
FROM test JOIN test2 ON test.id = test2.id AND test.id2 = test2.id2

[Mike Bayer]

hi! thanks for reporting. Your original code is incorrect. SQLAlchemy does not instrument the Python and keyword. To get an "AND" expression, you have to use either the and_() function or the & bitwise operator:

    __mapper_args__ = {
        'inherit_condition': ((id == Test.id) & (id2 == Test.id2)),
    }

output:

SELECT test2.id AS test2_id, test.id AS test_id, test2.id2 AS test2_id2, test.id2 AS test_id2 
FROM test JOIN test2 ON test2.id = test.id AND test2.id2 = test.id2

[Jean-Sébastien Suzanne]

Thanks for your quick response. Sorry for my mistake.