bug/question about includeVia

[claudiobosticco]

SmartClient Version: SNAPSHOT_v12.1d_2019-12-17/Enterprise Deployment (built 2019-12-17)

Hello, I've got a test case where, if I don't specify the includeVia attribute, the join is not correct. To me the includeVia doesn't seem necessary, so it may be a bug.

Here are the simplified dataSources:

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_POSTI_PER_CLUB_PRELAZIONE_EVENTI_FO"
            tableName="JOFC_POSTI_PER_CLUB"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
            inheritsFrom="JOFC_POSTI_PER_CLUB_BASE"
            dropExtraFields="false"
>
    <fields>
        <field name="NAZIONE_UTILIZZATORE" type="text" includeFrom="JOFC_SOCI.NAZIONE"/>

        <field name="NAZIONE_INTESTATARIO" type="text" includeFrom="JOFC_ABBONAMENTI.JOFC_SOCI.NAZIONE"
               includeVia="ID_ABBONAMENTO_FK.ID_SOCIO_FK.ID_NAZIONE_NASCITA_FK"
        />
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_POSTI_PER_CLUB_BASE"
            tableName="JOFC_POSTI_PER_CLUB"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
>
    <fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_ID_JOFC</sequenceName>
        </field>

        <field name="ID_SOCIO_FK" type="integer" foreignKey="JOFC_SOCI.ID_REC"/>
        <field name="ID_ABBONAMENTO_FK" type="integer" foreignKey="JOFC_ABBONAMENTI.ID_REC" joinType="outer"/>
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_SOCI"
            tableName="JOFC_SOCI"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
            inheritsFrom="JOFC_SOCI_BASE"
            dropExtraFields="false"
>

    <fields>
        <field name="NAZIONE" type="text" includeFrom="JPC_NAZIONI.NOME"/>
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_SOCI_BASE"
            tableName="JOFC_SOCI"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
>
    <fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_ID_JOFC</sequenceName>
        </field>
        <field name="ID_NAZIONE_NASCITA_FK" type="integer" foreignKey="JPC_NAZIONI.ID_REC" relatedTableAlias="NAZION" displayField="NAZIONE"
               joinType="outer"/>
    </fields>
</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_ABBONAMENTI"
            tableName="JOFC_ABBONAMENTI"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
            inheritsFrom="JOFC_ABBONAMENTI_BASE"
>

</DataSource>

<DataSource xmlns:fmt="WEB-INF/" xmlns="http://www.smartclient.com/schema"
            ID="JOFC_ABBONAMENTI_BASE"
            tableName="JOFC_ABBONAMENTI"
            schema="DBJOFC"
            dbName="DBJOFC"
            serverType="sql"
            requiresAuthentication="true"
>
    <fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_ID_JOFC</sequenceName>
        </field>

        <field name="ID_SOCIO_FK" type="integer" foreignKey="JOFC_SOCI.ID_REC" relatedTableAlias="SOCI"/>
    </fields>
</DataSource>

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

The includeVia on the NAZIONE_INTESTARIO field in the JOFC_POSTI_PER_CLUB_PRELAZIONE_EVENTI_FO dataSource doesn't seem necessary, but without it the generated query (for JOFC_POSTI_PER_CLUB_PRELAZIONE_EVENTI_FO.fetchData()) is:

SELECT JOFC_POSTI_PER_CLUB.ID_REC,
       JOFC_POSTI_PER_CLUB.ID_SOCIO_FK,
       JOFC_POSTI_PER_CLUB.ID_ABBONAMENTO_FK,
       NAZION.NOME      AS NAZIONE_UTILIZZATORE,
       SOCI_NAZION.NOME AS NAZIONE_INTESTATARIO
FROM DBJOFC.JOFC_POSTI_PER_CLUB
         JOIN DBJOFC.JOFC_SOCI ON JOFC_POSTI_PER_CLUB.ID_SOCIO_FK = JOFC_SOCI.ID_REC
         LEFT OUTER JOIN DBSALES.JPC_NAZIONI NAZION ON JOFC_SOCI.ID_NAZIONE_NASCITA_FK = NAZION.ID_REC
         LEFT OUTER JOIN DBJOFC.JOFC_ABBONAMENTI ON JOFC_POSTI_PER_CLUB.ID_ABBONAMENTO_FK = JOFC_ABBONAMENTI.ID_REC
         JOIN DBJOFC.JOFC_SOCI SOCI ON JOFC_ABBONAMENTI.ID_SOCIO_FK = SOCI.ID_REC
         LEFT OUTER JOIN DBSALES.JPC_NAZIONI SOCI_NAZION ON SOCI.ID_NAZIONE_NASCITA_FK = SOCI_NAZION.ID_REC
WHERE ('1' = '1' AND '1' = '1')

while with the includeVia the query is correctly:

SELECT JOFC_POSTI_PER_CLUB.ID_REC,
       JOFC_POSTI_PER_CLUB.ID_SOCIO_FK,
       JOFC_POSTI_PER_CLUB.ID_ABBONAMENTO_FK,
       NAZION.NOME                        AS NAZIONE_UTILIZZATORE,
       ID_ABBONAMENTO_FK_SOCI_NAZION.NOME AS NAZIONE_INTESTATARIO
FROM DBJOFC.JOFC_POSTI_PER_CLUB
         JOIN DBJOFC.JOFC_SOCI ON JOFC_POSTI_PER_CLUB.ID_SOCIO_FK = JOFC_SOCI.ID_REC
         LEFT OUTER JOIN DBSALES.JPC_NAZIONI NAZION ON JOFC_SOCI.ID_NAZIONE_NASCITA_FK = NAZION.ID_REC
         LEFT OUTER JOIN DBJOFC.JOFC_ABBONAMENTI ID_ABBONAMENTO_FK ON JOFC_POSTI_PER_CLUB.ID_ABBONAMENTO_FK = ID_ABBONAMENTO_FK.ID_REC
         JOIN DBJOFC.JOFC_SOCI ID_ABBONAMENTO_FK_SOCI ON ID_ABBONAMENTO_FK.ID_SOCIO_FK = ID_ABBONAMENTO_FK_SOCI.ID_REC
         LEFT OUTER JOIN DBSALES.JPC_NAZIONI ID_ABBONAMENTO_FK_SOCI_NAZION
                         ON ID_ABBONAMENTO_FK_SOCI.ID_NAZIONE_NASCITA_FK = ID_ABBONAMENTO_FK_SOCI_NAZION.ID_REC
WHERE ('1' = '1' AND '1' = '1')

[claudiobosticco]

Hello, did you succeed in replicating this?

[Isomorphic]

This was successfully reproduced on our end, thanks to your detailed report. We will follow up shortly on the subject.

[Isomorphic]

You've got expected behaviour in both cases. With includeVia involved generated alias contains full path as expected according to its docs. In the absence of includeVia, but with relatedTableAlias involved, we generate alias by concatenating relatedTableAlias and FK field names starting with first relatedTableAlias found in chain of relations leading to the target table. We are doing the best we can, to clarify:

1. with no relatedTableAlias - we always use just target datasource tableName;
2. with just relatedTableAlias on FK field to target datasource - use that;
3. with relatedTableAlias earlier in relation chain we start to concatenate relatedTableAlias or FK fIeld names starting with first relatedTableAlias until we reach target datasource.

#2 and #3 may seem questionable, but this way it feels more natural in simple setups, like for example in your other post you expect table alias to be just "AWAY" as opposed to "ID_LISTINO_FK_AWAY".

If you want to enforce that generated alias would include full path to target datasource, you may use includeVia as you already discovered. Or you could add relatedTableAlias to the first FK in relation chain in which case we will start generating alias right away. In your setup this would mean adding relatedTableAlias="ALIAS" to JOFC_POSTI_PER_CLUB_BASE.ID_ABBONAMENTO_FK field causing generated alias to be "ALIAS_SOCI_NAZION".