Some criteria are missing when indexes are used on the source tables

[fox123]

Hi,

I have an issue in Teiid-8.10Alpha1 running a query on two tables from two different datasources.

The first table is defined in PG as:

CREATE TABLE tx1
(
  a character varying,
  b character varying,
  c integer,
  d integer,
  e integer
)

and contains the following rows:

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) ;

The second table is defined in MySQL and is defined as:

CREATE TABLE tx2 (
  a int(11),
  b int(11),
  c int(11),
  d int(11),
  e int(11)
)

with the following data:

insert into tx2 VALUES(1,2,3,4,5) ;

If I run this query:

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;

the expected result is correctly returned:

a	b	c	d	e
1	2	3	4	4

The problem occurs if I define indexes on each column of tx1. In this case my test query returns a wrong result:

a	b	c	d	e
1	2	2	2	2
1	2	3	3	3
1	2	3	4	4

If I look at the query plans generated for these queries, it seems that some criteria got lost. This is, for example, the last step from the debug plan when the source table contains indexes (criteria "tx1.c=tx2.c" and "tx1.d=tx2.d" are missing):

OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] [tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
  JoinNode(1) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[tx1.a=convert(tx2.a, string), convert(tx1.e, long) <> tx2.e, tx1.b = convert(tx2.b, string)] output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
    AccessNode(2) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4 FROM pg.tx3 AS g_0 ORDER BY c_0
    AccessNode(3) output=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4, convert(g_0.a, string) AS c_5 FROM my.tx2 AS g_0 ORDER BY c_5

Can you please have a look?

[shawkins]

I am not able to reproduce this.  Can you show a full plan debug using SHOWPLAN DEBUG?  And the full Teiid DDL for the tx1/tx2 tables.

[fox123]

Hi Steven,

thanks for the quick reply. Please, in the following you can find the Teiid DDL for the two tables:

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
);

I defined the two models in the VDB as:

<model name="my">
        <property name="importer.useFullSchemaName" value="false"/>
  <property name="importer.tableTypes" value="TABLE,VIEW"/>
  <property name="importer.importKeys" value="false"/>
        <source name="test_tables" translator-name="mysql" connection-jndi-name="java:/mysql-test-tables"/>
    </model>


  <model name="pg">
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
  <property name="importer.importKeys" value="true"/>
  <property name="importer.importIndexes" value="true"/>
        <source name="test_tables_pg" translator-name="postgresql" connection-jndi-name="java:/pg-test-tables"/>
  </model>

and this is finally the full plan debug:

============================================================================
USER COMMAND:
SELECT tx1.a, tx1.b, tx1.c, tx1.d, tx1.e FROM pg.tx1 AS tx1 INNER JOIN my.tx2 AS tx2 ON tx1.a = convert(tx2.a, string) AND tx1.b = convert(tx2.b, string) AND convert(tx1.c, long) = tx2.c AND convert(tx1.d, long) = tx2.d AND convert(tx1.e, long) <> tx2.e LIMIT 100


----------------------------------------------------------------------------
OPTIMIZE:
SELECT tx1.a, tx1.b, tx1.c, tx1.d, tx1.e FROM pg.tx1 AS tx1 INNER JOIN my.tx2 AS tx2 ON tx1.a = convert(tx2.a, string) AND tx1.b = convert(tx2.b, string) AND convert(tx1.c, long) = tx2.c AND convert(tx1.d, long) = tx2.d AND convert(tx1.e, long) <> tx2.e LIMIT 100


----------------------------------------------------------------------------
GENERATE CANONICAL:
SELECT tx1.a, tx1.b, tx1.c, tx1.d, tx1.e FROM pg.tx1 AS tx1 INNER JOIN my.tx2 AS tx2 ON tx1.a = convert(tx2.a, string) AND tx1.b = convert(tx2.b, string) AND convert(tx1.c, long) = tx2.c AND convert(tx1.d, long) = tx2.d AND convert(tx1.e, long) <> tx2.e LIMIT 100


CANONICAL PLAN:
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e]})
      Source(groups=[pg.tx1 AS tx1])
      Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING PlaceAccess


AFTER:
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100})
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000})
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000})
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING PushNonJoinCriteria


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1])
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING CleanCriteria


AFTER:
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=null})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=null})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null})
        Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=null})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null})
        Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=null})




============================================================================
EXECUTING RaiseAccess


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=null})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null})
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null})
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING CopyCriteria


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=null, IS_COPIED=true})
      Access(groups=[pg.tx1 AS tx1])
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING CleanCriteria


AFTER:
TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=null})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=null, IS_COPIED=true})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null})
        Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=null})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null})
        Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=null})




============================================================================
EXECUTING PlanJoins


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=null})
    Select(groups=[my.tx2 AS tx2, pg.tx1 AS tx1], props={SELECT_CRITERIA=convert(tx1.e, long) <> tx2.e, EST_SELECTIVITY=0.8742223})
      Select(groups=[my.tx2 AS tx2, pg.tx1 AS tx1], props={SELECT_CRITERIA=convert(tx1.d, long) = tx2.d, EST_SELECTIVITY=0.12577777})
        Select(groups=[my.tx2 AS tx2, pg.tx1 AS tx1], props={SELECT_CRITERIA=convert(tx1.c, long) = tx2.c, EST_SELECTIVITY=0.12577777})
          Select(groups=[my.tx2 AS tx2, pg.tx1 AS tx1], props={SELECT_CRITERIA=tx1.b = convert(tx2.b, string), EST_SELECTIVITY=0.12577777})
            Select(groups=[my.tx2 AS tx2, pg.tx1 AS tx1], props={SELECT_CRITERIA=tx1.a = convert(tx2.a, string), EST_SELECTIVITY=0.12577777})
              Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=CROSS JOIN, JOIN_STRATEGY=NESTED_LOOP})
                Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
                  Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=null, EST_COL_STATS={tx1.a=[-1.0, -1.0], tx1.b=[-1.0, -1.0], tx1.c=[-1.0, -1.0], tx1.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
                Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
                  Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=null, EST_COL_STATS={tx2.a=[-1.0, -1.0], tx2.b=[-1.0, -1.0], tx2.c=[-1.0, -1.0], tx2.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})




============================================================================
EXECUTING PushSelectCriteria


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=null})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1])
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING RaiseAccess


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=NESTED_LOOP, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING ChooseJoinStrategy


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a, tx1.b, convert(tx1.c, long), convert(tx1.d, long)], RIGHT_EXPRESSIONS=[convert(tx2.a, string), convert(tx2.b, string), tx2.c, tx2.d], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1])
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING ChooseDependent


AFTER:
TupleLimit(groups=[])
  Project(groups=[pg.tx1 AS tx1])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a, tx1.b, convert(tx1.c, long), convert(tx1.d, long)], RIGHT_EXPRESSIONS=[convert(tx2.a, string), convert(tx2.b, string), tx2.c, tx2.d], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
        Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=null, EST_COL_STATS={tx1.a=[-1.0, -1.0], tx1.b=[-1.0, -1.0], tx1.c=[-1.0, -1.0], tx1.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=null, EST_CARDINALITY=-1.0})
        Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=null, EST_COL_STATS={tx2.a=[-1.0, -1.0], tx2.b=[-1.0, -1.0], tx2.c=[-1.0, -1.0], tx2.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})




============================================================================
EXECUTING PushLimit


AFTER:
Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=null, SOURCE_HINT=null})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=null})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a, tx1.b, convert(tx1.c, long), convert(tx1.d, long)], RIGHT_EXPRESSIONS=[convert(tx2.a, string), convert(tx2.b, string), tx2.c, tx2.d], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e]})
      Access(groups=[pg.tx1 AS tx1])
        Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING AssignOutputElements


AFTER:
Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], SOURCE_HINT=null})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a, tx1.b, convert(tx1.c, long), convert(tx1.d, long)], RIGHT_EXPRESSIONS=[convert(tx2.a, string), convert(tx2.b, string), tx2.c, tx2.d], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
        Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_COL_STATS={tx1.a=[-1.0, -1.0], tx1.b=[-1.0, -1.0], tx1.c=[-1.0, -1.0], tx1.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e], EST_CARDINALITY=-1.0})
        Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e], EST_COL_STATS={tx2.a=[-1.0, -1.0], tx2.b=[-1.0, -1.0], tx2.c=[-1.0, -1.0], tx2.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})




============================================================================
EXECUTING CalculateCost


AFTER:
Project(groups=[pg.tx1 AS tx1], props={PROJECT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], SOURCE_HINT=null, EST_CARDINALITY=100.0, EST_COL_STATS={tx1.a=[100.0, 1.0], tx1.b=[100.0, 1.0], tx1.c=[100.0, 1.0], tx1.d=[100.0, 1.0], ...}})
  TupleLimit(groups=[], props={MAX_TUPLE_LIMIT=100, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=100.0, EST_COL_STATS={tx1.a=[100.0, 0.0], tx1.b=[100.0, 0.0], tx1.c=[100.0, 0.0], tx1.d=[100.0, 0.0], ...}})
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a, tx1.b, convert(tx1.c, long), convert(tx1.d, long)], RIGHT_EXPRESSIONS=[convert(tx2.a, string), convert(tx2.b, string), tx2.c, tx2.d], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
        Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_COL_STATS={tx1.a=[-1.0, -1.0], tx1.b=[-1.0, -1.0], tx1.c=[-1.0, -1.0], tx1.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e], EST_CARDINALITY=-1.0})
        Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e], EST_COL_STATS={tx2.a=[-1.0, -1.0], tx2.b=[-1.0, -1.0], tx2.c=[-1.0, -1.0], tx2.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})




============================================================================
EXECUTING ImplementJoinStrategy


AFTER:
Project(groups=[pg.tx1 AS tx1])
  TupleLimit(groups=[])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a], RIGHT_EXPRESSIONS=[convert(tx2.a, string)], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e, tx1.b = convert(tx2.b, string)], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
        Sort(groups=[], props={SORT_ORDER=ORDER BY tx1.a, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]})
          Source(groups=[pg.tx1 AS tx1], props={OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_COL_STATS={tx1.a=[-1.0, -1.0], tx1.b=[-1.0, -1.0], tx1.c=[-1.0, -1.0], tx1.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)], EST_CARDINALITY=-1.0})
        Sort(groups=[], props={SORT_ORDER=ORDER BY convert(tx2.a, string), OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)]})
          Source(groups=[my.tx2 AS tx2], props={OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e], EST_COL_STATS={tx2.a=[-1.0, -1.0], tx2.b=[-1.0, -1.0], tx2.c=[-1.0, -1.0], tx2.d=[-1.0, -1.0], ...}, EST_CARDINALITY=-1.0})




============================================================================
EXECUTING MergeCriteria


AFTER:
Project(groups=[pg.tx1 AS tx1])
  TupleLimit(groups=[])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2])
      Access(groups=[pg.tx1 AS tx1])
        Sort(groups=[])
          Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Sort(groups=[])
          Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING PlanSorts


AFTER:
Project(groups=[pg.tx1 AS tx1])
  TupleLimit(groups=[])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2], props={JOIN_TYPE=INNER JOIN, JOIN_STRATEGY=MERGE, JOIN_CRITERIA=[tx1.a = convert(tx2.a, string), tx1.b = convert(tx2.b, string), convert(tx1.c, long) = tx2.c, convert(tx1.d, long) = tx2.d, convert(tx1.e, long) <> tx2.e], LEFT_EXPRESSIONS=[tx1.a], RIGHT_EXPRESSIONS=[convert(tx2.a, string)], NON_EQUI_JOIN_CRITERIA=[convert(tx1.e, long) <> tx2.e, tx1.b = convert(tx2.b, string)], OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0})
      Access(groups=[pg.tx1 AS tx1])
        Sort(groups=[])
          Source(groups=[pg.tx1 AS tx1])
      Access(groups=[my.tx2 AS tx2])
        Sort(groups=[])
          Source(groups=[my.tx2 AS tx2])




============================================================================
EXECUTING CollapseSource


AFTER:
Project(groups=[pg.tx1 AS tx1])
  TupleLimit(groups=[])
    Join(groups=[pg.tx1 AS tx1, my.tx2 AS tx2])
      Access(groups=[pg.tx1 AS tx1], props={SOURCE_HINT=null, MODEL_ID=Schema name=pg, nameInSource=null, uuid=tid:0018ab35abe6-00000df7-00000000, OUTPUT_COLS=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=SELECT tx1.a, tx1.b, tx1.c, tx1.d, tx1.e FROM pg.tx1 AS tx1 ORDER BY tx1.a})
      Access(groups=[my.tx2 AS tx2], props={SOURCE_HINT=null, MODEL_ID=Schema name=my, nameInSource=null, uuid=tid:0018ab35ab9b-00000dac-00000000, OUTPUT_COLS=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)], EST_CARDINALITY=-1.0, ATOMIC_REQUEST=SELECT tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string) FROM my.tx2 AS tx2 ORDER BY convert(tx2.a, string)})




============================================================================
CONVERTING PLAN TREE TO PROCESS TREE


PROCESS PLAN =
ProjectNode(0) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] [tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
  LimitNode(1) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] limit 100
    JoinNode(2) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[tx1.a=convert(tx2.a, string), convert(tx1.e, long) <> tx2.e, tx1.b = convert(tx2.b, string)] output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
      AccessNode(3) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4 FROM pg.tx1 AS g_0 ORDER BY c_0
      AccessNode(4) output=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4, convert(g_0.a, string) AS c_5 FROM my.tx2 AS g_0 ORDER BY c_5


============================================================================


----------------------------------------------------------------------------
OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] [tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
  LimitNode(1) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] limit 100
    JoinNode(2) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[tx1.a=convert(tx2.a, string), convert(tx1.e, long) <> tx2.e, tx1.b = convert(tx2.b, string)] output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e]
      AccessNode(3) output=[tx1.a, tx1.b, tx1.c, tx1.d, tx1.e] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4 FROM pg.tx1 AS g_0 ORDER BY c_0
      AccessNode(4) output=[tx2.a, tx2.b, tx2.c, tx2.d, tx2.e, convert(tx2.a, string)] SELECT g_0.a AS c_0, g_0.b AS c_1, g_0.c AS c_2, g_0.d AS c_3, g_0.e AS c_4, convert(g_0.a, string) AS c_5 FROM my.tx2 AS g_0 ORDER BY c_5


============================================================================

[shawkins]

Yes there is an issue.  It's the combination of the indexes and that the Teiid types do not match for the comparisons: convert(tx1.c, long) = tx2.c

More than likely the mysql integer is unsigned and being imported as a long.  A fix will be needed to address this.  Can you log something?

[fox123]

I logged a new issue:

[TEIID-3296] Some criteria are missing when indexes are used on the source tables - JBoss Issue Tracker

[shawkins]

Thank you.  This will be resolved for 8.10 Beta2 next week.