Baike.dev
Log in
Back to tool/Back to issues
#13594·sqlalchemy

documentation bug

Author: sqlalchemy-reporterCreated Sep 16, 2026Updated Sep 16, 2026
Labelsrequires triage

Describe the bug

Title: Docs: LATERAL correlation example in tutorial raises GroupingError when actually run

The "LATERAL correlation" example in the SELECT tutorial (https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#lateral-correlation) only shows print(stmt), unlike neighboring examples which show real execution. If you actually execute the generated SQL against PostgreSQL, it fails:

psycopg.errors.GroupingError: column "address.email_address" must appear in the GROUP BY clause or be used in an aggregate function

SQLAlchemy 2.0.52, PostgreSQL docker (postgres (PostgreSQL) 18.6 (Debian 18.6-1.pgdg13+2)), psycopg 3.3.5.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#lateral-correlation

SQLAlchemy Version in Use

2.0.52

DBAPI (i.e. the database driver)

psycopg 3.3.5

Database Vendor and Major Version

(PostgreSQL) 18.6

Python Version

3.13.4

Operating system

windows

To Reproduce

python
from typing import List, Optional

from sqlalchemy import String, ForeignKey, select, Column, Integer, MetaData, Table, func, create_engine
from sqlalchemy.orm import Mapped, Session, mapped_column, relationship, DeclarativeBase

class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(back_populates="user")
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id = mapped_column(ForeignKey("user_account.id"))
    user: Mapped[User] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"


metadata_obj = MetaData()
user_table = Table(
    "user_account",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("name", String(30)),
    Column("fullname", String),
)


address_table = Table(
    "address",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("user_id", ForeignKey("user_account.id"), nullable=False),
    Column("email_address", String, nullable=False),
)


def reset_database(engine):
    Base.metadata.drop_all(bind=engine)
    Base.metadata.create_all(bind=engine)


def insert_data(engine) -> None:
    with Session(engine) as session:
        patrick = User(
            name="patrick",
            fullname="Patrick Star",
            addresses=[
                Address(email_address="[email protected]"),
                Address(email_address="[email protected]"),
            ],
        )

        sandy = User(
            name="sandy",
            fullname="Sandy Cheeks",
            addresses=[
                Address(email_address="[email protected]"),
            ],
        )

        bob = User(
            name="bob",
            fullname="Bob Sponge",
            addresses=[
                Address(email_address="[email protected]"),
            ],
        )

        session.add_all([patrick, sandy, bob])
        session.commit()


engine = create_engine("postgresql+psycopg://postgres:postgres@localhost:5432/app", echo=False)
reset_database(engine)
insert_data(engine)
engine.echo = True
subq = (
    select(
        func.count(address_table.c.id).label("address_count"),
        address_table.c.email_address,
        address_table.c.user_id,
    )
    .where(user_table.c.id == address_table.c.user_id)
    .lateral()
)
stmt = (
    select(user_table.c.name, subq.c.address_count, subq.c.email_address)
    .join_from(user_table, subq)
    .order_by(user_table.c.id, subq.c.email_address)
)
with Session(engine) as session:
    res = list(session.execute(stmt))
print(res)

Error

C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Scripts\python.exe C:\Users\anon\PycharmProjects\SQLAlchemyTest\report.py 
2026-09-16 15:51:48,412 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2026-09-16 15:51:48,413 INFO sqlalchemy.engine.Engine SELECT user_account.name, anon_1.address_count, anon_1.email_address 
FROM user_account JOIN LATERAL (SELECT count(address.id) AS address_count, address.email_address AS email_address, address.user_id AS user_id 
FROM address 
WHERE user_account.id = address.user_id) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.email_address
2026-09-16 15:51:48,413 INFO sqlalchemy.engine.Engine [generated in 0.00014s] {}
2026-09-16 15:51:48,417 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
 self.dialect.do_execute(
 ~~~~~~~~~~~~~~~~~~~~~~~^
 cursor, str_statement, effective_parameters, context
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 952, in do_execute
 cursor.execute(statement, parameters)
 ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\psycopg\cursor.py", line 117, in execute
 raise ex.with_traceback(None)
psycopg.errors.GroupingError: column "address.email_address" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: ...TERAL (SELECT count(address.id) AS address_count, address.em...
 ^

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

Traceback (most recent call last):
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\report.py", line 103, in <module>
 res = list(session.execute(stmt))
 ~~~~~~~~~~~~~~~^^^^^^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2373, in execute
 return self._execute_internal(
 ~~~~~~~~~~~~~~~~~~~~~~^
 statement,
 ^^^^^^^^^^
 ...<4 lines>...
 _add_event=_add_event,
 ^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\orm\session.py", line 2280, in _execute_internal
 result = conn.execute(
 statement, params or {}, execution_options=execution_options
 )
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1421, in execute
 return meth(
 self,
 distilled_parameters,
 execution_options or NO_OPTIONS,
 )
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\sql\elements.py", line 526, in _execute_on_connection
 return connection._execute_clauseelement(
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
 self, distilled_params, execution_options
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1643, in _execute_clauseelement
 ret = self._execute_context(
 dialect,
 ...<8 lines>...
 cache_hit=cache_hit,
 )
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1848, in _execute_context
 return self._exec_single_context(
 ~~~~~~~~~~~~~~~~~~~~~~~~~^
 dialect, context, statement, parameters
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1988, in _exec_single_context
 self._handle_dbapi_exception(
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
 e, str_statement, effective_parameters, cursor, context
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 2365, in _handle_dbapi_exception
 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
 self.dialect.do_execute(
 ~~~~~~~~~~~~~~~~~~~~~~~^
 cursor, str_statement, effective_parameters, context
 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 )
 ^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 952, in do_execute
 cursor.execute(statement, parameters)
 ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
 File "C:\Users\anon\PycharmProjects\SQLAlchemyTest\.venv\Lib\site-packages\psycopg\cursor.py", line 117, in execute
 raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.GroupingError) column "address.email_address" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: ...TERAL (SELECT count(address.id) AS address_count, address.em...
 ^
[SQL: SELECT user_account.name, anon_1.address_count, anon_1.email_address 
FROM user_account JOIN LATERAL (SELECT count(address.id) AS address_count, address.email_address AS email_address, address.user_id AS user_id 
FROM address 
WHERE user_account.id = address.user_id) AS anon_1 ON user_account.id = anon_1.user_id ORDER BY user_account.id, anon_1.email_address]
(Background on this error at: https://sqlalche.me/e/20/f405)

Process finished with exit code 1

Additional context

subq and stmt were copied from the documentation.

Source: sqlalchemy/sqlalchemy

View original on GitHubView discussion on GitHub