<p dir="auto"><strong>Describe the bug</strong><br>
If both the relationship and query define <code class="notranslate">order_by</code>, subsequent executions of the same query lead to addition of duplicate order clauses to the query. This also manifests itself as a memory leak.</p>
<p dir="auto"><strong>To Reproduce</strong></p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, sessionmaker


engine = create_engine('postgresql://test@localhost/db', echo=True)
Base = declarative_base()
Session = sessionmaker(engine)


class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)

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

    parent = relationship(
        Parent, backref=backref('children', lazy='dynamic', order_by=id)
    )

Base.metadata.create_all(engine)

with Session() as session:
    parent = Parent(id=1)
    session.add(parent)
    parent.children.order_by(Child.id.asc()).all()  # ORDER BY child.id, child.id ASC
    parent.children.order_by(Child.id.asc()).all()  # ORDER BY child.id, child.id ASC, child.id ASC
    parent.children.order_by(Child.id.asc()).all()  # ORDER BY child.id, child.id ASC, child.id ASC, child.id ASC"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span> <span class="pl-k">import</span> <span class="pl-v">Column</span>, <span class="pl-v">ForeignKey</span>, <span class="pl-v">Integer</span>, <span class="pl-s1">create_engine</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">ext</span>.<span class="pl-s1">declarative</span> <span class="pl-k">import</span> <span class="pl-s1">declarative_base</span>
<span class="pl-k">from</span> <span class="pl-s1">sqlalchemy</span>.<span class="pl-s1">orm</span> <span class="pl-k">import</span> <span class="pl-s1">backref</span>, <span class="pl-s1">relationship</span>, <span class="pl-s1">sessionmaker</span>


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">'postgresql://test@localhost/db'</span>, <span class="pl-s1">echo</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()
<span class="pl-v">Session</span> <span class="pl-c1">=</span> <span class="pl-en">sessionmaker</span>(<span class="pl-s1">engine</span>)


<span class="pl-k">class</span> <span class="pl-v">Parent</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'parent'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)

<span class="pl-k">class</span> <span class="pl-v">Child</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">'child'</span>
    <span class="pl-s1">id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-s1">primary_key</span><span class="pl-c1">=</span><span class="pl-c1">True</span>)
    <span class="pl-s1">parent_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">Integer</span>, <span class="pl-v">ForeignKey</span>(<span class="pl-v">Parent</span>.<span class="pl-s1">id</span>))

    <span class="pl-s1">parent</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(
        <span class="pl-v">Parent</span>, <span class="pl-s1">backref</span><span class="pl-c1">=</span><span class="pl-en">backref</span>(<span class="pl-s">'children'</span>, <span class="pl-s1">lazy</span><span class="pl-c1">=</span><span class="pl-s">'dynamic'</span>, <span class="pl-s1">order_by</span><span class="pl-c1">=</span><span class="pl-s1">id</span>)
    )

<span class="pl-v">Base</span>.<span class="pl-s1">metadata</span>.<span class="pl-en">create_all</span>(<span class="pl-s1">engine</span>)

<span class="pl-k">with</span> <span class="pl-v">Session</span>() <span class="pl-k">as</span> <span class="pl-s1">session</span>:
    <span class="pl-s1">parent</span> <span class="pl-c1">=</span> <span class="pl-v">Parent</span>(<span class="pl-s1">id</span><span class="pl-c1">=</span><span class="pl-c1">1</span>)
    <span class="pl-s1">session</span>.<span class="pl-en">add</span>(<span class="pl-s1">parent</span>)
    <span class="pl-s1">parent</span>.<span class="pl-s1">children</span>.<span class="pl-en">order_by</span>(<span class="pl-v">Child</span>.<span class="pl-s1">id</span>.<span class="pl-en">asc</span>()).<span class="pl-en">all</span>()  <span class="pl-c"># ORDER BY child.id, child.id ASC</span>
    <span class="pl-s1">parent</span>.<span class="pl-s1">children</span>.<span class="pl-en">order_by</span>(<span class="pl-v">Child</span>.<span class="pl-s1">id</span>.<span class="pl-en">asc</span>()).<span class="pl-en">all</span>()  <span class="pl-c"># ORDER BY child.id, child.id ASC, child.id ASC</span>
    <span class="pl-s1">parent</span>.<span class="pl-s1">children</span>.<span class="pl-en">order_by</span>(<span class="pl-v">Child</span>.<span class="pl-s1">id</span>.<span class="pl-en">asc</span>()).<span class="pl-en">all</span>()  <span class="pl-c"># ORDER BY child.id, child.id ASC, child.id ASC, child.id ASC</span></pre></div>
<p dir="auto">I'd have expected the last 3 lines to generate identical queries, but each subsequent line adds an additional clause to the <code class="notranslate">ORDER BY</code>.</p>
<p dir="auto"><strong>Error</strong><br>
No error, but a memory leak.</p>
<p dir="auto"><strong>Versions.</strong></p>
<ul dir="auto">
<li>OS: MacOS 11.4</li>
<li>Python: 3.6.13</li>
<li>SQLAlchemy: 1.4.15</li>
<li>Database: PostgreSQL</li>
<li>DBAPI: psycopg2</li>
</ul>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">(original reporter: rbu) We had quite a hard time figuring out the JOIN order as generated by a query(A).join(B) statement. All examples in the documentation have A as left and B as right side. Apparently though, when giving an explicit criterion as query(A).join(B.a), B will be the left side even when adding a select_from.</p>
<p dir="auto">Details:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# surprising
&gt;&gt;&gt; print DBSession.query(User.id).outerjoin(Track.user)
SELECT user.id AS user_id 
FROM track LEFT OUTER JOIN user ON user.id = track.user_id

# bug?
&gt;&gt;&gt; print DBSession.query(User.id).select_from(User).outerjoin(Track.user)
SELECT user.iduser AS user_id 
FROM track LEFT OUTER JOIN user ON user.id = track.user_id

# expected
&gt;&gt;&gt; print DBSession.query(User.id).outerjoin(User.tracks)
SELECT user.iduser AS user_id 
FROM user LEFT OUTER JOIN track ON user.id = track.user_id"><pre class="notranslate"><code class="notranslate"># surprising
&gt;&gt;&gt; print DBSession.query(User.id).outerjoin(Track.user)
SELECT user.id AS user_id 
FROM track LEFT OUTER JOIN user ON user.id = track.user_id

# bug?
&gt;&gt;&gt; print DBSession.query(User.id).select_from(User).outerjoin(Track.user)
SELECT user.iduser AS user_id 
FROM track LEFT OUTER JOIN user ON user.id = track.user_id

# expected
&gt;&gt;&gt; print DBSession.query(User.id).outerjoin(User.tracks)
SELECT user.iduser AS user_id 
FROM user LEFT OUTER JOIN track ON user.id = track.user_id
</code></pre></div>
<p dir="auto">This magic ordering based on the explicit criterion given should be covered in the documentation, but we did not find such note.</p>