[SERVER-13785] Can't canonicalize query: BadValue bad sort specification Created: 29/Apr/14  Updated: 10/Dec/14  Resolved: 01/May/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Roman Tkachenko Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Major Change
Operating System: ALL
Steps To Reproduce:

Run in the Mongo shell:

db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { }, sort: [ [ "_lock", 1 ] ] })

Expected: Returned updated document.

Actual: Returned an error.

Participants:

 Description   

After upgrading from 2.4 to 2.6.0 our find_and_modify commands which use sorting started failing with the above error.

PyMongo generates the following command for find_and_modify with sorting:

{ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { $set: { _lock: 1 } }, sort: [ [ "_lock", 1 ] ] }

I tested it with 2.4 and it works fine, but on 2.6.0 it fails with the error (with the latest PyMongo 2.7):

> db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { }, sort: [ [ "_lock", 1 ] ] })
{
	"errmsg" : "exception: nextSafe(): { $err: \"Can't canonicalize query: BadValue bad sort specification\", code: 17287 }",
	"code" : 13106,
	"ok" : 0
}

Looks like MongoDB has stopped respecting "sort: [ [ "_lock", 1 ] ]" syntax.

Is this a bug or expected change? This effectively breaks PyMongo's find_and_modify with sort functionality.



 Comments   
Comment by J Rassi [ 01/May/14 ]

r0mant: it seems that you're confusing PyMongo syntax with the server's query syntax. The ability to use an array as a sort spec is a convenience offered by some user-facing methods in PyMongo, which translate it into query syntax before sending it over to the server. It has never been valid to send these directly to the server (in 2.4 and earlier the server ignores invalid sort specs, but in 2.6 the server returns an error: see the sort() Specification Values page).

To illustrate that the server ignores invalid sort specs, see the following shell session from 2.4.10. The document targeted by findAndModify is not the document with the lowest value of _lock.

> db.version()
2.4.10
> db.test.insert({_id: 10, _lock: 10})
WriteResult({ "nInserted" : 1 })
> db.test.insert({_id: 5, _lock: 5})
WriteResult({ "nInserted" : 1 })
> db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { $set: { _lock: 1 } }, sort: [ [ "_lock", 1 ] ] })
{
	"lastErrorObject" : {
		"updatedExisting" : true,
		"n" : 1,
		"connectionId" : 1,
		"err" : null,
		"ok" : 1
	},
	"value" : {
		"_id" : 10,
		"_lock" : 10
	},
	"ok" : 1
}

Your application should be using the PyMongo find_and_modify() method (which performs sort spec translation), not the low-level command() method (which doesn't know what a sort spec is).

Resolving as works-as-designed.

Comment by Bernie Hackett [ 30/Apr/14 ]

Sorry for the confusion. There was an assumption here that you were reporting a PyMongo bug, not a server bug. I apologize.

It seems you are correct. In MongoDB 2.4 your syntax is allowed:

MongoDB shell version: 2.4.10
connecting to: test 
> db.test.insert({"_lock": 1024})
> db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { }, sort: [ [ "_lock", 1 ] ] })
{
	"lastErrorObject" : {
		"updatedExisting" : true,
		"n" : 1,
		"connectionId" : 1,
		"err" : null,
		"ok" : 1
	},
	"value" : {
		"_id" : ObjectId("53603ffae4370b4bc24f8e6a"),
		"_lock" : 1024
	},
	"ok" : 1
}
> db.version()
2.4.10
 
Tue Apr 29 17:13:54.596 [conn1] command test.$cmd command: { findandmodify: "test", query: { _lock: { $lte: 9999999999999.0 } }, update: {}, sort: [ [ "_lock", 1.0 ] ] } ntoreturn:1 keyUpdates:0 locks(micros) w:455 reslen:163 0ms

In MongoDB 2.6 it is not:

MongoDB shell version: 2.6.0
connecting to: test
> db.version()
2.6.0
> db.test.insert({"_lock": 1024})
WriteResult({ "nInserted" : 1 })
> db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { }, sort: [ [ "_lock", 1 ] ] })
{
	"errmsg" : "exception: nextSafe(): { $err: \"Can't canonicalize query: BadValue bad sort specification\", code: 17287 }",
	"code" : 13106,
	"ok" : 0
}
 
 
2014-04-29T17:18:24.545-0700 [conn1] assertion 17287 Can't canonicalize query: BadValue bad sort specification ns:test.test query:{ query: { _lock: { $lte: 9999999999999.0 } }, orderby: { 0: [ "_lock", 1.0 ] } }

I have to let the kernel team investigate further. I'm not sure if this behavior change was intentional or not.

Comment by Roman Tkachenko [ 30/Apr/14 ]

Bernie, you're right - my point was that it used to work in 2.4.x.

Comment by Roman Tkachenko [ 30/Apr/14 ]

Bernie,

This is the code snippet to reproduce the issue. It does not use "find_and_modify" method directly:

import pymongo
from bson import SON
 
connection = pymongo.ReplicaSetConnection("<servers>", replicaSet="main")
 
params = SON(findandmodify="<coll>")
params["query"] = {"_lock": {"$lte": 9999999999}}
params["update"] = {"$set": {"test": 1}}
params["sort"] = [("_lock", pymongo.ASCENDING)]
 
print connection["<db>"].command(params)["value"]

It gives me this:

Traceback (most recent call last):
  File "mongo.py", line 13, in <module>
    print connection["mg_prod"].command(params)["value"]
  File "/home/mg/mgcore-py/lib/python2.7/site-packages/pymongo/database.py", line 435, in command
    uuid_subtype, compile_re, **kwargs)[0]
  File "/home/mg/mgcore-py/lib/python2.7/site-packages/pymongo/database.py", line 341, in _command
    msg, allowable_errors)
  File "/home/mg/mgcore-py/lib/python2.7/site-packages/pymongo/helpers.py", line 178, in _check_command_response
    raise OperationFailure(msg % errmsg, code, response)
pymongo.errors.OperationFailure: command SON([('findandmodify', 'resourcegroups'), ('query', {'_lock': {'$lte': 9999999999}}), ('update', {'$set': {'test': 1}}), ('sort', [('_lock', 1)])]) failed: exception: nextSafe(): { $err: "Can't canonicalize query: BadValue bad sort specification", code: 17287 }

So I guess this is not the issue with PyMongo per-se, but rather with Mongo core that does not accept sort specification like [("_lock", pymongo.ASCENDING)] anymore.

If this is the intended behavior, feel free to close the issue.

Comment by Bernie Hackett [ 29/Apr/14 ]

I just realized that the example you posted with the error is from the mongodb shell, not PyMongo:

> db.runCommand({ findandmodify: "test", query: { _lock: { $lte: 9999999999999 } }, update: { }, sort: [ [ "_lock", 1 ] ] })
{
    "errmsg" : "exception: nextSafe(): { $err: \"Can't canonicalize query: BadValue bad sort specification\", code: 17287 }",
    "code" : 13106,
    "ok" : 0
}

PyMongo takes a list of lists/tuples as it's sort argument because python dicts are not order preserving. It converts the list of lists/tuples to an ordered dict behind the scenes so the user doesn't have to deal with bson.son.SON or collections.OrderedDict.

The mongo shell, on the other hand, expects you to pass a document for the sort parameter.

Comment by Bernie Hackett [ 29/Apr/14 ]

I can't reproduce this issue, with or without an index on _lock:

>>> pymongo.version
'2.7'
>>> c.server_info()['version']
u'2.6.0'
>>> from pymongo import ASCENDING
>>> c.test.test.find_and_modify({"_lock": {"$lte": 9999999999999}}, {"$set": {"_lock": 30}}, sort=[("_lock", ASCENDING)])
>>> 
>>> c.test.test.insert({"_lock": 1024})
ObjectId('53603592fba5224d0dbf46cb')
>>> c.test.test.ensure_index("_lock")
u'_lock_1'
>>> c.test.test.find_and_modify({"_lock": {"$lte": 9999999999999}}, {"$set": {"_lock": 30}}, sort=[("_lock", ASCENDING)])
{u'_id': ObjectId('53603592fba5224d0dbf46cb'), u'_lock': 1024}

Note I'm not using datetime here since your example is a syntax error in python 2.x and 3.x:

>>> from datetime import datetime
>>> datetime.utcnow() + 30
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: unsupported operand type(s) for +: 'datetime.datetime' and 'int'

Can you post the full traceback you are getting from find_and_modify?

Comment by Roman Tkachenko [ 29/Apr/14 ]

Hi Bernie,

Originally I faced this behavior with PyMongo 2.5 but then (just today) installed the latest PyMongo 2.7 and got the same result.

Comment by Bernie Hackett [ 29/Apr/14 ]

r0mant, can you tell me what version of PyMongo you are using?

Comment by Roman Tkachenko [ 29/Apr/14 ]

Also, plain "find" handles that syntax just fine.

Actually, correction: here I meant that PyMongo's "find" method generates sort object as a dict {"_lock": 1} which is understood by MongoDB.

Comment by J Rassi [ 29/Apr/14 ]

jesse / behackett: could one of you triage?

Comment by Roman Tkachenko [ 29/Apr/14 ]

Hi Jason,

Appreciate you formatting my comment/description properly.

So here's the call:

find_and_modify({"_lock": {"$lte": datetime.utcnow()}}, {"$set": {"_lock": datetime.utcnow() + 30}}, sort=[("_lock", ASCENDING)])

If I specify sort like this

sort={"_lock": ASCENDING}

it works. However, I haven't found any mention of this syntax deprecation. I have read through "2.6 Compatibility" guide and the only breaking change mentioned there is that use of "true"/"false" is not permitted anymore.

Also, plain "find" handles that syntax just fine.

Thanks,
Roman

Comment by J Rassi [ 29/Apr/14 ]

Could you paste the full call you made to the pymongo find_and_modify() method to generate this?

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