5 Replies Latest reply on Jan 22, 2015 11:41 AM by Steven Hawkins

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

    Salvatore R Newbie

      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:

      abcde
      12344

       

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

      abcde
      12222
      12333
      12344

       

      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?

        • 1. Re: Some criteria are missing when indexes are used on the source tables
          Steven Hawkins Master

          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.

          • 2. Re: Re: Some criteria are missing when indexes are used on the source tables
            Salvatore R Newbie

            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
            
            
            ============================================================================
            
            
            • 3. Re: Re: Some criteria are missing when indexes are used on the source tables
              Steven Hawkins Master

              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?

              • 5. Re: Re: Some criteria are missing when indexes are used on the source tables
                Steven Hawkins Master

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