Bug: If length of the criteria field>4000

[pavo123]

Hi Isomorphic,

please take a look at this test case.
If I use a field with length greater than 4000, in criteria, then I have a query like this:

SELECT COUNT(*) FROM T_TEST WHERE ('1'='1' OR '1'='1')

In my test case it happens when I click on the button "Fetch data content".

When I click on button "Fetch data subject", it works as it should:

SELECT COUNT(*) FROM T_TEST WHERE ((LOWER(T_TEST.SUBJECT) LIKE LOWER('%mail%') ESCAPE'\'  AND T_TEST.SUBJECT IS NOT NULL) OR (LOWER(T_TEST.SUBJECT) LIKE LOWER('%4%') ESCAPE'\'  AND T_TEST.SUBJECT IS NOT NULL))

Oracle script

CREATE TABLE t_test
    (
     id INTEGER  NOT NULL ,
     mail_subject VARCHAR2 (100 CHAR) ,
     mail_content VARCHAR2 (100 CHAR) ,
     modified_by INTEGER  NOT NULL ,
     modified_at DATE DEFAULT SYSDATE  NOT NULL
    )
;

ALTER TABLE t_test
    ADD CONSTRAINT PK_test PRIMARY KEY ( id ) ;

CREATE SEQUENCE T_TEST_ID
    NOCACHE
    ORDER ;

CREATE OR REPLACE TRIGGER t_test_BI
BEFORE INSERT ON t_test
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
    SELECT T_TEST_ID.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 1', 'Column mail content 1', 1);
INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 2', 'Column mail content 1', 1);
INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 3', 'Column mail content 1', 1);
INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 4', 'Column mail content 1', 1);
INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 5', 'Column mail content 1', 1);
COMMIT;

T_TEST.ds.xml

<DataSource
    ID="T_TEST" dbName="Oracle"
    serverType="sql"
    tableName="T_TEST"
>
       <fields>
        <field name="MAIL_CONTENT" length="10000" type="text" escapeHTML="true" />
        <field name="SUBJECT" length="200" type="text" escapeHTML="true" />
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" />
    </operationBindings>
</DataSource>

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.AdvancedCriteria;
import com.smartgwt.client.data.Criterion;
import com.smartgwt.client.types.OperatorId;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.Button;
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 extends VLayout implements EntryPoint {
    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();
            }
        });

        setWidth100();
        setHeight100();

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

    private class MyWindow extends Window {
        public MyWindow() {
            setWidth(600);
            setHeight(300);
            setMembersMargin(0);
            setModalMaskOpacity(70);
            setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
            setShowMinimizeButton(false);
            setIsModal(true);
            setShowModalMask(true);
            centerInPage();
            final String value = "mail 4";

            final ListGrid lg = new ListGrid() {
                {
                    setDataSource("T_TEST");
                    setShowFilterEditor(true);

                    ListGridField mailContentLGF = new ListGridField("MAIL_CONTENT");
                    ListGridField subjectLGF = new ListGridField("SUBJECT");
                    setFields(mailContentLGF, subjectLGF);
                }
            };

            Button fetchContentButton = new Button("Fetch data content");
            fetchContentButton.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    String[] s = value.split(" ");
                    AdvancedCriteria finalCriteria = new AdvancedCriteria(OperatorId.OR);
                    for (int i = 0; i < s.length; i++) {
                        AdvancedCriteria ad = new AdvancedCriteria(new Criterion("MAIL_CONTENT", OperatorId.ICONTAINS, s[i]));
                        finalCriteria.appendToCriterionList(ad);
                    }
                    lg.fetchData(finalCriteria);
                }
            });

            Button fetchSubjectButton = new Button("Fetch data subject");
            fetchSubjectButton.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    String[] s = value.split(" ");
                    AdvancedCriteria finalCriteria = new AdvancedCriteria(OperatorId.OR);
                    for (int i = 0; i < s.length; i++) {
                        AdvancedCriteria ad = new AdvancedCriteria(new Criterion("SUBJECT", OperatorId.ICONTAINS, s[i]));
                        finalCriteria.appendToCriterionList(ad);
                    }
                    lg.fetchData(finalCriteria);
                }
            });

            addItem(lg);
            addItem(fetchContentButton);
            addItem(fetchSubjectButton);
        }
    }
}

Using v11.1p_2018-12-03/PowerEdition Deployment (built 2018-12-03) and Oracle DB,

Best regards
Pavo

[Isomorphic]

The problem here is that Oracle has limitations where the LIKE operator doesn't work normally on longer text fields (>4000) - it only searches the first 4000.

We're looking at what's best to do in the framework here - perhaps provide settings for the various possibilities - but in the meantime, you can get around this immediately using the customSQL features, eg customSelectExpression.

[Isomorphic]

Limitation of 4000 characters length causing field being ignored in criteria is removed. The fix will be available for download in nightly builds since Jan 8 (tomorrow). Let us know please how it worked for you.

[pavo123]

Hi Isomorphic,

it works fine for me, thank you!

Best regards
Pavo