Uploaded image for project: 'InsightEdge Platform'
  1. GS-13714

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Medium
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 14.2
    • Labels:
    • Platform:
      All
    • SalesForce Case ID:
      12185
    • Acceptance Test:
      Hide
      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
      Show
      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
    • Sprint:
    • Product:
      XAP
    • Edition:
      Premium

      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.

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: