<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">e.g.:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1091,18 +1091,19 @@ _PRECEDENCE = {
     sub: 7,
 
     concat_op: 6,
-    match_op: 6,
-    notmatch_op: 6,
-
-    ilike_op: 6,
-    notilike_op: 6,
-    like_op: 6,
-    notlike_op: 6,
-    in_op: 6,
-    notin_op: 6,
-
-    is_: 6,
-    isnot: 6,
+
+    match_op: 5,
+    notmatch_op: 5,
+
+    ilike_op: 5,
+    notilike_op: 5,
+    like_op: 5,
+    notlike_op: 5,
+    in_op: 5,
+    notin_op: 5,
+
+    is_: 5,
+    isnot: 5,
 
     eq: 5,
     ne: 5,"><pre class="notranslate"><code class="notranslate">--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -1091,18 +1091,19 @@ _PRECEDENCE = {
     sub: 7,
 
     concat_op: 6,
-    match_op: 6,
-    notmatch_op: 6,
-
-    ilike_op: 6,
-    notilike_op: 6,
-    like_op: 6,
-    notlike_op: 6,
-    in_op: 6,
-    notin_op: 6,
-
-    is_: 6,
-    isnot: 6,
+
+    match_op: 5,
+    notmatch_op: 5,
+
+    ilike_op: 5,
+    notilike_op: 5,
+    like_op: 5,
+    notlike_op: 5,
+    in_op: 5,
+    notin_op: 5,
+
+    is_: 5,
+    isnot: 5,
 
     eq: 5,
     ne: 5,
</code></pre></div>
<p dir="auto">rationale:  MySQL, Oracle and now Postgresql all have relatively flat precedences for these operators:</p>
<p dir="auto">(mysql):<br>
<a href="https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html" rel="nofollow">https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html</a></p>
<p dir="auto">(oracle):</p>
<p dir="auto"><a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834" rel="nofollow">https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834</a></p>
<p dir="auto">pg 9.4, less flat: <a href="https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE" rel="nofollow">https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE</a></p>
<p dir="auto">pg 9.5, now is more flat: <a href="https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE" rel="nofollow">https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE</a></p>
<p dir="auto">making these all have the same precedence means we'll just have more parenthesis.</p>
<h3 dir="auto">Describe the bug</h3>
<p dir="auto">Define tables <code class="notranslate">A</code> and <code class="notranslate">B</code> with a relationship defined as <code class="notranslate">A.bs = relationship("B", primaryjoin="foreign(B.a_id) == remote(A.id)")</code>.</p>
<p dir="auto">Trying to query then with <code class="notranslate">aliased</code> classes results in a query with an incorrect join condition:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="a_alias = aliased(A)
b_alias = aliased(B)
query = select(a_alias, b_alias).join(a_alias.bs.of_type(b_alias))

print(query)
SELECT a_1.id, b_1.id AS id_1, b_1.a_id 
FROM a AS a_1
JOIN b AS b_1 ON b_1.a_id = a.id"><pre class="notranslate"><span class="pl-s1">a_alias</span> <span class="pl-c1">=</span> <span class="pl-en">aliased</span>(<span class="pl-v">A</span>)
<span class="pl-s1">b_alias</span> <span class="pl-c1">=</span> <span class="pl-en">aliased</span>(<span class="pl-v">B</span>)
<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-s1">a_alias</span>, <span class="pl-s1">b_alias</span>).<span class="pl-en">join</span>(<span class="pl-s1">a_alias</span>.<span class="pl-s1">bs</span>.<span class="pl-en">of_type</span>(<span class="pl-s1">b_alias</span>))

<span class="pl-en">print</span>(<span class="pl-s1">query</span>)
<span class="pl-v">SELECT</span> <span class="pl-s1">a_1</span>.<span class="pl-s1">id</span>, <span class="pl-s1">b_1</span>.<span class="pl-s1">id</span> <span class="pl-v">AS</span> <span class="pl-s1">id_1</span>, <span class="pl-s1">b_1</span>.<span class="pl-s1">a_id</span> 
<span class="pl-v">FROM</span> <span class="pl-s1">a</span> <span class="pl-v">AS</span> <span class="pl-s1">a_1</span>
<span class="pl-v">JOIN</span> <span class="pl-s1">b</span> <span class="pl-v">AS</span> <span class="pl-s1">b_1</span> <span class="pl-v">ON</span> <span class="pl-s1">b_1</span>.<span class="pl-s1">a_id</span> <span class="pl-c1">=</span> <span class="pl-s1">a</span>.<span class="pl-s1">id</span></pre></div>
<p dir="auto">The rendered join incorrectly references table <code class="notranslate">a</code> in its <code class="notranslate">ON</code> clause, which is the non-aliased name of the table related to class <code class="notranslate">A</code>. It should be <code class="notranslate">b_1.a_id = a_1.id</code> instead.</p>
<h3 dir="auto">To Reproduce</h3>
<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, select
from sqlalchemy.orm import Session, aliased, declarative_base, relationship

Base = declarative_base()

class A(Base):
    __tablename__ = &quot;a&quot;

    id = Column(Integer, primary_key=True)
    bs = relationship(
        &quot;B&quot;,
        primaryjoin=&quot;foreign(B.a_id) == remote(A.id)&quot;,
    )

class B(Base):
    __tablename__ = &quot;b&quot;
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey(&quot;a.id&quot;))


engine = create_engine(&quot;sqlite://&quot;)
Base.metadata.create_all(engine)

session = Session(engine)

a_alias = aliased(A)
b_alias = aliased(B)

query = select(a_alias, b_alias).join(a_alias.bs.of_type(b_alias))
session.execute(query).all()"><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-s1">select</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-v">Session</span>, <span class="pl-s1">aliased</span>, <span class="pl-s1">declarative_base</span>, <span class="pl-s1">relationship</span>

<span class="pl-v">Base</span> <span class="pl-c1">=</span> <span class="pl-en">declarative_base</span>()

<span class="pl-k">class</span> <span class="pl-v">A</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"a"</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">bs</span> <span class="pl-c1">=</span> <span class="pl-en">relationship</span>(
        <span class="pl-s">"B"</span>,
        <span class="pl-s1">primaryjoin</span><span class="pl-c1">=</span><span class="pl-s">"foreign(B.a_id) == remote(A.id)"</span>,
    )

<span class="pl-k">class</span> <span class="pl-v">B</span>(<span class="pl-v">Base</span>):
    <span class="pl-s1">__tablename__</span> <span class="pl-c1">=</span> <span class="pl-s">"b"</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">a_id</span> <span class="pl-c1">=</span> <span class="pl-v">Column</span>(<span class="pl-v">ForeignKey</span>(<span class="pl-s">"a.id"</span>))


<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-en">create_engine</span>(<span class="pl-s">"sqlite://"</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-s1">session</span> <span class="pl-c1">=</span> <span class="pl-v">Session</span>(<span class="pl-s1">engine</span>)

<span class="pl-s1">a_alias</span> <span class="pl-c1">=</span> <span class="pl-en">aliased</span>(<span class="pl-v">A</span>)
<span class="pl-s1">b_alias</span> <span class="pl-c1">=</span> <span class="pl-en">aliased</span>(<span class="pl-v">B</span>)

<span class="pl-s1">query</span> <span class="pl-c1">=</span> <span class="pl-en">select</span>(<span class="pl-s1">a_alias</span>, <span class="pl-s1">b_alias</span>).<span class="pl-en">join</span>(<span class="pl-s1">a_alias</span>.<span class="pl-s1">bs</span>.<span class="pl-en">of_type</span>(<span class="pl-s1">b_alias</span>))
<span class="pl-s1">session</span>.<span class="pl-en">execute</span>(<span class="pl-s1">query</span>).<span class="pl-en">all</span>()</pre></div>
<h3 dir="auto">Error</h3>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1900, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: a.id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File &quot;/home/gustavo/Gustavo/random/sqlalchemy_aliased_primaryjoin_relationship.py&quot;, line 36, in &lt;module&gt;
    session.scalars(query).all()
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py&quot;, line 1771, in scalars
    return self.execute(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py&quot;, line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/elements.py&quot;, line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1943, in _execute_context
    self._handle_dbapi_exception(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 2124, in _handle_dbapi_exception
    util.raise_(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py&quot;, line 208, in raise_
    raise exception
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py&quot;, line 1900, in _execute_context
    self.dialect.do_execute(
  File &quot;/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py&quot;, line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: a.id
[SQL: SELECT b_1.id, b_1.a_id 
FROM a AS a_1 JOIN b AS b_1 ON b_1.a_id = a.id]
(Background on this error at: https://sqlalche.me/e/14/e3q8)"><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: a.id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/gustavo/Gustavo/random/sqlalchemy_aliased_primaryjoin_relationship.py", line 36, in &lt;module&gt;
    session.scalars(query).all()
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1771, in scalars
    return self.execute(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/gustavo/miniconda3/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: a.id
[SQL: SELECT b_1.id, b_1.a_id 
FROM a AS a_1 JOIN b AS b_1 ON b_1.a_id = a.id]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
</code></pre></div>
<h3 dir="auto">Versions</h3>
<ul dir="auto">
<li><strong>OS:</strong> Ubuntu 22.04.1 LTS</li>
<li><strong>Python:</strong> 3.10.4</li>
<li><strong>SQLAlchemy:</strong> 1.4.41</li>
<li><strong>Database:</strong> sqlite-3.38.3</li>
<li><strong>DBAPI:</strong> sqlite (but I also got the same problem using psycopg2)</li>
</ul>
<h3 dir="auto">Additional context</h3>
<p dir="auto">Removing the <code class="notranslate">primaryjoin</code> clause in the <code class="notranslate">relationship</code> definition of <code class="notranslate">A.bs</code> gives the right result.</p>