[SERVER-13705] $lte and $gt Query Operators not working properly for String Comparisions Created: 24/Apr/14  Updated: 10/Dec/14  Resolved: 24/Apr/14

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

Type: Bug Priority: Critical - P2
Reporter: Prajjwal Assignee: Unassigned
Resolution: Done Votes: 0
Labels: query
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

1. Insert Some Names :
> db.people.insert(

{"name":"Adam"}

)
> db.people.insert(

{"name":"Bob"}

)
> db.people.insert(

{"name":"Charlie"}

)
> db.people.insert(

{"name":"Dwayne"}

)
> db.people.insert(

{"name":"Elika"}

)

2. Query for documents using $lt Query Operator to get all the documents having "name" starting with A,B and C.
> db.people.find({"name":{"$lte":"C"}})
Actual Output:

{ "_id" : ObjectId("5358dfdf8211b04a14804731"), "name" : "Adam" } { "_id" : ObjectId("5358dfe38211b04a14804732"), "name" : "Bob" }

Expected Output:

{ "_id" : ObjectId("5358dfdf8211b04a14804731"), "name" : "Adam" } { "_id" : ObjectId("5358dfe38211b04a14804732"), "name" : "Bob" } { "_id" : ObjectId("5358dfe78211b04a14804733"), "name" : "Charlie" }

It did not return the document where name is "Charlie".

Similarly: $gt operator to get all the documents where name starts with Character greater than C, the following query behaves improperly and returns Charlie too. which was not expected.

  1. Should not return Charlie.
    > db.people.find({"name":{"$gt":"C"}})
    Actual Output: { "_id" : ObjectId("5358dfe78211b04a14804733"), "name" : "Charlie" } { "_id" : ObjectId("5358dfef8211b04a14804734"), "name" : "Dwayne" } { "_id" : ObjectId("5358dff78211b04a14804735"), "name" : "Elika" }

    Expected Output:

    { "_id" : ObjectId("5358dfef8211b04a14804734"), "name" : "Dwayne" } { "_id" : ObjectId("5358dff78211b04a14804735"), "name" : "Elika" }
Participants:

 Description   

db version v2.4.6
git version: b9925db5eac369d77a3a5f5d98a145eaaacd9673



 Comments   
Comment by Matt Kangas [ 24/Apr/14 ]

prajjwal1988, this is working as designed.

  1. db.people.find({"name":{"$lte":"C" }}) - query for "name" less than the single character "C".
    • "Charlie" <= "C" ? no
  2. db.people.find({"name":{"$gt":"C"}}) - query for "name" greater than the single character "C"
    • "Charlie" > "C" ? yes

Perhaps you want to use a $regex?

The BSON spec does not define sort order for types, but our implementation for String comparison is:

  • do memcmp() on UTF8 bytes of the two values
  • when one String is a strict prefix of the other, the longer String is greater

Here's the relevant comparison code in v2.6:
https://github.com/mongodb/mongo/blame/v2.6/src/mongo/bson/bson-inl.h#L98-L110

Also relevant:
http://docs.mongodb.org/manual/reference/bson-types/#string

Generated at Thu Feb 08 03:32:36 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.