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:
    Code:
    [URL]http://127.0.0.1:8888/builtinds/sc/RESTHandler/animals/fetch/fetch[/URL]
    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


          #5
          Hi Isomorphic,

          do you have an update on this one?

          Best regards
          Blama

          Comment


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

            Comment


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

              Comment


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

                Comment


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

                  Comment

                  Working...
                  X