[SERVER-2013] Have $or queries with two $in queries to return unique results Created: 27/Oct/10  Updated: 07/Apr/23  Resolved: 27/Oct/10

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 1.5.3, 1.5.4, 1.5.5, 1.5.6, 1.5.7, 1.5.8, 1.6.0, 1.6.1, 1.6.2, 1.6.3, 1.6.4, 1.7.0, 1.7.1
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Chris Assignee: Aaron Staple
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

It seems $or queries should have the option to return unique results instead of (as far as I can tell) amalgamating the results of the individual $or statements.

For example if you have

1:

color: Color value is invalid

2:

color: Color value is invalid

$or: [ {color: {$in:['blue','red']} }, {shape: {$in:[circle,square]} } ]

Right now it seems to returns 4 results in the cursor. Both documents are returned twice.



 Comments   
Comment by Aaron Staple [ 27/Oct/10 ]

This is SERVER-1883 which has been fixed and backported.

Comment by Chris [ 27/Oct/10 ]

Hey sorry I didn't actually test out that example since I thought it was an unimplemented feature and it would exhibit same result with non-subobjects.

It turns out it may be a bug then since it only occurs on subobject query and a unique index

Take this modified example:

db.f.save( {_id:ObjectId("4cc7e1348b3106c682ef1bce"),color:'blue',shape:{a:'circle'}} );
db.f.save( {_id:ObjectId("4cc7dfc38b3106c682ef1bca"),color:'red',shape:'square'} );

db.f.ensureIndex(

{"shape.a":1}

);

db.f.find( { $or: [ {_id: {$in:[ObjectId("4cc7e1348b3106c682ef1bce")]} }, {"shape.a": {$in:['circle']} } ] } );
Normal: only 1 result

db.f.find( { $or: [ {"shape.a": {$in:['circle']} }, {_id:{$in:[ObjectId("4cc7e1348b3106c682ef1bce")]}} ] } );
Weird: same result comes up twice, only switched the order of the or queries.

It doesn't just happen when the second $or query is _id, it happens whenever the second one has a unique index on it.

So this does the same thing:

db.f.save( {color:'blue',shape:{a:'circle'}} );
db.f.save(

color: Color value is invalid

);

db.f.ensureIndex(

{"color":1}

,

{unique:1}

);
db.f.ensureIndex(

{"shape.a":1}

);

db.f.find( { $or: [ {"shape.a": {$in:['circle']} }, {color:{$in:['blue']}} ] } );

On the bright side it seems the fix is just to put the unique indexed query first in the $or array;

Comment by Aaron Staple [ 27/Oct/10 ]

I'm not seeing this behavior:

> db.f.drop()
true
> db.f.save(

color: Color value is invalid

);
> db.f.save(

color: Color value is invalid

);
> db.f.find( { $or: [ {color: {$in:['blue','red']} }, {shape: {$in:['circle','square']} } ] } );

{ "_id" : ObjectId("4cc7d3e218caa33b02e6bda5"), "color" : "blue", "shape" : "circle" } { "_id" : ObjectId("4cc7d3e918caa33b02e6bda6"), "color" : "red", "shape" : "square" }

>

I tried a few index combinations as well and saw the same result.

Do you have a test script which generates this issue?

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