Client filtering of ComboBoxItem does not account for leading zeros in numeric fields

[godonnell_ip]

[SmartClient Version: v11.0p_2016-10-01/PowerEdition Deployment (built 2016-10-01)] and [SmartClient Version: v10.0p_2016-02-18/PowerEdition Deployment (built 2016-02-18)]

NOTE: code snippets from the SmartGWT libraries are taken from v10.0p_2016-02-18/PowerEdition Deployment (built 2016-02-18)

The problem here is that after performing the initial server fetch of matching values to populate entries within a ComboBoxItem dropdown, client-side filtering performed when the filter is further refined (e.g. by typing more characters) is not stripping leading zeros when attempting to match numeric fields. Leading zeros on numeric fields (since they are not relevant) should be ignored when determining whether client-side refinement can be performed or a server-side fetch is required.

The problem can be seen by modifying the http://www.smartclient.com/#comboFilterRelated example as follows:

 isc.DynamicForm.create({     ID:"testForm",     width: 550,     numCols:4,     fields : [     {         name: "filteredCombo", title: "Item (ComboBox)", editorType: "ComboBoxItem",          addUnknownValues:false, wrapTitle: false,         optionDataSource: "supplyItem",          displayField:"itemName", valueField:"SKU",         filterFields:["itemID", "itemName"],         pickListWidth:300,         pickListFields:[             {name:"itemID"},             {name:"itemName"}         ]     }     ] });

Now, type "012" quickly and you will see a number of values that start with "12" returned. This is because the server side has the following logic in SQLWhereClause.stringComparison() - specifically, lines 1387 and 1389 convert a numeric value supplied as a String to a BigDecimal or BigInteger. This, of course, means any leading zeros entered by the user are now no longer relevant:

/*      */   private String stringComparison(String fieldName, String columnType, String operator, Object objVal, boolean negate, SQLDriver driver, DSField field, BasicDataSource ds)
/*      */     throws Exception
/*      */   {
/* 1366 */     if (fieldName == null) {
/* 1367 */       log.error("Found a null fieldName");
/* 1368 */       return "('1'='1')";
/*      */     }
/*      */    
/*      */
/*      */
/*      */
/* 1374 */     if ((driver.castNumbersBeforeLikeCompare()) && (
/* 1375 */       (ds.simpleTypeInheritsFrom(columnType, "integer")) || (ds.simpleTypeInheritsFrom(columnType, "float"))))
/*      */     {
/* 1377 */       fieldName = "CAST(" + fieldName + " AS varchar(50))";
/*      */     }
/*      */    
/* 1380 */     if (((ds.simpleTypeInheritsFrom(columnType, "integer")) || (ds.simpleTypeInheritsFrom(columnType, "float"))) && (objVal != null)) {
/*      */       try
/*      */       {
/* 1383 */         if ((objVal instanceof Date)) {
/* 1384 */           objVal = new Long(((Date)objVal).getTime()).toString();
/*      */         }
/* 1386 */         else if (ds.simpleTypeInheritsFrom(columnType, "float")) {
/* 1387 */           objVal = new BigDecimal(objVal.toString()).toString();
/*      */         } else {
/* 1389 */           objVal = new BigInteger(objVal.toString()).toString();
/*      */         }
/*      */       }
/*      */       catch (Exception e) {
/* 1393 */         log.warn("Got non-numeric value '" + objVal + "' for numeric column '" + fieldName + "', creating literal false expression: " + e);
/*      */        
/*      */
/*      */
/*      */
/* 1398 */         if (negate) {
/* 1399 */           return "('1'='1')";
/*      */         }
/* 1401 */         return "'0'='1'";
/*      */       }
/*      */     }

However, instead of entering "012" quickly, now enter just "0" and allow the code to perform a server request to obtain values. No values are returned. Now type "12" and the client-side code will attempt to refine the previously returned result set (avoiding an additional server-side fetch). However, since there are no values to refine, still not values are displayed in the ComboBoxItem dropdown.

Compare this to the behavior if you instead first search for "012" (entered quickly so that the server request is initiated for values beginning with "12" and therefore values are returned) and then equally quickly delete "2" and type "3". A server request is initiated to fetch results beginning with "13". The client code clearly recognizes that this is not just a refinement. It seems to me that somewhere around where this determination is made, the code should check if it is searching on a numeric field and whether the previous filter value on which the result set is based evaluates to zero. If so, and if the new value evaluates to a non-zero value, then invoke a server request. Does that sound about right?

Thanks,
Gary O'Donnell

[Isomorphic]

Is this scenario actually coming up in a real-world UI? We can't see users entering a leading zero and being surprised at the inability to get results from a numeric field, and it doesn't seem important that rapidly typing "012" happens to get results.

The reason for asking is that the simplest fix is to just stop allowing criteria with a leading zero to work on numeric fields at all. Adding client-side code to adaptive filtering to special case for the current server behavior with leading zeroes is complicated, and would be assuming it knows what the backend will do, whereas other backends might do something different. A yet more complicated approach of having a setting that can be applied to both client and server to synchronize filtering seems clearly not worth it.

So all reasonable ways of solving this problem don't seem like they are a marked improvement on the existing behavior.

[godonnell_ip]

Unfortunately yes, this is a real-world scenario in our application.

The application in question exposes functionality that has been available in our legacy application for several decades. It does so in conjunction with the legacy application, meaning not only that users are very used to a specific way of working, but also that for the applications to interoperate correctly we must adhere to certain long-established principles. One of these is that an stock item number is based on a combination of IDs for class, vendor, style, color and size. Each has a maximum number of digits and, when rendered as a single number, leading zeros must be used so that their values can be determined by their position the number.

However, when working with individual components of the number (i.e. when managing the definition of color codes within the system), users work with only the component of the number that represents that value - in the case of color that is either a 3-digit or 4 digit value (depending on which version of our legacy application we are talking about - you are probably starting to get an idea of the scale of the issue at this point...)

The legacy application enforces that users always enter leading zeros when referring to components of the item number, and in order to allow a seamless user experience we should (and do) also expect this to occur in the SmartGWT-based UI. Our users are finding, though, that they are experiencing different results based on, of all things, how quickly or slowly they type in the digits of the values on which they are searching.

That's leaving aside the unpleasant fact that in some cases numeric values are stored in text fields with leading zeros intact, and therefore leading zeros are required in those cases to locate matches. Yes, it's ugly, but there are not only historical reasons for this but also practical ones (e.g. depending on configuration those numbers may either be numeric values generated by our system, or alphanumeric values provided by other systems).

I agree with you that in a perfect world we would tell users if it hurts then don't do that, but in reality I think it is reasonable to anticipate that this could occur in any situation, and most especially where fixed-length values are in play.

Consider, for example, the user who is working from a printed system-generated list of which has values formatted, for whatever reason, with leading zeros. If they transcribe one of those numbers verbatim from the list into a SmartGWt ComboBox, why should they sometimes get the result they expect and sometimes not?

[godonnell_ip]

On further reflection, I think my first suggestion pretty much hit the nail on the head, whereas the one you proposed might in some respects be considered equivalent.

I see that within filterDataBoundPickList(), getPickListFilterCriteria() called and this is the method in which the criteria is initially established. Of course, this can be overridden or changed with setPickListFilterCriteriaFunction(), so this is not the best place to make a change.

However, right after that call we then have the opportunity to modify the returned criteria, as is potentially done in conditional calls to resolveDynamicCriteria() and filterCriteriaForFormValues(). I would suggest that in this general area the criteria be further modified to strip values for filter fields that are bound to numeric fields of their leading zeros (if that means all characters are stripped, then the value should be set to 0). In this way, the subsequently called logic in willFetchData() will detect a difference between oldCriteria and newCriteria and will correctly (and necessarily) trigger a new server request.

In this way, all other cases would remain unchanged and yet the issue I identified in the original post would be satisfied.

Thoughts?

[Isomorphic]

We have made a change that should resolve this for you. The change should be present in tomorrow's builds of SmartClient 10.0 and greater, and Smart GWT 5.0 and greater

[godonnell_ip]

Thanks - I'm looking forward to this and other fixes you mentioned, but I don't see a nightly build for 6.0p Power Edition dated later than 10/7 (I do see a build of 6.0p LGPL from 10/10).