[jira] [Updated] (GEODE-9632) Wrong output for the range query with wildcard character

["Mario Kevo (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/GEODE-9632?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mario Kevo updated GEODE-9632:
------------------------------
    Description: 
We are using a range index on an attribute that is defined as HashMap.

The problem is when we are using wildcard character(%), there is no results for the query despite of there are some entries that meet the condition we are checking.

There is an example:

 
{code:java}
gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE '342234525745'" 
Result      : true
Limit       : 100
Rows        : 1
Query Trace : Query Executed in 9.082156 ms; indexesUsed(1):index1(Results: 1)


gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE '34223452574%'" 
Result      : true
Limit       : 100
Rows        : 0
Query Trace : Query Executed in 4.677162 ms; indexesUsed(1):index1(Results: 100)

{code}
 As we are using indexes to have a better performance of executing a query it first need to check how many entries has that field which we are looking for. It stores it in index results and then check how many of them meet condition we defined in our query.

The problem is that there is parameter INDEX_THRESHOLD_SIZE which default value is 100. If there is a lot of entries in the region it will write just first 100 entries that is found.

This parameter can be changed while starting servers by adding *-Dgemfire.Query.INDEX_THRESHOLD_SIZE=<value>*, but if we set it to a higher value than the limit is, it will overwrite it. So there should be some changes to take this attribute into account.

 

 

  was:
We are using a range index on an attribute that is defined as HashMap.

The problem is when we are using wildcard characters the Results field in QueryTrace is 100, but should be 0 as there is no attribute with that value or something similar.

There is an example:

 
{code:java}
gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE '342234525745'"
Result      : true
Limit       : 100
Rows        : 1
Query Trace : Query Executed in 8.95536 ms; indexesUsed(1):index1(Results: 1)
gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE 'something%'"
Result      : true
Limit       : 100
Rows        : 0
Query Trace : Query Executed in 17.171972 ms; indexesUsed(1):index1(Results: 100)
{code}
 

{color:#1d1c1d}When we are using it without range index we got this:{color}
{code:java}
gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE 'something%'" 
Result : true
Limit : 100
Rows : 0
Query Trace : Query Executed in 14049.559 ms; indexesUsed(0){code}
 

 


> Wrong output for the range query with wildcard character
> --------------------------------------------------------
>
>                 Key: GEODE-9632
>                 URL: https://issues.apache.org/jira/browse/GEODE-9632
>             Project: Geode
>          Issue Type: Bug
>          Components: querying
>            Reporter: Mario Kevo
>            Assignee: Mario Kevo
>            Priority: Major
>
> We are using a range index on an attribute that is defined as HashMap.
> The problem is when we are using wildcard character(%), there is no results for the query despite of there are some entries that meet the condition we are checking.
> There is an example:
>  
> {code:java}
> gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE '342234525745'" 
> Result      : true
> Limit       : 100
> Rows        : 1
> Query Trace : Query Executed in 9.082156 ms; indexesUsed(1):index1(Results: 1)
> gfsh>query --query="<trace>SELECT e.key, e.value from /example-region.entrySet e where e.value.positions['SUN'] LIKE '34223452574%'" 
> Result      : true
> Limit       : 100
> Rows        : 0
> Query Trace : Query Executed in 4.677162 ms; indexesUsed(1):index1(Results: 100)
> {code}
>  As we are using indexes to have a better performance of executing a query it first need to check how many entries has that field which we are looking for. It stores it in index results and then check how many of them meet condition we defined in our query.
> The problem is that there is parameter INDEX_THRESHOLD_SIZE which default value is 100. If there is a lot of entries in the region it will write just first 100 entries that is found.
> This parameter can be changed while starting servers by adding *-Dgemfire.Query.INDEX_THRESHOLD_SIZE=<value>*, but if we set it to a higher value than the limit is, it will overwrite it. So there should be some changes to take this attribute into account.
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)