sqlalchemy: Multiple lateral joins trigger a "cartesian product" warning

Describe the bug Joining more than two lateral queries produces the right SQL command but yields a “cartesian product” warning

Expected behavior No warning should be emitted

To Reproduce

from sqlalchemy import Column, Integer, Float, select, true
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = "A"
    id = Column(Integer, primary_key=True)
    value = Column(Float)


class B(Base):
    __tablename__ = "B"
    id = Column(Integer, primary_key=True)
    value = Column(Float)
    id_a = Column(Integer)


class C(Base):
    __tablename__ = "C"
    id = Column(Integer, primary_key=True)
    value = Column(Float)
    id_a = Column(Integer)


a = select(A.id, A.value).subquery()
b = select(B.value).where(a.c.id == B.id_a).order_by(B.value).limit(1).lateral()
c = select(C.value).where(a.c.id == C.id_a).order_by(C.value).limit(1).lateral()

query = select(a, b, c).select_from(a).join(b, true()).join(c, true())

# Execute query here

Note that the correct SQL is produced:

[SQL: SELECT anon_1.id, anon_1.value, anon_2.value AS value_1, anon_3.value AS value_2 
FROM (SELECT "A".id AS id, "A".value AS value 
FROM "A") AS anon_1 JOIN LATERAL (SELECT "B".value AS value 
FROM "B" 
WHERE anon_1.id = "B".id_a ORDER BY "B".value
 LIMIT ? OFFSET ?) AS anon_2 ON 1 = 1 JOIN LATERAL (SELECT "C".value AS value 
FROM "C" 
WHERE anon_1.id = "C".id_a ORDER BY "C".value
 LIMIT ? OFFSET ?) AS anon_3 ON 1 = 1]
[parameters: (1, 0, 1, 0)]

Error sqlalchemy/sql/compiler.py:357: SAWarning: SELECT statement has a cartesian product between FROM element(s) "anon_3" and FROM element "anon_1". Apply join condition(s) between each element to resolve.

Versions.

  • OS: OSX 11.2
  • Python: 3.9.1
  • SQLAlchemy: 1.4.0b2
  • Database: All
  • DBAPI: N/A

Additional context If we only join with one lateral query, then the warning is not emitted.

I also tried to add a .subquery() before the lateral(), and different combinations of join (chained or not) to no avail.

Note: Also, I am confused at to whether or not a subquery() is needed before a lateral, or if it is already implied?

Thank you,

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 15 (8 by maintainers)

Most upvoted comments

Thanks for the idea @CaselIT !

This is what worked for me (I was able to pinpoint the fix on only the very query that was at fault):

with warnings.catch_warnings():
    warnings.filterwarnings(
        "ignore", "SELECT statement has a cartesian product.*", SAWarning
    )
    ...