SQLDataSource ansi joins and $defaultTableClause

[claudiobosticco]

SmartClient Version: SNAPSHOT_v12.1d_2019-07-20/Enterprise Deployment (built 2019-07-20)

Hello, I've just noticed this behaviour. In a dataSource based on table="A", which includes a field from table B:

    <field name="foo" includeFrom="B.foo"/>

where I include another field from a table C without using the includeFrom attribute:
<field name="bar" tableName="C"/>
and the join with C is written in the tableClause:

<tableClause>
    $defaultTableClause                
    JOIN C ON B.ID = C.ID
</tableClause>

I obtain a query formed like this:

FROM
A
JOIN C ON B.ID = C.ID
JOIN B ON A.ID = B.ID

which clearly doesn't work, while I was expecting this:

FROM
A
JOIN B ON A.ID = B.ID
JOIN C ON B.ID = C.ID

because I thought that the $defaultTableClause would contain the automatic ANSI joins.

Actually I could use the includeFrom also for the 'bar' field, but in the actual use case I have a condition for the join, which is something I can't express with the includeFrom attribute.

So I'm asking if it's a bug or a known limitation, as I don't see a note in the documentation against mixing includeFrom and explicit ANSI joins.

[claudiobosticco]

Hello, actually I've found another use case where it seems problematic to have in the same dataSource some 'includeFrom' fields and a field declared with a customSelectExpression.

The customSelectExpression is defined as COALESCE(table_A.foo, table_B.bar, table_C.foobar), and this same field is a foreignKey used to join a table_D via includeFrom. So the COALESCE() is used in the join condition.

Also table_B and table_C are joined via includeFrom.
The table_B join is written before the join using the COALESCE function, but the table_C join is written after, so it raises an error. because table_C.foobar is not a valid identifier (ORA-00904).

[Isomorphic]

The issue from #1 is fixed and available for download in nightly builds since Aug 10 (today).

Regarding #2, please check if it is still an issue after the fix above and let us know. Also, if issue will still be present please describe it with more details and a sample.

[Isomorphic]

It's come to our attention that sometimes the most useful behavior is to include an ANSI join in the table clause, and sometimes not - certain queries become more difficult, or have to be entirely hand-coded, depending on which approach you take. So we've made it switchable - to get the behavior you want when you are configured to use ANSI joins, you now need to set includeAnsiJoinsInTableClause to "true" on your operationBinding.