[problem in migrating to 11.0] filter uses 'iContains' for a datetime field

[claudiobosticco]

SmartClient Version: v11.0p_2016-05-22/EVAL Development Only (expires 2016.07.21_04.37.11) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Chrome on OSX

Hello, I've got a ListGrid (ID="movimentiGrid"), with allowFilterExpressions: true.
The grid is bound to a dataSource which has a field of type datetime (which actually is not shown in the grid):

        <field name="EXPORTED_TS" type="datetime" exportFormat="dd/MM/yyyy HH:mm:ss"/>

The grid may be filtered (using a SelectItem) for specific dates, like this:

        {
            name: "selectLotto", type: "select", multiple: true, title: "Filtro per Lotto", required: true, width: 200,
            optionDataSource: jfLotti, displayField: "LOTTO_DESC", valueField: "LOTTO_DATE", allowEmptyValue: true,
            changed: function (form, item, value) {
                movimentiGrid.fetchData({EXPORTED_TS: value});
            }
        }

as you may see, I was using a simple criteria. When I typed something in the filterEditor grid, this additional criteria was combined with the previous criteria (as an AdvancedCriteria).

Now with 11.0, this is what is logged when I type something in the filterEditor:

2016-05-25 14:39:10,752 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"CAUSAL",
                operator:"iContainsPattern",
                value:"n02"
            },
            {
                operator:"iContains",
                fieldName:"EXPORTED_TS",
                value:[
                    new Date(1438939656000)
                ]
            }
        ]
    },
    operationConfig:{
        dataSource:"JF_MOVIMENTI",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "NAME_LAST",
        "NAME_FIRST"
    ],
    componentId:"movimentiGrid",
    appID:"builtinApplication",
    operation:"movimentiNonScartati",
    oldValues:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"CAUSAL",
                operator:"iContainsPattern",
                value:"n02"
            },
            {
                operator:"iContains",
                fieldName:"EXPORTED_TS",
                value:[
                    new Date(1438939656000)
                ]
            }
        ]
    }
} 
2016-05-25 14:39:10,752 INFO  IDACall Performing 1 operation(s) 
2016-05-25 14:39:10,752 DEBUG DeclarativeSecurity Processing security checks for DataSource null, field null 
2016-05-25 14:39:10,752 DEBUG DeclarativeSecurity DataSource JF_MOVIMENTI is not in the pre-checked list, processing... 
2016-05-25 14:39:10,753 DEBUG AppBase [builtinApplication.movimentiNonScartati] No userTypes defined, allowing anyone access to all operations for this application 
2016-05-25 14:39:10,753 DEBUG AppBase [builtinApplication.movimentiNonScartati] No public zero-argument method named '_movimentiNonScartati' found, performing generic datasource operation 
2016-05-25 14:39:10,753 INFO  SQLDataSource [builtinApplication.movimentiNonScartati] Performing fetch operation with
    criteria: {criteria:[{fieldName:"CAUSAL",operator:"iContainsPattern",value:"n02"},{criteria:[{value:new Date(1438939656000),fieldName:"EXPORTED_TS",operator:"iContains"}],operator:"or"}],operator:"and",_constructor:"AdvancedCriteria"}    values: {criteria:[{fieldName:"CAUSAL",operator:"iContainsPattern",value:"n02"},{criteria:[{value:new Date(1438939656000),fieldName:"EXPORTED_TS",operator:"iContains"}],operator:"or"}],operator:"and",_constructor:"AdvancedCriteria"} 
2016-05-25 14:39:10,754 WARN  SQLWhereClause [builtinApplication.movimentiNonScartati] Attempt to compare a Date as String, testing value Fri Aug 07 11:27:36 CEST 2015 with operator iContains. This is an invalid comparison, so we are returning FALSE. 
2016-05-25 14:39:10,754 INFO  SQLDataSource [builtinApplication.movimentiNonScartati] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE 
                $defaultWhereClause
                AND JF_MOVIMENTI.FL_SCART = 'F'
             ORDER BY $defaultOrderClause 
2016-05-25 14:39:10,754 DEBUG SQLDataSource [builtinApplication.movimentiNonScartati] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE 
                $defaultWhereClause
                AND JF_MOVIMENTI.FL_SCART = 'F'
             
2016-05-25 14:39:10,754 DEBUG SQLDataSource [builtinApplication.movimentiNonScartati] Eval'd row count query: SELECT COUNT(*) FROM DBSALES.JF_MOVIMENTI WHERE 
                ((LOWER(JF_MOVIMENTI.CAUSAL) LIKE LOWER('%n02%') ESCAPE'\'  AND JF_MOVIMENTI.CAUSAL IS NOT NULL) AND ('0'='1'))
                AND JF_MOVIMENTI.FL_SCART = 'F'
             
2016-05-25 14:39:10,755 DEBUG PoolableSQLConnectionFactory [builtinApplication.movimentiNonScartati] makeObject() created an unpooled Connection '254276716' 
2016-05-25 14:39:10,755 DEBUG SQLConnectionManager [builtinApplication.movimentiNonScartati] Borrowed connection '254276716' 
2016-05-25 14:39:10,755 DEBUG SQLTransaction [builtinApplication.movimentiNonScartati] Started new dbJpcEP transaction "254276716" 
2016-05-25 14:39:10,755 DEBUG SQLDataSource [builtinApplication.movimentiNonScartati] Setting DSRequest as being part of a transaction 
2016-05-25 14:39:10,755 INFO  SQLDriver [builtinApplication.movimentiNonScartati] Executing SQL query on 'dbJpcEP' using connection '254276716': SELECT COUNT(*) FROM DBSALES.JF_MOVIMENTI WHERE 
                ((LOWER(JF_MOVIMENTI.CAUSAL) LIKE LOWER('%n02%') ESCAPE'\'  AND JF_MOVIMENTI.CAUSAL IS NOT NULL) AND ('0'='1'))
                AND JF_MOVIMENTI.FL_SCART = 'F'
             
2016-05-25 14:39:10,765 INFO  DSResponse DSResponse: List with 0 items

As you may see, the AdvancedCriteria uses 'iContains' for the 'EXPORTED_TS' field which is of type datetime.
The framework warns me about that:

2016-05-25 14:39:10,754 WARN  SQLWhereClause [builtinApplication.movimentiNonScartati] Attempt to compare a Date as String, testing value Fri Aug 07 11:27:36 CEST 2015 with operator iContains. This is an invalid comparison, so we are returning FALSE.

Now, to obtain the same behavior, I've got to use an AdvancedCriteria for the fetch, like this:

                movimentiGrid.fetchData(    
                    {
                        _constructor: "AdvancedCriteria",
                        operator: "and",
                        criteria: [
                            {fieldName: "EXPORTED_TS", operator: "equals", value: value}
                        ]
                    }
                );

But I'm worried that I may have this kind of problem in many places, for several applications.

Also, If I set allowFilterExpressions: false in the grid, there aren't problems. Also allowFilterOperators: true causes the same problem if you actually make a filter using a specific operator.

So the question is: am I misusing something? Or is it a bug?

[claudiobosticco]

[update]

For now I didn't succeeded in making a test case in the showcase. This is working:

isc.ListGrid.create({
    ID: "countryList",
    width:500, height:300, alternateRecordStyles:true,
    dataSource: worldDS, dataPageSize:20,
    canEdit:true, filterOnKeypress:true,
allowFilterExpressions: true,
    fields:[
        {name:"countryCode", width:50},
        {name:"countryName"},
        {name:"capital"},
        {name:"continent"},
        {name:"area"}
    ],
    showFilterEditor: true
}).fetchData({independence: new Date(2016, 4 ,25)});

but the independence field is of type date, not datetime.

[Isomorphic]

The problem in your original code appears to be that the "value" you are supplying in the simple criteria is an Array of one Date, whereas just a Date would be expected.

[claudiobosticco]

Actually, the value is from a SelectItem with multiple: true, so yes, it is an array. Are you saying that when there's only one item selected, I must pass a single value?

[Isomorphic]

Ah, sorry, an Array is actually legal there. However, looking at the involved functions, they would handle an Array of Dates as well - let us know if you isolate this to a framework bug.