Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    12.0p Join generation change between March version and current nightly

    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:
    Code:
    <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:
    Code:
    sql.useAnsiJoins: true
    URL to call in SuperDevMode in order to issue fetch:
    Resulting SQL using 12.0p_2019-03-23 (OK):
    Code:
    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):
    Code:
    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

    #2
    Hi Isomorphic,

    do you have an answer on this?

    Best regards
    Blama

    Comment


      #3
      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

      Comment


        #4
        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.

        Comment

        Working...
        X