Flask-SQLAlchemy different record count for .count() and .all()

Question

In SQLAlchemy, ModelName.query.filter_by(field_name=value).count() returns correct record count, but ModelName.query.filter_by(field_name=value).all() only returns a single record. Doing a db.session.execute("SELECT * FROM table_name WHERE field_name = 'value'") works fine. Has anybody faced a similar problem? Does anyone have any idea what could possibly be wrong. Any pointers will help.

Information that might be helpful
I am using MS SQL Server 2008 R2 and accessing it using FreeTDS/pyodbc. I do not control the database and can not change it.

Thanks in advance.

Answer 1

I think I may have fallen in the same problem. My query do multiple Joins, than the raw result can bring back multiple rows of the same primary key, each row is counted by .count(), however when you call .all() a distinct is applied on the primary key and only unique rows are fetched, than the number of records on .all() list is different from .count().

This should be equal:

query.distinct().count() == query.all()

Best regards