Server-side AdvancedCriteria goes wrong

[Sytematic]

Hello,

Using: (v8.3p_2013-01-09/PowerEdition Deployment 2013-01-09)

I have the following server-side method:

protected void fetchWorklistDataFromDatabase() throws Exception {
	DSRequest req = new DSRequest("Order_","fetch");
			
	req.setOperationId("FetchWorklistData");
	AdvancedCriteria cr = new AdvancedCriteria(DefaultOperators.And,
		new Criterion[] {
			new DateRangeCriterion("orderDate", "between" ,this.startDate, this.endDate),
			new SimpleCriterion("SaasCustomer_id", "equals", this.SaasCustomer_id)
		}
	);
			
	if(this.criteria != null && this.criteria.get("Worklist_id") != null){
		Criterion worklist = null;
		if((Integer)criteria.get("Worklist_id") == 0){
			worklist = new IsNullCriterion("Worklist_id");
					
		}
		else {
			worklist = new SimpleCriterion("Worklist_id", "equals", (Integer) criteria.get("Worklist_id"));
		}
				
		AdvancedCriteria c2 = new AdvancedCriteria(
				DefaultOperators.And,
				new Criterion[]{
						cr.asCriterion(),
						worklist
		});

		req.setAdvancedCriteria(c2);
	}
			
	DSResponse resp = req.execute();
	worklistData = resp.getDataList();
}

with the following operationBinding:

...
    <field name="orderDate" type="datetime" required="false" />
    <field name="SaasCustomer_id" type="integer" foreignKey="saasCustomers.SaasCustomer_id"/>  
    <field name="Worklist_id" type="integer" tableName="Worklist"  customSQL="true" />

...
    <operationBinding operationType="fetch" requiresAuthentication="true" requiresRole="CAN_READ_ORDER_" operationId="FetchWorklistData" customCriteriaFields="Worklist_id, orderDate, SaasCustomer_id"> 
      <selectClause>*</selectClause>  
      <tableClause> <![CDATA[
			Order_  INNER JOIN Person 						       ON Order_.Person_id = Person.Person_id
	    		    INNER JOIN Order__orderedDishes_Dish 	       ON Order_.Order__id = Order__orderedDishes_Dish.Order__id
	    		    INNER JOIN Dish 						       ON Order__orderedDishes_Dish.Dish_id = Dish.Dish_id
	    		    LEFT OUTER JOIN Worklist					   ON Dish.Worklist_id = Worklist.Worklist_id
	    		    LEFT OUTER JOIN Order__orderedPackages_Package ON Order_.Order__id = Order__orderedPackages_Package.Order__id
	    		    LEFT OUTER JOIN Package						   ON Order__orderedPackages_Package.Package_id = Package.Package_id
	    		    INNER JOIN SaasCustomer					       ON Order_.SaasCustomer_id = SaasCustomer.SaasCustomer_id
		 ]]> </tableClause>  
</operationBinding>

The problem is that my method does not add the DateRangeCriterion. the field orderDate is part of the Order_.ds.xml.

The resolved SQL query looks like this:

SELECT * FROM  
			Order_  INNER JOIN Person 						       ON Order_.Person_id = Person.Person_id
	    		    INNER JOIN Order__orderedDishes_Dish 	       ON Order_.Order__id = Order__orderedDishes_Dish.Order__id
	    		    INNER JOIN Dish 						       ON Order__orderedDishes_Dish.Dish_id = Dish.Dish_id
	    		    LEFT OUTER JOIN Worklist					   ON Dish.Worklist_id = Worklist.Worklist_id
	    		    LEFT OUTER JOIN Order__orderedPackages_Package ON Order_.Order__id = Order__orderedPackages_Package.Order__id
	    		    LEFT OUTER JOIN Package						   ON Order__orderedPackages_Package.Package_id = Package.Package_id
	    		    INNER JOIN SaasCustomer					       ON Order_.SaasCustomer_id = SaasCustomer.SaasCustomer_id
		   WHERE 
    	(((('0'='1') AND (Order_.SaasCustomer_id = 1 AND Order_.SaasCustomer_id IS NOT NULL)) AND (Worklist.Worklist_id = 2 AND Worklist.Worklist_id IS NOT NULL)))

Question: why is the orderDate not being used in the generated SQL? If I instead make a 'normal' criteria via req.setCriteria(), and i add the orderDate, it DOES add the criterion (except then it's not a date range, so can't use that).

What am I doing wrong here? Or is it a bug? It seems I cannot possibly add the date range via the req.setAdvancedCriteria(). Any help appreciated!

[Isomorphic]

If orderDate is marked customSQL="true", since it doesn't appear in customCriteriaFields, criteria against that field would be ignored.

If that doesn't solve it for you and you think there might be general problem with programmatically adding a DateRangeCriterion, please try reproducing a similar problem starting from a sample.

[Sytematic]

Yeah, I know about the customSQL. In my case, orderDate is just a regular field of the Order_ datasource and does not have the customSQL flag.

Also; when I just use setCriteria, and put in orderDate, it works.

The bottom line is, that in whatever way I try to create an advanced criteria containing a criterion (of whatever type) on the field "orderDate", it is not added. I also tried mimicking a date range by using a set of SimpleCriterion's, but it keeps being ignored.

[Isomorphic]

Can you show the request from the developer console?

[Sytematic]

Bingo, I found it. Just when I created a velocity template work-around/hack, and feeling really bad about it :-)

It had to do with the way my jUnit tests were run, it crashed earlier with a warning not being able to find builtinTypes.xml.

This way, everything else seemed to work fine (ie. the test case was run), but this obscure error came up.

When I fixed this error, the criteria were added nicely.