Bugs item #1081794, was opened at 2004-12-08 17:21
Message generated for change (Comment added) made by starksm
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1081794&group_id=22866
Category: JBossCMP
Group: v3.2
Status: Open
Resolution: None
Priority: 5
Submitted By: gunter zeilinger (gunterze)
Assigned to: Alexey Loubyansky (loubyansky)
Summary: JDBCEJBQLCompiler: Invalid SQL for Left join read ahead
Initial Comment:
JDBCEJBQLCompiler does not take care to put the right
table name in front of the LEFT JOIN phrase:
Extending the GangsterBean by finder:
<query>
<query-method>
<method-name>findMembers_leftjoin</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<jboss-ql><![CDATA[
SELECT OBJECT(g)
FROM gangster g
WHERE g.organization.name = ?1
]]>
</jboss-ql>
<read-ahead>
<strategy>on-find</strategy>
<page-size>4</page-size>
<eager-load-group>basic</eager-load-group>
<left-join cmr-field="hangout"
eager-load-group="quick info"/>
</read-ahead>
</query>
results in:
SELECT t0_g.id, t0_g.name, t0_g.nick_name,
t0_g.badness, t1_g_hangout.id, t1_g_hangout.city,
t1_g_hangout.st, t1_g_hangout.zip FROM GANGSTER t0_g,
ORGANIZATION t2_g_organization LEFT OUTER JOIN LOCATION
t1_g_hangout ON t0_g.hangout=t1_g_hangout.id WHERE
(t2_g_organization.name = ? AND
t0_g.organization=t2_g_organization.name)
0
The order of GANGSTER t0_g, ORGANIZATION
t2_g_organization is wrong. HSQL seems to be able to
intepretate the statement anyway, but other DBs (e.g.
PostgreSQL) report a SQL error.
The EJBQLToSQL92Compiler simple ignores the Left join
read ahead configuration for the cmr field:
SELECT t0_g.id, t0_g.name, t0_g.nick_name,
t0_g.badness, t0_g.cell_area, t0_g.cell_exch,
t0_g.cell_ext, t0_g.page_area, t0_g.page_exch,
t0_g.page_ext, t0_g.email, t0_g.organization,
t0_g.hangout FROM GANGSTER t0_g LEFT OUTER JOIN
ORGANIZATION t1_g_organization ON
t0_g.organization=t1_g_organization.name WHERE
t1_g_organization.name = ?
----------------------------------------------------------------------
Comment By: Scott M Stark (starksm)
Date: 2004-12-29 12:52
Message:
Logged In: YES
user_id=175228
All issues have been moved to http://jira.jboss.com. Existing
issues have been moved. New issues will be closed with this
canned reponse.
----------------------------------------------------------------------
Comment By: gunter zeilinger (gunterze)
Date: 2004-12-09 13:59
Message:
Logged In: YES
user_id=93547
I am not familiar with the SQL92 spec. I only know, that
PostgreSQL 7.4.6 and DB2 8.1 returns a SQL error:
PostgreSQL: "ERROR: relation "t0_g" does not exist"
DB2: "DB2 SQL error: SQLCODE: -338, SQLSTATE: 42972,
SQLERRMC: null"
with SQLSTATE: 42972 = "An expression in a join-condition or
ON clause of a MERGE statement references columns in more
than one of the operand tables."
if the "left-hand" table of the OUT JOIN is not placed
immediately before the OUT JOIN phrase.
----------------------------------------------------------------------
Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-12-09 01:31
Message:
Logged In: YES
user_id=543482
EJBQLToSQL92Compiler does not support read-ahead at all. It
always generates SQL equivalent to on-find for the target
entity unless it's an ejbSelect that queries for a CMP field
or function.This is documented on the wiki.
About syntax error. Is this the spec? It seems to be clear
from the statement how joining should be done. I don't see a
reason for a driver to complain unless it's spec.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1081794&group_id=22866
-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://productguide.itmanagersjournal.com/
_______________________________________________
JBoss-Development mailing list
JBoss-Development@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jboss-development
