If one of from tables used in a query is empty, the query returns no
tables. This is because of implicit cross join. Here is a simplified
example.
Code:
from sqlalchemy import create_engine, Column, Integer, or_
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)
print 'Add one A object'
session.add(A(id=1))
session.commit()
without_or = session.query(A.id).filter(A.id == 1)
with_or = session.query(A.id).filter(or_(A.id == 1, B.id == 2))
print 'Without or:', without_or.all()
print 'With or:', with_or.all()
print 'Add one B object'
session.add(B(id=1))
session.commit()
print 'Without or:', without_or.all()
print 'With or:', with_or.all()
Output:
Add one A object
Without or: [(1,)]
With or: []
Add one B object
Without or: [(1,)]
With or: [(1,)]
This is because with_or creates following SQL expression:
SELECT a.id AS a_id
FROM a, b
WHERE a.id = 1 OR b.id = 2
SQL expression's FROM clause is a cartesian product (cross join) of two
tables. If one of tables is empty, cartesian product is empty (naturally).
In this case, changing cross join to full join resolves the problem:
SELECT a.id AS a_id
FROM a FULL JOIN b ON TRUE
WHERE a.id = 1 OR b.id = 2
At least SQLite and PostgreSQL use the implicit cross join.
I don't want to create a issue before I know if I have understood something
wrong.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
