12.0p "outputs" on .ds.xml field or in clientside DSRequest affects SQL generation more than expected

[Blama]

Hi Isomorphic,

I'm trying to use a ListGrid summary row of a ListGrid with many joins and serverside added security WHERE-clauses with setSummaryRowDataSource(), as the ListGrid is very big and I always want the summary row.
I only need summaries for some fields, so I'm currently playing with outputs:

Specifies, for this operationBinding only, the list of field names that should be returned to the client. Typically this will be a subset of the DataSource.fields, but note that this is not a requirement; outputs can include fields that are not defined in the DataSource's field list. In this case, the server will return extra fields even if DataSource.dropExtraFields is true.

You specify this property as a string containing a comma-separated list of field names (eg, "foo, bar, baz")

Here I'd expect that this will affect the fields after SELECT in the SQL, but it also seems to affect WHERE clauses, as now conditions as generated for tablename.null, which obviously can't work (is tablename.fieldname as expected without the outputs). I remember having a similar issue before and will look up the thread (I have a feeling that issue had to do with not-generated joins).
This also is true if you set the outputs clientside with setOutputs().

To me this seems wrong and not matching the description that this is happening. If you agree I can try to create a testcase if needed.

Best regards
Blama

[Isomorphic]

We would agree that outputs should not affect what you can pass as criteria for a where clause, so if you think there's a bug here, we'd definitely like to see a test case.

[Blama]

Hi Isomorphic,

this was my old unfinished thread with most likely the same problem.

Please see this testcase (v12.0p_2021-01-29).
The two last buttons result in the mentioned problem. The 3rd button has a nice improvement of not generating the (unneeded) join to Employees. I assume this is a feature and should stay this way.

Best regards
Blama

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.DSRequest;
import com.smartgwt.client.data.DataSource;
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.form.fields.SelectItem;
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 ListGrid animalsGrid;
    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("\"outputs\" problem with Criteria" + w.getTitle());
        w.setShowMinimizeButton(false);
        w.setIsModal(true);
        w.setShowModalMask(true);
        w.centerInPage();
        animalsGrid = new MyListGrid();

        final VLayout vLayout = new VLayout(5);

        IButton req0Btn = new IButton("fetchData(), without serverside criteria, no serverside outputs (OK, with join)", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                vLayout.removeMember(animalsGrid);
                animalsGrid = new MyListGrid();
                vLayout.addMember(animalsGrid, 0);
                animalsGrid.fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("normalFetch");
                    }
                });
            }
        });
        req0Btn.setWidth(600);

        IButton req1Btn = new IButton("fetchData(), with serverside criteria, no serverside outputs (OK, with join)", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                vLayout.removeMember(animalsGrid);
                animalsGrid = new MyListGrid();
                vLayout.addMember(animalsGrid, 0);
                animalsGrid.fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("criteriaFetch");
                    }
                });
            }
        });
        req1Btn.setWidth(600);

        IButton req2Btn = new IButton(
                "fetchData() with outputs, without serverside criteria, no serverside outputs (OK, no join (performance improvement))",
                new ClickHandler() {
                    @Override
                    public void onClick(ClickEvent event) {
                        vLayout.removeMember(animalsGrid);
                        animalsGrid = new MyListGrid();
                        vLayout.addMember(animalsGrid, 0);
                        animalsGrid.fetchData(null, null, new DSRequest() {
                            {
                                setOperationId("normalFetch");
                                setOutputs("scientificName, commonName");
                            }
                        });
                    }
                });
        req2Btn.setWidth(600);

        IButton req3Btn = new IButton("fetchData() with outputs, with serverside criteria, no serverside outputs (broken)", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                vLayout.removeMember(animalsGrid);
                animalsGrid = new MyListGrid();
                vLayout.addMember(animalsGrid, 0);
                animalsGrid.fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("criteriaFetch");
                        setOutputs("scientificName, commonName");
                    }
                });
            }
        });
        req3Btn.setWidth(600);

        IButton req4Btn = new IButton("fetchData(), with serverside criteria, with serverside outputs (broken)", new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                vLayout.removeMember(animalsGrid);
                animalsGrid = new MyListGrid();
                vLayout.addMember(animalsGrid, 0);
                animalsGrid.fetchData(null, null, new DSRequest() {
                    {
                        setOperationId("outputsCriteriaFetch");
                    }
                });
            }
        });
        req4Btn.setWidth(600);

        vLayout.addMembers(animalsGrid, req0Btn, req1Btn, req2Btn, req3Btn, req4Btn);
        w.addItem(vLayout);
        w.show();
    }

    private class MyListGrid extends ListGrid {
        public MyListGrid() {
            setDataSource(DataSource.get("animals"));
            setHeight100();
            setAutoFetchData(false);
            setCanEdit(true);
            setCanRemoveRecords(true);

            ListGridField scientificName = new ListGridField("scientificName");
            ListGridField commonName = new ListGridField("commonName");
            ListGridField diet = new ListGridField("diet");
            ListGridField information = new ListGridField("information");
            information.setHidden(true);
            ListGridField status = new ListGridField("status");
            ListGridField lifeSpan = new ListGridField("lifeSpan");
            lifeSpan.setEditorProperties(new SelectItem() {
                {
                    ListGridField employeeID = new ListGridField("EmployeeId");
                    ListGridField employeeName = new ListGridField("Name");
                    setAllowEmptyValue(true);
                    setPickListFields(employeeID, employeeName);
                    setSortField("Name");
                }
            });
            ListGridField employeeEmployeeId = new ListGridField("employeeEmployeeId");
            ListGridField employeeName = new ListGridField("employeeName");

            setFields(scientificName, commonName, diet, information, status, lifeSpan, employeeEmployeeId, employeeName);
        }
    }
}

Animals.java:

package com.smartgwt.sample.server.listener;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.criteria.AdvancedCriteria;
import com.isomorphic.criteria.Criterion;
import com.isomorphic.criteria.DefaultOperators;
import com.isomorphic.criteria.criterion.IsNullCriterion;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Animals {
    public DSResponse criteriaFetch(DSRequest request, HttpServletRequest servletRequest) throws Exception {
        request.addToCriteria(new AdvancedCriteria(DefaultOperators.Or,
                new Criterion[] { new IsNullCriterion("employeeName"), new IsNullCriterion("employeeEmployeeType") }));
        return request.execute();
    }

    public DSResponse normalFetch(DSRequest request, HttpServletRequest servletRequest) throws Exception {
        return request.execute();
    }
}

Animals.ds.xml:

<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="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" customSQL="true" />
        <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 foreignKey="employees.EmployeeId" name="lifeSpan" title="Owner" type="integer" joinType="outer" displayField="employeeName" relatedTableAlias="joinEmp" />
        <field name="employeeName"            includeFrom="employees.Name" title="employeeName-iF" />
        <field name="employeeJob"             includeFrom="employees.Job" />
        <field name="employeeEmail"           includeFrom="employees.Email" />
        <field name="employeeEmployeeType"    includeFrom="employees.EmployeeType" />
        <field name="employeeEmployeeStatus"  includeFrom="employees.EmployeeStatus" />
    </fields>
    <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.Animals" />
    <operationBindings>
        <operationBinding operationType="fetch" operationId="outputsCriteriaFetch" serverMethod="criteriaFetch"
            progressiveLoading="true" outputs="scientificName, commonName, status">
            <customFields>diet</customFields>
            <whereClause>(1 != 2) AND ($defaultWhereClause)</whereClause>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="criteriaFetch" serverMethod="criteriaFetch"
            progressiveLoading="true">
            <whereClause>(1 != 2) AND ($defaultWhereClause)</whereClause>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="normalFetch" serverMethod="normalFetch"
            progressiveLoading="true">
            <whereClause>(1 != 2) AND ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>
</DataSource>

[Blama]

The broken queries for button 4 and 5 are:

[B]Button 4:[/B]
SELECT
LIMIT 0 76 animals.commonName, animals.scientificName
FROM animals
WHERE (1 != 2)
  AND ((((animals.[B]null [/B]IS NULL)
   OR (animals.[B]null [/B]IS NULL))))

[B]Button 5:[/B]
SELECT
LIMIT 0 76 animals.commonName, animals.scientificName, animals.status
FROM animals
WHERE (1 != 2) AND ((((animals.[B]null [/B]IS NULL) OR (animals.[B]null [/B]IS NULL))))

[Isomorphic]

This is fixed and available for download in nightly builds since March 30.

[Blama]

Hi Isomorphic,

this is fixed for me in sample using v12.0p_2021-03-31, thanks a lot.
I don't need this right now in my own code as I've worked around it with <selectClause>, but it's good to know that this is working.

Best regards
Blama