TableClause.count() gives the wrong answer

For tables without a primary key, TableClause.count() surprisingly compiles down to SELECT count(<first-column-in-ColumnCollection>)... instead of SELECT count(*).... As a consequence, the returned result is incorrect by the number of NULLs in the first column in the column collection. This behavior is unexpected, and I couldn't find any documentation on the subject.

Here is the offending code (from https://bitbucket.org/zzzeek/sqlalchemy/src/7189d0bc82598c2d6dcbb55b054837416db2ee7d/lib/sqlalchemy/sql/selectable.py?at=master&fileviewer=file-view-default):

    @util.dependencies("sqlalchemy.sql.functions")
    def count(self, functions, whereclause=None, **params):
        """return a SELECT COUNT generated against this
        :class:`.TableClause`."""

        if self.primary_key:
            col = list(self.primary_key)[0]
        else:
            col = list(self.columns)[0]
        return Select(
            [functions.func.count(col).label('tbl_row_count')],
            whereclause,
            from_obj=[self],
            **params)

Comments (7)