Bug in SQL Generation with includeVia and outputs (with simple testcase)

[Blama]

Hi Isomorphic,

please see the following changes in BuiltInDS-testcase (SNAPSHOT_v9.1d_2014-02-05/EVAL Deployment):

employees.ds.xml

<DataSource ID="employees" serverType="sql" tableName="employeeTable" recordName="employee" testFileName="/examples/shared/ds/test_data/employees.data.xml"
	titleField="Name">
	<fields>
		<field name="[B]userOrder[/B]" title="userOrder" type="integer" canEdit="false" hidden="true" [B]foreignKey="supplyItem.itemID"[/B] />
		<field name="Name" title="Name" type="text" length="128" />
		<field name="[B]EmployeeId[/B]" title="Employee ID" type="integer" primaryKey="true" required="true" [B]foreignKey="supplyItem.itemID"[/B] />
[B]		<field includeFrom="supplyItem.itemName" includeVia="userOrder" />
		<field includeFrom="supplyItem.SKU" includeVia="EmployeeId" />
[/B]
		<field name="ReportsTo" title="Manager" type="integer" required="true" foreignKey="employees.EmployeeId" rootValue="1" detail="true" />
		<field name="Job" title="Title" type="text" length="128" />
		<field name="Email" title="Email" type="text" length="128" />
		<field name="EmployeeType" title="Employee Type" type="text" length="40" />
		<field name="EmployeeStatus" title="Status" type="text" length="40" />
		<field name="Salary" title="Salary" type="float" />
		<field name="OrgUnit" title="Org Unit" type="text" length="128" />
		<field name="Gender" title="Gender" type="text" length="7">
			<valueMap>
				<value>male</value>
				<value>female</value>
			</valueMap>
		</field>
		<field name="MaritalStatus" title="Marital Status" type="text" length="10">
			<valueMap>
				<value>married</value>
				<value>single</value>
			</valueMap>
		</field>
	</fields>
	<operationBindings>
		<operationBinding operationType="fetch" [B]outputs="Name, Job"[/B] />
	</operationBindings>
</DataSource>

Replaced fetch() in BuiltInDS.java method bindComponents(String dsName):

		boundList.fetchData(new AdvancedCriteria(OperatorId.AND, new Criterion[] { new Criterion("itemName", OperatorId.STARTS_WITH, "aaa"),
				new Criterion("SKU", OperatorId.STARTS_WITH, "bbb") }));

Resulting SQL:

[B]--With "outputs" in ds.xml (wrong)[/B]
SELECT COUNT (*)
  FROM supplyitem, employeetable
 WHERE     (    (supplyitem.itemname LIKE 'aaa%' ESCAPE '\' AND supplyitem.itemname IS NOT NULL)
            AND (supplyitem.sku LIKE 'bbb%' ESCAPE '\' AND supplyitem.sku IS NOT NULL))
       AND employeetable.userorder = supplyitem.itemid

[B]--Without "outputs" ds.xml (correct)[/B]
SELECT COUNT (*)
  FROM supplyitem a1, supplyitem a0, employeetable
 WHERE     ( (a0.itemname LIKE 'aaa%' ESCAPE '\' AND a0.itemname IS NOT NULL) AND (a1.sku LIKE 'bbb%' ESCAPE '\' AND a1.sku IS NOT NULL))
       AND employeetable.userorder = a0.itemid
       AND employeetable.employeeid = a1.itemid

As you can see, in the 1st case there is only one join to the supplyitem-table, while it should be two in both cases.

If you choose the criteria in a way that the count(*) will return >0, also the real query is malformed in the same way.

Best regards,
Blama

[sabaka]

It is a bug indeed. It will be fixed.

Meanwhile you can try adding relatedTableAlias attribute to your FK fields to make this work without a fix:

<field name="userOrder" title="userOrder" type="integer" canEdit="false" hidden="true" foreignKey="supplyItem.itemID" [b]relatedTableAlias="alias1"[/b] />
<field name="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true" foreignKey="supplyItem.itemID" [b]relatedTableAlias="alias2"[/b]/>

[sabaka]

This is fixed now.