Datasource field of enum type translated incorrectly ('0' = '1' OR ...)

[leeyuiwah]

Using smartgwt smartgwtpower-5.0p.
In our datasouce, among other DatasourceField definitions, the following 3 are of following type.

<field name="status" type="enum" title="Status" width="8%" defaultValue="active, acknowledged" multiple="true" >
<field name="severity" type="enum" title="severity" width="5%" initSortColumn="true" multiple="true" >
<field name="createtime" type="datetime" title="Time" width="12%" initSortColumn="true" customCriteriaExpression="(createtime #if ($criteriaOperator == 'greaterOrEqual') &gt;= #else &lt;= #end UNIX_TIMESTAMP($criteriaValue))" />

The column 'createtime' is stored as a numeric value in the database but being converted to datetime on the display.

Now, here is the problem. When we do not specify any start/end time values in the filter criteria, the where clause is getting generated correctly.
WHERE (((system_event_table.status LIKE '%active%' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%acknowledged%' AND system_event_table.status IS NOT NULL)) AND ((system_event_table.severity='crit') OR (system_event_table.severity='maj') OR (system_event_table.severity='min') OR (system_event_table.severity='warn')))

However when we specify in the GUI filter editor the time interval for createtime, the where clause looks as shown below. It appears that createtime is getting represented correctly. However, the values of enum types are not handled correctly (for both severity and status fields)

WHERE ((('0'='1' OR '0'='1' OR '0'='1' OR '0'='1') OR ('0'='1' OR '0'='1' OR '0'='1' OR '0'='1') OR ('0'='1' OR '0'='1' OR '0'='1' OR '0'='1') OR ('0'='1' OR '0'='1' OR '0'='1' OR '0'='1')) AND (createtime >= UNIX_TIMESTAMP('2015-02-18 00:00:00')) AND (((system_event_table.status = 'active' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE 'active, %' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%, active, %' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%, active' AND system_event_table.status IS NOT NULL)) OR ((system_event_table.status = 'acknowledged' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE 'acknowledged, %' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%, acknowledged, %' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%, acknowledged' AND system_event_table.status IS NOT NULL))))

Appreciate your help...
Thanks

[leeyuiwah]

I have figured out a work around. The problem was that we had defined the field as "enum"
<field name="severity" type="enum" ...>
It appears that when the where clause is formulated, you treat an enum as an integer. But in mysql, the enum are defined as strings. So, when I change field type to be of text, the query is fine.
i.e.,
<field name="severity" type="text" ...>

Not sure if the work-around is a good idea or would you
consider this a bug (i.e., considering an enum column as mapping to integer column in the database - which is not correct for mysql).

[leeyuiwah]

further mode, I'd like to point out that we were using smartgwtpower 3.1 and when we defined the fields as enum (i.e.,)
<field name="status" type="enum">
the queries were formed correctly (as shown below)
WHERE (((setbl.severity IN ('crit', 'maj', 'min', 'warn')) AND setbl.severity IS NOT NULL) AND (createtime >= UNIX_TIMESTAMP('2015-01-27 00:00:00')) AND (createtime <= UNIX_TIMESTAMP('2015-02-21 00:00:00'))))
When we switched over to smartgwtpower 5.1p, the queries started having constructs such as
WHERE ((('0'='1' OR '0'='1' OR '0'='1' OR '0'='1') OR ...
When we changed the type from enum to text the query works but it is inefficient.
WHERE ((((setbl.severity = 'crit' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE 'crit, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, crit, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, crit' AND setbl.severity IS NOT NULL)) OR ((setbl.severity = 'maj' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE 'maj, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, maj, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, maj' AND setbl.severity IS NOT NULL)) OR ((setbl.severity = 'min' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE 'min, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, min, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, min' AND setbl.severity IS NOT NULL)) OR ((setbl.severity = 'warn' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE 'warn, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, warn, %' AND setbl.severity IS NOT NULL) OR (setbl.severity LIKE '%, warn' AND setbl.severity IS NOT NULL))) AND (createtime >= UNIX_TIMESTAMP('2015-01-27 00:00:00')

The query formed in 3.1p is concise and readable...

Please reply

[Isomorphic]

You're missing a lot of required information:

1. full version (not just 5.0p)

2. complete server log for the misbehaving request

3. what the request looks like in the RPC tab of the Developer Console

However, as a more basic problem, type="enum" is for fields that declare a valueMap (see docs). It looks like this field should be declared as type="text". As far as a difference from 3.1, your usage was incorrect for 3.1 as well, now this incorrect usage happens to fail.

[leeyuiwah]

You're missing a lot of required information:

1. full version (not just 5.0p)

2. complete server log for the misbehaving request

3. what the request looks like in the RPC tab of the Developer Console

However, as a more basic problem, type="enum" is for fields that declare a valueMap (see docs). It looks like this field should be declared as type="text". As far as a difference from 3.1, your usage was incorrect for 3.1 as well, now this incorrect usage happens to fail.

Tried using the latest download: v10.0p_2015-02-25/PowerEdition Deployment 2015-02-25

The Datasource field is defined as shown below (valueMap is provided).

<field name="severity" type="text" title="severity" hidden="false" width="5%" initSortColumn="true" multiple="true" >
<valueMap>
<value ID="crit">Critical</value>
<value ID="maj">Major</value>
<value ID="min">Minor</value>
<value ID="warn">Warn</value>
<value ID="info">Info</value>
<value ID="clear">Clear</value>
<value ID="other">Other</value>
</valueMap>
</field>

<field name="status" type="enum" title="Status" hidden="false" width="8%" multiple="true" >
<valueMap>
<value>active</value>
<value>reset_cleared</value>
<value>manual_cleared</value>
<value>auto_cleared</value>
<value>acknowledged</value>
</valueMap>
</field>

[java] === 2015-02-25 11:05:35,440 [0-36] DEBUG RPCManager - Request #1 (DSRequest) payload: {
[java] criteria:{
[java] status:[
[java] "active",
[java] "acknowledged"
[java] ],
[java] severity:[
[java] "crit",
[java] "maj",
[java] "min",
[java] "warn"
[java] ]
[java] },
[java] operationConfig:{
[java] dataSource:"sysEventsDS",
[java] repo:null,
[java] operationType:"fetch",
[java] textMatchStyle:"substring"
[java] },
[java] startRow:0,
[java] endRow:75,
[java] sortBy:[
[java] "severity",
[java] "-createtime"
[java] ],
[java] componentId:"isc_ListGrid_0",
[java] appID:"builtinApplication",
[java] operation:"sysEventsDS_fetch",
[java] oldValues:{
[java] status:[
[java] "active",
[java] "acknowledged"
[java] ],
[java] severity:[
[java] "crit",
[java] "maj",
[java] "min",
[java] "warn"
[java] ]
[java] }
[java] }

[java] === 2015-02-25 11:05:35,441 [0-36] DEBUG MultipleFieldCriteriaTransformer - [builtinApplication.sysEventsDS_fetch] Transformed criteria {
[java] status:[
[java] "active",
[java] "acknowledged"
[java] ],
[java] severity:[
[java] "crit",
[java] "maj",
[java] "min",
[java] "warn"
[java] ]
[java] }
[java] === 2015-02-25 11:05:35,441 [0-36] INFO SQLDataSource - [builtinApplication.sysEventsDS_fetch] Performing fetch operation with
[java] criteria: {status:["active","acknowledged"],severity:["crit","maj","min","warn"]} values: {status:["active","acknowledged"],severity:["crit","maj","min","warn"]}

Generated where clause:
[java] WHERE (((system_event_table.status LIKE '%active%' AND system_event_table.status IS NOT NULL) OR (system_event_table.status LIKE '%acknowledged%' AND system_event_table.status IS NOT NULL)) AND ((system_event_table.severity LIKE '%crit%' AND system_event_table.severity IS NOT NULL) OR (system_event_table.severity LIKE '%maj%' AND system_event_table.severity IS NOT NULL) OR (system_event_table.severity LIKE '%min%' AND system_event_table.severity IS NOT NULL) OR (system_event_table.severity LIKE '%warn%' AND system_event_table.severity IS NOT NULL)))

Wildcard search using LIKE '%...%' can cause performance problems in mysql.
A value search (e.g., status='maj') is a string lookup as opposed to (LIKE '%maj%')
is a regular expression match which is far less efficient, especially if we are dealing with large tables.

[leeyuiwah]

Response please...

Response please...

[Isomorphic]

A developer is scheduled to look at this report - we'll respond on this thread when we know more.

Thanks,
Isomorphic Software Support

[Isomorphic]

There are a number of things combining here to give you these odd results, but the basic problem is that you are marking the dataSourceFields as multiple: true, when in fact they are not.

As the documentation for DataSourceField.multiple says, it "indicates that this field should always be Array-valued". You don't want an array-valued field, you just want to be able to filter based on multiple valid values for a particular single-valued field. In other words, you want to be able to say "include all records where severity is 'crit' or 'maj'"; you don't want a single record that has a severity of both 'crit' and 'maj', which is what multiple: true is intended to be used for.

So, what you characterize as an "inefficient" query is actually the minimal query necessary to include all intersections of the multiple criteria you have specified, and the multi-valued fields the framework thinks it is querying against. The reason for the difference from 3.1, by the way, is that we have introduced simple storage options for multiple: true fields since that release.

The solution to this is to remove the multiple="true" indication from your dataSource fields. You can retain the behavior of a multi-select widget in the filter by specifying multiple: true in the ListGridField's filter properties, like this:

ListGridField severity = new ListGridField("severity");
SelectItem properties = new SelectItem();
properties.setMultiple(true);
severity.setFilterEditorProperties(properties);