Criteria on Integer fields generate LIKE queries

[basis.cedric]

Hello,
I'm using GWT 2.5.1 and SmartGWT Power Edition 3.1p-2013-08-05. In production I'm using SQL Server 2008 R2 with Microsoft JDBC Driver v4.
When I add a criteria on an integer the SQL surprisingly generates LIKE '%0%' queries.
This can be reproduced on the BuiltinDS application with HSQL too.
Here are the steps to reproduce the problem:
1. Create a new DataSource class:

package com.smartgwt.sample.server.listener;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.sql.SQLDataSource;

public class BuiltinDsDatasource extends SQLDataSource {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Override
	public DSResponse executeFetch(DSRequest req) throws Exception {
		req.addToCriteria("lifeSpan", 50);
		return super.executeFetch(req);
	}
}

Register this class on the animal datasource:

<DataSource
    ID="animals"
	serverType="sql"
	tableName="animals"
    testFileName="animals.data.xml"
serverConstructor="com.smartgwt.sample.server.listener.BuiltinDsDatasource"
>

When you choose the animals DS in the top grid the generated query is ok:

SELECT LIMIT 0 75  animals.commonName, animals.diet, animals.information, animals.lifeSpan, animals.picture, animals.scientificName, animals.status FROM animals WHERE (animals.lifeSpan=50)

If you use put something in the bound form and use fetch, delete what you put in the bound form and use the filter functionality, the generated query is wrong:

SELECT LIMIT 0 75  animals.commonName, animals.diet, animals.information, animals.lifeSpan, animals.picture, animals.scientificName, animals.status FROM animals WHERE (LOWER(animals.lifeSpan) LIKE '%50%' ESCAPE '\')

There shouldn't have a LIKE operator on an integer field. The problem here is that if I you change the life span of an animal to 150 it will appear in the list (at least on SQL Server 2008).

In the datasource class I've tried to put:

req.addToCriteria("lifeSpan", OperatorId.EQUALS.toString(), 50);

But with this it doesn't filter anymore the list.
I can't simply use:

req.setTextMatchStyle(TextMatchStyle.EXACT.getValue());

Because all fetch will be filtered, including the one for the grids where I want the substring behaviour.

[Isomorphic]

This is by design - substring or startsWith filtering on numeric fields is useful, so when you tell the system to do it, it does it rather than disabling any such filtering.

If you want exact match, use AdvancedCriteria client-side to configure that. If you try to only add such criteria server-side, this won't work with Adaptive Filtering since the client doesn't know about criteria added only at the server side.

[basis.cedric]

The problem with using Advanced Criteria is that I can't use it in customized where clause.
For instance if I have a customized operationBinding like that:

<tableClause>
    parlamentar 
    inner join parlamentarBloco on parlamentar.id_parlamentar = parlamentarBloco.id_parlamentar
   </tableClause>
   <whereClause>
    parlamentarBloco.id_bloco = $criteria.id_bloco
   </whereClause>

Criteria is passed that way:

[builtinApplication.buscaParlamentaresPorBloco] Performing fetch operation with
 criteria: {criteria:[{value:33,fieldName:"id_bloco",operator:"equals"},{value:1,fieldName:"excluido",operator:"notEqual"}],operator:"and",_constructor:"AdvancedCriteria"} values: {criteria:[{value:33,fieldName:"id_bloco",operator:"equals"},{value:1,fieldName:"excluido",operator:"notEqual"}],operator:"and",_constructor:"AdvancedCriteria"}

And the resulting SQL is:

SELECT PARLAMENTAR.EXCLUIDO AS excluido, PARLAMENTAR.HABILITADO AS habilitado, PARLAMENTAR.ID_PARLAMENTAR AS id, PARLAMENTAR.LOGIN AS login, PARLAMENTAR.ID_PARTIDO AS partido, PARLAMENTAR.NM_POLITICO AS politico, PARTIDO.SG_PARTIDO AS siglaPartido FROM 
    parlamentar 
    inner join parlamentarBloco on parlamentar.id_parlamentar = parlamentarBloco.id_parlamentar
    LEFT OUTER JOIN PARTIDO ON PARLAMENTAR.ID_PARTIDO = PARTIDO.ID_PARTIDO WHERE 
    parlamentarBloco.id_bloco = null

[Isomorphic]

See the SQL Templating docs - use $advancedCriteria if you are trying to access a criterion by it's fieldName and know there is only one criterion on the field.

[basis.cedric]

My bad, thanks for the tip!