Uploaded image for project: 'Application Server 3  4  5 and 6'
  1. Application Server 3 4 5 and 6
  2. JBAS-1550

JDBCEJBQLCompiler generates a non valid query

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • JBossAS-4.0.4.GA
    • JBossAS-4.0.1 SP1
    • CMP service
    • 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.

      Attachments

        Activity

          People

            olubyans@redhat.com Alexey Loubyansky
            danp_jira danp (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: