Issue when refreshing objects in joined hierarchy schema in 3.0.0

[jdefaver]

Hi,

I got an issue when upgrading from 2.5.1 to 3.0.0.

I'm using a joined hierarchy system and two different databases (sqlite and postgresql).

I realized that if an object from the postgresql DB was expired, the system would try to load it from the default DB, sqlite in my case, even if it came originally from the postgresql DB.

Here's a minimal code to reproduce the issue:

from sqlalchemy import Column, Integer, String, case, and_, Table

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///first.db"
app.config["SQLALCHEMY_BINDS"] = {"second": "sqlite:///second.db"}
db = SQLAlchemy(app)


class Notices(db.Model):
    __bind_key__ = "second"
    __tablename__ = "Notices"
    ID_Event = db.Column(db.Text)
    Packet_Type = db.Column(db.Text, nullable=False, primary_key=True)

    __mapper_args__ = {
            "polymorphic_on": Packet_Type
            }


class Notices_Suite(Notices):
    __tablename__ = "Suite_Notices"
    Packet_Type = db.Column(db.Text, db.ForeignKey("Notices.Packet_Type"), nullable=False, primary_key=True)
    RA = db.Column(db.Float)

    __mapper_args__ = {
            "polymorphic_identity": "STANDARD",
            }


with app.app_context():
    db.create_all()
    n = Notices_Suite(
        Packet_Type="STANDARD",
        ID_Event="event",
        RA=0,
    )
    db.session.add(n)
    db.session.commit()
    alert = db.session.query(Notices).first()
    print(alert)
    db.session.expire(alert)
    print(alert.ID_Event)

Running the code raises an exception stating that the 'Notices' table does not exist, because it is trying to get it from the default DB instead of the "second"

The expected behaviour is that the object would be reloaded when needed.

Environment:

• Python version: 3.10
• Flask-SQLAlchemy version: 3.0.0
• SQLAlchemy version: 1.4.36

[davidism]

You can't have foreign keys between databases, and that hasn't changed. That's just a limitation of databases, not something that Flask-SQLAlchemy or SQLAlchemy can change.

[jdefaver]

Both models belong to the same database. This seems to work well on 2.5.1.

The first, default database is not used in this example, it is just there to show that the system defaults there after the expire.

[davidism]

I missed that you were using inheritance.

[davidism]

OK, I see what's going on, the order that our Session.get_bind checks things to find the right bind can fail for joined inheritance in some cases (and probably for other types of queries). When it fails to find a specific bind, it falls back to the default bind.