SQLite OperationalError when empty or_() used in query with multiple filters.

Issue #2257 resolved
Former user created an issue

When an empty or_() is passed to a query with multiple .filter() calls, it adds an empty () to the SQL. SQLite errors on this. I've tested this in the current release of 0.7.2, and the error also occurs in the latest (as of writing) snapshot of 0.7.3.

The error thrown:

sqlalchemy.exc.OperationalError: (OperationalError) near ")":
syntax error u'SELECT test.id AS test_id \nFROM test \nWHERE test.id = ? AND ()' (1,)

For a real world example, I'm appending to an or_() in a loop, the loop may not add any conditions, my code looks something like this:

    for user in user_ids:
        disjunction.append(User.id == user)

    query = query.filter(disjunction)

The bug isn't too serious, my current workaround is simple and does the job, but it would be useful for this to be fixed regardless:

    if len(user_ids) > 0:
        for user_id in user_ids:
            disjunction.append(User.id == user_id)

        query = query.filter(disjunction)

Comments (7)

  1. Mike Bayer repo owner

    I might consider this more of a "feature" than a bug, as this behavior hasn't really changed much for many years and nobody's ever had an issue with it, but don't worry that doesn't make much difference on your end. Attached is a patch that seems to do it nicely while not getting in the way of valid usages of blank "groupings" like function calls with no arguments, etc.

    Fun ! try some of these out

    from sqlalchemy.sql import table, column, and_, or_, select
    
    t = table('t', column('x'))
    
    print select([t](t)).where(and_(t.c.x==5, or_(and_(or_(t.c.x==7)))))
    print select([t](t)).where(and_(or_(t.c.x==12, and_(or_(t.c.x==8)))))
    print select([t](t)).where(and_(or_(or_(t.c.x==12), and_(or_(), or_(and_(t.c.x==8)), and_()))))
    
  2. Former user Account Deleted

    Thank you for the patch, I'm not sure I agree with OperationalError's being thrown being a 'feature' because of it been around so long. Is there any chance this will be fixed in a default branch in the future? I can't really see it being a relatively uncommon issue being a reason to not apply that patch to mainline sqlalchemy.

  3. Mike Bayer repo owner

    Would you say its a bug if you instead said and_('some expression', ''), and it rendered "some expression AND" ? There are many ways to render bad SQL with SQLAlchemy as it applies very little interpretation to the SQL commands given.

    The patch is on the 0.7.3 milestone, current released version is 0.7.2. "needs tests" status indicates unit tests must be written before the patch is committed.

  4. Log in to comment