Issues while querying Number type metadata fileds through JSP content broker query API

Question

I have a legacy implementation where we have some components got number type metadata filed. Downstream applications uses JSP content broker query API and queries for components with number filters.

Here is the sample code in JSP

String values[] = null;
String ids = request.getParameter("ids");

values = ids.split(",");
Criteria customKeyCriteria = new CustomMetaKeyCriteria("id");
    ArrayList<CustomMetaValueCriteria> criteriaList = new ArrayList<CustomMetaValueCriteria>();

    for (String value : values) {       
            criteriaList.add(new CustomMetaValueCriteria(new Float(value)));        
    }

    Criteria customValueCriteria = new OrCriteria(criteriaList.toArray(new Criteria[values.length]));

    Query query = new Query(new AndCriteria(customKeyCriteria, customValueCriteria));
    String[] result = query.executeQuery();

So if the value in number type metadata field is less than or equal to 7 digits, i am seeing the unique result in query (which is what we need). But if it is more than seven digits, we are getting multiple results.

Example: if we query with metadata id=27496532 we are getting two components with metadata id 27496532 and 27496531.

CMS version: SDL Web 8.1

Content Broker Database version: SDL Tridion 2013 SP1

I have seen something similar question asked here long back, i would like to know present status on such number type metadata field problem?

I would also like to know "Custom number fields turned to float" section in Known Issues refers to same?

Finally i would like to know what are my options with out changing number field type to string?

Answer 1

In general, the datatype Float has limited precision; it has a huge range, but it is not possible to represent every integer value within that range.

So, in general, exact matches on float values are tricky: you may get both false positive and false negative matches.

Bottom line: if you want to do exact matches, you should not use datatype Float (nor Double, for that matter) and thus not use a Number field.