Loading...

Type: Bug
Resolution: Works as Designed
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 3.6.3, 4.0.1
Component/s: Querying
Labels:
None

Operating System:
ALL

Steps To Reproduce:

Hide

Start with empty collection and the index:

> db.comment.drop()
true
> db.comment.createIndex({ 'links.id': 1 })
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

Initially, the query strategy indicates it will use the correct index

> db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.comment",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "$expr" : {
                                                "$eq" : [
                                                        "$links.id",
                                                        {
                                                                "$const" : "some-string"
                                                        }
                                                ]
                                        }
                                },
                                {
                                        "links.id" : {
                                                "$_internalExprEq" : "some-string"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                                "$expr" : {
                                        "$eq" : [
                                                "$links.id",
                                                {
                                                        "$const" : "some-string"
                                                }
                                        ]
                                }
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "links.id" : 1
                                },
                                "indexName" : "links.id_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "links.id" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "links.id" : [
                                                "[\"some-string\", \"some-string\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "caa1852655c1",
                "port" : 27017,
                "version" : "3.6.3",
                "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
        },
        "ok" : 1
}

But when an element is inserted, it "discovers" that its a multi-key index

> db.comment.insert({ links: [{ id: 'some-string', type: 'test' }] })
WriteResult({ "nInserted" : 1 })

and the query no longer uses the correct index

> db.comment.find({$expr:{$eq:['$links.id', 'some-string']}}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.comment",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "$expr" : {
                                                "$eq" : [
                                                        "$links.id",
                                                        {
                                                                "$const" : "some-string"
                                                        }
                                                ]
                                        }
                                },
                                {
                                        "links.id" : {
                                                "$_internalExprEq" : "some-string"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "$expr" : {
                                                        "$eq" : [
                                                                "$links.id",
                                                                {
                                                                        "$const" : "some-string"
                                                                }
                                                        ]
                                                }
                                        },
                                        {
                                                "links.id" : {
                                                        "$_internalExprEq" : "some-string"
                                                }
                                        }
                                ]
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "caa1852655c1",
                "port" : 27017,
                "version" : "3.6.3",
                "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5"
        },
        "ok" : 1
}

Show

Start with empty collection and the index: > db.comment.drop() true > db.comment.createIndex({ 'links.id' : 1 }) { "createdCollectionAutomatically" : true , "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } Initially, the query strategy indicates it will use the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$ const " : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "FETCH" , "filter" : { "$expr" : { "$eq" : [ "$links.id" , { "$ const " : "some-string" } ] } }, "inputStage" : { "stage" : "IXSCAN" , "keyPattern" : { "links.id" : 1 }, "indexName" : "links.id_1" , "isMultiKey" : false , "multiKeyPaths" : { "links.id" : [ ] }, "isUnique" : false , "isSparse" : false , "isPartial" : false , "indexVersion" : 2, "direction" : "forward" , "indexBounds" : { "links.id" : [ "[\" some-string\ ", \" some-string\ "]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017, "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 } But when an element is inserted, it "discovers" that its a multi-key index > db.comment.insert({ links: [{ id: 'some-string' , type: 'test' }] }) WriteResult({ "nInserted" : 1 }) and the query no longer uses the correct index > db.comment.find({$expr:{$eq:[ '$links.id' , 'some-string' ]}}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.comment" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$ const " : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "winningPlan" : { "stage" : "COLLSCAN" , "filter" : { "$and" : [ { "$expr" : { "$eq" : [ "$links.id" , { "$ const " : "some-string" } ] } }, { "links.id" : { "$_internalExprEq" : "some-string" } } ] }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "caa1852655c1" , "port" : 27017, "version" : "3.6.3" , "gitVersion" : "9586e557d54ef70f9ca4b43c26892cd55257e1a5" }, "ok" : 1 }

An $expr expression in a query will not use a multi-key index. Ticket ~~SERVER-31760~~ indicates this was fixed in 3.6.3 but still fails to work if the index is a multi-key index. This manifests only after an element is inserted that makes the index a multi-key index.

A normal query will use the correct index, but, as with the motivation for ~~SERVER-31760~~, it is desired to work within a $lookup pipeline where $expr would be required.

The problem exists in 3.6.3 as well as 4.0.1

is related to

SERVER-96826 $expr with find() does not use an index

Closed

Details

Description

Attachments

Issue Links

Activity

People

Dates