I think its not using subquery because mysql 3.x doesn't support it.
Anyway, here is an example of the same problem (slightly more complicated example, but
all I have at hand right now)
For this query, using using jboss with mysql 3.23.x:
DYNAMIC-QL: SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE (
(hierarchy.partyGroup IS NULL) OR (NOT (hierarchy.mainInternalHierarchy IS
NULL)) ) ORDER BY hierarchy.code
JBoss 3.2.1 did this (working):
SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
FROM cmp_hierarchy t0_hierarchy LEFT JOIN MainInternalHierarchy_Hierarchys
t2_hierarchy_mainInternalHierarc ON
t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK LEFT JOIN
PartyGroup_Hierarchys t1_hierarchy_partyGroup_RELATION ON
t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK
WHERE ((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR (NOT
(t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS NULL))) ORDER BY t0_hierarchy.CODE
ASC
;
JBoss 3.2.4RC2 does this (doesn't fall over, but appears not correct):
SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
FROM cmp_hierarchy t0_hierarchy, cmp_main_internal_hierarchy
t3_hierarchy_mainInternalHierarc, maininternalhierarchy_hierarchys
t2_hierarchy_mainInternalHierarc, cmp_party_group t4_hierarchy_partyGroup,
partygroup_hierarchys t1_hierarchy_partyGroup_RELATION
WHERE (((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR ( NOT
(t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS
NULL))) AND t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK AND
t3_hierarchy_mainInternalHierarc.id=t2_hierarchy_mainInternalHierarc.MAIN_INTERNAL_HIERARCHY_FK
AND t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK AND
t4_hierarchy_partyGroup.id=t1_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK) ORDER BY
t0_hierarchy.CODE ASC
;
I did also test with 3.2.4 final and it still didn't work. I can't be 100% sure its
the same generated sql as for 3.2.4rc2 above because I don't have the output here
right now.
I also don't have access to the output of the sql92 compiler. I will post it when I
do.
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3839313#3839313
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3839313
-------------------------------------------------------
This SF.Net email is sponsored by The 2004 JavaOne(SM) Conference
Learn from the experts at JavaOne(SM), Sun's Worldwide Java Developer
Conference, June 28 - July 1 at the Moscone Center in San Francisco, CA
REGISTER AND SAVE! http://java.sun.com/javaone/sf Priority Code NWMGYKND
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
