Bugs item #676533, was opened at 2003-01-29 14:15
Message generated for change (Comment added) made by linuxlx
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: Mathew McBride (linuxlx)
Date: 2003-12-14 01:00
Message:
Logged In: YES
user_id=405029
Perhaps this situation is the cause of bug 859407 - where
nukes fails because it tried to fill pn_uid with null on
PostgreSQL
----------------------------------------------------------------------
Comment By: Nobody/Anonymous (nobody)
Date: 2003-06-05 08: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 19: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: IBM Linux Tutorials.
Become an expert in LINUX or just sharpen your skills. Sign up for IBM's
Free Linux Tutorials. Learn everything from the bash shell to sys admin.
Click now! http://ads.osdn.com/?ad_id=1278&alloc_id=3371&op=click
_______________________________________________
JBoss-Development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
