Bugs item #676533, was opened at 2003-01-28 19:15
Message generated for change (Comment added) made by nobody
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=676533&group_id=22866
Category: JBossCMP
Group: CVS HEAD
Status: Open
Resolution: None
Priority: 5
Submitted By: Jeremy Boynes (jboynes)
Assigned to: Jeremy Boynes (jboynes)
Summary: Multistep IS NULL SQL incorrect
Initial Comment:
With the commerce test schema, the EJB-QL query
SELECT OBJECT(l)
FROM LineItem l
WHERE l.order.billingAddress IS NULL
generates the SQL
SELECT DISTINCT t0_l.id
FROM LINEITEMEJB t0_l,
ORDER_DATA t1_l_order
WHERE (t1_l_order.BILLING_ADDRESS IS NULL)
AND (t1_l_order.BILLING_ADDRESS=t1_l_order.id)
which results in an SQLException because there is no ID
column in ORDER_DATA but in reality it is just totally
messed up.
----------------------------------------------------------------------
Comment By: Nobody/Anonymous (nobody)
Date: 2003-06-04 15:05
Message:
Logged In: NO
I have a similar situation with a 1-M.
Head looks like:
/**
* The Entity bean represents a Style of piece of recorded
music.
*
* @author Rick Boykin
* @version $Revision$
*
* @ejb:bean name="Style"
* display-name="Style representing a musical style"
* type="CMP"
* cmp-version="2.x"
* view-type="both"
* jndi-name="StyleRemoteHome"
* local-jndi-name="StyleLocalHome"
* primkey-field="id"
* schema="Style"
* reentrant="true"
*
* @ejb:pk class="java.lang.Integer" generate="false"
*
* @ejb:security-identity use-caller-identity="true"
*
* @ejb:transaction type="Required"
*
* @ejb:data-object
extends="com.epitome.wax.ejb.interfaces.AbstractData"
* setdata="true"
*
* @ejb:finder signature="java.util.Collection findAll()"
*
* @ejb:finder signature="java.util.Collection findAllParents()"
* query="SELECT OBJECT(style) FROM Style style WHERE
style.id IS NOT NULL AND style.subStyles IS EMPTY"
* result-type-mapping="Local"
* unchecked="true"
*
* @jboss:create-table create="true"
* @jboss:remove-table remove="false"
*
**/
Applicable fields look like....
/**
* Retrieve the Style's Parent.
*
* @return Returns a StyleLocal representing the Parent
* of this Style.
*
* @ejb:persistent-field
* @ejb:interface-method view-type="local"
* @ejb:relation name="Style-Style" role-
name="StyleHasParentStyle"
* @jboss:relation related-pk-field="id" fk-column="id"
*/
public abstract StyleLocal getParent();
/**
* Set the Style's Parent.
*
* @param style The Parent of this Style.
*
*/
public abstract void setParent( StyleLocal style );
/**
* Retrieve the Style's SubStyles.
*
* @return Returns a StyleLocal Collection representing
the SubStyles
* of this Style.
*
* @ejb:persistent-field
* @ejb:interface-method view-type="local"
* @ejb:relation name="Style-Style" role-
name="StyleHasSubStyles"
*/
public abstract Collection getSubStyles();
/**
* Set the Style's Parent.
*
* @param style The Parent of this Style.
*/
public abstract void setSubStyles( Collection styles );
I've tried several incarnations of the query.
EJB-QL: SELECT OBJECT(style) FROM Style style WHERE
style.id IS NOT NULL AND style.subStyles IS EMPTY
yelds...
SQL: SELECT DISTINCT t0_style.id FROM Style t0_style LEFT
JOIN Style t1_style_subStyles ON
t0_style.id=t1_style_subStyles.id WHERE t0_style.id IS NOT
NULL AND t1_style_subStyles.id IS NULL
And:
EJB-QL: SELECT OBJECT(style) FROM Style style WHERE
style.parent IS NULL
yields...
SQL: SELECT t0_style.id FROM Style t0_style WHERE
t0_style.id IS NULL
I'm using MySQLDS on jboss 3.2.1 - tomcat 4.x.x.
----------------------------------------------------------------------
Comment By: Sacha Labourey (slaboure)
Date: 2003-01-29 00:24
Message:
Logged In: YES
user_id=95900
I suspect this bug to occur in other situations as well and not
strictly in this "multi-step" schema.
Here is the situation:
Given a EJB A, which builds a composite recursive
datastructure, something like:
setId(Long id) // The unique identifier of A
getId() : Long
setParent(A)
getParent() : A
setChildren(Collection ofAs)
getChildren() : Collection
If you want to query all A's, which do not have a parent:
SELECT OBJECT(a) FROM A a WHERE a.parent IS NULL
Executing this EJB-QL i always get an empty collection.
The SQL, which is generated is something like:
SELECT t0_a.id FROM A t0_a WHERE (t0_a.parent IS
NULL) AND (t0_a.parent=t0_a.id)
which is wrong in this case.
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=676533&group_id=22866
-------------------------------------------------------
This SF.net email is sponsored by: Etnus, makers of TotalView, The best
thread debugger on the planet. Designed with thread debugging features
you've never dreamed of, try TotalView 6 free at www.etnus.com.
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development