Issue 1:
Migrated issue, originally created by Michael Bayer (@zzzeek)

test:

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

Base = declarative_base()

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
b_id = Column(ForeignKey('b.id'))
c_id = Column(ForeignKey('c.id'))

b = relationship("B")
c = relationship("C")

class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
c_id = Column(ForeignKey('c.id'))

c = relationship("C")

class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
d_id = Column(ForeignKey('d.id'))
d = relationship("D")

class D(Base):
__tablename__ = 'd'
id = Column(Integer, primary_key=True)

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

s = Session(e)

c = C(d=D())

s.add(
A(b=B(c=c), c=c)
)
s.commit()

c_alias_1 = aliased(C)
c_alias_2 = aliased(C)

q = s.query(A)
q = q.join(A.b).join(c_alias_1, B.c).join(c_alias_1.d)
q = q.options(contains_eager(A.b).contains_eager(B.c, alias=c_alias_1).contains_eager(C.d))
q = q.join(c_alias_2, A.c)
q = q.options(contains_eager(A.c, alias=c_alias_2))

a1 = q.all()[0]
assert 'd' in a1.c.__dict__

this test relies on the order in which we load attributes, and for some reason PYTHONHASHSEED isn't doing the job in all cases, so test with this patch:

diff --git a/lib/sqlalchemy/orm/loading.py b/lib/sqlalchemy/orm/loading.py
index 50afaf6..8de66de 100644
--- a/lib/sqlalchemy/orm/loading.py
+++ b/lib/sqlalchemy/orm/loading.py
@@ -295,6 +295,13 @@ def _instance_processor(
quick_populators = path.get(
context.attributes, "memoized_setups", _none_set)

+ import random
+ props = list(props)
+ random.shuffle(props)
+
+ print(
+ "PROPS!!!!",[p.key for p in props]
+ )
for prop in props:
if prop in quick_populators:

there's no way to force this loader to happen in all cases because ultimately when the C object is already there, the "isnew" flag is not set and we naturally hit the "existing" loader. However, the "existing" loader can do a check here and populate the key, and we are checking anyway:

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 78e9293..dff51b9 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -1559,13 +1559,15 @@ class JoinedLoader(AbstractRelationshipLoader):
# call _instance on the row, even though the object has
# been created, so that we further descend into properties
existing = _instance(row)
- if existing is not None \
- and key in dict_ \
- and existing is not dict_[key]:
- util.warn(
- "Multiple rows returned with "
- "uselist=False for eagerly-loaded attribute '%s' "
- % self)
+ if existing is not None:
+ if key in dict_:
+ if existing is not dict_[key]:
+ util.warn(
+ "Multiple rows returned with "
+ "uselist=False for eagerly-loaded attribute '%s' "
+ % self)
+ else:
+ dict_[key] = existing

def load_scalar_from_joined_exec(state, dict_, row):
_instance(row)

# this is an inlined path just for column-based attributes.

would need to do the deep thinking here to see if this is right.

Issue 2:
I have an OAuth 2.0 application that has a database, user data and token data are stored in this database. I have also another application that has its own database and need to connect to OAuth 2.0 database, to check if the user' token is valid or not, and its own database.

I connect to multiple databases on the second application this way:

# db.py
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# app.py
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://' + \
app.config['DATABASE_USER'] + ':' + \
app.config['DATABASE_PASSWORD'] + '@' + \
app.config['DATABASE_HOST'] + '/' + \
app.config['DATABASE']

app.config['SQLALCHEMY_BINDS'] = {
'oauth': 'mysql://' +
app.config['DATABASE_USER_OAUTH'] + ':' +
app.config['DATABASE_PASSWORD_OAUTH'] + '@' +
app.config['DATABASE_HOST_OAUTH'] + '/' +
app.config['DATABASE_OAUTH']
}

To check if the user' token is valid or not, on the second application I need to duplicate the user and token models (ORM) generated on the OAuth 2.0 application. The problem is that I just need the duplicated models to create a decorator, but when I generate the second application' database, the models (user and token) are tracked by SQLAlchemy and its structure is created on the second application' database. When I send a token to the second application, the OAuth 2.0 database it's used get token information, this causes a weirdo behavior in my application.

I have set the models to use the OAuth 2.0 connection:

# oauth.py
class User(db.Model):
__bind_key__ = 'oauth'

__tablename__ = 'oauth2_user'

# ...

class Token(db.Model, OAuth2TokenMixin):
__bind_key__ = 'oauth'

__tablename__ = 'oauth2_token'

# ...

But it doesn't work. I have also read the table configuration documentation, but I did not found nothing that can help me.

MySQL engine, Flask-SQLAlchemy==2.3.2, mysqlclient==1.3.14, SQLAlchemy==1.2.15.

Obs: I don't want to create the user and token table on the second application' database, but I want to have the user and token models available and at the same time use the user and token tables present on OAuth 2.0 database