Executing a SQL query with a function on an indexed field returned incorrect results

Description

When using sql functions on fields that have an index the result is wrong.

I have a unit test where these queries return 0...
gigaSpace.count(new SQLQuery<>(FooWithIndex.class, "LOWER(field) = 'foo3'"))
gigaSpace.count(new SQLQuery<>(FooWithIndex.class, "UPPER(field) = 'FOO3'"))
gigaSpace.count(new SQLQuery<>(FooWithIndex.class, "CHAR_LENGTH(field) = 4"))

.. while thees queries return non zero...
gigaSpace.count(new SQLQuery<>(FooWithoutIndex.class, "LOWER(field) = 'foo3'"))
gigaSpace.count(new SQLQuery<>(FooWithoutIndex.class, "UPPER(field) = 'FOO3'"))
gigaSpace.count(new SQLQuery<>(FooWithoutIndex.class, "CHAR_LENGTH(field) = 4))

The only difference between FooWithIndex and FooWithoutIndex is that the former has @SpaceIndex on getField(),
Please see the supplied code for details.

I have reproduced the issue with:
@SpaceIndex
@SpaceIndex(type=SpaceIndexType.EQUAL)
@SpaceIndex(type=SpaceIndexType.ORDERED)

Reproduction project is attached.

Workaround

None

Acceptance Test

added following tests:
com.gigaspaces.test.support.SO4IT.case12185.SqlFunctionsOnIndexFieldTest
com.gigaspaces.test.support.SO4IT.case12185.SqlFunctionsOnCompoundIndexFieldTest
com.gigaspaces.test.support.SO4IT.case12185.SqlFunctionsOnExtendedIndexFieldTest
com.gigaspaces.test.support.SO4IT.case12185.SqlFunctionsOnNestedFieldTest

Status

Assignee

Evgeny Fisher

Reporter

Yuval Dori

Labels

Priority

Medium

SalesForce Case ID

12185

Fix versions

Commitment Version/s

None

Due date

None

Product

XAP

Edition

Premium

Platform

All