Is this valid EJB-QL?

Question

I have the following construct in EJB-QL several EJB 2.1 finder methods:

SELECT distinct OBJECT(rd) FROM RequestDetail rd, DetailResponse dr 
      WHERE dr.updateReqResponseParentID is not null 
      and dr.updateReqResponseParentID = ?1 
      and rd.requestDetailID = dr.requestDetailID
      and rd.deleted is null and dr.deleted is null

IDEA's EJB-QL inspection flags the use of the two object FROM RequestDetail rd, DetailResponse dr with an inspection which says: Several ranged variable declarations are not supported, use collection member declarations instead (e.g. IN(o.lineItems))

The queries themselves function fine (as in return the expected results) on JBoss 4.2. Is IDEA all wet here, or is there a valid issue with the query? And what is the actual preferred alternative syntax for such a query?

Edit: Thanks for the help from all those who answered, especially Romain. I reported this as an issue to JetBrains.

Answer 1

I would compare the queries against an official resource like BNF Grammar of EJB QL for J2EE 1.3 (since you mentioned EJB2.1)

For a more recent spec for JEE5 Full Query Language Syntax

Answer 2

Assuming detailResponse is the name of an association-field whose value is a collection of instances of DetailResponse, you can this for navigation via the association fields:

  SELECT distinct OBJECT(rd) FROM RequestDetail rd IN (rd.detailResponse) dr WHERE
  dr.updateReqResponseParentID is not null and dr.updateReqResponseParentID = ?1 
  and rd.deleted id null and dr.deleted is null

Answer 3

The query is fine - It looks like an IDEA Code Inspector issue.
I cannot find any bugs logged against it as such.

You might want to ask their support if you have the paid version.

I did find a minor issue, in the spec for EJB 2.1 (And all others for that matter) it says to use upper case for NULL.

11.2.6.10 Null Comparison Expressions
The syntax for the use of the comparison operator IS NULL in a conditional expression is as follows: {single_valued_path_expression | input_parameter }IS [NOT ] NULL A null comparison expression tests whether or not the single-valued path expression or input parameter is a NULL value.

SELECT distinct OBJECT(rd) FROM RequestDetail rd, DetailResponse dr 
      WHERE dr.updateReqResponseParentID IS NOT NULL 
      and dr.updateReqResponseParentID = ?1 
      and rd.requestDetailID = dr.requestDetailID
      and rd.deleted IS NULL and dr.deleted IS NULL

Here is a section from the EJB 2.1 spec that matches your queries...

11.2.5.3 Range Variable Declarations The EJB QL syntax for declaring an identification variable as a range variable is similar to that of SQL; optionally, it uses the AS keyword.

range_variable_declaration ::= abstract_schema_name [AS ] identifier Objects or values that are related to an entity bean are typically obtained by navigation using path expressions. However, navigation does not reach all objects. Range variable declarations allow the Bean Provider to designate a “root” for objects which may not be reachable by navigation. If the Bean Provider wants to select values by comparing more than one instance of an entity bean abstract schema type, more than one identification variable ranging over the abstract schema type is needed in the FROM clause.

The following finder method query returns orders whose quantity is greater than the order quantity for John Smith. This example illustrates the use of two different identification variables in the FROM clause, both of the abstract schema type Order. The SELECT clause of this query determines that it is the orders with quantities larger than John Smith’s that are returned.

SELECT DISTINCT OBJECT(o1)
FROM Order o1, Order o2
WHERE o1.quantity > o2.quantity AND
o2.customer.lastname = ‘Smith’ AND
o2.customer.firstname= ‘John’