Skip to content

Syntax error when using a joinedload of a relationship with a null comparison as primaryjoin, a query_expression on the entity and a limit on the query. 1.4 regression #6259

New issue
New issue
Closed
Closed
Syntax error when using a joinedload of a relationship with a null comparison as primaryjoin, a query_expression on the entity and a limit on the query. 1.4 regression#6259
Labels
bugSomething isn't workinggreat mcveAn issue with a great mcveormregressionsomething worked and was broken by a change
Milestone
 
1.4.x
@paradoxxxzero

Description

@paradoxxxzero
paradoxxxzero
opened on Apr 14, 2021

Describe the bug

This may be quite the edge case but SQLAlchemy 1.4 emits SQL containing a syntax error (at least on PostgreSQL) when querying with a limit an entity containing a query_expression and using a joinedload on a relationship that has a null comparison as primaryjoin.

Expected behavior
It should behave like in SQLAlchemy 1.3 where there is no syntax error in emitted SQL.

To Reproduce

Let's consider this simple example:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import (
    declarative_base,
    joinedload,
    query_expression,
    relationship,
    sessionmaker,
)

url = "postgresql+psycopg2://..."
engine = create_engine(url)
Base = declarative_base()


class Address(Base):
    __tablename__ = "addresses"
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=True)
    user_id = Column(Integer, ForeignKey("users.id"))


class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    expr = query_expression()
    addresses_with_email = relationship(
        Address,
        primaryjoin=lambda: (Address.user_id == User.id)
        & (Address.email_address != None),
    )


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
query = session.query(User).options(joinedload("addresses_with_email")).limit(10)
print(query)

query.all()

Error

The generated SQL should be:

SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.users_id AS anon_1_users_id, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM (
  SELECT NULL AS anon_2, users.id AS users_id 
  FROM users 
  LIMIT %(param_1)s
) AS anon_1
LEFT OUTER JOIN addresses AS addresses_1 ON addresses_1.user_id = anon_1.users_id 
AND addresses_1.email_address IS NOT NULL

like it is on SQLAlchemy 1.3 but in 1.4 the generated SQL is:

SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.users_id AS anon_1_users_id, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM (
  SELECT NULL AS anon_2, users.id AS users_id 
  FROM users 
  LIMIT %(param_1)s
) AS anon_1 
LEFT OUTER JOIN addresses AS addresses_1 ON addresses_1.user_id = anon_1.users_id 
AND addresses_1.email_address IS NOT anon_1.anon_2

which raises the following syntax error:

Traceback (most recent call last):
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 692, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: syntax error at or near "anon_1"
LINE 4: ...n_1.users_id AND addresses_1.email_address IS NOT anon_1.ano...
                                                             ^


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

Traceback (most recent call last):
  File "null.py", line 40, in <module>
    query.all()
  File "*/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2685, in all
    return self._iter().all()
  File "*/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2820, in _iter
    result = self.session.execute(
  File "*/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1670, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "*/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1389, in _execute_clauseelement
    ret = self._execute_context(
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1748, in _execute_context
    self._handle_dbapi_exception(
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1929, in _handle_dbapi_exception
    util.raise_(
  File "*/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_context
    self.dialect.do_execute(
  File "*/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 692, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "anon_1"
LINE 4: ...n_1.users_id AND addresses_1.email_address IS NOT anon_1.ano...
                                                             ^

[SQL: SELECT anon_1.anon_2 AS anon_1_anon_2, anon_1.users_id AS anon_1_users_id, addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id 
FROM (SELECT NULL AS anon_2, users.id AS users_id 
FROM users 
 LIMIT %(param_1)s) AS anon_1 LEFT OUTER JOIN addresses AS addresses_1 ON addresses_1.user_id = anon_1.users_id AND addresses_1.email_address IS NOT anon_1.anon_2]
[parameters: {'param_1': 10}]
(Background on this error at: http://sqlalche.me/e/14/f405)

Versions.

  • OS: Linux
  • Python: 3.9
  • SQLAlchemy: 1.4.7 (same in all 1.4.x versions)
  • Database: postgresql 13.2
  • DBAPI: psycopg2

Thanks!

Activity

paradoxxxzero
added
requires triageNew issue that requires categorization
on Apr 14, 2021
zzzeek
added
bugSomething isn't working
orm
regressionsomething worked and was broken by a change
and removed
requires triageNew issue that requires categorization
on Apr 14, 2021
zzzeek
added this to the 1.4.x milestone on Apr 14, 2021
zzzeek
added
great mcveAn issue with a great mcve
on Apr 14, 2021
zzzeek

zzzeek commented on Apr 14, 2021

@zzzeek
zzzeek
on Apr 14, 2021
Member

very clear, the issue here is most likely in a fairly typical area of breakage and I think this might even be within 1.4 breaking but i need to bisect to get the exact change.

This may be quite the edge case

unfortunately SQLAlchemy has to live on the edge :) all of these things refer to imperfections in how it handles expressions so we'll have to resolve this.

zzzeek

zzzeek commented on Apr 14, 2021

@zzzeek
zzzeek
on Apr 14, 2021
Member

regression due to #5198. I knew i was nervous about that one.

zzzeek

zzzeek commented on Apr 14, 2021

@zzzeek
zzzeek
on Apr 14, 2021
Member

but that's in 1.3 also. hmm

zzzeek

zzzeek commented on Apr 14, 2021

@zzzeek
zzzeek
on Apr 14, 2021
Member

ok somehow it's the change in #5198 plus how 1.4 works also, OK

zzzeek

zzzeek commented on Apr 14, 2021

@zzzeek
zzzeek
on Apr 14, 2021
Member

this one was all over the place.

sqla-tester

sqla-tester commented on Apr 14, 2021

@sqla-tester
sqla-tester
on Apr 14, 2021
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

Fix with_expression() cache leak; don't adapt singletons https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2745

sqlalchemy-bot
closed this as completedin 60b0a69on Apr 15, 2021
zzzeek

zzzeek commented on Apr 15, 2021

@zzzeek
zzzeek
on Apr 15, 2021
Member

thanks for reporting!

4 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinggreat mcveAn issue with a great mcveormregressionsomething worked and was broken by a change

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@paradoxxxzero@sqla-tester

        Issue actions
          Syntax error when using a joinedload of a relationship with a null comparison as primaryjoin, a query_expression on the entity and a limit on the query. 1.4 regression · Issue #6259 · sqlalchemy/sqlalchemy