Affects Version/s: 1.2 branch
Fix Version/s: None
Component/s: Core Library
When a join table spans DataMaps, the join query generated typically fails, unless the schemas are accessible via the same DataSource.
Caused by: java.sql.SQLException: invalid schema name: ENG_WORK_MGMT
in statement [SELECT DISTINCT t0.ACTIVITY_YEAR, t0.BUDGET_CODE, t0.GL,
t0.IS_ACTIVE, t0.NAME, t0.OBJ_TYPE, t0.SUB_ACTIVITY, t0.WORK_ORDER,
t0.ACTIVITY, t0.ID, t0.WORK_TYPE FROM CORE_WORK_MGMT.WORK t0,
ENG_WORK_MGMT.AUTHORIZATION_DOC__WORK t1 WHERE t0.ID = t1.WORK_ID AND
(t1.AUTHORIZATION_DOCUMENT_ID = ?)]
Unfortunately, fixing this equates to "horrible performance" ("cartesian
product in memory, turning Cayenne in a database engine"), but that's better than no performance.
One possible solution is "doing joins within Cayenne SQLAction code "
For more details, see the mailing list thread: