sqlalchemy.sql.expression.tuple_ should either mention compatibility issues in docs or show proper error on failure

[Priit Laes]

Currently when constructing query containing tuple_(foo).in_(...) one gets quite unhelpful OperationalError when trying to use it on SQLite backend.

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer
from sqlalchemy.sql import and_, or_
from sqlalchemy.sql.expression import tuple_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///')
#engine = create_engine('postgresql:///test')
session = sessionmaker(bind=engine)()
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'

    id = Column(Integer, primary_key=True)
    a = Column(Integer)
    b = Column(Integer)

    def __init__(self, a, b):
        self.a = a
        self.b = b

    def __repr__(self):
        return '(%d %d)' % (self.a, self.b)

Base.metadata.create_all(engine)

session.add_all([2), Foo(3, 2), Foo(3, 3), Foo(1, 3)](Foo(1,))
session.commit()
items = [2), (3, 3)]((1,)

q = session.query(Foo).filter(tuple_(Foo.a, Foo.b).in_(items))
print q
print q.all()

[Mike Bayer]

Thanks for this report.

Usually, unless there is a very obvious location to throw a NotImplementedError, such as for a specific construct that is known not to be usable at all on a particular target backend such as Sequence on MySQL, we let the backend report the error. Advantages to this include:

1. consistency. SQLAlchemy's expression language is built to support many SQL features that are not supported by all backends. It's not feasible to catch every possible type of expression construct or geometry that may fail on some backends. The kinds of things that fail range from obvious things like particular keywords, to more subtle things like SQLite not supporting the syntax a JOIN (b JOIN c ON b.id=c.id) ON a.id=b.id. We let the backend do it's job of reporting the error in the most accurate and specific way possible.

2. Avoiding redundancy in favor of stability. All the database backends we talk to are entirely capable of reporting when a particular SQL expression is not valid. For SQLAlchemy to replicate this logic throughout all of its backends would an enormous, redundant undertaking, and would work much more poorly than the very mature syntax error reporting logic built into each database. I disagree that the error message you're getting here is "unhelpful", but if you'd like SQLite to have a clearer message for particular invalid syntaxes that would be an issue with SQLite.

3. complexity/performance. Trying to detect all possible error conditions that a particular SQL parser might throw would require an enormous amount of checks built into the compiler system.

4. forwards compatibility. A new version of SQLite might support some SQL feature that we assumed it didn't support. Now users can't use that feature without a new SQLAlchemy release that checks the version of SQLite in use and allows/disallows that syntax.

In this case Tuple_ doesn't have it's own compilation method, it just uses that of ClauseList, so doesn't even have an implementation of its own. If we put a NotImplementedError right in the middle of a new visit_tuple(), that could get in the way of people using it within custom constructs - it's just a parenthesized list of parameters. Detecting specifically "tuple.in_(something)" for non-supporting backends would be a specific, complexity-adding check that would be a pretty arbitrary exception to the general rule that we don't police SQL syntaxes, so I'm -1 on any change here.

[Priit Laes]

sqlalchemy-tuple-warn.patch

[Priit Laes]

Thanks for the clarification.

I'm happy if the code stays the same, as long as it's documented, because I spent way too long time trying to figure out what I was doing wrong (on SQLite). ;)

[Mike Bayer]

OK that I can do, e0ec05366f7363edd1873c4d095e11151cdd4dff. Thanks !

[Mike Bayer]

Removing milestone: 0.7.6 (automated comment)