Bugs item #551533, was opened at 2002-05-03 04:40
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=551533&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Open
Resolution: Accepted
Priority: 5
Submitted By: Alexei Guevara (aguevara)
Assigned to: Dain Sundstrom (dsundstrom)
Summary: EJB-QL - missing in generated SQL
Initial Comment:
EJB-QL:
SELECT DISTINCT OBJECT(r)
FROM record r,
IN (r.fields) f1,
IN (r.fields) f2,
IN (r.assns) assn
WHERE f1.attrName=?1
AND f1.attrValue=?2
AND f2.attrName=?3
AND f2.attrValue=?4
AND assn.dataSourceName<>?5
GENERATED SQL:
SELECT DISTINCT t0_r.id
FROM record t0_r, field t1_f1, field t2_f2,
datasourcerecordassn t3_assn
WHERE t1_f1.attr_name = ?
AND t1_f1.attr_value = ?
AND t2_f2.attr_name = ?
AND t2_f2.attr_value = ?
AND t3_assn.ds_name <> ?
AND (t0_r.id=t3_assn.record_id
AND t0_r.id=t2_f2.record_id)
BUG: there is a join missing between t1_f1 and t0_r
(t0_r.id=t1_f1.record_id)
CORRECT SQL:
SELECT DISTINCT t0_r.id
FROM record t0_r, field t1_f1, field t2_f2,
datasourcerecordassn t3_assn
WHERE t1_f1.attr_name = ?
AND t1_f1.attr_value = ?
AND t2_f2.attr_name = ?
AND t2_f2.attr_value = ?
AND t3_assn.ds_name <> ?
AND (t0_r.id=t3_assn.record_id
AND t0_r.id=t2_f2.record_id
AND t0_r.id=t1_f1.record_id)
----------------------------------------------------------------------
Comment By: Stephen Coy (scoy)
Date: 2002-07-02 08:20
Message:
Logged In: YES
user_id=463096
It was copy/pasted directly from server.log.
Come to think of it, the right paren may have been lost when moving it from the sql
client where I was testing it. I restored the '?' from an actual parameter and
probably wiped out the parenthesis.
Oracle was definitely bitching about the "t6_rs_role".
Note that I should have said "is *no* t6_rs_role" below.
----------------------------------------------------------------------
Comment By: Stephen Coy (scoy)
Date: 2002-07-02 01:12
Message:
Logged In: YES
user_id=463096
It was copy/pasted directly from server.log.
Come to think of it, the right paren may have been lost when moving it from the sql
client where I was testing it. I restored the '?' from an actual parameter and
probably wiped out the parenthesis.
Oracle was definitely bitching about the "t6_rs_role".
Note that I should have said "is *no* t6_rs_role" below.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-07-02 00:48
Message:
Logged In: YES
user_id=251431
Oops. Are you sure that you posted exactally what you are
getting? It looks like you are missing a paren in this SQL:
WHERE (t1_u.loginId = ? AND t0_p.pcode
= ? <RPAREN_MISSING> AND (t1_u.loginId=t2_rs.owneruser AND
----------------------------------------------------------------------
Comment By: Stephen Coy (scoy)
Date: 2002-07-01 20:19
Message:
Logged In: YES
user_id=463096
This fix has broken one of our queries:
The ejbql is:
select distinct object(p) from User as u, in (u.userRoleSeqs) as rs, in
(rs.role.rolePrivs) as p where u.loginId = ?1 and p.pcode = ?2
The generated SQL used to be:
SELECT DISTINCT t0_p.pcode, t0_p.description, t0_p.greyOut FROM USERS t1_u,
USER_ROLES_SEQ t2_rs, PRIVS t0_p, ROLE_PRIVS t3_rs_role_rolePrivs_RELATION_, ROLES
t4_rs_role WHERE (t1_u.loginId = ? AND t0_p.pcode = ?) AND (t2_rs.roleid=t4_rs_role.id
AND t4_rs_role.id=t3_rs_role_rolePrivs_RELATION_.roleid AND
t0_p.pcode=t3_rs_role_rolePrivs_RELATION_.privcode AND t1_u.loginId=t2_rs.owneruser)
and is now:
SELECT DISTINCT t0_p.pcode, t0_p.description, t0_p.greyOut FROM USERS t1_u,
USER_ROLES_SEQ t2_rs, PRIVS t0_p, ROLE_PRIVS t3_rs_role_rolePrivs_RELATION_ WHERE
(t1_u.loginId = ? AND t0_p.pcode = ? AND (t1_u.loginId=t2_rs.owneruser AND
t6_rs_role.id=t3_rs_role_rolePrivs_RELATION_.roleid AND
t0_p.pcode=t3_rs_role_rolePrivs_RELATION_.privcode AND t2_rs.roleid=t6_rs_role.id)
which fails completely because there is t6_rs_role in the FROM clause.
The relationships are:
Privs
*^
|
Roles
^
*|
UserRoleSeq
*^
|
Users
which hopefully is not mutilated too much when I submit this.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-06-27 09:12
Message:
Logged In: YES
user_id=251431
Fixed in HEAD and Branch_3_0
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=551533&group_id=22866
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
