Tortoise-orm: Error while performing a COUNT query after filtering on the id of the related model with sqllite3.

[croshchupkin]

The subj. I got the following exception: sqlite3.OperationalError: near "*": syntax error

For repro, please create the following models:

# models.py
from tortoise import Model, fields


class Contact(Model):
    id = fields.IntField(pk=True)
    phone_no = fields.CharField(30, null=False, default='')
    user = fields.ForeignKeyField('models.User', related_name='contacts',
                                  on_delete=fields.CASCADE, null=False)


class User(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(128, null=False, required=True)

Then run the following script:

from tortoise import run_async, Tortoise

from models import Contact


async def run():
    await Tortoise.init(
        db_url='sqlite://db.sqlite3',
        modules={'models': ['models']}
    )
    await Tortoise.generate_schemas()

    await Contact\
        .filter(user__id=1)\
        .count()


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

The raw query seems to be:

SELECT COUNT("contact".*) FROM "contact" LEFT OUTER JOIN "user" ON "user"."id"="contact"."user_id" WHERE "user"."id"=1

[grigi]

Oh, that is definitely wrong. We will have to confirm wether it is a bug in how we use PyPika, or in PyPika itself.

Thank you for the sample script.

[csrftoken]

I use mysql, same isuse, use count.

[grigi]

I'm travelling this week, I'll look at it next week. To fix the SQL issue it should be:

SELECT COUNT("contact"."*") FROM "contact" LEFT OUTER JOIN "user" ON "user"."id"="contact"."user_id" WHERE "user"."id"=1

I strongly suspect the issue is in PyPika not quoting * when there is a table-name involved, or it could be the way we use count() in it.

[grigi]

Please confirm that v0.11.8 fixes the issue for you?

[croshchupkin]

It seems that everything works fine now. I don't get any exceptions on the repro code.