Incorrect advancedCriteria query

[edulid]

I am getting incorrect results from the server-side AdvancedCriteria:

EntryPoint:

public class TestingModule implements EntryPoint {

	@Override
	public void onModuleLoad() {

		ListGrid lg = new ListGrid();
		lg.setDataSource(DataSource.get("table"));

		ListGridField idField = new ListGridField("f_id");
		idField.setTitle("ID");

		lg.setFields(idField);

		lg.setWidth100();
		lg.setHeight100();

		final VStack layout = new VStack(10);
		layout.setWidth100();
		layout.setHeight100();
		layout.setMembers(lg);

		lg.fetchData();

		layout.draw();
	}

}

table.ds.xml:

<DataSource ID="table" serverType="sql" tableName="t_vertrag">
	<fields>
		<field name="f_id" type="sequence" primaryKey="true" />

		<field name="f_vertrag_gekuendigt" type="date" />
		<field name="f_vertrag_beginn" type="date" />
		<field name="f_vertragsende" type="date" />

	</fields>

	<operationBindings>
		<operationBinding operationType="fetch">
			<serverObject className="de.mks_infofabrik.kids.server.dmi.TestDMIHandler"
				methodName="doFetch" />
		</operationBinding>

	</operationBindings>

</DataSource>

TestDMIHandler:

public class TestDMIHandler {

	public DSResponse doFetch(DSRequest dsRequest) throws Exception {

		dsRequest.setAdvancedCriteria(getSchuelerCriteria(new Date()));
		DSResponse response = dsRequest.execute();

		return response;
	}

	private AdvancedCriteria getSchuelerCriteria(Date stichtag) {
		AdvancedCriteria vertragGekuendigtCriteria = new AdvancedCriteria(
				DefaultOperators.Or.getID(), new Criterion[] {
						new IsNullCriterion("f_vertrag_gekuendigt"),
						new SimpleCriterion("f_vertrag_gekuendigt",
								DefaultOperators.LessThan.getID(), stichtag) });
		AdvancedCriteria vertragZeitraumCriteria = new AdvancedCriteria(
				DefaultOperators.And.getID(),
				new Criterion[] {
						new SimpleCriterion("f_vertrag_beginn",
								DefaultOperators.LessOrEqual.getID(), stichtag),
						new SimpleCriterion("f_vertragsende",
								DefaultOperators.GreaterOrEqual.getID(),
								stichtag) });

		AdvancedCriteria schuelerCriteria = new AdvancedCriteria(
				DefaultOperators.And.getID(),
				vertragGekuendigtCriteria.asCriterion(),
				vertragZeitraumCriteria.asCriterion());

		return schuelerCriteria;
	}

}

The result query, which is incorrect:

SELECT   t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, 
t_vertrag.f_vertrag_beginn, t_vertrag.f_vertragsende 

FROM t_vertrag WHERE (((t_vertrag.f_vertrag_gekuendigt IS NULL) 
OR (t_vertrag.f_vertrag_gekuendigt < '20141103' OR t_vertrag.f_vertrag_gekuendigt IS NULL)) 
AND ((t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertragsende >= '20141103' 
AND t_vertrag.f_vertragsende IS NOT NULL)))

The correct SQL would be:

SELECT   t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, 
t_vertrag.f_vertrag_beginn, t_vertrag.f_vertragsende 

FROM t_vertrag WHERE (((t_vertrag.f_vertrag_gekuendigt IS NULL) 
OR (t_vertrag.f_vertrag_gekuendigt < '20141103' AND t_vertrag.f_vertrag_gekuendigt IS NOT NULL)) 
AND ((t_vertrag.f_vertrag_beginn <= '20141103' AND t_vertrag.f_vertrag_beginn IS NOT NULL) AND (t_vertrag.f_vertragsende >= '20141103' 
AND t_vertrag.f_vertragsende IS NOT NULL)))

What is happening here ?

Using SmartGWT 4.1p power v9.1p_2014-10-28/PowerEdition Deployment (built 2014-10-28) with MSSQL 2014.

[Blama]

Hello edulid,

that's on purpose. You'll have to add a NOT_NULL-criterion to your (Advanced)Criteria in order to get rid of the NULL-value rows.
See this two threads.

@Isomorphic: You could amend the OperatorID-docs regarding this in order to show that it is on purpose.

Best regards,
Blama

[edulid]

The first part of the "vertragGekuendigtCriteria" is generating :

t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL

while the second part is generating:

t_vertrag.f_vertragsende >= '20141103' 
AND t_vertrag.f_vertragsende IS NOT NULL

so the second part is correct, while the first part is incorrect. So the behavior is still not consistent.

I read the posts you linked and I am not sure why these criteria are changed from the framework.

[edulid]

I changed the method to:

private AdvancedCriteria getSchuelerCriteria(Date stichtag) {
		OrCriterion vertragGekuendigtCriteria = new OrCriterion(
				new Criterion[] {
						new IsNullCriterion("f_vertrag_gekuendigt"),
						new AndCriterion(new SimpleCriterion(
								"f_vertrag_gekuendigt",
								DefaultOperators.LessThan.getID(), stichtag),
								new NotNullCriterion("f_vertrag_gekuendigt")) });

		AndCriterion vertragZeitraumCriteria = new AndCriterion(
				new Criterion[] {
						new AndCriterion(
								new SimpleCriterion("f_vertrag_beginn",
										DefaultOperators.LessOrEqual.getID(),
										stichtag), new NotNullCriterion(
										"f_vertrag_beginn")),
						new AndCriterion(new SimpleCriterion("f_vertragsende",
								DefaultOperators.GreaterOrEqual.getID(),
								stichtag), new NotNullCriterion(
								"f_vertragsende")) });

		AdvancedCriteria schuelerCriteria = new AdvancedCriteria(
				DefaultOperators.And.getID(), vertragGekuendigtCriteria,
				vertragZeitraumCriteria);

		return schuelerCriteria;
	}

It seems to work but it is double-checking a lot of things:

SELECT   t_vertrag.f_id, t_vertrag.f_vertrag_gekuendigt, 
t_vertrag.f_vertrag_beginn, 
t_vertrag.f_vertragsende 
FROM t_vertrag 

WHERE ((

(t_vertrag.f_vertrag_gekuendigt IS NULL) OR ((t_vertrag.f_vertrag_gekuendigt < '20141103' OR t_vertrag.f_vertrag_gekuendigt IS NULL) AND (t_vertrag.f_vertrag_gekuendigt IS NOT NULL))) AND (((t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)) AND ((t_vertrag.f_vertragsende >= '20141103' AND t_vertrag.f_vertragsende IS NOT NULL) AND (t_vertrag.f_vertragsende IS NOT NULL))))

Example:

(t_vertrag.f_vertrag_beginn <= '20141103' OR
 t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)

So it is checking if it is null and if it is not null! Both, which is unnecessary.

The SQL should be:

t_vertrag.f_vertrag_beginn <= '20141103'  AND t_vertrag.f_vertrag_beginn IS NOT NULL

Isomorphic, could you please confirm that it is not possible to generate this code with smartGWT?

[Blama]

The first part of the "vertragGekuendigtCriteria" is generating :

t_vertrag.f_vertrag_beginn <= '20141103' OR t_vertrag.f_vertrag_beginn IS NULL

while the second part is generating:

t_vertrag.f_vertragsende >= '20141103' 
AND t_vertrag.f_vertragsende IS NOT NULL

so the second part is correct, while the first part is incorrect. So the behavior is still not consistent.

It is consistent, but more complex:

• < and <= are amended with OR IS NULL
  
• > and >= and = are amended with AND IS NOT NULL

That way the NULL-rows appear once (and not twice as it would be with the simple consistent behaviour of adding OR IS NULL every time).

I don't know why this "AND IS NOT NULL" is happening - perhaps it is already there for many years and was added in order to help (very) old database versions to use indexes. From a logical point of view it is useless - although I don't think that it will have any negative impact in production.

Best regards,
Blama

[Blama]

Example:

(t_vertrag.f_vertrag_beginn <= '20141103' OR
 t_vertrag.f_vertrag_beginn IS NULL) AND (t_vertrag.f_vertrag_beginn IS NOT NULL)

So it is checking if it is null and if it is not null! Both, which is unnecessary.

The SQL should be:

t_vertrag.f_vertrag_beginn <= '20141103'  AND t_vertrag.f_vertrag_beginn IS NOT NULL

Isomorphic, could you please confirm that it is not possible to generate this code with smartGWT?

I'm pretty sure that this is not possible currently as it would require the framework to parse and modify the Criteria. Again, I don't think that this has any negative performance implications but agree that it indeed looks ugly and makes the generated SQL harder to read.

But lets wait for Isomorphic to answer. Out of curiosity I'm also interested in this.

Best regards,
Blama

[Isomorphic]

Just to chime in - yes, the SQL is already correct for the reasons Blama explained. It is not minimal, and we don't make an attempt to generate minimal SQL, since:

1. query optimizers in modern databases already remove redundant checks

2. with all the options for field-level customized SQL, trying to minimize SQL across different fields would have lots of tricky cases where it is unclear whether the SQL can be minimized safely