Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6104

Joins in nested view commands lead to OoM

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Blocker Blocker
    • None
    • 16.0.1
    • Query Engine
    • None
    • Hide

      1. Add MySQL (in my case I use adventureworks test database) database configuration in standalone-teiid.xml (adventureworks):

                      <datasource jndi-name="java:/adventureworks" pool-name="adventureworks" enabled="true" use-java-context="true">
                    <connection-url>jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull</connection-url>
                    <driver>mysql</driver>
                    <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                    <pool>
                        <min-pool-size>2</min-pool-size>
                        <max-pool-size>70</max-pool-size>
                    </pool>
                    <security>
                        <user-name>XXXX</user-name>
                        <password>XXXX</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                    <timeout>
                        <blocking-timeout-millis>120000</blocking-timeout-millis>
                        <idle-timeout-minutes>5</idle-timeout-minutes>
                    </timeout>
                </datasource>

      2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source:

          <model name="adventureworks">
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
        <property name="importer.importKeys" value="false"/>
        <source name="adventureworks" translator-name="mylobs" connection-jndi-name="java:/adventureworks"/>
    </model>

      3. Add the following dependent views:

                   create view salesperson AS SELECT "SalesPersonID","TerritoryID","SalesQuota","Bonus","rowguid","CommissionPct","SalesYTD","SalesLastYear","ModifiedDate" FROM "oracle_DB.salesperson";
             create view salesperson_1 AS SELECT "SalesPersonID", "TerritoryID", "SalesQuota", "Bonus", "rowguid", "CommissionPct", "SalesYTD", "SalesLastYear", "ModifiedDate" FROM "oracle_DB.salesperson";
             create view v1 as SELECT * FROM "views.salesperson" a INNER JOIN "views.salesperson_1" b ON "a.SalesPersonID" = "b.SalesPersonID";
             create view v2 as SELECT * FROM "views.salesperson" a INNER JOIN "views.salesperson_1" b ON "a.SalesPersonID" = "b.SalesPersonID";
             create view v3 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v2 a join views.v1 b on a.SalesPersonID = b.SalesPersonID;
             create view v4 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v3 a join views.v2 b on a.SalesPersonID = b.SalesPersonID;
             create view v5 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v4 a join views.v3 b on a.SalesPersonID = b.SalesPersonID;
             create view v6 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v5 a join views.v4 b on a.SalesPersonID = b.SalesPersonID;
             create view v7 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v6 a join views.v5 b on a.SalesPersonID = b.SalesPersonID;
             create view v8 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v7 a join views.v6 b on a.SalesPersonID = b.SalesPersonID;
             create view v9 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v8 a join views.v7 b on a.SalesPersonID = b.SalesPersonID;
             create view v10 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v9 a join views.v8 b on a.SalesPersonID = b.SalesPersonID;
             create view v11 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v10 a join views.v9 b on a.SalesPersonID = b.SalesPersonID;
             create view v12 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v11 a join views.v10 b on a.SalesPersonID = b.SalesPersonID;
             create view v13 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v12 a join views.v11 b on a.SalesPersonID = b.SalesPersonID;
             create view v14 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v13 a join views.v12 b on a.SalesPersonID = b.SalesPersonID;
             create view v15 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v14 a join views.v13 b on a.SalesPersonID = b.SalesPersonID;
             create view v16 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v15 a join views.v14 b on a.SalesPersonID = b.SalesPersonID;
             create view v17 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v16 a join views.v15 b on a.SalesPersonID = b.SalesPersonID;
             create view v18 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v17 a join views.v16 b on a.SalesPersonID = b.SalesPersonID;
             create view v19 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v18 a join views.v17 b on a.SalesPersonID = b.SalesPersonID;
             create view v20 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v19 a join views.v18 b on a.SalesPersonID = b.SalesPersonID;
             create view v21 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v20 a join views.v19 b on a.SalesPersonID = b.SalesPersonID;
             create view v22 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v21 a join views.v20 b on a.SalesPersonID = b.SalesPersonID;
             create view v23 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v22 a join views.v21 b on a.SalesPersonID = b.SalesPersonID;
             create view v24 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v23 a join views.v22 b on a.SalesPersonID = b.SalesPersonID;
             create view v25 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v24 a join views.v23 b on a.SalesPersonID = b.SalesPersonID;
             create view v26 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v25 a join views.v24 b on a.SalesPersonID = b.SalesPersonID;
             create view v27 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v26 a join views.v25 b on a.SalesPersonID = b.SalesPersonID;
             create view v28 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v27 a join views.v26 b on a.SalesPersonID = b.SalesPersonID;
             create view v29 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v28 a join views.v27 b on a.SalesPersonID = b.SalesPersonID;
             create view v30 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v29 a join views.v28 b on a.SalesPersonID = b.SalesPersonID;
             create view v31 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v30 a join views.v29 b on a.SalesPersonID = b.SalesPersonID;
             create view v32 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v31 a join views.v30 b on a.SalesPersonID = b.SalesPersonID;
             create view v33 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v32 a join views.v31 b on a.SalesPersonID = b.SalesPersonID;
             create view v34 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v33 a join views.v32 b on a.SalesPersonID = b.SalesPersonID;
             create view v35 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v34 a join views.v33 b on a.SalesPersonID = b.SalesPersonID;
             create view v36 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v35 a join views.v34 b on a.SalesPersonID = b.SalesPersonID;
             create view v37 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v36 a join views.v35 b on a.SalesPersonID = b.SalesPersonID;
             create view v38 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v37 a join views.v36 b on a.SalesPersonID = b.SalesPersonID;
             create view v39 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v38 a join views.v37 b on a.SalesPersonID = b.SalesPersonID;
             create view v40 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v39 a join views.v38 b on a.SalesPersonID = b.SalesPersonID;
             create view v41 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v40 a join views.v39 b on a.SalesPersonID = b.SalesPersonID;
             create view v42 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v41 a join views.v40 b on a.SalesPersonID = b.SalesPersonID;
             create view v43 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v42 a join views.v41 b on a.SalesPersonID = b.SalesPersonID;
             create view v44 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v43 a join views.v42 b on a.SalesPersonID = b.SalesPersonID;
             create view v45 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v44 a join views.v43 b on a.SalesPersonID = b.SalesPersonID;
             create view v46 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v45 a join views.v44 b on a.SalesPersonID = b.SalesPersonID;
             create view v47 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v46 a join views.v45 b on a.SalesPersonID = b.SalesPersonID;
             create view v48 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v47 a join views.v46 b on a.SalesPersonID = b.SalesPersonID;
             create view v49 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v48 a join views.v47 b on a.SalesPersonID = b.SalesPersonID;
             create view v50 as
                SELECT a."SalesPersonID", a."TerritoryID", a."SalesQuota", a."Bonus", a."rowguid", a."CommissionPct", a."SalesYTD", a."SalesLastYear", a."ModifiedDate", b."SalesPersonID_1", b."TerritoryID_1", b."SalesQuota_1", b."Bonus_1", b."rowguid_1", b."CommissionPct_1", b."SalesYTD_1", b."SalesLastYear_1", b."ModifiedDate_1" 
                FROM views.v49 a join views.v48 b on a.SalesPersonID = b.SalesPersonID;

      4. Running this query:

      SELECT * FROM "views.v50";;

      leads to OoM, running this query:

      select * from views.v9 ;;

      leads to "Too many tables" exception on MySQL.

      Show
      1. Add MySQL (in my case I use adventureworks test database) database configuration in standalone-teiid.xml (adventureworks): <datasource jndi-name= "java:/adventureworks" pool-name= "adventureworks" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/adventureworks?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> XXXX </user-name> <password> XXXX </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </validation> <timeout> <blocking-timeout-millis> 120000 </blocking-timeout-millis> <idle-timeout-minutes> 5 </idle-timeout-minutes> </timeout> </datasource> 2. Add in test-vdb.xml java:/adventureworks configured in previous step as a data source: <model name= "adventureworks" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "adventureworks" translator-name= "mylobs" connection-jndi-name= "java:/adventureworks" /> </model> 3. Add the following dependent views: create view salesperson AS SELECT "SalesPersonID" , "TerritoryID" , "SalesQuota" , "Bonus" , "rowguid" , "CommissionPct" , "SalesYTD" , "SalesLastYear" , "ModifiedDate" FROM "oracle_DB.salesperson" ; create view salesperson_1 AS SELECT "SalesPersonID" , "TerritoryID" , "SalesQuota" , "Bonus" , "rowguid" , "CommissionPct" , "SalesYTD" , "SalesLastYear" , "ModifiedDate" FROM "oracle_DB.salesperson" ; create view v1 as SELECT * FROM "views.salesperson" a INNER JOIN "views.salesperson_1" b ON "a.SalesPersonID" = "b.SalesPersonID" ; create view v2 as SELECT * FROM "views.salesperson" a INNER JOIN "views.salesperson_1" b ON "a.SalesPersonID" = "b.SalesPersonID" ; create view v3 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v2 a join views.v1 b on a.SalesPersonID = b.SalesPersonID; create view v4 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v3 a join views.v2 b on a.SalesPersonID = b.SalesPersonID; create view v5 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v4 a join views.v3 b on a.SalesPersonID = b.SalesPersonID; create view v6 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v5 a join views.v4 b on a.SalesPersonID = b.SalesPersonID; create view v7 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v6 a join views.v5 b on a.SalesPersonID = b.SalesPersonID; create view v8 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v7 a join views.v6 b on a.SalesPersonID = b.SalesPersonID; create view v9 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v8 a join views.v7 b on a.SalesPersonID = b.SalesPersonID; create view v10 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v9 a join views.v8 b on a.SalesPersonID = b.SalesPersonID; create view v11 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v10 a join views.v9 b on a.SalesPersonID = b.SalesPersonID; create view v12 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v11 a join views.v10 b on a.SalesPersonID = b.SalesPersonID; create view v13 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v12 a join views.v11 b on a.SalesPersonID = b.SalesPersonID; create view v14 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v13 a join views.v12 b on a.SalesPersonID = b.SalesPersonID; create view v15 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v14 a join views.v13 b on a.SalesPersonID = b.SalesPersonID; create view v16 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v15 a join views.v14 b on a.SalesPersonID = b.SalesPersonID; create view v17 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v16 a join views.v15 b on a.SalesPersonID = b.SalesPersonID; create view v18 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v17 a join views.v16 b on a.SalesPersonID = b.SalesPersonID; create view v19 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v18 a join views.v17 b on a.SalesPersonID = b.SalesPersonID; create view v20 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v19 a join views.v18 b on a.SalesPersonID = b.SalesPersonID; create view v21 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v20 a join views.v19 b on a.SalesPersonID = b.SalesPersonID; create view v22 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v21 a join views.v20 b on a.SalesPersonID = b.SalesPersonID; create view v23 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v22 a join views.v21 b on a.SalesPersonID = b.SalesPersonID; create view v24 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v23 a join views.v22 b on a.SalesPersonID = b.SalesPersonID; create view v25 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v24 a join views.v23 b on a.SalesPersonID = b.SalesPersonID; create view v26 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v25 a join views.v24 b on a.SalesPersonID = b.SalesPersonID; create view v27 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v26 a join views.v25 b on a.SalesPersonID = b.SalesPersonID; create view v28 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v27 a join views.v26 b on a.SalesPersonID = b.SalesPersonID; create view v29 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v28 a join views.v27 b on a.SalesPersonID = b.SalesPersonID; create view v30 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v29 a join views.v28 b on a.SalesPersonID = b.SalesPersonID; create view v31 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v30 a join views.v29 b on a.SalesPersonID = b.SalesPersonID; create view v32 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v31 a join views.v30 b on a.SalesPersonID = b.SalesPersonID; create view v33 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v32 a join views.v31 b on a.SalesPersonID = b.SalesPersonID; create view v34 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v33 a join views.v32 b on a.SalesPersonID = b.SalesPersonID; create view v35 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v34 a join views.v33 b on a.SalesPersonID = b.SalesPersonID; create view v36 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v35 a join views.v34 b on a.SalesPersonID = b.SalesPersonID; create view v37 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v36 a join views.v35 b on a.SalesPersonID = b.SalesPersonID; create view v38 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v37 a join views.v36 b on a.SalesPersonID = b.SalesPersonID; create view v39 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v38 a join views.v37 b on a.SalesPersonID = b.SalesPersonID; create view v40 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v39 a join views.v38 b on a.SalesPersonID = b.SalesPersonID; create view v41 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v40 a join views.v39 b on a.SalesPersonID = b.SalesPersonID; create view v42 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v41 a join views.v40 b on a.SalesPersonID = b.SalesPersonID; create view v43 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v42 a join views.v41 b on a.SalesPersonID = b.SalesPersonID; create view v44 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v43 a join views.v42 b on a.SalesPersonID = b.SalesPersonID; create view v45 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v44 a join views.v43 b on a.SalesPersonID = b.SalesPersonID; create view v46 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v45 a join views.v44 b on a.SalesPersonID = b.SalesPersonID; create view v47 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v46 a join views.v45 b on a.SalesPersonID = b.SalesPersonID; create view v48 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v47 a join views.v46 b on a.SalesPersonID = b.SalesPersonID; create view v49 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v48 a join views.v47 b on a.SalesPersonID = b.SalesPersonID; create view v50 as SELECT a. "SalesPersonID" , a. "TerritoryID" , a. "SalesQuota" , a. "Bonus" , a. "rowguid" , a. "CommissionPct" , a. "SalesYTD" , a. "SalesLastYear" , a. "ModifiedDate" , b. "SalesPersonID_1" , b. "TerritoryID_1" , b. "SalesQuota_1" , b. "Bonus_1" , b. "rowguid_1" , b. "CommissionPct_1" , b. "SalesYTD_1" , b. "SalesLastYear_1" , b. "ModifiedDate_1" FROM views.v49 a join views.v48 b on a.SalesPersonID = b.SalesPersonID; 4. Running this query: SELECT * FROM "views.v50" ;; leads to OoM, running this query: select * from views.v9 ;; leads to "Too many tables" exception on MySQL.

      Joins in nested view commands lead to OoM when building query plans, moreover such joins generate too many tables in from clause (for example, it can beat maximum of tables for from clause in MySQL, by default the maximum is 61). The main problem is in recursive calls of RelationalPlanner.buildTree method for the case when clause is a JoinPredicate.

              Unassigned Unassigned
              dalex005 Dmitrii Pogorelov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated: