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

Geo predicate is unable to use compound 2dsphere index if it is root of $or clause

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.8, 3.0.0-rc6
    • Affects Version/s: 2.6.6
    • Component/s: Querying
    • None
    • Fully Compatible
    • ALL
    • Hide

      Query:

      {
      	"a" : {
      		"$gt" : 5
      	},
      	"b" : {
      		"$lt" : 500
      	},
      	"$or" : [
      		{
      			"startLoc" : {
      				"$geoWithin" : {
      					"$geometry" : {
      						"type" : "MultiPolygon",
      						"coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]
      					}
      				}
      			}
      		},
      		{
      			"endLoc" : {
      				"$geoWithin" : {
      					"$geometry" : {
      						"type" : "MultiPolygon",
      						"coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]
      					}
      				}
      			}
      		}
      	]
      }
      
      1. Insert a document
        db.docs.insert({a: 100, b: 100, startLoc: {type: "Point", coordinates: [0,0]}, endLoc: {type: "Point", coordinates: [1,1]}})
        
      2. When the indexes are not compound indexes, the query can use the indexes:
        db.docs.ensureIndex({startLoc: "2dsphere"}) 
        db.docs.ensureIndex({endLoc: "2dsphere"})
        db.docs.getIndexKeys()
        db.docs.find({a: {$gt: 5},b: {$lt: 500}, startLoc:{"$geoWithin" : { "$geometry" : { "type" : "MultiPolygon", "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]}}} }).explain(true)
        
      3. When the indexes are compound 2dsphere indexes, the query cannot use the indexes:
        db.docs.dropIndexes()
        db.docs.ensureIndex({startLoc: "2dsphere", a: 1, b: 1})
        db.docs.ensureIndex({endLoc: "2dsphere", a: 1, b: 1})
        db.docs.getIndexKeys()
        db.docs.find({a: {$gt: 5},b: {$lt: 500}, startLoc:{"$geoWithin" : { "$geometry" : { "type" : "MultiPolygon", "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]}}} }).explain(true)
        
      Show
      Query: { "a" : { "$gt" : 5 }, "b" : { "$lt" : 500 }, "$or" : [ { "startLoc" : { "$geoWithin" : { "$geometry" : { "type" : "MultiPolygon" , "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]] } } } }, { "endLoc" : { "$geoWithin" : { "$geometry" : { "type" : "MultiPolygon" , "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]] } } } } ] } Insert a document db.docs.insert({a: 100, b: 100, startLoc: {type: "Point" , coordinates: [0,0]}, endLoc: {type: "Point" , coordinates: [1,1]}}) When the indexes are not compound indexes, the query can use the indexes: db.docs.ensureIndex({startLoc: "2dsphere" }) db.docs.ensureIndex({endLoc: "2dsphere" }) db.docs.getIndexKeys() db.docs.find({a: {$gt: 5},b: {$lt: 500}, startLoc:{ "$geoWithin" : { "$geometry" : { "type" : "MultiPolygon" , "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]}}} }).explain( true ) When the indexes are compound 2dsphere indexes, the query cannot use the indexes: db.docs.dropIndexes() db.docs.ensureIndex({startLoc: "2dsphere" , a: 1, b: 1}) db.docs.ensureIndex({endLoc: "2dsphere" , a: 1, b: 1}) db.docs.getIndexKeys() db.docs.find({a: {$gt: 5},b: {$lt: 500}, startLoc:{ "$geoWithin" : { "$geometry" : { "type" : "MultiPolygon" , "coordinates" : [[[[-1,-1],[2,-2],[2,2],[-2,2],[-1,-1]]]]}}} }).explain( true )

      Please refer to the steps to reproduce the issue. This issue doesn't affect compound 2dsphere index that is {2dsphereIndexVersion: 1}

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            linda.qin@mongodb.com Linda Qin
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: