[SERVER-17004] shell should raise a useful error on mixed usage of $query document with .explain() Created: 22/Jan/15  Updated: 24/Jan/15  Resolved: 24/Jan/15

Status: Closed
Project: Core Server
Component/s: Shell
Affects Version/s: 2.4.12
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Musti Assignee: Matt Kangas
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-6767 Interpret $query as special so you ca... Closed
Operating System: ALL
Steps To Reproduce:

db.test.find():
{
    "_id" : ObjectId("54c158402f512862b58738c4"),
    "a" : 8,
    "b" : 3
}
 
/* 1 */
{
    "_id" : ObjectId("54c158402f512862b58738c5"),
    "a" : 8,
    "b" : 9
}
 
/* 2 */
{
    "_id" : ObjectId("54c158402f512862b58738c6"),
    "a" : 7,
    "b" : 4
}
...
 
db.test.getIndexes():
{
    "0" : {
        "v" : 1,
        "name" : "_id_",
        "key" : {
            "_id" : 1
        },
        "ns" : "test.test"
    },
    "1" : {
        "v" : 1,
        "name" : "b_1",
        "key" : {
            "b" : 1
        },
        "ns" : "test.test"
    },
    "2" : {
        "v" : 1,
        "name" : "a_1",
        "key" : {
            "a" : 1
        },
        "ns" : "test.test"
    }
}

Participants:

 Description   

Consider the following code:

t = db.t;
t.drop();
t.ensureIndex({a: 1});
t.ensureIndex({b: 1});
t.find({$query: {a: 1}, $orderby: {b: -1}}).explain()

This should error with a useful message. a 2.4 server does not raise an error, whereas a 2.6 server raises an unhelpful error message. Instead, the shell could raise a useful error before sending the query to the server.

Original Description

I don't know if this is a bug, but the following two queries result in completely different outputs. The first output shows a full table scan whereas the second one claims to make use of the index. Which one is correct?

query 1:

db.java.find({ "$query" : { "a" : 1} , "$orderby" : { "b" : -1}}).explain()
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 100,
    "nscanned" : 100,
    "nscannedObjectsAllPlans" : 100,
    "nscannedAllPlans" : 100,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {},
    "server" : "XXXX"
}

query 2:

db.java.find({ "$query" : { "a" : 1} , "$orderby" : { "b" : -1}, "$explain" : true})
/* 0 */
{
    "cursor" : "BtreeCursor a_1",
    "isMultiKey" : false,
    "n" : 6,
    "nscannedObjects" : 6,
    "nscanned" : 6,
    "nscannedObjectsAllPlans" : 19,
    "nscannedAllPlans" : 19,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "a" : [ 
            [ 
                1, 
                1
            ]
        ]
    },
    "allPlans" : [ 
        {
            "cursor" : "BtreeCursor b_1 reverse",
            "n" : 0,
            "nscannedObjects" : 7,
            "nscanned" : 7,
            "indexBounds" : {
                "b" : [ 
                    [ 
                        {
                            "$maxElement" : 1
                        }, 
                        {
                            "$minElement" : 1
                        }
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor a_1",
            "n" : 6,
            "nscannedObjects" : 6,
            "nscanned" : 6,
            "indexBounds" : {
                "a" : [ 
                    [ 
                        1, 
                        1
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BasicCursor",
            "n" : 0,
            "nscannedObjects" : 6,
            "nscanned" : 6,
            "indexBounds" : {}
        }
    ],
    "oldPlan" : {
        "cursor" : "BtreeCursor a_1",
        "indexBounds" : {
            "a" : [ 
                [ 
                    1, 
                    1
                ]
            ]
        }
    },
    "server" : "nbwinviemg:27017"
}



 Comments   
Comment by Matt Kangas [ 24/Jan/15 ]

Duplicate of SERVER-6767

Comment by David Storch [ 22/Jan/15 ]

mustiguel, you're welcome. Let us know if you have any further questions.

Comment by Musti [ 22/Jan/15 ]

Hi David,

thank you very much for replying quickly and your explanation.
Actually, I saw the $query syntax after enabling debug options for the Java driver and after
trying out some explain methods I got confused.

Thx again!

Comment by David Storch [ 22/Jan/15 ]

Hi mustiguel,

Thanks for the bug report! The difference between $explain and .explain() is this case is due to unsupported query syntax. Queries must either be issued using the $query syntax in which operators are passed to .find() as $-prefixed fields, or must be issued using chaining syntax. For example, you can search for documents where a is 3, hint on a, and sort by b ascending using the $query syntax like so:

db.collection.find({$query: 3, $orderby: {b: 1}, $hint: {a: 1}});

Issuing the same query with chaining looks like this:

db.collection.find({a: 3}).sort({b: 1}).hint({a: 1});

Note that the chaining syntax is preferred and should be used whenever possible. If you are using a supported driver, it should have equivalent helpers for things like hint and sort so that you do not have to work with the raw $query document.

The query tagged as "query 1" above mixes $query with a chained .explain():

db.java.find({ "$query" : { "a" : 1} , "$orderby" : { "b" : -1}}).explain()

This mixed usage is invalid and should raise an error. Version 2.6.7 raises the following error:

> db.java.find({$query: 3, $orderby: {b: 1}}).explain()
Thu Jan 22 16:22:21.266 error: {
	"$err" : "Can't canonicalize query: BadValue unknown top level operator: $query",
	"code" : 17287
} at src/mongo/shell/query.js:128

Although this works as designed, I'm going to keep this open as a usability request to make the error message more specific.

Best,
Dave

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