Range query on BigDecimal in Hibernate Search

Question

I am trying to do a range query on an entity field of type BigDecimal but couldn't make it work. This is what I've done so far.

This is the entity class.

public class Deal {
    @Field(store = Store.YES)
    @Field(name = "budget_Sort", store = Store.YES, normalizer= @Normalizer(definition = SearchConstants.LOWER_CASE_NORMALIZER))
    @FieldBridge(impl = BigDecimalNumericFieldBridge.class)
    @SortableField(forField = "budget_Sort")
    @Column(name = "BUDGET", precision = 10, scale = 2)
    private BigDecimal budget = new BigDecimal(0);

    //other fields and methods omitted for brevity
}

The custom FieldBridge for BigDecimal is as follows. I chose type DOUBLE as the converted type.

public class BigDecimalNumericFieldBridge implements MetadataProvidingFieldBridge, TwoWayFieldBridge {

    private static final BigDecimal storeFactor = BigDecimal.valueOf( 100 );

    @Override
    public void set(String name, Object value, Document document, LuceneOptions luceneOptions) {
        if ( value != null ) {
            BigDecimal decimalValue = (BigDecimal) value;
            Double indexedValue = decimalValue.multiply( storeFactor ).doubleValue();
            luceneOptions.addNumericFieldToDocument( name, indexedValue, document );
            luceneOptions.addNumericDocValuesFieldToDocument(name, indexedValue, document);
        }
    }

    @Override
    public Object get(String name, Document document) {
        String fromLucene = document.get( name );
        if (Objects.nonNull(fromLucene)) {
            BigDecimal storedBigDecimal = new BigDecimal(fromLucene);
            return storedBigDecimal.divide(storeFactor);
        } else {
            return null;
        }
    }

    @Override
    public String objectToString(Object object) {
        return object.toString();
    }

    @Override
    public void configureFieldMetadata(String name, FieldMetadataBuilder builder) {
        builder.field( name, FieldType.DOUBLE );
    }
}

I then defined a class Range to pass lower bound, upper bound and field data type.

public class Range {
    private String lowerBound;
    private String upperBound;
    private String dataType;
}

And created the following object to test this range query.

Range budgetRange = new Range("9500", "10500",SearchConstants.BIGDECIMAL);

The actual query building logic is as follows.

    protected Query rangeFilterBuilder(String key, String field) {
        Query rangeQuery = null;
        String lowerBound = searchRequest.getRangeFilters().get(key).getLowerBound();
        String upperBound = searchRequest.getRangeFilters().get(key).getUpperBound();
        String dataType = searchRequest.getRangeFilters().get(key).getDataType();
        switch (dataType) {
            case SearchConstants.INTEGER:
                rangeQuery = queryBuilder.range().onField(field).from(Integer.valueOf(lowerBound)).to(Integer.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.LONG:
                rangeQuery = queryBuilder.range().onField(field).from(Long.valueOf(lowerBound)).to(Long.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.DOUBLE:
                rangeQuery = queryBuilder.range().onField(field).from(Double.valueOf(lowerBound)).to(Double.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.STRING:
                rangeQuery = queryBuilder.range().onField(field).from((lowerBound)).to(upperBound).createQuery();
                break;
            case SearchConstants.DATE:
                rangeQuery = queryBuilder.range().onField(field).from(LocalDateTime.parse(lowerBound)).to(LocalDateTime.parse(upperBound)).createQuery();
                break;
            case SearchConstants.BIGDECIMAL:
                rangeQuery = queryBuilder.range().onField(field).from(Double.valueOf(lowerBound)).to(Double.valueOf(upperBound)).createQuery();
        }
        return rangeQuery;
    }```

This always return 0 matching result regardless of the range. I've tried other fields of type int or long and they work as expected. Is there something that I missed for BigDecimal range query?

Answer 1

The problem is that you're multiplying the value by 100 when indexing, but not when querying. So when you search for value 42, the range [40, 45] won't match, but the range [4000, 4500] will.

The solution would be to multiply the bounds by 100 in your function, for the "BigDecimal" case:

        switch (dataType) {
            case SearchConstants.INTEGER:
                rangeQuery = queryBuilder.range().onField(field).from(Integer.valueOf(lowerBound)).to(Integer.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.LONG:
                rangeQuery = queryBuilder.range().onField(field).from(Long.valueOf(lowerBound)).to(Long.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.DOUBLE:
                rangeQuery = queryBuilder.range().onField(field).from(Double.valueOf(lowerBound)).to(Double.valueOf(upperBound)).createQuery();
                break;
            case SearchConstants.STRING:
                rangeQuery = queryBuilder.range().onField(field).from((lowerBound)).to(upperBound).createQuery();
                break;
            case SearchConstants.DATE:
                rangeQuery = queryBuilder.range().onField(field).from(LocalDateTime.parse(lowerBound)).to(LocalDateTime.parse(upperBound)).createQuery();
                break;
            case SearchConstants.BIGDECIMAL:
                rangeQuery = queryBuilder.range().onField(field).from(Double.valueOf(lowerBound) * 100.0).to(Double.valueOf(upperBound) * 100.0).createQuery();
        }

By the way, I really think your function should parse the number as a BigDecimal, not a Double, in the BigDecimal case. Then you can multiply it, and then convert it to Double, similarly to what you did in your function.

Also, you'll probably want to use Long instead of Double when indexing (and querying) a scaled BigDecimal: you'll get more performance and will be sure that the indexed number is exactly the one you want (no rounding). You don't need the range of a double for monetary amounts anyway, unless you're dealing with amounts higher than the total value of all assets on earth :)