[GEODE-2002] The index is not used on a query with a where clause containing a parameterized key on a map

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Fix Version/s: 1.1.0
• Component/s: querying
• Assignee: Unassigned
• Reporter: Barrett Oglesby
• Created: 14/Oct/16 22:22
• Updated: 10/Feb/17 00:27
• Resolved: 07/Dec/16 22:22

Description

This is the same as GEM-1026.

If an index is defined like:

<index name="instrumentTLAltIdIndex" from-clause="/instrument i, i.tradingLines tl" expression="tl.alternateReferences['SEDOL', 'INHOUSE_SEDOL']"/> 

And a query is defined like:

<trace> select i from from /instrument i, i.tradingLines t where t.alternateReferences[$1]='BFNY8K9' with bind parameter 'SEDOL'.

Then, the instrumentTLAltIdIndex index won't be used when the query is executed.

The message logged in the server log looks like (with indexesUsed(0) instead of indexesUsed(1)):

[info 2016/10/14 14:51:03.069 PDT  <ServerConnection on port 57074 Thread 2> tid=0x3e] Query Executed in 3.645424 ms; rowCount = 14; indexesUsed(0) "<trace> select i from /instrument i, i.tradingLines t where t.alternateReferences[$1]='BFNY8K9'"

The CompiledIndexOperation generateCanonicalizedExpression method returns index_iter2.alternateReferences[$1] instead of index_iter2.alternateReferences['INHOUSE_SEDOL'], and the AbstractMapIndex isMatchingWithIndexExpression method fails to match the index.

The $1 is generated by CompiledBindArgument generateCanonicalizedExpression.

Also, note that the trace logging is incorrect on any query using a map index.

The static query is defined like:

<trace> select i from from /instrument i, i.tradingLines t where t.alternateReferences['SEDOL']='BFNY8K9'

This logs a message in the server log like:

[info 2016/10/14 14:29:58.145 PDT  <ServerConnection on port 56969 Thread 1> tid=0x3c] Query Executed in 54.36587 ms; rowCount = 16; indexesUsed(1):instrumentTLAltIdIndex-SEDOL(Results: 0) "<trace> select i from /instrument i, i.tradingLines t where t.alternateReferences['SEDOL']='BFNY8K9'"

The Results: 0 part of the message is wrong. It should be Results: 16.

The IndexTrackingQueryObserver beforeIndexLookup method saves the IndexInfo at key=instrumentTLAltIdIndex-SEDOL, but the afterIndexLookup method attempts to retrieve it using key=instrumentTLAltIdIndex. The IndexInfo is not found, so its results are not updated.

Issue Links

• is duplicated by: GEODE-2185 Index not used with parameterized query