[teiid-issues] [JBoss JIRA] (TEIID-2450) Like criteria with 'Z' before wildcards throw an exception

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12763578#comment-12763578 ] 

Steven Hawkins commented on TEIID-2450:
---------------------------------------

Yes there is a regression here with TEIID-2181.  However only the exception with the Z case is an issue.

> Other than this exception it also has another problem. If we do query MY_COLUMN LIKE 'BA at _BAR' then upper condition would be 'A' (next character after '@').
But due to lowercase comparison it will be converted to 'a', so values like 'BAB_BAR', 'BAC_BAR', ... 'BA^_BAR' all would be returned because 'B', 'C', ..., '^' are between '@' and 'a'.

That will not happen for two reasons.  The first is just as you have stated above that the map comparison will be done using effectively lower case, thus upper B, C, and '^' are not between @ and A.  The next is that the predicate will be reevaluated (RecordTable.isValid) to ensure only case sensitive results apply (for example if Ba at ABAR exists it will match the partial index condition, but will not be returned).  

I have already confirmed this locally, but if you are not speaking hypothetically here, can you provide a test case that shows bad behavior?

> I also worry about CompareCriteria.LE in BaseIndexInfo.processCriteria(). Shouldn't it be strictly less? with less or equal when querying 'AAA_' you will get 'AAB_' values as well.

The same reasoning as above applies here as well.
                
> Like criteria with 'Z' before wildcards throw an exception
> ----------------------------------------------------------
>
>                 Key: TEIID-2450
>                 URL: https://issues.jboss.org/browse/TEIID-2450
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.2
>            Reporter: Paul Lysak
>            Assignee: Steven Hawkins
>
> If query contains letter 'Z' before wildcard (for example, query contains following part: MY_COLUMN LIKE 'BAZ_BAR') then exception is thrown:
> Caused by: java.lang.IllegalArgumentException: fromKey > toKey
> 	at java.util.TreeMap$NavigableSubMap.<init>(TreeMap.java:1240)
> 	...
> 	at org.teiid.dqp.internal.process.RecordTable.processQuery(RecordTable.java:189)
> 	...
> 	
> The reason of the problem is following.
> BaseIndexInfo.processCriteria() in order to build "less then" conditon from LIKE increments the code of last character in condition prefix:
> this.addCondition(i, new Constant(prefix.substring(0, prefix.length() -1) + (char) (prefix.charAt(prefix.length()-1)+1)), CompareCriteria.LE);
> If prefix happens to end in uppercase letter 'Z' then after increment it becomes '['. 
> But TempMetadataStore uses String.CaseInsensitiveComparator:
> public TempMetadataStore() {
> 	this(new TreeMap<String, TempMetadataID>(String.CASE_INSENSITIVE_ORDER));
> }
> And String API documentation clearly says that comparison is performed against lowercase versions. Therefore 'Z' is converted to 'z'.
> And later when trying to get subtree with lower bound = 'Z' and upper bound = '[' and exception is thrown because in fact 'z' is lesser then '['
> Other than this exception it also has another problem. If we do query MY_COLUMN LIKE 'BA at _BAR' then upper condition would be 'A' (next character after '@').
> But due to lowercase comparison it will be converted to 'a', so values like 'BAB_BAR', 'BAC_BAR', ... 'BA^_BAR' all would be returned because 'B', 'C', ..., '^' are between '@' and 'a'.
> I suggest either using case sensitive map (with prior conversion of all values to upper case) or converting values in BaseIndexInfo.processCriteria() into lower case.
> I also worry about CompareCriteria.LE in BaseIndexInfo.processCriteria(). Shouldn't it be strictly less? with less or equal when querying 'AAA_' you will get 'AAB_' values as well.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira