Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-10749

Query results differ depending on the utilized index

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical - P2
    • Resolution: Duplicate
    • Affects Version/s: 2.4.3
    • Fix Version/s: None
    • Component/s: Indexing
    • Labels:
      None
    • Operating System:
      ALL

      Description

      I haven't spotted any indexing errors, but today I noticed a very odd behavior:

      > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).count()
      2
      > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).count()
      2
       
       
      > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).toArray().length
      2
      > db.saved_search.find({'organization': DBRef('organization', 'orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8'), 'user': DBRef('user', 'user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD')}).hint({ _id: 1 }).toArray().length
      14

      There are two issues I see here:
      1. (minor) count doesn't respect the hint (had to resort to toArray().length)
      2. (major) the default index utilized for this query is broken (doesn't return all the docs that match the query).

      A bit more insight into the data:

      Indexes on the collection:

      > db.saved_search.getIndexes()
      [
      	{
      		"v" : 1,
      		"key" : {
      			"_id" : 1
      		},
      		"ns" : "closeio.saved_search",
      		"name" : "_id_"
      	},
      	{
      		"v" : 1,
      		"key" : {
      			"organization" : 1,
      			"user" : 1,
      			"query" : 1,
      			"is_shared" : 1,
      			"date_created" : -1
      		},
      		"ns" : "closeio.saved_search",
      		"name" : "organization_1_user_1_query_1_is_shared_1_date_created_-1",
      		"background" : false,
      		"dropDups" : false
      	}
      ]

      One of the returned docs:

      {
      		"_id" : "save_m0n9GBfilJ3rovhDZVET9bPyM1nMu0a31wtWaofJtDy",
      		"date_created" : ISODate("2013-08-22T07:18:11.583Z"),
      		"date_updated" : ISODate("2013-09-03T14:25:18.482Z"),
      		"is_shared" : false,
      		"name" : "Synchs streak",
      		"organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"),
      		"query" : "custom.Stage:Sync/Implemented not opportunity_status:\"Synch/ Implemented\"",
      		"user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD")
      	}

      One of the docs that weren't returned (but should've been):

      {
      		"_id" : "save_OgHsF7KrU8kD5J1SoTU7jLtutAZQNNIZJ7NmDOmW9NK",
      		"is_shared" : false,
      		"date_updated" : ISODate("2013-09-11T14:20:09.108Z"),
      		"query" : "country:uk and description:ebay not ( \"custom.Lead Owner\":\"Bagdat Baimagambetov\" \"custom.Lead Owner\":\"Brian OLeary\" \"custom.Lead Owner\":\"Chris Marley\" \"custom.Lead Owner\":\"Duncan Anderson\" \"custom.Lead Owner\":\"Gina Karlikoff\" \"custom.Lead Owner\":\"Jim Gregory\" \"custom.Lead Owner\":\"John Nuclear\" \"custom.Lead Owner\":\"Liam Delahunty\" \"custom.Lead Owner\":\"Louis McCarthy\" \"custom.Lead Owner\":\"Matteo Pedrioli\" \"custom.Lead Owner\":\"Patrice Meiner\" \"custom.Lead Owner\":\"Rory Kierans\" \"custom.Lead Owner\":\"Rory Reilly\" \"custom.Lead Owner\":\"Sujeet Gorahava\" \"custom.Lead Owner\":\"trial - jim\" \"custom.Lead Owner\":Trial-Dave \"custom.Lead Owner\":trial-joana \"custom.Lead Owner\":trial-matthew \"custom.Lead Owner\":triál-dave ) not lead_status:\"No Contact Details\" not lead_status:\"Won and then Lost\" not opportunity_status:\"eBay Won\" not opportunity_status:\"Won Web Shop\"",
      		"user" : DBRef("user", "user_UlODAoCGCqnOjWCv0dXIPTnz39GQWOT4DD3rjRLcECD"),
      		"date_created" : ISODate("2013-09-11T14:20:09.108Z"),
      		"organization" : DBRef("organization", "orga_qr7OtHV26J9tr3TJ3aEhDHLccsulXbD9sJI4wnkhKw8"),
      		"name" : "UK leads"
      	}

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                2 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: