Accessing a Query with a Subquery will break it for subsequent use

[spacemanspiff2007]

trafficstars

Filtering relationships with sub queries does not work.

To Reproduce

from tortoise import fields, run_async, Tortoise
from tortoise.expressions import Subquery
from tortoise.fields import ManyToManyRelation, ManyToManyField
from tortoise.models import Model


class MyModel(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()
    properties: ManyToManyRelation["Property"]


class Property(Model):
    id = fields.IntField(pk=True)
    my_models: ManyToManyRelation["MyModel"] = ManyToManyField("models.MyModel", related_name='properties')


async def run():

    # Generate the schema
    await Tortoise.init({"connections": {"default_connection": {"engine": "tortoise.backends.sqlite", "credentials": {"file_path": ':memory:'},},}, "apps": {"models": {"models": ['__main__'], "default_connection": "default_connection"},},})
    await Tortoise.generate_schemas()

    m1 = await MyModel.create(id=1, name='Model1')
    m2 = await MyModel.create(id=2, name='Model2')
    p1 = await Property.create(id=1)
    p2 = await Property.create(id=2)

    await m1.properties.add(p1)
    await m2.properties.add(p1, p2)
    print('Properties Model1:', await m1.properties.all())    # Ouput: Model1: [<Property: 1>]
    print('Properties Model2:', await m2.properties.all())    # Ouput: Model2: [<Property: 1>, <Property: 2>]
    print()

    sub1 = Subquery(MyModel.filter(properties__id=1).values("id"))  # filter all relations with Property 1
    sub2 = Subquery(MyModel.filter(properties__id=2).values("id"))  # filter all relations with Property 2

    query = MyModel.filter(pk__in=sub1)  # This should select Model 1 and Model 2
    query = query.filter(pk__in=sub2)    # This should select Model 2

    # ------------------------------------------------------------------------------------------------------------------
    # commenting out one of these statements will change the result
    print('SQL1 : ', query.sql())

    print('Count: ', await query.count())  # Output: 0  but expected 1
    print('All  : ', await query.all())    # Output: [] but expected [<MyModel: 1>]

    # this will always show something different than the first query.sql()
    print('SQL2 : ', query.sql())


run_async(run())

Result from snippet. count and all is not working. Note that the SQL statement changed from SQL1 to SQL2.

SQL1 :  SELECT "id","name" FROM "mymodel" WHERE "id" IN (SELECT "mymodel"."id" "id" FROM "mymodel" LEFT OUTER JOIN "property_mymodel" ON "mymodel"."id"="property_mymodel"."mymodel_id" LEFT OUTER JOIN "property" ON "property_mymodel"."property_id"="property"."id" WHERE "property"."id"=1) AND "id" IN (SELECT "mymodel"."id" "id" FROM "mymodel" LEFT OUTER JOIN "property_mymodel" ON "mymodel"."id"="property_mymodel"."mymodel_id" LEFT OUTER JOIN "property" ON "property_mymodel"."property_id"="property"."id" WHERE "property"."id"=2)
Count:  0
All  :  []
SQL2 :  SELECT "id","name" FROM "mymodel" WHERE "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=1) AND "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=2)

Result with SQL1 commented out. Note that count is working now, but all still isn't!

Count:  1
All  :  []
SQL2 :  SELECT "name","id" FROM "mymodel" WHERE "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=1) AND "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=2)

Result with SQL1 + count commented out. Note that all is working now!

All  :  [<MyModel: 2>]
SQL2 :  SELECT "name","id" FROM "mymodel" WHERE "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=1) AND "id" IN (SELECT "id" "id" FROM "mymodel" WHERE "id"=2)

PS: The Generated SQL1 seems strange with the two left outer joins:

SELECT
    "name", "id" 
FROM
    "mymodel" 
WHERE
    "id" IN 
    (
        SELECT
            "mymodel"."id" "id" 
        FROM
            "mymodel" 
            LEFT OUTER JOIN "property_mymodel" ON "mymodel"."id" = "property_mymodel"."mymodel_id" 
            LEFT OUTER JOIN "property" ON "property_mymodel"."property_id" = "property"."id" 
        WHERE
            "property"."id" = 1
    )
    AND "id" IN 
    (
        SELECT
            "mymodel"."id" "id" 
        FROM
            "mymodel" 
            LEFT OUTER JOIN "property_mymodel" ON "mymodel"."id" = "property_mymodel"."mymodel_id" 
            LEFT OUTER JOIN "property" ON "property_mymodel"."property_id" = "property"."id" 
        WHERE
            "property"."id" = 2
    )