SQLDataSource - strange behaviour where a field is ignored?

[claudiobosticco]

SmartClient Version: v11.1p_2019-12-02/Enterprise Deployment (built 2019-12-02)


Hello, I just noticed a strange behaviour in a SQLDataSource, where In my actual use case it seems that I forgot to add a join for the default fetch, which seems required for a field, but it does work indeed, as the field seems to be ignored.
It is like that in my application since a long time.

I've simplified it for a test case:

<DataSource xmlns="http://www.smartclient.com/schema"
            ID="IMA_ABBONAMENTI"
            tableName="IMA_ABBONAMENTI"
            schema="DBSALES"
            dbName="dbJpcEP"
            serverType="sql"
            dropExtraFields="false"
        >
    <fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true"
               sequenceName="SEQUENCE_ID_REC"/>
        <field name="ID_STAGIONE_FK" type="integer" foreignKey="JPC_STAGIONI.ID_REC"/>
        <field name="DATA_INIZIO_STAGIONE" nativeName="DATA_INIZIO" type="date" tableName="JPC_STAGIONI"/>
    </fields>
....

When I execute IMA_ABBONAMENTI.fetchData() the generated query doesn't contain JPC_STAGIONI.DATA_INIZIO:

SELECT IMA_ABBONAMENTI.ID_REC, 
       IMA_ABBONAMENTI.ID_STAGIONE_FK
FROM DBSALES.IMA_ABBONAMENTI
WHERE (IMA_ABBONAMENTI.ID_STAGIONE_FK = 4485361)

so DATA_INIZIO_STAGIONE seems to be ignored, which I find strange, but as it's working I really haven't noticed it...until I've added an operationBinding:

        <operationBinding operationType="fetch">
            <whereClause>$defaultWhereClause</whereClause>
        </operationBinding>

that seems to be sufficient to trigger an error, as now the generated query is:

SELECT IMA_ABBONAMENTI.ID_REC, 
       IMA_ABBONAMENTI.ID_STAGIONE_FK, 
       JPC_STAGIONI.DATA_INIZIO AS DATA_INIZIO_STAGIONE
FROM DBSALES.IMA_ABBONAMENTI
WHERE (IMA_ABBONAMENTI.ID_STAGIONE_FK = 4485361)

which leads to:

java.sql.SQLSyntaxErrorException: ORA-00904: "JPC_STAGIONI"."DATA_INIZIO": identificativo non valido

Why is there a different behaviour? Which is correct?

[Isomorphic]

In order to have a field with tableName defined to be included in fetch operation, you must join that table via SQL templating, like it is documented. Behaviour is expected in both cases described:
- if no SQL templating is involved, including field with tableName would lead to wrong SQL, so we ignore it.
- if there is SQL templating involved, we assume that table is joined manually and include it in SQL query. In your case it results in wrong SQL, cause JPC_STAGIONI table is not properly joined.

[claudiobosticco]

I didn't recall that fields with tableName are joined only in customized fetches, thanks for the heads up!