[SERVER-61025] numericOrdering produce incorrect query results Created: 27/Oct/21  Updated: 27/Oct/23  Resolved: 04/Nov/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.4.0, 5.0.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Germán Pedrosa Assignee: Edwin Zhou
Resolution: Works as Designed Votes: 1
Labels: collation, numericOrdering
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

db.createCollection('numericOrderingError', { collation: {locale: 'en_US', numericOrdering: true} });
db.getCollection('numericOrderingError').insertMany([
  {"clientCode" : "A0001"},
  {"clientCode" : "A1"}
])
db.getCollection('numericOrderingError').find({"clientCode": "A0001"})

 

Output: A0001 and A1

Expected: A0001

Participants:

 Description   

In collections with collation and "numericOrdering = true" the query results are incorrect when the search string includes zeros, leading zeros are ignored.
According to the documentation, the numericOrdering option should only affect the comparison of numeric text strings (in the example "clientCode" includes letters)



 Comments   
Comment by Edwin Zhou [ 04/Nov/21 ]

Hi german.pedrosa@ekon.es,

Thank you for your report. Enabling the numericOrdering option when specifying a collation for a collection will compare numeric strings as numbers. We should expect a number with leading zeros equal a number without leading zeros, and applies when querying a string that contains both numbers and letters. For example, with numericOrdering enabled, since "01" == "1", querying on "01" will include all forms of "1" including "01", "001", etc. This also means that "A1" == "A01" == "A001".

This behavior also matches this demo tool: https://icu4c-demos.unicode.org/icu-bin/collation.html

Since this is expected behavior, I will close this as works as designed.

Best,
Edwin

Generated at Thu Feb 08 05:51:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.