Generated join not using alias if accessing a table twice (w/o relatedTableAlias)

[Blama]

Hi Isomorphic,

please see the testcase (using v10.0p_2015-10-08/PowerEdition Deployment) from this thread (attached files in the end, plus addition of employee2 to the DataSourceLoader-string in BuiltInDS.html).
Remove the relatedTableAlias="bugHereAsWell" from the reportsTo field in employees.ds.xml.

Then just load the sample. The 1st generated fetch returns no data if run on HSQLDB and an error in Oracle. The generated statement is:

SELECT COUNT(*) FROM employeeTable, employeeTable WHERE employeeTable.ReportsTo = employeeTable.EmployeeId

The (obvious) error message is ORA-00918: column ambiguously defined.

When using Ansi-Joins, the generated statement is:

SELECT COUNT(*) FROM employeeTable JOIN employeeTable ON employeeTable.ReportsTo = employeeTable.EmployeeId

This might be loosely related to the bug in this thread (this is a minor one for me).

Best regards
Blama

[Isomorphic]

This isn't a bug. If you use includeFrom to include fields that, at the database level, come from the same table, you must either:
- explicitly specify an alias to use by defining "relatedTableAlias" on the foreignKey field, or
- specify "includeVia" on the included field, to enable implicit aliasing

We have modified the documentation to make this clearer

[Blama]

Ok, thank you. I already thought so after your comment here.

Secondly - if you resolve the bad value in the CSV file, you can end up with a SQL error of the form Not unique table/alias: 'employeeTable'
What's happening here is that the employee and employeesUpload dataSources have the same referenced SQL table and you need some additional settings to make this work.

When you join a table to itself via foreignKey, you have to tell the SQL subsystem how to resolve this with the "relatedTableAlias" property. The attached "employeesUpload" dataSource adds the related record validation and the necessary relatedTableAlias attribute to prevent the bad SQL on update.

Best regards
Blama