SQLAlchemy: ProgrammingError but the printed SQL statement works?

Question

I am trying to group and count the number of rows found in a table per month using SQLAlchemy.

The function is as follows:

def query_testing(
    session: scoped_session,
    from_date: datetime,
    to_date: datetime,
):
    qry = (
        session.query(
            func.count(U4.index).label("count"),
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            ).label("received"),
        )
        .filter(
            cast(U4.arrival, Date) >= from_date,
            cast(U4.arrival, Date) <= to_date,
        )
        .group_by(
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            ),
        )
        .order_by(
            func.dateadd(
                text("month"),
                func.datediff(text("month"), 0, U4.arrival),
                0,
            )
        )
    )

Unfortnately, executing the query using qry.all() causes a ProgrammingError:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'u4erp.received' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I print(qry) the statement and try to manually execute the generated SQL, the statement works (after manually changing ? to the their intended values). The printed statement is as follows:

SELECT
    count(u4erp.[index]) AS count,
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0) AS received
FROM
    u4erp
WHERE
    CAST(
        u4erp.received AS DATE
    ) >= '2020-01-01'
    AND CAST(
        u4erp.received AS DATE
    ) <= '2021-12-31'
GROUP BY
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0)
ORDER BY
    dateadd(MONTH, datediff(MONTH, 0, u4erp.received), 0)

What causes the printed SQL statement to work, but raises a ProgrammingError when using SQLAlchemy? How do I fix it?

Answer 1

I found a workaround for my specific usecase using text.

def query_testing(
    session: scoped_session,
    from_date: datetime,
    to_date: datetime,
):
    qry = (
        session.query(
            func.count(U4.index).label("count"),
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0) AS received"
            ),
        )
        .filter(
            cast(U4.arrival, Date) >= from_date,
            cast(U4.arrival, Date) <= to_date,
        )
        .group_by(
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0)"
            ),
        )
        .order_by(
            text(
                "DATEADD(MONTH, DATEDIFF(MONTH, 0, "
                "u4erp.received), 0)"
            ),
        )
    )