bug with multiple indirection

claudiobosticco

Registered Developer

Join Date: Sep 2008
Posts: 3091

14 Jan 2020, 07:09

Hello, I have a test case with a simplified version of my dataSources with multiple indirections, which works with:
SNAPSHOT_v12.1d_2020-01-12/Enterprise Deployment
and doesn't work with:
SNAPSHOT_v12.1d_2020-01-13/Enterprise Deployment (and the latest build has the same behaviour)

This is the test case:

Code:

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   tableName="JTK_POSTI_OMAGGIO"
                   ID="JTK_OMAGGI_TITOLARI"
                   serverType="sql"
                   requiresAuthentication="true"
       >
           <fields>
               <field primaryKey="true" name="ID_REC" type="sequence" hidden="true" sequenceName="SEQUENCE_ID_JTK"/>
               <field name="ID_TITOLARE_FK" type="integer" foreignKey="JTK_TITOLARI.ID_REC" relatedTableAlias="TITOLARI"/>
               <field name="ID_POSTO_FK" type="integer" foreignKey="JPC_POSTI_FILE_SETTORI.ID_REC" relatedTableAlias="POSTI"/>

               <field name="NOME" type="text" includeFrom="JTK_TITOLARI.NOME" includeVia="ID_TITOLARE_FK"/>
               <field name="ID_LISTINO_FK" type="integer" includeFrom="JPC_POSTI_FILE_SETTORI.JPC_FILE_SETTORI.JPC_SETTORI.ID_LISTINI_FK"/>

               <field name="DATA_MATCH" type="datetime" includeFrom="JPC_POSTI_FILE_SETTORI.JPC_FILE_SETTORI.JPC_SETTORI.JPC_LISTINI.DATA_INIZIO"/>
               <field name="ID_STAGIONI_FK" type="integer" includeFrom="JPC_LISTINI.ID_STAGIONI_FK"/>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   tableName="JTK_TITOLARI"
                   ID="JTK_TITOLARI"
                   serverType="sql"
                   requiresAuthentication="true"
                   dropExtraFields="false"
       >
           <fields>
               <field primaryKey="true" name="ID_REC" type="sequence" hidden="true" sequenceName="SEQUENCE_ID_JTK"/>
               <field sqlType="varchar" sqlLength="200" name="NOME" length="200" type="upperTrimText" required="false"></field>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_POSTI_FILE_SETTORI_BASE"
                   tableName="JPC_POSTI_FILE_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   dropExtraFields="false"
                   requiresAuthentication="true"
       >
           <fields>
               <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
                   <sequenceName>SEQUENCE_ID_POSTI_FILE_SETTORI</sequenceName>
               </field>
               <field name="NUMERO_POSTO" type="text"/>
               <field foreignKey="JPC_FILE_SETTORI.ID_REC" name="ID_FILE_SETTORI_FK" relatedTableAlias="FILE_" joinType="outer" type="integer"/>
               <field foreignKey="JPC_LISTINI.ID_REC" name="ID_LISTINI_FK" type="integer" tableName="JPC_SETTORI"/>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_POSTI_FILE_SETTORI"
                   tableName="JPC_POSTI_FILE_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   dropExtraFields="false"
                   inheritsFrom="JPC_POSTI_FILE_SETTORI_BASE"
                   requiresAuthentication="true"
       >
           <fields>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_FILE_SETTORI_BASE"
                   tableName="JPC_FILE_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   requiresAuthentication="true"
       >
           <fields>
               <field primaryKey="true" name="ID_REC" type="sequence" hidden="true">
                   <sequenceName>SEQUENCE_ID_REC</sequenceName>
               </field>                                                                                                                                                
               <field foreignKey="JPC_SETTORI.ID_REC" name="ID_SETTORI_FK" type="integer" relatedTableAlias="SETTORI" joinType="outer"/>
               <field name="CODICE_SETTORE" type="integer" includeFrom="JPC_SETTORI.CODICE"/>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_FILE_SETTORI"
                   tableName="JPC_FILE_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   inheritsFrom="JPC_FILE_SETTORI_BASE"
                   requiresAuthentication="true"
       >
           <fields>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_SETTORI_BASE"
                   tableName="JPC_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   requiresAuthentication="true"
       >
           <fields>
               <field primaryKey="true" name="ID_REC" type="sequence" hidden="true">
                   <sequenceName>SEQUENCE_ID_REC</sequenceName>
               </field>
               <field sqlLength="20" name="CODICE" length="20" type="text" updateRequires="false"/>

               <field foreignKey="JPC_LISTINI.ID_REC" name="ID_LISTINI_FK" type="integer" hidden="true"></field>
           </fields>
       </DataSource>

       <DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
                   ID="JPC_SETTORI"
                   tableName="JPC_SETTORI"
                   schema="DBSALES"
                   dbName="dbJpcEP"
                   serverType="sql"
                   inheritsFrom="JPC_SETTORI_BASE"
                   requiresAuthentication="true"
       >
       </DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JPC_LISTINI_BASE"
            tableName="JPC_LISTINI"
            schema="DBSALES"
            dbName="dbJpcEP"
            serverType="sql"
            requiresAuthentication="true"
>
    <fields>
        <field primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_ID_REC</sequenceName>
        </field>
        <field name="DATA_INIZIO" type="datetime" required="true" />
        <field foreignKey="JPC_STAGIONI.ID_REC" name="ID_STAGIONI_FK" type="integer" hidden="true" />
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JPC_LISTINI"
            tableName="JPC_LISTINI"
            schema="DBSALES"
            dbName="dbJpcEP"
            serverType="sql"
            inheritsFrom="JPC_LISTINI_BASE"
            requiresAuthentication="true"
>
    <fields>
        <field name="DATA_INIZIO_STAGIONE" type="date"
               includeFrom="JPC_STAGIONI.DATA_INIZIO"/>
     </fields>

</DataSource>

With the older build, JTK_OMAGGI_TITOLARI.fetchData() produces this (working) query:

Code:

SELECT JTK_POSTI_OMAGGIO.ID_REC,
       JTK_POSTI_OMAGGIO.ID_TITOLARE_FK,
       JTK_POSTI_OMAGGIO.ID_POSTO_FK,
       TITOLARI.NOME,
       SETTORI.ID_LISTINI_FK   AS ID_LISTINO_FK,
       JPC_LISTINI.DATA_INIZIO AS DATA_MATCH
FROM DBSALES.JTK_TITOLARI TITOLARI,
     DBSALES.JPC_SETTORI SETTORI,
     DBSALES.JPC_FILE_SETTORI FILE_,
     DBSALES.JTK_POSTI_OMAGGIO,
     DBSALES.JPC_POSTI_FILE_SETTORI POSTI,
     DBSALES.JPC_LISTINI
WHERE ('1' = '1')
  AND JTK_POSTI_OMAGGIO.ID_TITOLARE_FK = TITOLARI.ID_REC
  AND JTK_POSTI_OMAGGIO.ID_POSTO_FK = POSTI.ID_REC
  AND POSTI.ID_FILE_SETTORI_FK = FILE_.ID_REC(+)
  AND FILE_.ID_SETTORI_FK = SETTORI.ID_REC(+)
  AND SETTORI.ID_LISTINI_FK = JPC_LISTINI.ID_REC

while with the newer builds:

Code:

SELECT JTK_POSTI_OMAGGIO.ID_REC,
       JTK_POSTI_OMAGGIO.ID_TITOLARE_FK,
       JTK_POSTI_OMAGGIO.ID_POSTO_FK,
       TITOLARI.NOME,
       POSTI_FILE__SETTORI.ID_LISTINI_FK AS ID_LISTINO_FK,
       a0.DATA_INIZIO                    AS DATA_MATCH,
       POSTI_ID_LISTINI_FK.ID_STAGIONI_FK
FROM DBSALES.JPC_LISTINI a0,
     DBSALES.JTK_TITOLARI TITOLARI,
     DBSALES.JPC_SETTORI POSTI_FILE__SETTORI,
     DBSALES.JPC_FILE_SETTORI POSTI_FILE_,
     DBSALES.JTK_POSTI_OMAGGIO,
     DBSALES.JPC_POSTI_FILE_SETTORI POSTI,
     DBSALES.JPC_LISTINI POSTI_ID_LISTINI_FK
WHERE ('1' = '1')
  AND JTK_POSTI_OMAGGIO.ID_TITOLARE_FK = TITOLARI.ID_REC
  AND JTK_POSTI_OMAGGIO.ID_POSTO_FK = POSTI.ID_REC
  AND POSTI.ID_FILE_SETTORI_FK = POSTI_FILE_.ID_REC(+)
  AND POSTI_FILE_.ID_SETTORI_FK = POSTI_FILE__SETTORI.ID_REC(+)
  AND POSTI_FILE__SETTORI.ID_LISTINI_FK = a0.ID_REC
  AND POSTI.ID_LISTINI_FK = POSTI_ID_LISTINI_FK.ID_REC

Tags: None

Isomorphic

Administrator

Join Date: May 2006

Posts: 54650
#2

20 Jan 2020, 05:25

First, datasource JTK_OMAGGI_TITOLARI has an extra field in "newer" query ID_STAGIONI_FK, which it does not have in the "working" query. So, queries are produced in different setups. Putting that aside in "newer" query JPC_LISTINI table is joined multiple times, since fields DATA_MATCH and ID_STAGIONI_FK include fields from it via different relation paths.

Second, if any inconsistency is involved it is in the "'working" query with the older build, cause JPC_LISTINI is clearly joined with some relatedTableAlias'es involved, so it shouldn't appear in query without an alias (unless there are more differences in "working" setup). We did make same changes in this area recently and "newer" query behaves as expected, i.e. generated aliases are exactly as they are expected to be, including a0 alias, which replaces long (exceeds 30 symbols) alias POSTI_FILE__SETTORI_ID_LISTINI_FK.

You may find useful to take another look at these resources:
- DSField.incluceVia docs;
- DSField.relatedTableAlias docs;
- your other thread we had an explanation how aliases are generated.
Comment

Previous template Next

Announcement

bug with multiple indirection

Comment