[SERVER-42117] Bad hint (json order) Created: 09/Jul/19  Updated: 06/Dec/22  Resolved: 29/Jul/19

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

Type: Bug Priority: Major - P3
Reporter: Grégory NEUT Assignee: Backlog - Triage Team
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File ids.png    
Assigned Teams:
Server Triage
Operating System: ALL
Participants:

 Description   

I have created a collection having a compound index, as :

db.ConnectedobjectValue.getIndexes()[{ "v" : 2, "key" : { "entryDate" : -1, "idParameter" : -1, "idConnectedobject" : -1 }, "name" : "entryDate_-1_idParameter_-1_idConnectedobject_-1", "ns" : "DB.ConnectedobjectValue", "background" : true }]

Then I tried to perform a simple find using this compound index :

db.ConnectedobjectValue.find({}).hint({ entryDate: -1, idConnectedobject: -1, idParameter: -1 })

And I got the following error :

Error: error: {Error: error: { "ok" : 0, "errmsg" : "error processing query: ns=ELIOT.ConnectedobjectValueTree: $and\nSort: {}\nProj: {}\n planner returned error: bad hint", "code" : 2, "codeName" : "BadValue"}

I have found that changing the order of the names in my find request make it to work :

db.ConnectedobjectValue.find({}).hint({ entryDate: -1, idParameter: -1, idConnectedobject: -1 })

Which is non sense considering that json do not guarantee keys order.



 Comments   
Comment by Danny Hatcher (Inactive) [ 29/Jul/19 ]

To follow up orelsanpls, you could have also resolved this issue by using a Map rather than an Object. As you mentioned, JSON does not preserve order. NODE-578 provided the option to use a Map instead so that the ordering is consistent every time. As this is a known fix for the issue, I'm going to close this ticket.

Comment by Eric Sedor [ 10/Jul/19 ]

Thanks orelsanpls, we are going to attempt a reproduction of this and will update this ticket if we have additional questions.

Comment by Grégory NEUT [ 10/Jul/19 ]

I'm using :

mongodb v4.0.3

node.js v8.9.4

mongoose@5.6.3 which use the npm packages :

     - "mongodb": "3.2.7"
     - "mongodb-core": "3.2.7"

Comment by Grégory NEUT [ 10/Jul/19 ]

Hello, thank you for replying to this ticket.

Yes 

{ idConnectedobject: -1, idParameter: -1, entryDate: -1 }

  resulted in bad hint. Now that I've restarted my program, I do not reproduce, so I strongly guess that the keys that was given for the hint got transmitted to mongodb in the wrong order.

Mongoose node.js package is relying on mongodb-native package which just pass the hint option to mongodb (here is what I've found on theirs githubs) :

https://github.com/mongodb/node-mongodb-native/blob/master/lib/utils.js

Line 165

/** * @ignore */
var normalizeHintField = function normalizeHintField(hint) {
  var finalHint = null;
 
  if (typeof hint === 'string') {    
     finalHint = hint; 
  } else if (Array.isArray(hint)) {
    finalHint = {};
 
    hint.forEach(function(param) {  
        finalHint[param] = 1;
    });
  } else if (hint != null && typeof hint === 'object') { 
   finalHint = {};
 
   for (var name in hint) {
      finalHint[name] = hint[name];
   }
  }
  
  return finalHint;
};

 
About the `for ... in` implementation, there are this stack overflow answer https://stackoverflow.com/questions/280713/elements-order-in-a-for-in-loop 

The behavior of `for ... in` could be specific to the node.js version

PS: 

I didn't knew I could pass the name of the index, thank you for the input, I do use this method now and it works well!

Comment by Eric Sedor [ 09/Jul/19 ]

Hi orelsanpls,

To hint to the index { "entryDate" : -1, "idParameter" : -1, "idConnectedobject" : -1 } you should be able to pass { "entryDate" : -1, "idParameter" : -1, "idConnectedobject" : -1 } (the key specification) or "entryDate_-1_idParameter_-1_idConnectedobject_-1", the index name.

So your original description sounds like what should be expected. For your follow-up comment can you please clarify:

Are you saying that { idConnectedobject: -1, idParameter: -1, entryDate: -1 } results in a bad hint even though an index exists on { idConnectedobject: -1, idParameter: -1, entryDate: -1 }?

Comment by Grégory NEUT [ 09/Jul/19 ]

Actually I saw that the compound index key order is important as specified in the documentation https://docs.mongodb.com/manual/core/index-compound/

 I think it's more of a node.js issue. Because in node.js the order of the keys are not guaranteed which leads to possible issues.

My program is in `node.js` using `npm` package `mongoose`, even providing the indexes in the correct order, I get a bad hint :

stringError: 'MongoError: error processing query: ns=DB.ConnectedobjectValue limit=80Tree: $and\n idParameter $eq 
ObjectId(\'b00060300000000000000046\')\n entryDate $lte new Date(1562663058593)\nSort: { entryDate: -1 }\nProj: { _id: 1, entryDate: 1, idConnectedobject: 1, idParameter: 1, value: 1 }\n planner returned error: bad hint\n at Connection.<anonymous> (/x/node_modules/mongodb-core/lib/connection/pool.js:443:61)\n at emitTwo (events.js:126:13)\n at Connection.emit (events.js:214:7)\n at processMessage (/x/node_modules/mongodb-core/lib/connection/connection.js:364:10)\n at Socket.<anonymous> (/x/node_modules/mongodb-core/lib/connection/connection.js:533:15)\n at emitOne (events.js:116:13)\n at Socket.emit (events.js:211:7)\n at addChunk (_stream_readable.js:263:12)\n at readableAddChunk (_stream_readable.js:250:11)\n at Socket.Readable.push 
(_stream_readable.js:208:10)\n at TCP.onread (net.js:594:20)'

 
Order I set in my code :

{  idConnectedobject: -1,
   idParameter: -1,
   entryDate: -1,   }

The indexes list :

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