EJBQLJoinAppender uses the same SQL table alias for many-many tables every time
they are joined in
--------------------------------------------------------------------------------------------------
Key: CAY-1502
URL: https://issues.apache.org/jira/browse/CAY-1502
Project: Cayenne
Issue Type: Bug
Components: Core Library
Affects Versions: 3.0.1
Reporter: Dave Lamy
Priority: Minor
EJBQLJoinAppender will create a SQL table alias for many-many join tables, but
it does not have an EJBQL alias to work with as a key since these tables are
not referenced in EJBQL. The problem is that if a many-many table is
referenced in the same query multiple times but from different contexts, the
SQL generation will wind up joining to the table multiple times with the same
alias, which is invalid SQL.
Example EJBQL (test_class.parents are also of type test_class)
SELECT COUNT(distinct a.id) FROM test_class a LEFT JOIN a.parents b JOIN
a.people c LEFT JOIN b.people d WHERE b.firstName = 'foo' OR d.firstName = 'foo'
basically creates
SELECT COUNT(DISTINCT t0.ID) AS sc0
FROM TEST_CLASS t0
LEFT OUTER JOIN TEST_CLASS t1 ON (t0.PARENT_ID = t1.ID) // a.parents
INNER JOIN TEST_CLASS_PEOPLE t2 ON (t0.ID = t2.TEST_CLASS_ID) //
a.people, first half
JOIN PERSON t3 ON (t2.PERSON_ID = t3.ID) // a.people, second half
LEFT OUTER JOIN TEST_CLASS_PEOPLE t2 ON (t1.ID = t2.TEST_CLASS_ID) //
b.people, first half, note the alias duplication
JOIN PERSON t5 ON (t2.PERSON_ID = t5.ID) // b.people, second half
WHERE t3.FIRST_NAME = 'foo' OR t5.FIRST_NAME = 'foo'
Note the reuse of the "t2" table alias for the join table.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
