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

e.g.:

--- 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,

rationale: MySQL, Oracle and now Postgresql all have relatively flat precedences for these operators:

(mysql):

https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/operator-precedence.html

(oracle):

https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions001.htm#i1034834

pg 9.4, less flat: https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE

pg 9.5, now is more flat: https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE

making these all have the same precedence means we'll just have more parenthesis.

Issue 2:
Describe the bug

Define tables A and B with a relationship defined as A.bs = relationship("B", primaryjoin="foreign(B.a_id) == remote(A.id)").

Trying to query then with aliased classes results in a query with an incorrect join condition:

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

The rendered join incorrectly references table a in its ON clause, which is the non-aliased name of the table related to class A. It should be b_1.a_id = a_1.id instead.

To Reproduce

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__ = "a"

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

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

engine = create_engine("sqlite://")
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()

Error

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 <module>
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)

Versions

OS: Ubuntu 22.04.1 LTS

Python: 3.10.4

SQLAlchemy: 1.4.41

Database: sqlite-3.38.3

DBAPI: sqlite (but I also got the same problem using psycopg2)

Additional context

Removing the primaryjoin clause in the relationship definition of A.bs gives the right result.