[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=12763722#comment-12763722 ] 

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

> But there should be some reason for creating submap. I guess it's speed, am I right?

Yes the whole point is to avoid full scans when possible regardless of whether we're targeting a real table or a memory backed construct like the system tables.

> So it would be great to have that submap work right if it isn't a complex task.

It is working as intended.

> If you look at ASCII table you'll see that characters follow in such order:

Yes, I am aware of the character ordering.  Here again it does not matter if if ^ _ etc. are initially included in the submap.  What matters is that the partial prefix matching reduces the amount of the table that we scan.
                
> 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
>             Fix For: 8.4
>
>
> 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