0 Replies Latest reply on Nov 7, 2005 10:39 AM by Florian Kammermann

    JBoss genereates a false SQL-Statment  (left-join cmr-field)

    Florian Kammermann Newbie

      Ok the following initial situation:

      4 tables:
      -article
      -articleattribute
      -pricing
      -availability

      article has realtions to the other tables. There are no other relations.

      following xml is inside the jbosscmp-jdbc.xml:
      **************************************************************

      <query-method>
      <method-name>findListId</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <jboss-ql>[CDATA[SELECT OBJECT(o) FROM Article AS o, IN (o.articleAttributes) AS aa WHERE aa.name = ?1 AND aa.value = ?2]]</jboss-ql>
      <read-ahead>
      on-find
      <page-size>1</page-size>
      <left-join cmr-field="articleAttributes"/>
      <left-join cmr-field="pricings"/>
      <left-join cmr-field="availabilities"/>
      </read-ahead>

      **************************************************************



      This generates me the following sql-statment:
      **************************************************************
      SELECT

      t0_o.seq, t0_o.artno,
      t1_o_articleAttributes.seq, t1_o_articleAttributes.artno, t1_o_articleAttributes.name,
      t1_o_articleAttributes.value, t1_o_articleAttributes.country, t1_o_articleAttributes.language,
      t1_o_articleAttributes.fk_article,

      t2_o_pricings.seq, t2_o_pricings.artno, t2_o_pricings.qty, t2_o_pricings.type,
      t2_o_pricings.pricelist, t2_o_pricings.valuetype, t2_o_pricings.valuedecimal,
      t2_o_pricings.valuelong, t2_o_pricings.currency, t2_o_pricings.fk_article,

      t3_o_availabilities.seq, t3_o_availabilities.artno, t3_o_availabilities.qty,
      t3_o_availabilities.whid, t3_o_availabilities.date, t3_o_availabilities.type,
      t3_o_availabilities.fk_article

      FROM article t0_o, articleattribute t4_aa

      LEFT OUTER JOIN articleattribute t1_o_articleAttributes
      ON t0_o.seq=t1_o_articleAttributes.fk_article
      LEFT OUTER JOIN pricing t2_o_pricings
      ON t0_o.seq=t2_o_pricings.fk_article
      LEFT OUTER JOIN availability t3_o_availabilities
      ON t0_o.seq=t3_o_availabilities.fk_article


      WHERE ((t4_aa.name = 'listId' AND t4_aa.value = '65066eaf91a8d4b716da2bea390ee0f9d3fefd08')) AND t0_o.seq=t4_aa.fk_article
      **************************************************************

      This is false:
      - in the from there should be only one table before the first left outer join.
      - in the where is the last statment also for nothing (handled trough the left outer join)
      - in the where the false table (the waste table from the from-statment) is used


      Seems that JBoss generate false SQL.

      When I take out the navigation over the relation in the jboss-ql like this:
      -SELECT OBJECT(o) FROM Article AS o WHERE o.artno = ?1

      then I become a correct statment.

      Following Statment I created miself an is correct:
      **************************************************************
      SELECT

      t0_o.seq, t0_o.artno,

      t1_o_articleAttributes.seq, t1_o_articleAttributes.artno, t1_o_articleAttributes.name,
      t1_o_articleAttributes.value, t1_o_articleAttributes.country, t1_o_articleAttributes.language,
      t1_o_articleAttributes.fk_article,

      t2_o_pricings.seq, t2_o_pricings.artno, t2_o_pricings.qty, t2_o_pricings.type,
      t2_o_pricings.pricelist, t2_o_pricings.valuetype, t2_o_pricings.valuedecimal,
      t2_o_pricings.valuelong, t2_o_pricings.currency, t2_o_pricings.fk_article,

      t3_o_availabilities.seq, t3_o_availabilities.artno, t3_o_availabilities.qty,
      t3_o_availabilities.whid, t3_o_availabilities.date, t3_o_availabilities.type,
      t3_o_availabilities.fk_article

      FROM article t0_o

      LEFT OUTER JOIN articleattribute t1_o_articleAttributes
      ON t0_o.seq=t1_o_articleAttributes.fk_article
      LEFT OUTER JOIN pricing t2_o_pricings
      ON t0_o.seq=t2_o_pricings.fk_article
      LEFT OUTER JOIN availability t3_o_availabilities
      ON t0_o.seq=t3_o_availabilities.fk_article


      WHERE ((t1_o_articleAttributes.name = 'listId' AND t1_o_articleAttributes.value = '65066eaf91a8d4b716da2bea390ee0f9d3fefd08'))
      **************************************************************

      The Database is a MS SQL Server