11.1p: Severe issue with generated delete statement targeting "NULL" as column when tables are connected via foreignKey

[Blama]

Hi Isomorphic,

I noticed today that my application issues invalid syntax delete statements when tables are connected via foreignKey. This has to have been introduced just recently (can't exactly tell, but it is not happening with v11.1p_2018-01-18, which is what I have deployed currently). I assume it is related to the changes in this post of yours, which would fit from the date.

Please see this BuiltInDS-based sample (tested with v11.1p_2018-01-29):
BuiltInDS.java:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.Version;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.SortSpecifier;
import com.smartgwt.client.types.SortDirection;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Window;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
    private VLayout mainLayout;
    private IButton recreateBtn;

    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");

        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        mainLayout = new VLayout(20);
        mainLayout.setWidth100();
        mainLayout.setHeight100();

        recreateBtn = new IButton("Recreate");
        recreateBtn.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                recreate();
            }
        });
        mainLayout.addMember(recreateBtn);
        recreate();
        mainLayout.draw();
    }

    private void recreate() {
        Window w = new Window();
        w.setWidth("95%");
        w.setHeight("95%");
        w.setMembersMargin(0);
        w.setModalMaskOpacity(70);
        w.setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
        w.setTitle("TITLE" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();

        final ListGrid animalsGrid = new ListGrid(DataSource.get("animals"));

        animalsGrid.setHeight100();
        animalsGrid.setAutoFetchData(false);
        animalsGrid.setCanEdit(true);
        animalsGrid.setCanRemoveRecords(true);

        ListGridField scientificName = new ListGridField("scientificName");
        scientificName.setCanEdit(false);
        ListGridField commonName = new ListGridField("commonName");
        ListGridField lifeSpan = new ListGridField("lifeSpan");
        ListGridField status = new ListGridField("status");
        ListGridField incName = new ListGridField("incName");

        animalsGrid.setFields(scientificName, commonName, lifeSpan, status, incName);
        animalsGrid.setSort(new SortSpecifier[] { new SortSpecifier(scientificName.getName(), SortDirection.ASCENDING) });
        animalsGrid.fetchData();
        w.addItem(animalsGrid);
        w.show();
    }
}

animals.ds.xml (minor changes, FK added):

<DataSource
    ID="animals"
    serverType="sql"
    tableName="animals"
    testFileName="animals.data.xml" useAnsiJoins="true" 
>
    <fields>
        <field name="commonName"      title="Animal"             type="text"/>
        <field name="scientificName"  title="Scientific Name"    type="text"  primaryKey="true"  required="true"/>
        <field name="lifeSpan"        title="Life Span"          type="integer"/>
        <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/"/>
        <field name="incName" includeFrom="employees.Name" />
        <field foreignKey="employees.EmployeeId" name="lifeSpan" title="Life Span" type="integer" joinType="outer" />
    </fields>
</DataSource>

Now if you try to delete one entry, it works. Generated SQL is:

DELETE FROM animals WHERE (animals.commonName='Red Legged Tarantula' AND animals.lifeSpan=20 AND animals.scientificName='Brachypelma smithi' AND animals.information='Tarantulas shed their skin about once a month as hatchlings and will slow down to about once every year or two as adults. Their number one enemy is the Spider Hunting Wasp.' AND animals.diet='Insects, small reptiles and mammals' AND animals.picture='Tarantula.jpg' AND animals.status='Not Endangered')

Next, change the lifespan of one entry to "4". In the last column "Charles Madigen" appears. If you try to delete this row, the SQL and error is:

DELETE FROM animals WHERE (animals.commonName='Howler Monkey' [B]AND animals.null='Charles Madigen'[/B] AND animals.lifeSpan=4 AND animals.scientificName='Alouatta spp.' AND animals.information=' These animals produce a low growling sound that has been compared to the roar of a lion. These calls can be heard up to 3 km through the forest and are used to communicate danger as well as keeping the troop together.' AND animals.diet='Herbivore' AND animals.picture='HowlerMonkey.jpg' AND animals.status='Endangered')

user lacks privilege or object not found: ANIMALS.NULL

Best regards
Blama

[Isomorphic]

You are right, this is an indirect consequence of the changes made in the post you're mentioning. Related changes are reverted and will be available for download in nightly builds since Feb 2 (tomorrow). Sorry for the inconvenience.

[Blama]

Hi Isomorphic,

I forgot to retest this, as it was working in my application again. Using v11.1p_2018-04-07 this is behaving again as expected.

Best regards
Blama