<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">Firstly. Fantastic software guys!<br>
Here's the problem:<br>
changing lazy=True to lazy='dynamic' on an orm.relation function with a 'secondary_join' and an 'order_by' filter results in broken SQL.<br>
Essentially the secondary table is aliased in the SQL but the order_by reference to the secondary is not.<br>
I have a small program that exhibits the problem on a MySQL database.</p>
<p dir="auto"><strong>Migrated issue, originally created by pkyosx (<a href="https://github.com/pkyosx">@pkyosx</a>)</strong></p>
<p dir="auto">Suppose we have Parent and Child classes as One-To-Many relationship. If I move child from one parent to another, the order of changing foreign_key and foreign_object result in different behavior. Following are the briefing of reproducing steps. Attachments are runnable reproducing scripts. It still happens in 1.1.0b1.dev0 build.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="// Models
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship(&quot;Child&quot;, back_populates=&quot;parent&quot;)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship(&quot;Parent&quot;, back_populates=&quot;children&quot;)

// Initial Setting
p1 = Parent()
p2 = Parent()
c = Child()
c.parent = p1
s.commit()

// Scenario1: Move c from p1 to p2
c.parent_id = p2.id
c.parent = p2
assert c in p2.children, &quot;this will fail&quot;

// Scenario 2: Move c from p1 to p2
c.parent = p2
c.parent_id = p2.id
assert c in p2.children, &quot;this will success&quot;"><pre class="notranslate"><code class="notranslate">// Models
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

// Initial Setting
p1 = Parent()
p2 = Parent()
c = Child()
c.parent = p1
s.commit()

// Scenario1: Move c from p1 to p2
c.parent_id = p2.id
c.parent = p2
assert c in p2.children, "this will fail"

// Scenario 2: Move c from p1 to p2
c.parent = p2
c.parent_id = p2.id
assert c in p2.children, "this will success"
</code></pre></div>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/3708/fail.py">fail.py</a> | <a href="../wiki/imported_issue_attachments/3708/test.py">test.py</a></p>