[teiid-issues] [JBoss JIRA] (TEIID-3627) Infinispan-dsl-cache translator: comparison operators(GE, LE) problem with string

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-3627:
---------------------------------------

What is the issue for JDG?

> Because the String doesn't work, all data types for this use have to be disabled.

Or like you say, you can just change to unsearchable on the string columns.

Otherwise the ldap changes for 8.12 added a searchability called equality_only.  This allows for pushdown of just equality.

> Infinispan-dsl-cache translator: comparison operators(GE,LE) problem with string
> --------------------------------------------------------------------------------
>
>                 Key: TEIID-3627
>                 URL: https://issues.jboss.org/browse/TEIID-3627
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.7.1.6_2
>            Reporter: Jan Stastny
>            Assignee: Van Halbert
>
> Comparison of string values provides wrong results for GE and LE operators. I provide example queries, notice the number of rows returned by the queries.
> For query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum <= -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}
> LimitNode(0) output=[g_0.stringNum] limit 100
>     AccessNode(1) output=[g_0.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum
> {code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum <= '-22' ORDER BY g_0.stringNum{code}
> * result 0 rows
> But for query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum < -22 ORDER BY StringNum{code}
> * Process Tree:
> 	{code:plain}ProjectNode(0) output=[c.stringNum AS StringNum] [c.stringNum AS StringNum]
> 	  LimitNode(1) output=[c.stringNum] limit 100
> 	    SortNode(2) output=[c.stringNum] [SORT] [c.stringNum]
> 	      SelectNode(3) output=[c.stringNum] c.stringNum < '-22'
> 		AccessNode(4) output=[c.stringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0{code}
> * result 14 rows
> And query: {code:sql}SELECT BQT1.SmallA.StringNum FROM BQT1.SmallA WHERE BQT1.SmallA.StringNum = -22 ORDER BY StringNum{code}
> * Process Tree:
> {code:plain}LimitNode(0) output=[c.stringNum AS StringNum] limit 100
> 		AccessNode(1) output=[c.stringNum AS StringNum] SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * SRC CMD: {code:sql}SELECT g_0.stringNum FROM SmallAs.smallARemotecache AS g_0 WHERE g_0.stringNum = '-22' ORDER BY g_0.stringNum{code}
> * result 1 row
> The first query should then return 15 rows instead of 0. Also the queries differ in a way they are processed, the first one is pushed down to infinispan, the other two are processed by teiid, which is probably a regression originally tracked here: TEIID-3424
> The same cause introduces problems with similar queries:
> {code:sql}Select IntKey, StringKey From BQT1.SmallA WHERE NOT(StringKey > 10 AND IntKey < 47) ORDER BY IntKey{code}
> Which is processed as:
> * Process Tree: 
> 		{code:plain}LimitNode(0) output=[c.intKey AS IntKey, c.stringKey AS StringKey] limit 100
> 		  AccessNode(1) output=[c.intKey AS IntKey, c.stringKey AS StringKey] SELECT g_0.intKey, g_0.stringKey FROM SmallAs.smallARemotecache AS g_0 WHERE (g_0.stringKey <= '10') OR (g_0.intKey >= 47) ORDER BY g_0.intKey{code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)