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