[teiid-issues] [JBoss JIRA] (TEIID-3296) Some criteria are missing when indexes are used on the source tables

[Steven Hawkins (JIRA)]

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

Steven Hawkins edited comment on TEIID-3296 at 1/22/15 11:38 AM:
-----------------------------------------------------------------

Added predicates containing non-column reference expressions to be preserved.  This needs to be queued for 8.7.x


was (Author: shawkins):
Added predicates containing non-column reference expressions to be preserved.

> Some criteria are missing when indexes are used on the source tables
> --------------------------------------------------------------------
>
>                 Key: TEIID-3296
>                 URL: https://issues.jboss.org/browse/TEIID-3296
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.2
>            Reporter: Salvatore R
>            Assignee: Steven Hawkins
>             Fix For: 8.10
>
>
> The following two tables are defined in two different datasources (PostgreSQL and MySQL):
> {code:sql}
> CREATE TABLE "pg"."tx1"  
> (  
>    a varchar(2147483647),  
>    b varchar(2147483647),  
>    c integer,  
>    d integer,  
>    e integer  
> );  
> CREATE INDEX tx1_b ON "pg"."tx1"(b);  
> CREATE INDEX tx1_a ON "pg"."tx1"(a);  
> CREATE INDEX tx1_e ON "pg"."tx1"(e);  
> CREATE INDEX tx1_d ON "pg"."tx1"(d);  
> CREATE INDEX tx1_c ON "pg"."tx1"(c);  
>   
> CREATE TABLE "my"."tx2"  
> (  
>    a integer,  
>    b integer,  
>    c integer,  
>    d integer,  
>    e integer  
> );
> insert into tx1 VALUES(1,1,1,1,1) ;  
> insert into tx1 VALUES(1,2,2,2,2) ;  
> insert into tx1 VALUES(1,2,3,3,3) ;  
> insert into tx1 VALUES(1,2,3,4,4) ;  
> insert into tx1 VALUES(1,2,3,4,5) ;  
> insert into tx2 VALUES(1,2,3,4,5) ; 
> {code}
> Running the following query:
> {code:sql}
> select tx1.* from pg.tx1 as tx1 join my.tx2 as tx2 on tx1.a=tx2.a and tx1.b=tx2.b and tx1.c=tx2.c and tx1.d=tx2.d and tx1.e!=tx2.e;  
> {code}
> a wrong result is returned:
> ||a||b||c||d||e||
> |1|2|2|2|2|
> |1|2|3|3|3|
> |1|2|3|4|4|
> If indexes are removed from the source table, the expected result is correctly returned:
> ||a||b||c||d||e||
> |1|2|3|4|4|



--
This message was sent by Atlassian JIRA
(v6.3.11#6341)