Loading...

Type: Bug
Resolution: Won't Fix
Priority: Major - P3
Fix Version/s: None
Affects Version/s: 2.4.13
Component/s: Querying
Labels:
None

Operating System:
ALL
Steps To Reproduce:
Hide

db.andor.drop() db.andor.ensureIndex( { a: 1, b: 1, c: 1 } ) db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain() db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()

The query with the explict rooted $and will use a BasicCursor, whereas without it uses the index as expected.
Show
db.andor.drop() db.andor.ensureIndex( { a: 1, b: 1, c: 1 } ) db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain() db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain() The query with the explict rooted $and will use a BasicCursor, whereas without it uses the index as expected.
CAR Domain/s:
None

Aha! Reference:
None
Tracking Level:
None
Risk Status:
None
Exec Notes:
None
Goal Name(s):
None
Goal Link:
None

In 2.4, a query of the form { $and: [ { $or: [ ... ] } ] } does not choose an available suitable index:

> db.version()
2.4.13
> db.andor.drop()
false
> db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
Cannot use commands write mode, degrading to compatibility mode
{ "ok" : 1 }
> db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
        },
        "server" : "genique:11111"
}
> db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "nscannedObjectsAllPlans" : 0,
                        "nscannedAllPlans" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "nscannedObjectsAllPlans" : 0,
                        "nscannedAllPlans" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nYields" : 0,
                        "nChunkSkips" : 0,
                        "millis" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "millis" : 0,
        "server" : "genique:11111"
}

Whereas in 2.6, the right index is selected as expected:

> db.version()
2.6.10
> db.andor.drop()
true
> db.andor.ensureIndex( { a: 1, b: 1, c: 1 } )
{
        "createdCollectionAutomatically" : true,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.andor.find( { $and: [ { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "cursor" : "QueryOptimizerCursor",
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "server" : "genique:27017",
        "filterSet" : false
}
> db.andor.find( { $or: [ { a: 1, b: 1, c: 1 }, { a: 2, b: 2, c: 2 } ] } ).explain()
{
        "clauses" : [
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "b" : [
                                        [
                                                1,
                                                1
                                        ]
                                ],
                                "c" : [
                                        [
                                                1,
                                                1
                                        ]
                                ]
                        }
                },
                {
                        "cursor" : "BtreeCursor a_1_b_1_c_1",
                        "isMultiKey" : false,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                                "a" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "b" : [
                                        [
                                                2,
                                                2
                                        ]
                                ],
                                "c" : [
                                        [
                                                2,
                                                2
                                        ]
                                ]
                        }
                }
        ],
        "cursor" : "QueryOptimizerCursor",
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "server" : "genique:27017",
        "filterSet" : false
}

Details

Description

Attachments

Activity

People

Dates