annotate group_by confused

models:

from tortoise import Tortoise, fields, run_async
from tortoise.functions import Sum
from tortoise.models import Model


class OrderInfo(Model):
    pay_time: int = fields.IntField()
    pay_amount: int = fields.IntField()
    order_no: str = fields.CharField(128)


class BillInfo(Model):
    order: fields.ForeignKeyRelation = fields.OneToOneField('models.OrderInfo', related_name='bill')
    status: int = fields.IntField()
    amount: int = fields.IntField()


async def run():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()
    now = 0
    for i in range(11):
        pay_time = now - i if i % 2 else now + i
        order_row = await OrderInfo.create(pay_time=pay_time, order_no=i, pay_amount=i)
        status = 0 if i % 2 else 1
        bill = await BillInfo.create(order=order_row, status=status, amount=i)
    print(await BillInfo.filter(status=1).count())
    print(await BillInfo.filter(order__pay_time__gt=now).count())
    print(await BillInfo.filter(status=1).annotate(total_amount=Sum('amount')).values('total_amount'))
    print(await BillInfo.filter(order__pay_time__gt=now).annotate(total_amount=Sum('amount')).values('total_amount'))

    conn = Tortoise.get_connection("default")
    sql = """SELECT SUM(bi.amount) total_amount FROM billinfo bi LEFT JOIN orderinfo oi ON bi.order_id = oi.id WHERE oi.pay_time > 0"""
    rows = await conn.execute_query_dict(sql)
    print(rows)


if __name__ == "__main__":
    run_async(run())

code

BillInfo.filter(order__pay_time__gt=now).annotate(total_amount=Sum('amount')).values('total_amount')

produce sql:

SELECT SUM(bi.amount) total_amount FROM billinfo bi LEFT JOIN orderinfo oi ON bi.order_id = oi.id WHERE oi.pay_time > 0 GROUP BY bi.id

Expected sql

SELECT SUM(bi.amount) total_amount FROM billinfo bi LEFT JOIN orderinfo oi ON bi.order_id = oi.id WHERE oi.pay_time > 0

Run the supplied async reproduction in run() with the in-memory SQLite database and compare the generated SQL with the expected query. Trace the ORM's handling of annotate() and values() when the filter uses order__pay_time__gt; done means the unnecessary GROUP BY bi.id is no longer generated and the reported aggregate result matches the expected SQL.