Details
-
Bug
-
Resolution: Done
-
Major
-
JBossAS-4.0.1 SP1
-
None
Description
JDBCEJBQLCompiler generates a non valid query when uses read-ahead option:
JBoss-QL query:
2005-03-07 12:37:40,401 DEBUG org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Question#ejbSelectGeneric DYNAMIC-QL:
SELECT OBJECT(q) FROM Question q WHERE q.topic.subject.id = ?1 OFFSET ?2 LIMIT ?3
Corresponding fragment in jbosscmp-jdbc.xml is:
<query>
<query-method>
<method-name>ejbSelectGeneric</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.Object[]</method-param>
</method-params>
</query-method>
<dynamic-ql/>
<read-ahead>
<strategy>on-find</strategy>
<eager-load-group>*</eager-load-group>
<left-join cmr-field="topic" eager-load-group="*">
<left-join cmr-field="subject" eager-load-group="*"/>
</left-join>
<left-join cmr-field="questiondifficulty" eager-load-group="*"/>
<left-join cmr-field="questiontype" eager-load-group="*"/>
</read-ahead>
</query>
Note: This is a dynamic-ql query because in the feature I will add (dynamically) new elements.
The generated query is:
org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.Question#ejbSelectGeneric Executing SQL:
SELECT t0_q.id, t0_q.description, t0_q.topicId, t0_q.questionBody, t0_q.questionImage,
t0_q.explanationBody, t0_q.explanationImage,
t0_q.difficulty, t0_q.maxTime, t0_q.generic, t0_q.type, t1_q_topic.id, t1_q_topic.subjectId,
t1_q_topic.name, t2_q_topic_subject.id,
t2_q_topic_subject.name, t2_q_topic_subject.author, t2_q_topic_subject.lastModified,
t2_q_topic_subject.comments,
t2_q_topic_subject.deprecated, t2_q_topic_subject.categoryId, t2_q_topic_subject.pos,
t3_q_questiondifficulty.id,
t3_q_questiondifficulty.name, t4_q_questiontype.id, t4_q_questiontype.name
FROM QUESTION t0_q
LEFT OUTER JOIN TOPIC t1_q_topic ON t0_q.topicId=t1_q_topic.id
LEFT OUTER JOIN SUBJECT t2_q_topic_subject ON t1_q_topic.subjectId=t2_q_topic_subject.id
LEFT OUTER JOIN QUESTIONDIFFICULTY t3_q_questiondifficulty ON t0_q.difficulty=t3_q_questiondifficulty.id
LEFT OUTER JOIN QUESTIONTYPE t4_q_questiontype ON t0_q.type=t4_q_questiontype.id,
TOPIC t1_q_topic,
SUBJECT t2_q_topic_subject
WHERE (t2_q_topic_subject.id = ? AND t0_q.topicId=t1_q_topic.id AND t1_q_topic.subjectId=t2_q_topic_subject.id)
The error is:
Error: The correlation name 't1_q_topic' is specified multiple times in a FROM clause.
In this case two CMR fields (topic and subject) are part of both WHERE clause and read-ahead/left-join section.