Results missing in ejb-ql in jboss 3.2.3

[jofli]

Hi, I have a problem with ejb-ql and IS NULL expression combined with '=' expression. What I'm trying to do in pure SQL is quite complex by here here it is:

select id
from article_byline
where
product_id=1 and
(section_id is null or section_id=1) and
(article_type_id is null or article_type_id=1) and
(article_part_id is null or article_part_id=1);

and this is the xdoclet that I'm using:

* @jboss.query
* signature = "java.util.Collection findByInheritance(int productId, int sectionId, int articleTypeId, int articlePartId)"
* query = "SELECT OBJECT(abl) FROM ArticleByline AS abl WHERE abl.product.id = ?1 AND
* (abl.section IS NULL OR abl.section.id = ?2) AND
* (abl.articleType IS NULL OR abl.articleType.id = ?3) AND
* (abl.articlePart IS NULL OR abl.articlePart.id = ?4) ORDER BY abl.id"

which results in the following query by jboss:

SELECT t0_abl.id, t0_abl.id
FROM article_byline t0_abl,
product t1_abl_product,
section t2_abl_section,
article_type t3_abl_articleType,
article_part t4_abl_articlePart
WHERE
(t1_abl_product.id = 1 AND
(t0_abl.section_id IS NULL OR t2_abl_section.id = 1) AND
(t0_abl.article_type_id IS NULL OR t3_abl_articleType.id = 1) AND
(t0_abl.article_part_id IS NULL OR t4_abl_articlePart.id = 1) AND
t0_abl.product_id=t1_abl_product.id AND
t0_abl.section_id=t2_abl_section.id AND
t0_abl.article_type_id=t3_abl_articleType.id AND
t0_abl.article_part_id=t4_abl_articlePart.id
)
ORDER BY t0_abl.id ASC

and this will miss all the rows where the column IS NULL;

Could this be a bug in jboss/xdoclet or is this not possible in ejb-ql, or most likely; have I done something wrong in my ejb-ql definition?
Does anyone out there have any idea? Not using a BMP-finder, that is.

Many thanx in advance.

[sesques]

Hi,

I think that you cannot use CMR fields for this query. Using CMR fields force the container to join the tables, so the null values are omitted.
In fact, testing null values on foreign keys is similar to testing values on CMP fields.

I think you have 2 major solutions:

1) Add CMP fields for all the foreign keys (choose a different name from the corresponding CMR) and test the null value on the CMP fields.

2) Try to override the EJB-QL mapping with a DeclareSQL statement (I never test it but it is designed to desperate hopeless.

Pascal

[jofli]

Thank you for your reply. It actually works with declaredSQL like this:

* @jboss.declared-sql
* signature = "java.util.Collection findByInheritance(int productId, int sectionId, int articleTypeId, int articlePartId)"
* alias = "abl"
* where = "abl.product_id = {0} AND
* (abl.section_id IS NULL OR abl.section_id = {1}) AND
* (abl.article_type_id IS NULL OR abl.article_type_id = {2}) AND
* (abl.article_part_id IS NULL OR abl.article_part_id = {3})"