SmartgGWT server generates wrong query

[abthere]

It looks like SmartgGWT server generates wrong query for the supplied AdvancedCriteria in certain cases. For example a criteria that specifies inStock filed be ‘true’, AND operator and SKU been greater than Item produces output that includes records with inStock ‘true’ and ‘false’:

criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"inStock",
                operator:"equals",
                value:true
            },
            {
                operator:"greaterThanField",
                fieldName:"SKU",
                value:"itemName"
            }
        ]
    }

Translates into this WHERE clause when applied to supplyItem table:
((supplyItem.inStock = 'true' AND supplyItem.inStock IS NOT NULL) AND (supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NULL) OR (supplyItem.SKU > supplyItem.itemName AND supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NOT NULL))
The result includes two records with inStock=’true’ and one record with inStock=’false’
Isomorphic SmartClient/SmartGWT Framework (v8.3p_2012-12-02/Enterprise Deployment 2012-12-02)

Test case based on builtinDS example that can be executed against sample database:

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.data.AdvancedCriteria;
import com.smartgwt.client.data.Criterion;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.types.OperatorId;
import com.smartgwt.client.types.TopOperatorAppearance;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.form.FilterBuilder;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.layout.VStack;

/**
 * Entry point classes define <code>onModuleLoad()</code>.
 */
public class BuiltInDS implements EntryPoint {

	/**
	 * This is the entry point method.
	 */
	public void onModuleLoad() {
		DataSource ds = DataSource.get("supplyItem");

		final FilterBuilder filterBuilder = new FilterBuilder();
		filterBuilder.setDataSource(ds);
		filterBuilder.setTopOperatorAppearance(TopOperatorAppearance.RADIO);

		AdvancedCriteria criteria = new AdvancedCriteria(OperatorId.AND, new Criterion[] {
				new Criterion("inStock", OperatorId.EQUALS, true),
				new Criterion("SKU", OperatorId.GREATER_THAN_FIELD, "itemName") });

		filterBuilder.setCriteria(criteria);
		
		final ListGrid grid = new ListGrid();
		
		
		grid.setWidth(550);
		grid.setHeight(224);
		grid.setDataSource(ds);

		IButton filterButton = new IButton("Filter");
		filterButton.addClickHandler(new ClickHandler() {
			public void onClick(ClickEvent event) {
				grid.filterData(filterBuilder.getCriteria());
			}
		});

		VStack vStack = new VStack(10);
		vStack.addMember(filterBuilder);
		vStack.addMember(filterButton);
		vStack.addMember(grid);

		vStack.draw();
		
		grid.filterData(filterBuilder.getCriteria());
	}
}

Server log:
[WARN] Server class 'com.isomorphic.base.InitListener' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/isomorphic_core_rpc.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
[WARN] Server class 'org.hsqldb.Server' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/hsqldb-1.8.0.10.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
[Server@31fd5a2c]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@31fd5a2c]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@31fd5a2c]: Startup sequence initiated from main() method
[Server@31fd5a2c]: Loaded properties from [C:\work\smartgwtee-3.1p\samples\test-case\war\server.properties]
[Server@31fd5a2c]: Initiating startup sequence...
[Server@31fd5a2c]: Server socket opened successfully in 1 ms.
[Server@31fd5a2c]: Database [index=0, id=0, db=file:C:\work\smartgwtee-3.1p\samples\test-case\war\WEB-INF\db\hsqldb\isomorphic, alias=isomorphic] opened sucessfully in 1180 ms.
[Server@31fd5a2c]: Startup sequence completed in 1182 ms.
[Server@31fd5a2c]: 2012-12-17 13:49:11.332 HSQLDB server 1.8.0 is online
[Server@31fd5a2c]: To close normally, connect and execute SHUTDOWN SQL
[Server@31fd5a2c]: From command line, use [Ctrl]+[C] to abort abruptly
ISC: Configuring log4j from: file:/C:/work/smartgwtee-3.1p/samples/test-case/war/WEB-INF/classes/log4j.isc.config.xml
[WARN] Server class 'org.apache.commons.collections.map.LinkedMap' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-collections-3.2.1.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:11,483 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework initialization called from com.isomorphic.base.InitListener
=== 2012-12-17 13:49:11,484 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework - Initializing
=== 2012-12-17 13:49:11,489 [main] INFO ConfigLoader - Attempting to load framework.properties from CLASSPATH
[WARN] Server class 'isc.org.apache.oro.text.perl.Perl5Util' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/isc-jakarta-oro-2.0.6.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:11,626 [main] INFO ConfigLoader - Successfully loaded framework.properties from CLASSPATH at location: jar:file:/C:/work/smartgwtee-3.1p/lib/isomorphic_core_rpc.jar!/framework.properties
=== 2012-12-17 13:49:11,626 [main] INFO ConfigLoader - Attempting to load project.properties from CLASSPATH
=== 2012-12-17 13:49:11,627 [main] INFO ConfigLoader - Unable to locate project.properties in CLASSPATH
=== 2012-12-17 13:49:11,631 [main] INFO ConfigLoader - Successfully loaded isc_interfaces.properties from CLASSPATH at location: jar:file:/C:/work/smartgwtee-3.1p/lib/isomorphic_core_rpc.jar!/isc_interfaces.properties
=== 2012-12-17 13:49:11,632 [main] INFO ConfigLoader - Attempting to load server.properties from CLASSPATH
=== 2012-12-17 13:49:11,634 [main] INFO ConfigLoader - Successfully loaded server.properties from CLASSPATH at location: file:/C:/work/smartgwtee-3.1p/samples/test-case/war/WEB-INF/classes/server.properties
=== 2012-12-17 13:49:11,639 [main] INFO Logger - Logging system started.
=== 2012-12-17 13:49:11,640 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework (v8.3p_2012-12-02/Enterprise Deployment 2012-12-02) - Initialization Complete
=== 2012-12-17 13:49:11,641 [main] INFO ISCInit - Auto-detected webRoot - using: C:\work\smartgwtee-3.1p\samples\test-case\war
=== 2012-12-17 13:49:11,657 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework initialization called from com.isomorphic.base.Base
=== 2012-12-17 13:49:11,657 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework is already initialized
[WARN] Server class 'org.hibernate.proxy.HibernateProxy' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/hibernate3.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
[WARN] Server class 'net.sf.cglib.transform.impl.InterceptFieldEnabled' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/cglib-2.2.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
[WARN] Server class 'com.isomorphic.compression.ServletCompression' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/isomorphic_compression.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
log4j:WARN No appenders could be found for logger (org.apache.jasper.compiler.JspRuntimeContext).
log4j:WARN Please initialize the log4j system properly.
=== 2012-12-17 13:49:11,743 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework initialization called from com.isomorphic.base.Init
=== 2012-12-17 13:49:11,743 [main] INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework is already initialized
[WARN] Server class 'org.apache.commons.fileupload.FileItemFactory' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-fileupload-1.2.1.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:17,131 [l0-2] INFO RequestContext - URL: '/builtinds/sc/DataSourceLoader', User-Agent: 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0': Moz (Gecko) with Accept-Encoding header
[WARN] Server class 'org.apache.commons.pool.impl.GenericKeyedObjectPool' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-pool-1.4.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:17,204 [l0-2] INFO PoolManager - SmartClient pooling disabled for 'supplyItem' objects
=== 2012-12-17 13:49:17,225 [l0-3] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\load_skin.js
[WARN] Server class 'org.apache.commons.jxpath.AbstractFactory' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-jxpath-1.3.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:17,263 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\builtinTypes.xml: 9ms
=== 2012-12-17 13:49:17,264 [l0-0] INFO Compression - /builtinds/sc/modules/ISC_Core.js: 783134 -> 204964 bytes
=== 2012-12-17 13:49:17,384 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\ds\supplyItem.ds.xml: 4ms
=== 2012-12-17 13:49:17,393 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\DataSource.ds.xml: 8ms
[WARN] Server class 'org.apache.velocity.context.Context' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/velocity-1.6.1.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:17,441 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\DataSourceField.ds.xml: 5ms
=== 2012-12-17 13:49:17,469 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\ValueMap.ds.xml: 2ms
=== 2012-12-17 13:49:17,475 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\Validator.ds.xml: 3ms
[WARN] Server class 'com.isomorphic.sql.SQLDataSource' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/isomorphic_sql.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:17,577 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\ds\animals.ds.xml: 3ms
=== 2012-12-17 13:49:17,595 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\ds\employees.ds.xml: 3ms
=== 2012-12-17 13:49:17,610 [l0-2] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\OperationBinding.ds.xml: 4ms
=== 2012-12-17 13:49:17,863 [l0-2] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\skin_styles.css
=== 2012-12-17 13:49:26,583 [l0-6] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\actions\remove.png
=== 2012-12-17 13:49:26,587 [l0-2] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\pickers\comboBoxPicker.png
=== 2012-12-17 13:49:26,594 [l0-3] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\DynamicForm\checked.png
=== 2012-12-17 13:49:26,596 [l0-5] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\actions\add.png
=== 2012-12-17 13:49:26,596 [l0-0] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\blank.gif
=== 2012-12-17 13:49:26,664 [l0-0] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\loadingSmall.gif
=== 2012-12-17 13:49:26,713 [l0-0] INFO RequestContext - URL: '/builtinds/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0': Moz (Gecko) with Accept-Encoding header
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Host:127.0.0.1:8888
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Accept-Language:en-US,en;q=0.5
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Referer:http://127.0.0.1:8888/BuiltInDS.html?gwt.codesvr=127.0.0.1:9997
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Cookie:GLog=%7B%0D%20%20%20%20left%3A-1200%2C%20%0D%20%20%20%20top%3A152%2C%20%0D%20%20%20%20width%3A1182%2C%20%0D%20%20%20%20height%3A1196%2C%20%0D%20%20%20%20priorityDefaults%3A%7B%0D%20%20%20%20%20%20%20%20Log%3A4%2C%20%0D%20%20%20%20%20%20%20%20TreeGrid%3A5%2C%20%0D%20%20%20%20%20%20%20%20ListGrid%3A5%2C%20%0D%20%20%20%20%20%20%20%20RPCManager%3A3%2C%20%0D%20%20%20%20%20%20%20%20RPCManagerResponse%3A3%0D%20%20%20%20%7D%2C%20%0D%20%20%20%20defaultPriority%3A3%2C%20%0D%20%20%20%20trackRPC%3Atrue%0D%7D; SPRING_SECURITY_REMEMBER_ME_COOKIE=VVNFUjc6MTM1NTc4MjQyNjMxMzo1NTMzMmUyOTg4OTI4MjM3NWE4MzljNmI1Y2Q4Y2ViZg; JSESSIONID=8nmdivhnfcss; isc_cState=ready
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Content-Length:2062
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - session exists: null
=== 2012-12-17 13:49:26,720 [l0-0] DEBUG IDACall - remote user: null
[WARN] Server class 'org.apache.commons.codec.net.URLCodec' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-codec-1.3.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:26,742 [l0-0] DEBUG XML - Parsed XML from (in memory stream): 5ms
=== 2012-12-17 13:49:26,747 [l0-0] DEBUG XML - Parsed XML from C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\system\schema\List.ds.xml: 2ms
=== 2012-12-17 13:49:26,753 [l0-0] DEBUG RPCManager - Processing 1 requests.
=== 2012-12-17 13:49:26,768 [l0-0] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
_constructor:"AdvancedCriteria",
operator:"and",
criteria:[
{
fieldName:"inStock",
operator:"equals",
value:true
},
{
operator:"greaterThanField",
fieldName:"SKU",
value:"itemName"
}
]
},
operationConfig:{
dataSource:"supplyItem",
operationType:"fetch",
textMatchStyle:"substring"
},
startRow:0,
endRow:75,
componentId:"isc_ListGrid_0",
appID:"builtinApplication",
operation:"supplyItem_fetch",
oldValues:{
_constructor:"AdvancedCriteria",
operator:"and",
criteria:[
{
fieldName:"inStock",
operator:"equals",
value:true
},
{
operator:"greaterThanField",
fieldName:"SKU",
value:"itemName"
}
]
}
}
=== 2012-12-17 13:49:26,774 [l0-0] INFO IDACall - Performing 1 operation(s)
=== 2012-12-17 13:49:26,776 [l0-0] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2012-12-17 13:49:26,776 [l0-0] DEBUG DeclarativeSecurity - DataSource supplyItem is not in the pre-checked list, processing...
=== 2012-12-17 13:49:26,784 [l0-0] DEBUG AppBase - [builtinApplication.supplyItem_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-12-17 13:49:26,785 [l0-0] DEBUG AppBase - [builtinApplication.supplyItem_fetch] No public zero-argument method named '_supplyItem_fetch' found, performing generic datasource operation
=== 2012-12-17 13:49:26,785 [l0-0] INFO SQLDataSource - [builtinApplication.supplyItem_fetch] Performing fetch operation with
criteria: {criteria:[{fieldName:"inStock",operator:"equals",value:true},{operator:"greaterThanField",fieldName:"SKU",value:"itemName"}],operator:"and",_constructor:"AdvancedCriteria"} values: {criteria:[{fieldName:"inStock",operator:"equals",value:true},{operator:"greaterThanField",fieldName:"SKU",value:"itemName"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2012-12-17 13:49:26,800 [l0-0] INFO SQLDataSource - [builtinApplication.supplyItem_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
[WARN] Server class 'org.apache.commons.lang.StringUtils' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/commons-lang-2.4.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:26,912 [l0-0] DEBUG SQLDataSource - [builtinApplication.supplyItem_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2012-12-17 13:49:26,913 [l0-0] DEBUG SQLDataSource - [builtinApplication.supplyItem_fetch] Eval'd row count query: SELECT COUNT(*) FROM supplyItem WHERE ((supplyItem.inStock = 'true' AND supplyItem.inStock IS NOT NULL) AND (supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NULL) OR (supplyItem.SKU > supplyItem.itemName AND supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NOT NULL))
[WARN] Server class 'com.isomorphic.hibernate.HibernateDataSource' could not be found in the web app, but was found on the system classpath
[WARN] Adding classpath entry 'file:/C:/work/smartgwtee-3.1p/lib/isomorphic_hibernate.jar' to the web app classpath for this session
For additional info see: file:/C:/work/gwt-2.4.0/doc/helpInfo/webAppClassPath.html
=== 2012-12-17 13:49:26,987 [l0-0] INFO PoolManager - [builtinApplication.supplyItem_fetch] SmartClient pooling disabled for 'HSQLDB' objects
=== 2012-12-17 13:49:26,988 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] Initializing SQL config for 'HSQLDB' from system config - using DriverManager: org.hsqldb.jdbcDriver
=== 2012-12-17 13:49:26,989 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] org.hsqldb.jdbcDriver lookup successful
=== 2012-12-17 13:49:26,989 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
=== 2012-12-17 13:49:26,989 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] Passing JDBC URL only to getConnection
=== 2012-12-17 13:49:26,996 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] makeObject() created an unpooled Connection '638726281'
=== 2012-12-17 13:49:26,996 [l0-0] DEBUG SQLConnectionManager - [builtinApplication.supplyItem_fetch] Returning borrowed connection '638726281'
=== 2012-12-17 13:49:26,996 [l0-0] DEBUG SQLDriver - [builtinApplication.supplyItem_fetch] About to execute SQL query in 'HSQLDB' using connection '638726281'
=== 2012-12-17 13:49:26,996 [l0-0] INFO SQLDriver - [builtinApplication.supplyItem_fetch] Executing SQL query on 'HSQLDB': SELECT COUNT(*) FROM supplyItem WHERE ((supplyItem.inStock = 'true' AND supplyItem.inStock IS NOT NULL) AND (supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NULL) OR (supplyItem.SKU > supplyItem.itemName AND supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NOT NULL))
=== 2012-12-17 13:49:27,224 [l0-0] DEBUG SQLDataSource - [builtinApplication.supplyItem_fetch] Using SQL Limit query
=== 2012-12-17 13:49:27,224 [l0-0] DEBUG SQLDataSource - [builtinApplication.supplyItem_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT LIMIT 0 75 supplyItem.SKU, supplyItem.category, supplyItem.description, supplyItem.inStock, supplyItem.itemID, supplyItem.itemName, supplyItem.nextShipment, supplyItem.unitCost, supplyItem.units FROM supplyItem WHERE ((supplyItem.inStock = 'true' AND supplyItem.inStock IS NOT NULL) AND (supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NULL) OR (supplyItem.SKU > supplyItem.itemName AND supplyItem.SKU IS NOT NULL AND supplyItem.itemName IS NOT NULL))
=== 2012-12-17 13:49:27,224 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
=== 2012-12-17 13:49:27,224 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] Passing JDBC URL only to getConnection
=== 2012-12-17 13:49:27,226 [l0-0] DEBUG PoolableSQLConnectionFactory - [builtinApplication.supplyItem_fetch] makeObject() created an unpooled Connection '1459331759'
=== 2012-12-17 13:49:27,226 [l0-0] DEBUG SQLConnectionManager - [builtinApplication.supplyItem_fetch] Returning borrowed connection '1459331759'
=== 2012-12-17 13:49:27,317 [l0-0] INFO DSResponse - [builtinApplication.supplyItem_fetch] DSResponse: List with 3 items
=== 2012-12-17 13:49:27,317 [l0-0] DEBUG SQLConnectionManager - [builtinApplication.supplyItem_fetch] About to close jdbcConnection with hashcode "1459331759"
=== 2012-12-17 13:49:27,318 [l0-0] DEBUG SQLConnectionManager - About to close jdbcConnection with hashcode "638726281"
=== 2012-12-17 13:49:27,319 [l0-0] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2012-12-17 13:49:27,321 [l0-0] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2012-12-17 13:49:27,326 [l0-0] INFO Compression - /builtinds/sc/IDACall: 892 -> 551 bytes
=== 2012-12-17 13:49:27,703 [l0-0] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\DynamicForm\checked_Disabled.png
=== 2012-12-17 13:49:27,706 [l0-5] INFO Download - Returning 304: Not modified on conditional get of: C:\work\smartgwtee-3.1p\samples\test-case\war\builtinds\sc\skins\Enterprise\images\DynamicForm\unchecked_Disabled.png

[Isomorphic]

It looks like you might have configured your system using dataSource.strictSQLFiltering? If so, change this back to the default to get rational behavior. For details on why strictSQLFiltering produces such odd results, see the docs for the property.

[abthere]

I used supplyItem.ds.xml from builtinDS sample without any modifications. But setting strictSQLFiltering to false or true doesn't change the output of the generated query. It still includes wrong records. Here's supplyItem.ds.xml:

<DataSource
    ID="supplyItem"
    serverType="sql"
    tableName="supplyItem"
    titleField="itemName"
    testFileName="/examples/shared/ds/test_data/supplyItem.data.xml"
    dbImportFileName="/examples/shared/ds/test_data/supplyItemLarge.data.xml"
    strictSQLFiltering="false"
>
    <fields>
        <field name="itemID"      type="sequence" hidden="true"       primaryKey="true"/>
        <field name="itemName"    type="text"     title="Item"        length="128"       required="true"/>
        <field name="SKU"         type="text"     title="SKU"         length="10"        required="true"/>
        <field name="description" type="text"     title="Description" length="2000"/>
        <field name="category"    type="text"     title="Category"    length="128"       required="true"
               foreignKey="supplyCategory.categoryName"/>
        <field name="units"       type="enum"     title="Units"       length="5">
            <valueMap>
                <value>Roll</value>
                <value>Ea</value>
                <value>Pkt</value>
                <value>Set</value>
                <value>Tube</value>
                <value>Pad</value>
                <value>Ream</value>
                <value>Tin</value>
                <value>Bag</value>
                <value>Ctn</value>
                <value>Box</value>
            </valueMap>
        </field>
        <field name="unitCost"    type="float"    title="Unit Cost"   required="true">
            <validators>
                <validator type="floatRange" min="0" errorMessage="Please enter a valid (positive) cost"/>
                <validator type="floatPrecision" precision="2" errorMessage="The maximum allowed precision is 2"/>
            </validators>
        </field>
        <field name="inStock"   type="boolean"  title="In Stock"/>
        <field name="nextShipment"  type="date" title="Next Shipment"/>
    </fields>
</DataSource>

[abthere]

setting strictSQLFiltering is not working in ds.xml

It looks like the observed behavior can be attributed to strictSQLFiltering been set to true. This is confusing since the default documented to be false http://www.smartclient.com/smartgwt/javadoc/com/smartgwt/client/data/DataSource.html#setStrictSQLFiltering(java.lang.Boolean) We did some digging around this and turns out that it's impossible to change the attribute as outlined here http://forums.smartclient.com/showthread.php?t=24729
setting AdvancedCriteria strictSQLFiltering attribute to true or false doesn't change the generated query. It still returns rows with both true and false inStock values.

[Isomorphic]

This incorrect behavior was caused by a bug introduced as part of SQLDataSource support for field comparisons (greaterThanField, etc) a few months ago. We have now corrected it; the fix will be in nightly builds on 4.0d and 3.1p branches as of tomorrow, 12/19.