Arguments dropped in tuple comparison
Issue #4025
resolved
Observed in sqlalchemy==1.2.0b1, psycopg2==2.7.1, postgres 9.5
When the filter
/having
clause contains a tuple comparison, the additional args of the tuple are unexpectedly discarded.
from sqlalchemy import Column, BigInteger, create_engine
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine(URL(
'postgresql',
username='postgres',
host='localhost',
port=5432,
))
Base = declarative_base()
class Model(Base):
__tablename__ = 'model'
id = Column(BigInteger, primary_key=True)
other = Column(BigInteger)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
session = session_factory()
print session.query(Model).filter((Model.id, Model.other) < (100, 100))
This prints out:
SELECT model.id AS model_id, model.other AS model_other
FROM model
WHERE model.id < %(id_1)s
But I would expect
...
WHERE (model.id, model.other) < (%(id_1)s, %(other_1)s)
While I was able to get around this by manually casting as a tuple_
, is there a reason why this does not automatically manifest as a tuple comparison? Or throw an error to warn that arguments will be discarded?
I'm happy to investigate a PR, unless this is expected behaviour
Comments (3)
-
repo owner -
reporter Okay - thanks for the thorough analysis. I agree there isn't anything that could be done here.
-
reporter - changed status to resolved
- Log in to comment
The reason comparison operators like ==, < , > etc. produce SQL in SQLAlchemy is because we override python underscore names on SQLAlchemy objects like
Column
.In this case, what you have here are two plain Python tuples:
( <things> ) < ( <otherthings> )
. This expression cannot be intercepted using Python underscore methods because there is no way to modify the behavior of the__lt__()
function on the Pythontuple()
class.I was even surprised that you get an expression at all, as the comparison of tuples should produce a True or False value and that's it. But it appears Python does something surprising when the elements of the tuple themselves return an object value, which is that it returns the value of the comparison of the first two.
This script illustrates:
output:
With some consideration, I realize this actually makes sense because it compares Thing(1) to Thing(3), gets an answer other than "True", and then stops comparing, even though the object itself evaluates to True.
Hence it works if you put SQLAlchemy's SQL tuple construct
tuple_()
, which is part of SQLAlchemy and you'll note has it's own__lt__()
method that does the right thing. WIth the plain Python tuple in the way I don't see any way what you ask for would be possible.