Hi,
I'm using SmartClient Version: v8.3p_2014-02-04/PowerEdition Deployment (built 2014-02-04) (SmartGWT 3.1p) with PostgreSQL 9.2.2, Firefox 26.0, and Tomcat 7.0.33.
I have a ListGrid that supports several text fields and a filter editor. The ListGrid uses a sql datasource defined in a ds.xml file. Some of the text fields support arbitrary values. In most cases, everything works fine. When a text field includes an underscore (_) character and the user does a contains filter which includes the underscore character, the filter does not give the correct result. The result is correct if the filtering is done locally, but if the query goes to the server, the result is not correct. When it isn't correct, no rows are retrieved.
This is the log output when the ListGrid tries to filter on the groups field with the value group_A:
The interesting parts from the log are:
In the first line the filter criteria from the filter editor is: {fieldName:"groups",operator:"iContains",value:"group_A"}
In the last line this filter criteria is represented as: LIKE LOWER('%group\\_a%')
This interpretation of 'iContains group_A' is incorrect. The underscore is the postgres single character wild card so it should be escaped since I want a literal underscore. However, it is escaped twice so what I am filtering for is actually wildcard characters followed by the literal string group\ followed by one wildcard character followed by the character a followed by wildcard characters. With this filter, the query returns 0 rows. I believe the underscore character should only be escaped once.
Please help with this problem.
Thanks,
Kevin
I'm using SmartClient Version: v8.3p_2014-02-04/PowerEdition Deployment (built 2014-02-04) (SmartGWT 3.1p) with PostgreSQL 9.2.2, Firefox 26.0, and Tomcat 7.0.33.
I have a ListGrid that supports several text fields and a filter editor. The ListGrid uses a sql datasource defined in a ds.xml file. Some of the text fields support arbitrary values. In most cases, everything works fine. When a text field includes an underscore (_) character and the user does a contains filter which includes the underscore character, the filter does not give the correct result. The result is correct if the filtering is done locally, but if the query goes to the server, the result is not correct. When it isn't correct, no rows are retrieved.
This is the log output when the ListGrid tries to filter on the groups field with the value group_A:
Code:
=== 2014-02-28 11:12:37,462 [0-12] INFO SQLDataSource - [builtinApplication.getConsumptionData] Performing fetch operation with
criteria: {criteria:[{fieldName:"startTime",operator:"equals",value:new Date(1391014800000)},{fieldName:"endTime",operator:"equals",value:new Date(1393606800000)},{fieldName:"groups",operator:"iContains",value:"group_A"}],operator:"and",_constructor:"AdvancedCriteria"} values: {criteria:[{fieldName:"startTime",operator:"equals",value:new Date(1391014800000)},{fieldName:"endTime",operator:"equals",value:new Date(1393606800000)},{fieldName:"groups",operator:"iContains",value:"group_A"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2014-02-28 11:12:37,462 [0-12] INFO SQLDataSource - [builtinApplication.getConsumptionData] derived query: SELECT t5.name, sites.siteid, sites.identifier, sites.nodeId, array_to_string(array(select name from groups join sitegroups on groups.id = sitegroups.groupid where sitegroups.siteid = sites.siteid), ', ') as groups FROM sites left outer join nodes t0 on sites.nodeid = t0.nodeid left outer join amrSiteData t5 on sites.siteId = t5.siteId WHERE (sites.serviceType = 'Water' or sites.serviceType = 'Other') and t0.nodetype = 4 and $defaultWhereClause
=== 2014-02-28 11:12:37,462 [0-12] DEBUG SQLDataSource - [builtinApplication.getConsumptionData] Using SQL Limit query
=== 2014-02-28 11:12:37,462 [0-12] DEBUG SQLDataSource - [builtinApplication.getConsumptionData] SQL windowed select rows 0->75, result size 76. Query: SELECT t5.name, sites.siteid, sites.identifier, sites.nodeId, array_to_string(array(select name from groups join sitegroups on groups.id = sitegroups.groupid where sitegroups.siteid = sites.siteid), ', ') as groups FROM sites left outer join nodes t0 on sites.nodeid = t0.nodeid left outer join amrSiteData t5 on sites.siteId = t5.siteId WHERE (sites.serviceType = 'Water' or sites.serviceType = 'Other') and t0.nodetype = 4 and ('1'='1' AND '1'='1' AND (LOWER(array_to_string(array(select name from groups join sitegroups on groups.id = sitegroups.groupid where sitegroups.siteid = sites.siteid), ', ')) LIKE LOWER('%group\\_a%') AND array_to_string(array(select name from groups join sitegroups on groups.id = sitegroups.groupid where sitegroups.siteid = sites.siteid), ', ') IS NOT NULL)) OFFSET 0 LIMIT 76
In the first line the filter criteria from the filter editor is: {fieldName:"groups",operator:"iContains",value:"group_A"}
In the last line this filter criteria is represented as: LIKE LOWER('%group\\_a%')
This interpretation of 'iContains group_A' is incorrect. The underscore is the postgres single character wild card so it should be escaped since I want a literal underscore. However, it is escaped twice so what I am filtering for is actually wildcard characters followed by the literal string group\ followed by one wildcard character followed by the character a followed by wildcard characters. With this filter, the query returns 0 rows. I believe the underscore character should only be escaped once.
Please help with this problem.
Thanks,
Kevin
Comment