12.0p Join generation change between March version and current nightly

[Blama]

Hi Isomorphic,

comparing 12.0p_2019-03-23 and 12.0p_2019-10-23 we noticed that there is a change in join generation where I need to know which one is correct, because the use itself clearly is OK.
Please see this modified BuiltInDS testcase:

animals.ds.xml:

<DataSource
    ID="animals"
    serverType="sql"
    tableName="animals"
    testFileName="animals.data.xml"
>
    <fields>
        <field name="commonName"      title="Animal"             type="text"/>
        <field name="scientificName"  title="Scientific Name"    type="text"  primaryKey="true"  required="true" customSelectExpression="animals.scientificName" />
        <field name="lifeSpan"        title="Life Span"          type="integer" foreignKey="employees.EmployeeId" customSelectExpression="animals.lifeSpan" joinType="outer"/>
        <field includeFrom="employees.Name" />
        <field name="status"          title="Endangered Status"  type="text">
            <valueMap>
                <value>Threatened</value>
                <value>Endangered</value>
                <value>Not Endangered</value>
                <value>Not currently listed</value>
                <value>May become threatened</value>
                <value>Protected</value>
            </valueMap>
        </field>
        <field name="diet"            title="Diet"               type="text"/>
        <field name="information"     title="Interesting Facts"  type="text"  length="1000"/>
        <field name="picture"         title="Picture"            type="image" detail="true"
               imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch">
            <tableClause>animals animals</tableClause>
        </operationBinding>
    </operationBindings>
</DataSource>

server.properties addition:

sql.useAnsiJoins: true

URL to call in SuperDevMode in order to issue fetch:

[URL]http://127.0.0.1:8888/builtinds/sc/RESTHandler/animals/fetch/fetch[/URL]

Resulting SQL using 12.0p_2019-03-23 (OK):

SELECT animals.commonName,
       animals.scientificName AS scientificName,
       animals.lifeSpan       AS lifeSpan,
       animals.status,
       animals.diet,
       animals.information,
       animals.picture,
       employeeTable.Name
FROM animals animals
         LEFT OUTER JOIN employeeTable ON animals.lifeSpan = employeeTable.EmployeeId
WHERE ('1' = '1');

Resulting SQL using 12.0p_2019-10-23 (invalid SQL):

SELECT animals.commonName,
       animals.scientificName AS scientificName,
       animals.lifeSpan       AS lifeSpan,
       animals.status,
       animals.diet,
       animals.information,
       animals.picture,
       employeeTable.Name
FROM animals animals
WHERE ('1' = '1')

I do like the old behavior better, but you might consider your change a bugfix (because I entered tableClause and there is no such thing as "joinClause").

Best regards
Blama

[Blama]

Hi Isomorphic,

do you have an answer on this?

Best regards
Blama

[Blama]

Hi Isomorphic,

any answer on this one? It's important for us, so that I know if I need to rewrite code/views where we have this situation.

Thank you & Best regards
Blama

[Isomorphic]

Apologies for the silence, we are working on this and will report soon. Good news are that this is considered a regression, so the "old way" will be back. Additionally new clause will be documented to allow control over ansi joins.

[Blama]

Hi Isomorphic,

do you have an update on this one?

Best regards
Blama

[Isomorphic]

Once again apologies for the delay. This is fixed now and will be available for download in nightly builds since Dec 5 (today).

As mentioned before the "old" way is back, so your tableClause will work as expected. Additionally there is new ansiJoinClause in SQL templating, which by default is separated from tableClause, but if you would want them together, set new flag OperationBinding.includeAnsiJoinsInTableClause=true.

[claudiobosticco]

Hello Isomorphic, is it possible to set includeAnsiJoinsInTableClause globally in the server.properties ? I fear that it could break some of my SQL templating.

[Blama]

Hi Isomorphic,

just as an FYI: It seems that the join generation is working as expected using current v12.0p_2019-12-14.

Best regards
Blama

[Isomorphic]

Hello claudiobosticco, global setting is added, see in includeAnsiJoinsInTableClause docs.