[SERVER-57588] Inconsistent query results when an array position is indexed whose value is an array Created: 09/Jun/21  Updated: 29/Oct/23  Resolved: 22/Nov/21

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 5.2.0, 5.1.2, 5.0.6, 4.4.11, 4.2.19

Type: Bug Priority: Major - P3
Reporter: Alex Bevilacqua Assignee: Timour Katchaounov
Resolution: Fixed Votes: 0
Labels: query-director-triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v5.2, v5.1, v5.0, v4.4, v4.2, v4.0, v3.6
Sprint: Query Optimization 2021-06-28, QO 2021-09-20, QO 2021-10-04, QO 2021-10-18, QO 2021-11-01, QO 2021-11-15, QO 2021-11-29
Participants:

 Description   

When indexing an array element by position (ex: {"a.0"}) if the value at the indexed position is an array, the index won't be multikey and can effect the result of a query if the index is selected as the winning plan.

db.version() 
// => 4.4.6
db.foo.drop();
db.foo.insert(
{ a: 
    [ 
        [ "b" ],
        [ "c" ]        
    ]
});
 
print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } }));
// => 1
 
/*
"inputStage" : {
    "stage" : "COLLSCAN", 
    "filter" : {
        "a.0" : {
            "$elemMatch" : {
                "$eq" : "b"
            }
        }
    }, 
    "nReturned" : 1.0, 
    "executionTimeMillisEstimate" : 0.0, 
    "works" : 3.0, 
    "advanced" : 1.0, 
    "needTime" : 1.0, 
    "needYield" : 0.0, 
    "saveState" : 0.0, 
    "restoreState" : 0.0, 
    "isEOF" : 1.0, 
    "direction" : "forward", 
    "docsExamined" : 1.0
}
*/

Now if the following index is created and the operation run again the results are different:

db.foo.createIndex({ "a.0": 1 })
print(db.foo.count({ "a.0": { $elemMatch: { $in: ["b"] } } }));
// => 0
 
/*
"inputStage" : {
    "stage" : "FETCH", 
    "filter" : {
        "a.0" : {
            "$elemMatch" : {
                "$eq" : "b"
            }
        }
    }, 
    "inputStage" : {
        "stage" : "IXSCAN", 
        "keyPattern" : {
            "a.0" : 1.0
        }, 
        "indexName" : "a.0_1", 
        "isMultiKey" : false, 
        "multiKeyPaths" : {
            "a.0" : [
 
            ]
        }, 
        "isUnique" : false, 
        "isSparse" : false, 
        "isPartial" : false, 
        "indexVersion" : 2.0, 
        "direction" : "forward", 
        "indexBounds" : {
            "a.0" : [
                "[\"b\", \"b\"]"
            ]
        }
    }
}
*/



 Comments   
Comment by Timour Katchaounov [ 25/Feb/22 ]

The last commit actually reverts another commit for which I incorrectly entered this ticket.

Comment by Githook User [ 25/Feb/22 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: Revert "SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array"

This reverts commit ad12685ff33131f95053dc567639312999957475 due to incorrect git message.
Branch: v4.4
https://github.com/mongodb/mongo/commit/139a77bd5914a3d691ce43d06b94fc2f521ce4c4

Comment by Githook User [ 23/Feb/22 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array

(cherry picked from commit 90699509e15b33fda10832e79efcd158aee1f0eb)
Branch: v4.4
https://github.com/mongodb/mongo/commit/ad12685ff33131f95053dc567639312999957475

Comment by Githook User [ 12/Jan/22 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array

(cherry picked from commit 90699509e15b33fda10832e79efcd158aee1f0eb)
Branch: v4.2
https://github.com/mongodb/mongo/commit/4b8b91c2ff91f538eede6b6c875656d697a8a916

Comment by Githook User [ 21/Dec/21 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array

(cherry picked from commit 90699509e15b33fda10832e79efcd158aee1f0eb)
Branch: v4.4
https://github.com/mongodb/mongo/commit/481e16fd5be189f9d4cf2cff212a46a73f70da29

Comment by Githook User [ 20/Dec/21 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array

(cherry picked from commit 90699509e15b33fda10832e79efcd158aee1f0eb)
Branch: v5.0
https://github.com/mongodb/mongo/commit/d2a3a3038de810f7877e5a8c7edb2fd9e366eb1b

Comment by Githook User [ 20/Dec/21 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array

(cherry picked from commit 90699509e15b33fda10832e79efcd158aee1f0eb)
Branch: v5.1
https://github.com/mongodb/mongo/commit/cd6b5a4ac41af1472f8fdfa113cfa14701414fe9

Comment by Githook User [ 22/Nov/21 ]

Author:

{'name': 'Timour Katchaounov', 'email': 'timour.katchaounov@mongodb.com', 'username': 'timourk'}

Message: SERVER-57588 Inconsistent query results when an array position is indexed whose value is an array
Branch: master
https://github.com/mongodb/mongo/commit/90699509e15b33fda10832e79efcd158aee1f0eb

Comment by Timour Katchaounov [ 22/Nov/21 ]

This bug is present in all versions from 3.6 up. Let me know if it should be backported and how far back.

Generated at Thu Feb 08 05:42:15 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.