Similar Queries producing different result, help wanted

On the basis Lecture: Query Operators - Logic
I tried to replicate similar behavior however I am getting a different result.

1. sample_training.zips.find({ "$and": [ {"pop":{"$gte":5000}} , {"pop":{"$lte":1000000}} ] }).count()
2. sample_training.zips.find({"pop":{"$gte":5000,"$lte":1000000}})
3. sample_training.zips.find({{"pop":{"$gte":5000}},{ "pop": {"$lte":1000000}} })
4. sample_training.zips.find({"pop":{"$gte":5000}, "pop": {"$lte":1000000} })

1 , 2 & 3 are returning the same result however 4 is returning a different number. I do not know why it’s happening.
I will be really grateful if you can help me.

What timing! Last week I had the same question. I’ve been doing a lot of tests, and I think I can conclude this:

Tests (of the image):

db.grades.find( { “student_id”: {"$gt": 25 }}).count() // 99740
1º format> db.grades.find( { “student_id”: {"$lt": 100 }, “student_id”: {"$gt": 25 }}).count() // 99740
2º format> db.grades.find( { “student_id”: {"$gt": 25 }}, {“student_id”: {"$lt": 100 }}).count() // 99740

db.grades.find( { “student_id”: {"$lt": 100 }}).count() // 1000
1º format> db.grades.find( { “student_id”: {"$gt": 25 }, “student_id”: {"$lt": 100 }}).count() // 1000
2º format> db.grades.find( { “student_id”: {"$lt": 100 }}, { “student_id”: {"$gt": 25 }}).count() // 1000

As you can see, when we use that 1º format (without the implicit assumption of “$and”), the query uses only the LAST clause.
As you can see, when we use that 2º format (without the implicit assumption of “$and”), the query uses only the FIRST clause.
In fact, if we use pretty() instead of count(), with the 2º format, we have an error -----> “errmsg” : “Expression $lt takes exactly 2 arguments. 1 were passed in.”,
So with the supposed implicit “$and”, it doesn’t works either with this 1º format, as the video “Lecture: Query Operators - Logic” tell us, or with 2º format.

If we really want to do this, we have to use de operator “$and”, or this other correct format:

db.grades.find( { “$and”: [{ “student_id”: {"$gt": 25 }},{ “student_id”: {"$lt": 100 }}] }).count() // 740 OK
db.grades.find( { “student_id”: {"$gt": 25 , “$lt”: 100}}).count() // 740 OK

So, with the examples that you show:

db.zips.find({“pop”:{"$gte":5000}}).count() // 11193
db.zips.find({“pop”:{"$lte":1000000}}).count() // 29470

db.zips.find({ “$and”: [ {“pop”:{"$gte":5000}} , {“pop”:{"$lte":1000000}} ] }).count() // 11193 OK
db.zips.find({“pop”:{"$gte":5000,"$lte":1000000}}).count() // 11193 OK

db.zips.find({“pop”:{"$gte":5000}},{ “pop”: {"$lte":1000000}}).count() // 11193 ERROR, the query uses only the FIRST clause. It’s not Ok just because the result of db.zips.find({“pop”:{"$gte":5000}}).count() concurs.
db.zips.find({“pop”:{"$gte":5000}, “pop”: {"$lte":1000000} }).count() // 29470 ERROR, the query uses only the LAST clause.

P.D. Excuse my English.

L

It does only use {“pop”:{"$gte":5000}} because it is the only one as the first parameter of find(). The part { “pop”: {"$lte":1000000}} is the second parameter so it is not part of the query.

It does because it is the only one part of the query document. As any JSON document, you can only have one instance of a given field. The second pop value overwrite the first one. You may try

q = { a : 1 , a : 2 }

in the shell to convince you.

Yes, so, this is not correct.

1 Like

This is definitively not a valid query.

You can look at the output of explain() for the parsed query and you will see that only { "$lt" : 100 } is part of the query.

1 Like

Hi @Shivam_Soni, @Luis_Rapestre, and @steevej,

Thanks for highlighting this and recognizing that this fact is incorrect!!

I have forwarded the feedback to the concerned team and make sure we get the resolution as soon as possible.

Regards,
Kushagra

2 Likes