Bugs item #889390, was opened at 2004-02-03 01:36
Message generated for change (Comment added) made by loubyansky
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=889390&group_id=22866
Category: JBossCMP
Group: v3.2
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: Martin Handwerker (mhandwerker)
Assigned to: Alexey Loubyansky (loubyansky)
Summary: CMR's don't outer-join when they should
Initial Comment:
When I add the following EJB-QL finder to an entity bean:
SELECT DISTINCT OBJECT(a) FROM Acl a WHERE
(a.accessorGuid = ?1) and (a.objectTypeAsString = ?3
and (a.vCObject is null or a.vCObject.guid = ?2))"
JDBCEJBQLCompiler generates the following incorrect
SQL:
SELECT DISTINCT t0_a.acl_pk FROM vc_acl t0_a,
vc_object t1_a_vCObject WHERE ((t0_a.accessor_guid
= ?) AND (t0_a.object_type = ? AND (t0_a.obj_fk IS
NULL OR t1_a_vCObject.obj_pk = ?)) AND
t0_a.obj_fk=t1_a_vCObject.obj_pk)
Notice that there is a container-managed relationship
between the AclBean and VCObjectBean on the obj_fk
database column.
Because the EJB-QL mentions the "guid" field of
VCObjectBean ("a.vCObject.guid = ?2"),
JDBCEJBQLCompiler generates a join to VCObject accross
the obj_fk foreign key field ("...AND
t0_a.obj_fk=t1_a_vCObject.obj_pk"). It incorrectly
generates an inner-join, when an outer-join is required.
If Acl.obj_fk is null, "t0_a.obj_fk IS NULL" should allow
that Acl instance to be found, but it will not be found,
because the inner-join to VCObject fails.
BEA Weblogic 7.x, 8.1, and IBM WebSphere 4.0.x and
5.0 all correctly generate an outer join for this query.
I am running JBoss version 3.2.3 against Microsoft
SQLServer 2000.
Martin Handwerker
Celequest Corporation
Work: (650) 637-2168
Email: mhandwerker AT celequest DOT com
----------------------------------------------------------------------
>Comment By: Alexey Loubyansky (loubyansky)
Date: 2004-07-13 22:15
Message:
Logged In: YES
user_id=543482
In this case EJBQLToSQL92Compiler should be used.
http://www.jboss.org/wiki/Wiki.jsp?page=PlugableQlCompiler
Thanks.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=889390&group_id=22866
-------------------------------------------------------
This SF.Net email sponsored by Black Hat Briefings & Training.
Attend Black Hat Briefings & Training, Las Vegas July 24-29 -
digital self defense, top technical experts, no vendor pitches,
unmatched networking opportunities. Visit www.blackhat.com
_______________________________________________
JBoss-Development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
