Combining queries(doing an or on a queryset) performs or at wrong nesting levels

[karuhanga]

1. I started out with this queryset;

SELECT collection_collection.id, collection_collection.public_access, collection_collection.created_at, collection_collection.updated_at, collection_collection.created_by, collection_collection.updated_by, collection_collection.is_active, collection_collection.extras, collection_collection.uri, collection_collection.mnemonic, collection_collection.parent_type_id, collection_collection.parent_id, collection_collection.name, collection_collection.full_name, collection_collection.default_locale, collection_collection.supported_locales, collection_collection.website, collection_collection.description, collection_collection.external_id, collection_collection.custom_validation_schema, collection_collection.references, collection_collection.collection_type, collection_collection.preferred_source, collection_collection.repository_type, collection_collection.custom_resources_linked_source FROM collection_collection WHERE (collection_collection.is_active = True  AND collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133 ) [<Collection: 86285>, <Collection: 86772>]
{
        "op" : "query",
        "ns" : "ocl.collection_collection",
        "query" : {
                "find" : "collection_collection",
                "filter" : {
                        "parent_id" : "5d721432394d010a2a73c0e3",
                        "is_active" : true,
                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                },
                "ntoreturn" : 21
        },
        "ts" : ISODate("2019-09-06T08:09:23.162Z")
}

2. Added filters on top of original queryset;

SELECT collection_collection.id, collection_collection.public_access, collection_collection.created_at, collection_collection.updated_at, collection_collection.created_by, collection_collection.updated_by, collection_collection.is_active, collection_collection.extras, collection_collection.uri, collection_collection.mnemonic, collection_collection.parent_type_id, collection_collection.parent_id, collection_collection.name, collection_collection.full_name, collection_collection.default_locale, collection_collection.supported_locales, collection_collection.website, collection_collection.description, collection_collection.external_id, collection_collection.custom_validation_schema, collection_collection.references, collection_collection.collection_type, collection_collection.preferred_source, collection_collection.repository_type, collection_collection.custom_resources_linked_source FROM collection_collection WHERE (collection_collection.is_active = True  AND collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133  AND NOT (collection_collection.public_access = None )) [<Collection: 86772>]
{
        "op" : "query",
        "ns" : "ocl.collection_collection",
        "query" : {
                "find" : "collection_collection",
                "filter" : {
                        "parent_id" : "5d721432394d010a2a73c0e3",
                        "public_access" : {
                                "$ne" : "None"
                        },
                        "is_active" : true,
                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                },
                "ntoreturn" : 21
        },
        "ts" : ISODate("2019-09-06T08:09:23.166Z")
}

3. Added a different set of filters on the original queryset;

SELECT collection_collection.id, collection_collection.public_access, collection_collection.created_at, collection_collection.updated_at, collection_collection.created_by, collection_collection.updated_by, collection_collection.is_active, collection_collection.extras, collection_collection.uri, collection_collection.mnemonic, collection_collection.parent_type_id, collection_collection.parent_id, collection_collection.name, collection_collection.full_name, collection_collection.default_locale, collection_collection.supported_locales, collection_collection.website, collection_collection.description, collection_collection.external_id, collection_collection.custom_validation_schema, collection_collection.references, collection_collection.collection_type, collection_collection.preferred_source, collection_collection.repository_type, collection_collection.custom_resources_linked_source FROM collection_collection WHERE (collection_collection.is_active = True  AND collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133  AND ((collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133 ) OR (collection_collection.parent_type_id = 5d664271394d01003b4a8131  AND collection_collection.parent_id IN (5d721432394d010a2a73c0e8)))) [<Collection: 86285>, <Collection: 86772>]
{
        "op" : "query",
        "ns" : "ocl.collection_collection",
        "query" : {
                "find" : "collection_collection",
                "filter" : {
                        "$or" : [
                                {
                                        "parent_id" : "5d721432394d010a2a73c0e3",
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                                },
                                {
                                        "parent_id" : {
                                                "$in" : [
                                                        "5d721432394d010a2a73c0e8"
                                                ]
                                        },
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8131")
                                }
                        ],
                        "parent_id" : "5d721432394d010a2a73c0e3",
                        "is_active" : true,
                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                },
                "ntoreturn" : 21
        },
        "ts" : ISODate("2019-09-06T08:09:23.173Z")
}

4. And ored them(2 and 3) together;

SELECT collection_collection.id, collection_collection.public_access, collection_collection.created_at, collection_collection.updated_at, collection_collection.created_by, collection_collection.updated_by, collection_collection.is_active, collection_collection.extras, collection_collection.uri, collection_collection.mnemonic, collection_collection.parent_type_id, collection_collection.parent_id, collection_collection.name, collection_collection.full_name, collection_collection.default_locale, collection_collection.supported_locales, collection_collection.website, collection_collection.description, collection_collection.external_id, collection_collection.custom_validation_schema, collection_collection.references, collection_collection.collection_type, collection_collection.preferred_source, collection_collection.repository_type, collection_collection.custom_resources_linked_source FROM collection_collection WHERE ((collection_collection.is_active = True  AND collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133  AND NOT (collection_collection.public_access = None )) OR (collection_collection.is_active = True  AND collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133  AND ((collection_collection.parent_id = 5d721432394d010a2a73c0e3  AND collection_collection.parent_type_id = 5d664271394d01003b4a8133 ) OR (collection_collection.parent_type_id = 5d664271394d01003b4a8131  AND collection_collection.parent_id IN (5d721432394d010a2a73c0e8))))) [<Collection: 86285>, <Collection: 3870>, <Collection: 86772>, <Collection: 53710>]
{
        "op" : "query",
        "ns" : "ocl.collection_collection",
        "query" : {
                "find" : "collection_collection",
                "filter" : {
                        "$or" : [
                                {
                                        "parent_id" : "5d721432394d010a2a73c0e3",
                                        "public_access" : {
                                                "$ne" : "None"
                                        },
                                        "is_active" : true,
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                                },
                                {
                                        "parent_id" : "5d721432394d010a2a73c0e3",
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                                },
                                {
                                        "parent_id" : {
                                                "$in" : [
                                                        "5d721432394d010a2a73c0e8"
                                                ]
                                        },
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8131")
                                },
                                {
                                        "parent_id" : "5d721432394d010a2a73c0e3",
                                        "is_active" : true,
                                        "parent_type_id" : ObjectId("5d664271394d01003b4a8133")
                                }
                        ],
                },
                "ntoreturn" : 21
        },
        "ts" : ISODate("2019-09-06T08:09:23.178Z")
}

4 shows the queries we incorrectly combined, i.e instead of oring them together, it simply combined all the filters into a single or block.

[karuhanga]

I have forked mongo-db-engine and added tests to replicate this(karuhanga/mongodb-engine@7755014).