[Sqlalchemy-tickets] Issue #4143: Inconsistent results with count & all under a combination of many-to-many, outerjoin, or & contains (zzzeek/sqlalchemy)

[Fayaz K. <iss...@bi...>]

New issue 4143: Inconsistent results with count & all under a combination of many-to-many, outerjoin, or & contains
https://bitbucket.org/zzzeek/sqlalchemy/issues/4143/inconsistent-results-with-count-all-under

Fayaz Khan:

In this specific scenario, `query.count()` & `len(query.all())` do not match:

```
#!python

from sqlalchemy import create_engine, Column, ForeignKey, Integer, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker


Base = declarative_base()
promo_like_association = Table(
    'promo_like_association', Base.metadata,
    Column('promo_id', ForeignKey('promo.id'), primary_key=True),
    Column('like_id', ForeignKey('like.id'), primary_key=True))
user_like_association = Table(
    'user_like_association', Base.metadata,
    Column('user_id', ForeignKey('user.id'), primary_key=True),
    Column('like_id', ForeignKey('like.id'), primary_key=True))


class Like(Base):
    __tablename__ = 'like'
    id = Column(Integer, primary_key=True)
    users = relationship(
        'User', secondary=user_like_association, backref='likes')


class Promo(Base):
    __tablename__ = 'promo'
    id = Column(Integer, primary_key=True)
    likes = relationship(Like, secondary=promo_like_association)


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)


def main():
    database_uri = 'sqlite:///'
    engine = create_engine(database_uri)
    Session = sessionmaker(bind=engine)
    session = Session()
    Base.metadata.create_all(engine)
    user = User(likes=[Like()])
    session.add(user)
    session.add(Promo())
    check(session, Like.users.any(id=user.id))
    check(session, Like.users.contains(user))
    user.likes.append(Like())
    check(session, Like.users.any(id=user.id))
    check(session, Like.users.contains(user))  # Fails


def check(session, condition):
    # query tries to fetch promos for user with common likes
    # or promos that have no specific likes at all.
    query = (session.query(Promo).outerjoin(Promo.likes)
             .filter(condition | ~Promo.likes.any()))
    assert query.count() == len(query.all()) == 1


if __name__ == '__main__':
    main()

```

This will fail with:

```
#!python

Traceback (most recent call last):
  File "test_sqla_contains.py", line 60, in <module>
    main()
  File "test_sqla_contains.py", line 48, in main
    check(session, Like.users.contains(user))  # Fails
  File "test_sqla_contains.py", line 56, in check
    assert query.count() == len(query.all()) == 1
AssertionError

```

I'm working around the issue using `any(id=...)` instead of `contains()`.