[jira] Created: (CAY-1502) EJBQLJoinAppender uses the same SQL table alias for many-many tables every time they are joined in

["Dave Lamy (JIRA)" <ji...@apache.org>]

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.