Announcement

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

    Issue Filtering Based on Values of Included/Inherited DataSourceFields

    This issue is present in our code based on SmartClient Version: v10.0p_2015-10-19/PowerEdition Deployment (built 2015-10-19), but I have reproduced it in the BuiltInDS sample of the latest nightly build of SmartClient Version: v10.0p_2016-01-21/PowerEdition Deployment (built 2016-01-21).

    The issue is that for any fetch request satisfying both of the following generates incorrect SQL that fails:
    • Request is filtered using criteria containing a operator that accepts a datasource field value for the RHS operand (e.g. EQUALS_FIELD, CONTAINS_FIELD, etc.)
    • The field specified in the RHS operand is included or imported from another datasource.
    The generated SQL prepends the tableName for the datasource being queried to the corresponding field name of the RHS operand within the WHERE clause. It should instead prepend the tablename for the datasource from which the field is included or inherited.

    For example, If we do the following to the BuiltInDS project:
    • Change the datasource definition of employees.ds.xml to:
      • Have a foreign key relationship with supplyItem.ds.xml
      • Include the supplyItem.itemName and supplyItem.SKU fields.
    • Filter the grid by (for example):
      • Name "matches other field" Item
    ...then the following SQL is generated (issue highlighted in bold):

    Code:
    [INDENT]SELECT COUNT(*) FROM supplyItem, employeeTable WHERE (((employeeTable.Email IS NULL AND [B]employeeTable.itemName[/B] IS NULL) OR (employeeTable.Email = [B]employeeTable.itemName[/B] AND employeeTable.Email IS NOT NULL AND [B]employeeTable.itemName[/B] IS NOT NULL))) AND employeeTable.userOrder = supplyItem.itemID[/INDENT]
    which goes on to generate the following error:

    Code:
    [INDENT]HSQLDB threw exception: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: EMPLOYEETABLE.ITEMNAME - assuming stale connection and retrying query.[/INDENT]
    Clearly those highlighted references to employeeTable.itemName should instead refer to supplyItem.itemName. Although in my example I have included the supplyItem fields using foreignKey and includeFrom attributes, the problem was first noticed on a datasource that uses the inheritsFrom attribute at the datasource level and manually joins two tables in a custom operation binding. I have used more correct methods in my example, and it is worth observing that both mechanisms fail in the same way.

    To reproduce, make the following changes to BuiltInDS project, construct the required filter with the FilterBuilder component and click on the Filter button:

    employees.ds.xml
    Code:
    <DataSource
        ID="employees"
        serverType="sql"
        tableName="employeeTable"
        recordName="employee"
        testFileName="/examples/shared/ds/test_data/employees.data.xml"
        titleField="Name"
    >
        <fields>
            <field name="userOrder" title="userOrder" type="integer" canEdit="false" hidden="true"[B] foreignKey="supplyItem.itemID"[/B] />
            <field name="Name" title="Name" type="text" length="128" />
            <field name="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true"/>
    [B]     <field includeFrom="supplyItem.itemName"/>
            <field includeFrom="supplyItem.SKU"/>[/B]
    
            <field name="ReportsTo"       title="Manager"         type="integer"  required="true"
                   foreignKey="employees.EmployeeId"  rootValue="1" detail="true"/>
            <field name="Job"             title="Title"           type="text"     length="128"/>
            <field name="Email"           title="Email"           type="text"     length="128"/>
            <field name="EmployeeType"    title="Employee Type"   type="text"     length="40"/>
            <field name="EmployeeStatus"  title="Status"          type="text"     length="40"/>
            <field name="Salary"          title="Salary"          type="float"/>
            <field name="OrgUnit"         title="Org Unit"        type="text"     length="128"/>
            <field name="Gender"          title="Gender"          type="text"     length="7">
                <valueMap>
                    <value>male</value>
                    <value>female</value>
                </valueMap>
            </field>
            <field name="MaritalStatus"   title="Marital Status"  type="text"     length="10">
                <valueMap>
                    <value>married</value>
                    <value>single</value>
                </valueMap>
            </field>
        </fields>
    </DataSource>

    BuiltInDS.java
    Code:
    package com.smartgwt.sample.client;
    
    import com.google.gwt.core.client.EntryPoint;
    import com.smartgwt.client.core.KeyIdentifier;
    import com.smartgwt.client.data.DataSource;
    import com.smartgwt.client.data.Record;
    import com.smartgwt.client.types.SelectionStyle;
    import com.smartgwt.client.types.SortArrow;
    import com.smartgwt.client.util.PageKeyHandler;
    import com.smartgwt.client.util.Page;
    import com.smartgwt.client.util.SC;
    import com.smartgwt.client.widgets.IButton;
    import com.smartgwt.client.widgets.Label;
    import com.smartgwt.client.widgets.events.ClickEvent;
    import com.smartgwt.client.widgets.events.ClickHandler;
    import com.smartgwt.client.widgets.form.DynamicForm;
    import com.smartgwt.client.widgets.form.FilterBuilder;
    import com.smartgwt.client.widgets.grid.ListGrid;
    import com.smartgwt.client.widgets.grid.ListGridField;
    import com.smartgwt.client.widgets.grid.ListGridRecord;
    import com.smartgwt.client.widgets.grid.events.RecordClickEvent;
    import com.smartgwt.client.widgets.grid.events.RecordClickHandler;
    import com.smartgwt.client.widgets.layout.HLayout;
    import com.smartgwt.client.widgets.layout.VStack;
    import com.smartgwt.client.widgets.viewer.DetailViewer;
    
    /**
     * Entry point classes define <code>onModuleLoad()</code>.
     */
    public class BuiltInDS implements EntryPoint {
        private ListGrid boundList;
        private DynamicForm boundForm;
        private IButton saveBtn;
        private DetailViewer boundViewer;
        private IButton newBtn;
        private FilterBuilder fb;
    
        /**
         * This is the entry point method.
         */
        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();
                }
            });
    
    
            ListGrid grid = new ListGrid();
            grid.setLeft(20);
            grid.setTop(75);
            grid.setWidth(130);
            grid.setLeaveScrollbarGap(false);
            grid.setShowSortArrow(SortArrow.NONE);
            grid.setCanSort(false);
            grid.setFields(new ListGridField("dsTitle", "Select a DataSource"));
            grid.setData(new ListGridRecord[]{
                    new DSRecord("Animals", "animals"),
                    new DSRecord("Office Supplies", "supplyItem"),
                    new DSRecord("Employees", "employees")}
            );
            grid.setSelectionType(SelectionStyle.SINGLE);
            grid.addRecordClickHandler(new RecordClickHandler() {
                public void onRecordClick(RecordClickEvent event) {
                    DSRecord record = (DSRecord) event.getRecord();
                    bindComponents(record.getDsName());
                }
            });
    
            grid.draw();
    
            VStack vStack = new VStack();
            vStack.setLeft(175);
            vStack.setTop(75);
            vStack.setWidth("70%");
            vStack.setMembersMargin(20);
    
            Label label = new Label();
            label.setContents("<ul>" +
                    "<li>select a datasource from the list at left to bind to these components</li>" +
                    "<li>click a record in the grid to view and edit that record in the form</li>" +
                    "<li>click <b>New</b> to start editing a new record in the form</li>" +
                    "<li>click <b>Save</b> to save changes to a new or edited record in the form</li>" +
                    "<li>click <b>Clear</b> to clear all fields in the form</li>" +
                    "<li>click <b>Filter</b> to filter (substring match) the grid based on form values</li>" +
                    "<li>click <b>Fetch</b> to fetch records (exact match) for the grid based on form values</li>" +
                    "<li>double-click a record in the grid to edit inline (press Return, or arrow/tab to another record, to save)</li>" +
                    "</ul>");
            vStack.addMember(label);
    
            boundList = new ListGrid();
            boundList.setHeight(200);
            boundList.setCanEdit(true);
    
            boundList.addRecordClickHandler(new RecordClickHandler() {
                public void onRecordClick(RecordClickEvent event) {
                    Record record = event.getRecord();
                    boundForm.editRecord(record);
                    saveBtn.enable();
                    boundViewer.viewSelectedData(boundList);
                }
            });
            vStack.addMember(boundList);
    
            
            fb = new FilterBuilder();
            vStack.addMember(fb);
    
            boundForm = new DynamicForm();
            boundForm.setNumCols(6);
            boundForm.setAutoFocus(false);
    //        vStack.addMember(boundForm);
    
            HLayout hLayout = new HLayout(10);
            hLayout.setMembersMargin(10);
            hLayout.setHeight(22);
    
            saveBtn = new IButton("Save");
            saveBtn.addClickHandler(new ClickHandler() {
                public void onClick(ClickEvent event) {
                    boundForm.saveData();
                    if (!boundForm.hasErrors()) {
                        boundForm.clearValues();
                        saveBtn.disable();
                    }
                }
            });
            hLayout.addMember(saveBtn);
    
            newBtn = new IButton("New");
            newBtn.addClickHandler(new ClickHandler() {
                public void onClick(ClickEvent event) {
                    boundForm.editNewRecord();
                    saveBtn.enable();
                }
            });
            hLayout.addMember(newBtn);
    
            IButton clearBtn = new IButton("Clear");
            clearBtn.addClickHandler(new ClickHandler() {
                public void onClick(ClickEvent event) {
                    boundForm.clearValues();
                    saveBtn.disable();
                }
            });
            hLayout.addMember(clearBtn);
    
            IButton filterBtn = new IButton("Filter");
            filterBtn.addClickHandler(new ClickHandler() {
                public void onClick(ClickEvent event) {
                    boundList.filterData(fb.getCriteria());
                    saveBtn.disable();
                }
            });
            hLayout.addMember(filterBtn);
    
            IButton fetchBtn = new IButton("Fetch");
            fetchBtn.addClickHandler(new ClickHandler() {
                public void onClick(ClickEvent event) {
                    boundList.fetchData(boundForm.getValuesAsCriteria());
                    saveBtn.disable();
                }
            });
            hLayout.addMember(fetchBtn);
    
            vStack.addMember(hLayout);
            
            boundViewer = new DetailViewer();
            vStack.addMember(boundViewer);
    
            vStack.draw();
        }
    
        private void bindComponents(String dsName) {
            DataSource ds = DataSource.get(dsName);
                    
            boundList.setDataSource(ds);
            boundViewer.setDataSource(ds);
            boundForm.setDataSource(ds);
            fb.setDataSource(ds);
            boundList.fetchData();
            newBtn.enable();
            saveBtn.disable();
        }
    }
    Last edited by godonnell_ip; 22 Jan 2016, 13:50.

    #2
    Thanks for the detailed report. The issue is clear and we are working on the fix. We'll keep this thread updated.

    Comment


      #3
      This is fixed now and will be available for download in the nightly builds since Jan 29 (tomorrow). Please let us know how it worked for you.

      Comment


        #4
        First of all, thank you for addressing this so quickly. I have finally managed to try this out and can confirm that it works for the test case I supplied above, but doesn't quite work for our actual code. I have tried to identify exactly why by stepping through the construction of the WHERE clause, and here is what I found.

        Before I begin, I should mention that our application registers a dynamic datasource generator which performs multiple transformations on our XML datasource definitions - one of these transformations allows us to inherit from multiple datasources rather than just one. When we do so, we strip off the inheritsFrom attribute, explicitly copy the field definitions from the referenced datasource and add the tableName attribute to each inherited field.

        ​So this is somewhat out of the ordinary, but based on what I found I still think the libraries can be modified to accommodate both the desired base behavior as well as our own custom behavior. I'll let you tell me if I'm wrong about that.

        In this case, the ProductionOrderLineDelivery datasource inherits from the ProductionOrder, ProductionLine, ProductionDelivery and IPClass datasources and I set up a (test) filter based on orderNumber "is not equal to orderDate".

        Line 2128 of SQLWhereClause.fieldStringComparison() makes a call to SQLWhereClause.deriveColumnName(), in which it passes both the field definition and the tableName of the datasource definition within which orderDate (the RHS of the filter criteria) is contained. Notice in this first screenshot that field correctly has the tableName attribute value of ProductionOrder:
        Click image for larger version

Name:	inherit1.png
Views:	125
Size:	20.7 KB
ID:	234721


        And in these screenshots, note that the passed overrideTableName parameter is ProductionOrderLineDelivery - i.e. the tableName attribute of the datasource that contains the RHS operand (orderDate):
        Click image for larger version

Name:	inherit2.png
Views:	115
Size:	17.7 KB
ID:	234722


        ...as the value is received in the deriveColumnName() method:
        Click image for larger version

Name:	inherit3.png
Views:	103
Size:	20.1 KB
ID:	234723


        This (in my case) incorrect value is used as the prefix for the column name, rather than the actual tableName that is associated with the field parameter.

        Couldn't line 2128 instead test field.tableName to see if it has a null value and, if so, pass that value instead of the datasource's tableName attribute? The latter would be passed as a fallback if the field's attribute is not explicitly specified.

        Alternatively, couldn't the code in deriveColumnName perform a similar test when determining the prefix for a given column name?

        Thanks in advance for your comments.
        Last edited by godonnell_ip; 11 Feb 2016, 09:15.

        Comment


          #5
          This change you suggested looks harmless and provides additional flexibility for non-standard configurations like yours. So, it is added and may be downloaded in nightly build since today (Feb 11). Please confirm that your case is working like expected after you try the new build.

          Comment


            #6
            Hi godonnell_ip, Hi Isomorphic,

            how is it possible to see / step through SmartGWT Server Code in Eclipse like shown in post #4?
            Is this possible with the normal SmartGWT Power jar files?

            Thank you & Best regards
            Blama

            Comment


              #7
              Isomorphic - thank you. I will try out the new libraries.

              Blama - I'm happy to respond to this if Isomorphic confirms that I'm not drastically infringing on the terms & conditions by doing so. I am aware that that the License Obligations section specifically states that I agree:

              3.5 not to reverse engineer, decompile, unobfuscate, reformat, or otherwise attempt to read or make readable any portion of the code (or the underlying ideas, algorithms, structure or organization) of the Software, except and only to the extent and on the conditions that this obligation is not enforceable under applicable law;

              Clearly I have done so to some degree above, and if Isomorphic tells me to stop doing that then I will.

              Regards,
              Gary

              Comment


                #8
                Well, we're ALMOST there... I found a couple of issues, though. The first appears to be a relatively minor inconsistency, but the second causes me further problems.

                When I test the filter I mentioned above ("orderNumber is not equal to orderDate") using the "differs from field (case insensitive)",operator I receive a database error because the date field cannot be converted to lower case (we are using a DB2 for i database). I suppose this is expected and is something I can work around. The good new is that the generated SQL shows the correct intent in that it prefixes the column names with their correct table name (it's ugly, but what we are interested in is at the tail end of the expression:

                Code:
                SELECT COUNT(*) FROM
                ProductionDelivery
                LEFT JOIN ProductionDeliveryDestinationSummary
                on ProductionDelivery.id=ProductionDeliveryDestinationSummary.parentId
                LEFT JOIN ProductionLine on ProductionLine.id = ProductionDelivery.parentid
                LEFT JOIN ProductionOrder on ProductionOrder.id = ProductionLine.parentid
                LEFT JOIN IPMRVEN on ProductionOrder.vendorId = IPMRVEN.VVEN
                LEFT JOIN ProductionContact as vendorContact on ProductionOrder.vContactId = vendorContact.id
                LEFT JOIN BuyingAgent on ProductionOrder.bAgentId = BuyingAgent.id
                LEFT JOIN ProductionContact as buyingAgentContact on ProductionOrder.bContactId = buyingAgentContact.id
                LEFT JOIN InspectionAgent on ProductionOrder.iAgentId = InspectionAgent.id
                LEFT JOIN ProductionContact as inspectionAgentContact on ProductionOrder.iContactId = inspectionAgentContact.id
                LEFT JOIN IPCLASS ON ProductionLine.ITEMCLASS=IPCLASS.CCLS
                LEFT JOIN ShippingPort on ProductionOrder.shipPortId = ShippingPort.id
                WHERE ((((ProductionOrder.zone IN (5, 17, 100, 111, 141, 201, 216, 230, 280, 290, 300, 303, 410, 470, 480, 490, 630, 701, 780, 820, 821, 960, 998, 999, 14, 18, 49, 70, 99, 142, 191, 200, 231, 234, 270, 440, 491, 770, 771, 772, 773, 774, 777, 810, 880, 888, 961)) AND ProductionOrder.zone IS NOT NULL) AND ((ProductionOrder.division IN (1, 4, 5, 6, 7, 8, 14, 15, 17, 18, 28, 39, 41, 49, 54, 63, 70, 77, 81, 88, 90, 91, 96, 100, 101, 105, 111, 117, 141, 142, 201, 216, 230, 231, 232, 233, 270, 280, 300, 303, 310, 390, 399, 410, 420, 440, 470, 480, 490, 491, 492, 630, 770, 777, 780, 800, 808, 818, 820, 821, 822, 825, 881, 888, 960, 961, 962, 963, 998, 999)) AND ProductionOrder.division IS NOT NULL)) AND (NOT((ProductionOrder.orderDate IS NULL AND ProductionOrder.controlNum IS NULL) OR (LOWER(ProductionOrder.controlNum) LIKE LOWER(ProductionOrder.orderDate) {ESCAPE '\'} AND ProductionOrder.controlNum IS NOT NULL AND ProductionOrder.orderDate IS NOT NULL)))
                So, I modify my filter to use the case-sensitive variant of the operator, but now my filter is completely missing and is replaced with an expression that will always evaluate to true:

                Code:
                SELECT COUNT(*) FROM 
                                 ProductionDelivery 
                                 LEFT JOIN ProductionDeliveryDestinationSummary 
                                       on ProductionDelivery.id=ProductionDeliveryDestinationSummary.parentId
                                 LEFT JOIN ProductionLine on ProductionLine.id = ProductionDelivery.parentid 
                                 LEFT JOIN ProductionOrder on ProductionOrder.id = ProductionLine.parentid
                                  LEFT JOIN IPMRVEN on ProductionOrder.vendorId = IPMRVEN.VVEN
                                 LEFT JOIN ProductionContact as vendorContact on ProductionOrder.vContactId = vendorContact.id
                                 LEFT JOIN BuyingAgent on ProductionOrder.bAgentId = BuyingAgent.id
                                 LEFT JOIN ProductionContact as buyingAgentContact on ProductionOrder.bContactId = buyingAgentContact.id
                                 LEFT JOIN InspectionAgent on ProductionOrder.iAgentId = InspectionAgent.id
                                 LEFT JOIN ProductionContact as inspectionAgentContact on ProductionOrder.iContactId = inspectionAgentContact.id
                                 LEFT JOIN IPCLASS ON ProductionLine.ITEMCLASS=IPCLASS.CCLS
                                 LEFT JOIN ShippingPort on ProductionOrder.shipPortId = ShippingPort.id             
                             WHERE ((((ProductionOrder.zone IN (5, 17, 100, 111, 141, 201, 216, 230, 280, 290, 300, 303, 410, 470, 480, 490, 630, 701, 780, 820, 821, 960, 998, 999, 14, 18, 49, 70, 99, 142, 191, 200, 231, 234, 270, 440, 491, 770, 771, 772, 773, 774, 777, 810, 880, 888, 961)) AND ProductionOrder.zone IS NOT NULL) AND ((ProductionOrder.division IN (1, 4, 5, 6, 7, 8, 14, 15, 17, 18, 28, 39, 41, 49, 54, 63, 70, 77, 81, 88, 90, 91, 96, 100, 101, 105, 111, 117, 141, 142, 201, 216, 230, 231, 232, 233, 270, 280, 300, 303, 310, 390, 399, 410, 420, 440, 470, 480, 490, 491, 492, 630, 770, 777, 780, 800, 808, 818, 820, 821, 822, 825, 881, 888, 960, 961, 962, 963, 998, 999)) AND ProductionOrder.division IS NOT NULL)) AND (('1'='1')))
                This was completely unexpected for me, but the data types are different, so perhaps it's an expected behavior (i.e. assume that a text field will never equal the value in a numeric field). However, if that is so, then I wonder why the case-insensitive version got so far as executing the SQL statement and generating an error response? I would call this inconsistency issue #1. I almost prefer the error, since otherwise I might think that the filter worked as I intended, when in actual fact it's effectively being ignored, but I suppose that point could be argued both ways...

                Moving on, then, I then selected a different RHS operand (in this case, ProductionDelivery.destinations) where the data types are similar (both are defined as type="text"). This still yielded a failure to generate the correct SQL. In this log snippet, I include the request as it was passed to the server to demonstrate the criteria was correct. Note, though, the log entry that reads "no column name for field named: destinations":

                Code:
                >>> 2016-02-11 15:53:15,593 [ec-2] INFO  SQLDataSource - [builtinApplication.ProductionOrderLineDelivery_fetch] Performing fetch operation with
                    criteria: {criteria:[{criteria:[{value:["5","17","100","111","141","201","216","230","280","290","300","303","410","470","480","490","630","701","780","820","821","960","998","999","14","18","49","70","99","142","191","200","231","234","270","440","491","770","771","772","773","774","777","810","880","888","961"],fieldName:"zone",operator:"inSet"},{value:["1","4","5","6","7","8","14","15","17","18","28","39","41","49","54","63","70","77","81","88","90","91","96","100","101","105","111","117","141","142","201","216","230","231","232","233","270","280","300","303","310","390","399","410","420","440","470","480","490","491","492","630","770","777","780","800","808","818","820","821","822","825","881","888","960","961","962","963","998","999"],fieldName:"division",operator:"inSet"}],operator:"and"},{criteria:[{value:"destinationNames",fieldName:"controlNum",operator:"notEqualField"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}    values: {criteria:[{criteria:[{value:["5","17","100","111","141","201","216","230","280","290","300","303","410","470","480","490","630","701","780","820","821","960","998","999","14","18","49","70","99","142","191","200","231","234","270","440","491","770","771","772","773","774","777","810","880","888","961"],fieldName:"zone",operator:"inSet"},{value:["1","4","5","6","7","8","14","15","17","18","28","39","41","49","54","63","70","77","81","88","90","91","96","100","101","105","111","117","141","142","201","216","230","231","232","233","270","280","300","303","310","390","399","410","420","440","470","480","490","491","492","630","770","777","780","800","808","818","820","821","822","825","881","888","960","961","962","963","998","999"],fieldName:"division",operator:"inSet"}],operator:"and"},{criteria:[{value:"destinationNames",fieldName:"controlNum",operator:"notEqualField"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}
                
                >>> 2016-02-11 15:53:15,601 [ec-2] WARN  SQLWhereClause - [builtinApplication.ProductionOrderLineDelivery_fetch] no column name for field named: destinations
                
                >>> 2016-02-11 15:53:15,636 [ec-2] DEBUG SQLDataSource - [builtinApplication.ProductionOrderLineDelivery_fetch] Eval'd row count query: SELECT COUNT(*) FROM 
                                 ProductionDelivery 
                                 LEFT JOIN ProductionDeliveryDestinationSummary 
                                       on ProductionDelivery.id=ProductionDeliveryDestinationSummary.parentId
                                 LEFT JOIN ProductionLine on ProductionLine.id = ProductionDelivery.parentid 
                                 LEFT JOIN ProductionOrder on ProductionOrder.id = ProductionLine.parentid
                                  LEFT JOIN IPMRVEN on ProductionOrder.vendorId = IPMRVEN.VVEN
                                 LEFT JOIN ProductionContact as vendorContact on ProductionOrder.vContactId = vendorContact.id
                                 LEFT JOIN BuyingAgent on ProductionOrder.bAgentId = BuyingAgent.id
                                 LEFT JOIN ProductionContact as buyingAgentContact on ProductionOrder.bContactId = buyingAgentContact.id
                                 LEFT JOIN InspectionAgent on ProductionOrder.iAgentId = InspectionAgent.id
                                 LEFT JOIN ProductionContact as inspectionAgentContact on ProductionOrder.iContactId = inspectionAgentContact.id
                                 LEFT JOIN IPCLASS ON ProductionLine.ITEMCLASS=IPCLASS.CCLS
                                 LEFT JOIN ShippingPort on ProductionOrder.shipPortId = ShippingPort.id             
                             WHERE ((((ProductionOrder.zone IN (5, 17, 100, 111, 141, 201, 216, 230, 280, 290, 300, 303, 410, 470, 480, 490, 630, 701, 780, 820, 821, 960, 998, 999, 14, 18, 49, 70, 99, 142, 191, 200, 231, 234, 270, 440, 491, 770, 771, 772, 773, 774, 777, 810, 880, 888, 961)) AND ProductionOrder.zone IS NOT NULL) AND ((ProductionOrder.division IN (1, 4, 5, 6, 7, 8, 14, 15, 17, 18, 28, 39, 41, 49, 54, 63, 70, 77, 81, 88, 90, 91, 96, 100, 101, 105, 111, 117, 141, 142, 201, 216, 230, 231, 232, 233, 270, 280, 300, 303, 310, 390, 399, 410, 420, 440, 470, 480, 490, 491, 492, 630, 770, 777, 780, 800, 808, 818, 820, 821, 822, 825, 881, 888, 960, 961, 962, 963, 998, 999)) AND ProductionOrder.division IS NOT NULL)) AND (('1'='1')))
                That log entry I mentioned tipped me off, and you will see below that the ProductionDelivery.destinations field definition has a nativeName attribute. I point that out because after much headscratching I determined that other fields do work OK, including ProductionOrder.bankName, ProductionDelivery.vendorStyle and IPCLASS.CLNM. None of them have a nativeName attribute. Conversely, ProductionOrder.InitialApprovalBy is another field with a nativeName attribute and, yes, it fails on this, too.

                So what if I reverse the operands: instead of "orderNumber is not equal to destinations" I filter by "destinations is not equal to orderNumber"? Well, that works fine, also:

                Code:
                @@@ 2016-02-11 16:29:11,541 [ec-7] INFO  SQLDataSource - [builtinApplication.ProductionOrderLineDelivery_fetch] Performing fetch operation with
                        criteria: {criteria:[{criteria:[{value:["5","17","100","111","141","201","216","230","280","290","300","303","410","470","480","490","630","701","780","820","821","960","998","999","14","18","49","70","99","142","191","200","231","234","270","440","491","770","771","772","773","774","777","810","880","888","961"],fieldName:"zone",operator:"inSet"},{value:["1","4","5","6","7","8","14","15","17","18","28","39","41","49","54","63","70","77","81","88","90","91","96","100","101","105","111","117","141","142","201","216","230","231","232","233","270","280","300","303","310","390","399","410","420","440","470","480","490","491","492","630","770","777","780","800","808","818","820","821","822","825","881","888","960","961","962","963","998","999"],fieldName:"division",operator:"inSet"}],operator:"and"},{criteria:[{value:"controlNum",fieldName:"destinationNames",operator:"notEqualField"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}        values: {criteria:[{criteria:[{value:["5","17","100","111","141","201","216","230","280","290","300","303","410","470","480","490","630","701","780","820","821","960","998","999","14","18","49","70","99","142","191","200","231","234","270","440","491","770","771","772","773","774","777","810","880","888","961"],fieldName:"zone",operator:"inSet"},{value:["1","4","5","6","7","8","14","15","17","18","28","39","41","49","54","63","70","77","81","88","90","91","96","100","101","105","111","117","141","142","201","216","230","231","232","233","270","280","300","303","310","390","399","410","420","440","470","480","490","491","492","630","770","777","780","800","808","818","820","821","822","825","881","888","960","961","962","963","998","999"],fieldName:"division",operator:"inSet"}],operator:"and"},{criteria:[{value:"controlNum",fieldName:"destinationNames",operator:"notEqualField"}],operator:"and"}],operator:"and",_constructor:"AdvancedCriteria"}
                
                @@@ 2016-02-11 16:29:11,604 [ec-7] DEBUG SQLDataSource - [builtinApplication.ProductionOrderLineDelivery_fetch] Eval'd row count query: SELECT COUNT(*) FROM
                                                 ProductionDelivery
                                                 LEFT JOIN ProductionDeliveryDestinationSummary
                                                on ProductionDelivery.id=ProductionDeliveryDestinationSummary.parentId
                                                 LEFT JOIN ProductionLine on ProductionLine.id = ProductionDelivery.parentid
                                                 LEFT JOIN ProductionOrder on ProductionOrder.id = ProductionLine.parentid
                                                 LEFT JOIN IPMRVEN on ProductionOrder.vendorId = IPMRVEN.VVEN
                                                 LEFT JOIN ProductionContact as vendorContact on ProductionOrder.vContactId = vendorContact.id
                                                 LEFT JOIN BuyingAgent on ProductionOrder.bAgentId = BuyingAgent.id
                                                 LEFT JOIN ProductionContact as buyingAgentContact on ProductionOrder.bContactId = buyingAgentContact.id
                                                 LEFT JOIN InspectionAgent on ProductionOrder.iAgentId = InspectionAgent.id
                                                 LEFT JOIN ProductionContact as inspectionAgentContact on ProductionOrder.iContactId = inspectionAgentContact.id
                                                 LEFT JOIN IPCLASS ON ProductionLine.ITEMCLASS=IPCLASS.CCLS
                                                 LEFT JOIN ShippingPort on ProductionOrder.shipPortId = ShippingPort.id
                                         WHERE ((((ProductionOrder.zone IN (5, 17, 100, 111, 141, 201, 216, 230, 280, 290, 300, 303, 410, 470, 480, 490, 630, 701, 780, 820, 821, 960, 998, 999, 14, 18, 49, 70, 99, 142, 191, 200, 231, 234, 270, 440, 491, 770, 771, 772, 773, 774, 777, 810, 880, 888, 961)) AND ProductionOrder.zone IS NOT NULL) AND ((ProductionOrder.division IN (1, 4, 5, 6, 7, 8, 14, 15, 17, 18, 28, 39, 41, 49, 54, 63, 70, 77, 81, 88, 90, 91, 96, 100, 101, 105, 111, 117, 141, 142, 201, 216, 230, 231, 232, 233, 270, 280, 300, 303, 310, 390, 399, 410, 420, 440, 470, 480, 490, 491, 492, 630, 770, 777, 780, 800, 808, 818, 820, 821, 822, 825, 881, 888, 960, 961, 962, 963, 998, 999)) AND ProductionOrder.division IS NOT NULL)) AND ((((ProductionDelivery.destinations IS NULL AND ProductionOrder.controlNum IS NOT NULL) OR (ProductionDelivery.destinations IS NOT NULL AND ProductionOrder.controlNum IS NULL)) OR (ProductionDelivery.destinations <> ProductionOrder.controlNum AND ProductionDelivery.destinations IS NOT NULL AND ProductionOrder.controlNum IS NOT NULL))))
                This time I do not receive the log entry telling me the column for field x cannot be found and the resulting SQL is as I would expect.

                I would therefore call this failure to cater for nativeName attributes on the RHS of my filter expression issue#2.

                For reference, here are some of my field definitions I refer to above. You will note that they are of differing complexity, but the one constant is that those that fail on the RHS of a filter expression have a nativeName attribute, and those that do not have that attribute do not fail.

                From ProductionOrder.ds.xml:
                Code:
                <field name="controlNum" title="Order Number" type="text"/>
                <field name="bankName" menuItem="Bank>" title="Bank Name" type="text" length="40" detail="true" />
                <field name="InitialApprovalBy" menuItem="Activity>" nativeName="approve1By" title="Initial approval by" type="text" detail="true" canEdit="false" foreignKey="IPUser.code"/>
                From ProductionDelivery.ds.xml:
                Code:
                <field name="destinationNames" nativeName="destinations" type="text" title="Destinations" canEdit="false" width="100"/>
                <field name="destinationNames" nativeName="destinations" type="text" title="Destinations" canEdit="false" width="100"/>
                <field name="vendorStyle" title="Vendor Style" type="text" length="15" characterCasing="upper" customSelectExpression="ProductionLine.vendorStyle" customSQL="true"/>
                From IPCLASS.ds.xml:
                Code:
                <field name="CLNM" title="Class Name" type="text" length="25" characterCasing="upper" required="true" canFilter="true" optionOperationId="$DEFAULT_MERCH_HIERARCHY_SORT_Preference"/>
                Thanks again for your help with this.

                Comment


                  #9
                  We'll check on these new reported issues and see if we can reproduce them.

                  As far as the prohibition against reverse engineering, that has to be our default legal stance as communicated in the license, to give us some legal leverage against a third party that decompiles our source, changes package names and then compiles and markets it as their own product.

                  We don't generally have a problem with individual developers who are building SGWT-based applications doing reverse engineering to figure out bugs, and we grant that kind of permission to almost anyone who asks. To make it official: Isomorphic hereby grants users gdonnel_ip and Blama the right to reverse engineer, decompile and unobfuscate the source code of SmartGWT for the purpose of troubleshooting issues in SmartGWT-based applications.

                  And you may of course share your techniques here as well. We would suggest a new thread, however.

                  Comment


                    #10
                    Isomorphic - thanks again!

                    Blama - see http://forums.smartclient.com/forum/...phic-libraries

                    Comment


                      #11
                      First of all thank you for such detailed explanations, which allow to easily understand and reproduce the issue and to react quickly.

                      On issue#1, we've added a warning to the logs if unsupported type comparison detected (like text vs date) for both case sensitive and insensitive field operators.

                      As for issue#2, it is fixed and available for download in the nightly builds since today (Feb 15). The nativeName attribute for fields on the RHS of expression is respected now and operates as expected.

                      Let us know please how new build worked for you, thanks!

                      Comment


                        #12
                        Thank you very much - I have tested the new libraries and they have passed all my tests. I'm now passing this back to our QA group and we'll let you know if we see anything further, but I'm pretty sure you have taken care of all the issues that were related to the above.

                        Comment

                        Working...
                        X