Arguments dropped in tuple comparison

[Patrick Hayes]

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

[Mike Bayer]

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 Python tuple() 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:

class Comparison(object):
    def __init__(self, left, right):
        self.left = left
        self.right = right

    def __bool__(self):
        return self.left is self.right

    def __lt__(self, other):
        print "Comparison %s lt %s" % (self, other)

    def __repr__(self):
        return "Comparison(%s, %s)" % (self.left, self.right)

class Thing(object):
    def __init__(self, id):
        self.id = id

    def __lt__(self, other):
        print "%s < %s" % (self, other)
        return Comparison(self, other)

    def __repr__(self):
        return "Thing(%s)" % self.id

print (Thing(1), Thing(2)) < (Thing(3), Thing(4))

output:

#!

Thing(1) < Thing(3)
Comparison(Thing(1), Thing(3))

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.

[Patrick Hayes]

Okay - thanks for the thorough analysis. I agree there isn't anything that could be done here.