[teiid-issues] [JBoss JIRA] (TEIID-3296) Some criteria are missing when indexes are used on the source tables
Salvatore R (JIRA)
issues at jboss.org
Thu Jan 22 10:43:48 EST 2015
[ https://issues.jboss.org/browse/TEIID-3296?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Salvatore R updated TEIID-3296:
-------------------------------
Description:
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|
was:
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|
> 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
> Reporter: Salvatore R
> Assignee: Steven Hawkins
>
> 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)
More information about the teiid-issues
mailing list