Bug/Unexpected TextMatchStyle-behaviour in serverside DSRequest.addToCriteria()

[Blama]

Hi Isomorphic,

please note the following unexpected behaviour:

I have a client-side ComboBoxItem with

setTextMatchStyle(TextMatchStyle.SUBSTRING);
setValueField("ID"); [B]//integer-field[/B]
setDisplayField("NAME"); [B]// text-field[/B]

The generated SQL (so far as expected) is:

t_reseller.name LIKE '%mysearchstring%' ESCAPE '\' AND t_reseller.name IS NOT NULL

In my subclassed SQLDataSource I do:

@Override
public DSResponse executeFetch(DSRequest req) throws Exception {
	dsRequest.addToCriteria("TENANT_ID", DefaultOperators.Equals, 0); [B]// TENANT_ID is an integer-field[/B]
	return super.executeFetch(req);
}

The generated SQL looks like

AND (t_reseller.tenant_id LIKE '%0%' ESCAPE '\' AND t_reseller.tenant_id IS NOT NULL)

where I would expect a TENANT_ID=0 condition.
This is also true for an even more explicit

dsRequest.addToCriteria(new AdvancedCriteria(new SimpleCriterion("TENANT_ID", DefaultOperators.Equals, 0)));

The generated SQL for TENANT_ID changes when I change the TextMatchStyle of the ComboBoxItem. I think that this might be a bug (SNAPSHOT_v9.1d_2014-01-22/EVAL Deployment).

Best regards,
Blama

[Blama]

Hi Isomorphic,

please see the following minimal testcase based on builtInDS (using SNAPSHOT_v9.1d_2014-01-22/EVAL Deployment):

BuiltInDS.java

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.types.TextMatchStyle;
import com.smartgwt.client.widgets.form.DynamicForm;
import com.smartgwt.client.widgets.form.fields.ComboBoxItem;

public class BuiltInDS implements EntryPoint {
	private DynamicForm boundForm;

	public void onModuleLoad() {
		boundForm = new DynamicForm();

		ComboBoxItem animalCBI = new ComboBoxItem();
		animalCBI.setOptionDataSource(DataSource.get("animals"));
		animalCBI.setTextMatchStyle(TextMatchStyle.SUBSTRING);
		animalCBI.setValueField("commonName");
		animalCBI.setDisplayField("scientificName");
		boundForm.setFields(animalCBI);
		boundForm.draw();
	}

}

Animals.java (DMI):

package com.smartgwt.sample.server.listener;

import javax.servlet.http.HttpServletRequest;

import com.isomorphic.criteria.DefaultOperators;
import com.isomorphic.criteria.criterion.SimpleCriterion;
import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;

public class Animals {
	public DSResponse fetch(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
		dsRequest.addToCriteria(new SimpleCriterion("lifeSpan", DefaultOperators.Equals, 50));
		return dsRequest.execute();
	}
}

animals.ds.xml (with serverObject):

<DataSource ID="animals" serverType="sql" tableName="animals" testFileName="animals.data.xml">
	<fields>
		<field name="commonName" title="Animal" type="text" />
		<field name="scientificName" title="Scientific Name" type="text" primaryKey="true" required="true" />
		<field name="lifeSpan" title="Life Span" type="integer" />
		<field name="status" title="Endangered Status" type="text">
			<valueMap>
				<value>Threatened</value>
				<value>Endangered</value>
				<value>Not Endangered</value>
				<value>Not currently listed</value>
				<value>May become threatened</value>
				<value>Protected</value>
			</valueMap>
		</field>
		<field name="diet" title="Diet" type="text" />
		<field name="information" title="Interesting Facts" type="text" length="1000" />
		<field name="picture" title="Picture" type="image" detail="true" imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/" />
	</fields>
	<serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.Animals" />
</DataSource>

If you start and click on the arrow of the ComboBoxItem I'd expect a SQL WHERE clause like "WHERE lifeSpan = 50". Instead, generated is:

WHERE (LOWER(animals.lifeSpan) LIKE '%50%' ESCAPE '\')

Best regards,
Blama

[Isomorphic]

This should be fixed for tomorrow's builds. If criteria are simple and you explicitly pass an operator that doesn't match the dsRequest.textMatchStyle, criteria will be converted to AdvancedCriteria so that your added criteria execute with the exact operator you specified.

[Blama]

Hi Isomorphic,

I'm using SNAPSHOT_v9.1d_2014-01-23/EVAL Deployment now and still see this behaviour.

I noticed something strange:
When I don't create AdvCrit in my SQLDataSource for outerJoin includeFrom-fields, the LOWER() part works (this relates to this bug report.

When I add Criteria for outerJoin includeFrom-fields, LOWER() is not used on any field.

So the bug report is:
1) LIKE-fields created, where it should be equality
2) Removing of case-insensitivity (LOWER() removed), most likely related to outerJoining fields.

Best regards,
Blama

[Isomorphic]

We're showing expected behavior with your test case, please check your results.

Note with the "equals" AdvancedCriteria operator, you should not expect case-insensitive comparison. "iEquals" provides case-insensitive comparison.

[Blama]

Hi Isomorphic,

in my last post I was referring to my app. Just looked at the testcase. I can see changes, but it is still wrong.

Two cases:
1) Click the arrow of the CBI. It results in:

SELECT COUNT (*)
  FROM animals
 WHERE (LOWER (animals.lifespan) LIKE '%50%' ESCAPE '\' AND animals.lifespan IS NOT NULL)

(One time iContains, where it should have been Equals)



2) Reload, enter one letter and wait. It results in:

SELECT animals.commonname,
       animals.diet,
       animals.information,
       animals.lifespan,
       animals.picture,
       animals.scientificname,
       animals.status
  FROM animals
 WHERE (animals.commonname = 'b' AND animals.lifespan = 50)

(Two times Equals, where it should have been iContains and Equals)

This is tested with the testcase from above and SNAPSHOT_v9.1d_2014-01-23

Best regards,
Blama

[Blama]

Hi Isomorphic,

as update, using yesterday's SNAPSHOT_v9.1d_2014-01-25:

In my real world use-case I watch following behaviour:

My setup:

1. reseller.ds.xml w/
   • Many FK fields, some of them outer-join (most likely unrelated)
   • Operationbinding: operationId="fetchChildResellers" serverMethod="fetchChildResellers" outputs="NAME"
2. Client-side ComboBoxItem w/
   • setOptionDataSource(resellerDS);
   • setOptionOperationId("fetchChildResellers");
   • setTextMatchStyle(TextMatchStyle.SUBSTRING);
   • ListGrid props = new ListGrid(); props.setProgressiveLoading(true); setPickListProperties(props);
3. Server-side DMI "fetchChildResellers" w/
   • dsRequest.addToCriteria("ID", DefaultOperators.InSet.getID(), resellerList);
4. Server-side SQLDataSource subclass w/
   • dsRequest.addToCriteria("TENANT_ID", DefaultOperators.Equals, User.getUserTenantId(dsRequest.getHttpServletRequest()));

It results in the following for search-string "s":

• 1+2+3+4 (OK):
  WHERE ( (LOWER (t_reseller.name) LIKE LOWER ('%s%') ESCAPE '\' AND t_reseller.name IS NOT NULL)
  AND ( (t_reseller.id IN (2, 3, 4, 1, 5)) AND t_reseller.id IS NOT NULL)
  AND (t_reseller.tenant_id = 0 AND t_reseller.tenant_id IS NOT NULL))
  
• 1+2+4 (3's addToCriteria commented out, not OK):
  WHERE ( LOWER (t_reseller.name) LIKE '%s%' ESCAPE '\'
  AND t_reseller.name IS NOT NULL
  AND LOWER (t_reseller.tenant_id) LIKE '%0%' ESCAPE '\'
  AND t_reseller.tenant_id IS NOT NULL)
  
• 1+2+3 (4's addToCriteria commented out, OK):
  WHERE ( (LOWER (t_reseller.name) LIKE LOWER ('%s%') ESCAPE '\' AND t_reseller.name IS NOT NULL)
  AND ( (t_reseller.id IN (2, 3, 4, 1, 5)) AND t_reseller.id IS NOT NULL))
  
• 1+2 (3+4's addToCriteria commented out, OK):
  WHERE (LOWER (t_reseller.name) LIKE '%s%' ESCAPE '\' AND t_reseller.name IS NOT NULL)

It results in the following for no search-string (clicking the CBI's arrow):

• 1+2+3+4 (OK):
  WHERE ( ( (t_reseller.id IN (2, 3, 4, 1, 5)) AND t_reseller.id IS NOT NULL) AND (t_reseller.tenant_id = 0 AND t_reseller.tenant_id IS NOT NULL))
• 1+2+4 (3's addToCriteria commented out, not OK):
  WHERE (LOWER (t_reseller.tenant_id) LIKE '%0%' ESCAPE '\' AND t_reseller.tenant_id IS NOT NULL)
• 1+2+3 (4's addToCriteria commented out, OK):
  WHERE ( ( (t_reseller.id IN (2, 3, 4, 1, 5)) AND t_reseller.id IS NOT NULL))
• 1+2 (3+4's addToCriteria commented out, OK):
  WHERE ('1' = '1')

I hope this helps you track down the error.

Best regards,
Blama