JBoss genereates a false SQL-Statment (left-join cmr-field)
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