Path expression that evaluates to a collection

[Yevgen Bushuyev]

Hi,
Persistence spec 4.4.5 says that
"It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection.
For example, if o designates Order, the path expression o.lineItems.product is illegal
since navigation to lineItems results in a collection. This case should produce an error when the
query string is verified."

but following query executes without exceptions on sun app server:

select count(o.lineItems) from Order o group by [B]o.lineItems.product[/B]

and spec's article on the 'group by' doesn't mention any peculiarities for path expressions used in group by clause...

So, where is the bug, in spec, in sun's implementation or i'm missing something?

wbr, eugen.

[Yevgen Bushuyev]

and

select distinct o.lineItems.product from Order o where o.lineItems.product =  :-P	 roduct

works fine...

[Mikalai Zaikin]

Probably it's vendor (Sun) specific feature.

I would trust EJB specification.

[Yevgen Bushuyev]

The specs says "This case shouldproduce an error when the
query string is verified", so that could not be a feature of a provider, only direct violation of a spec... But i'm using a sun's server bundled with ee5, which is what is called 'reference implementation' i think...

[Mikalai Zaikin]

Howdy !

What is the query result ?

All products from every line item from every order ?

thanks,
MZ

[Yevgen Bushuyev]

having

Product p1 = new Product();
em.persist(p1);
 
Product p2 = new Product();
em.persist(p2);

BillingAddress billingAddress = new BillingAddress();
em.persist(billingAddress);
 
ShippingAddress shippingAddress = new ShippingAddress();
em.persist(shippingAddress);
 

Order o1 = new Order(shippingAddress, billingAddress);
 
o1.addLineItem(new LineItem(o1, p1));
o1.addLineItem(new LineItem(o1, p1));
o1.addLineItem(new LineItem(o1, p1));
 
em.persist(o1);
 
Order o2 = new Order(shippingAddress, billingAddress);
 
o2.addLineItem(new LineItem(o2, p2));
o2.addLineItem(new LineItem(o2, p2));
o2.addLineItem(new LineItem(o2, p2));
 
em.persist(o2);

then

                select count(o.lineItems) from Order o group by o.lineItems.product

returns 9, 9.

Tried to change it to

select o.lineItems.product, count(o.lineItems) from Order o group by o.lineItems.product

and got

Internal Exception: java.sql.SQLException: ORA-00979: not a GROUP BY expression
Error Code: 979
Call:SELECT t0.ID, COUNT(*) FROM LINEITEM t4, PRODUCT t3, ORDERR t2, LINEITEM t1
, PRODUCT t0 WHERE ((((t1.ORDER_ID = t2.ID) AND (t0.ID (+) = t1.PRODUCT_ID)) AND
(t4.ORDER_ID = t2.ID)) AND (t3.ID = t4.PRODUCT_ID)) GROUP BY t3.ID
Query:ReportQuery(test.entities.example.Order)
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(D
atabaseException.java:303)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.ba
sicExecuteCall(DatabaseAccessor.java:551)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.ex

.

But

select distinct o.lineItems.product from Order o where o.lineItems.product =  :-P	 roduct

still works ok

wbr, eugen.
[ December 22, 2006: Message edited by: Yevgen Bushuyev ]

[Yevgen Bushuyev]

the thing with group by should be like this:

select l.product, count(o.lineItems) from Order o, in (o.lineItems) l group by l.product

which is actually works.

wbr, eugen.

[Mikalai Zaikin]

When I asked about query result, I meant:

select distinct o.lineItems.product from Order o where o.lineItems.product =  :product

Just curious, is it runnig Ok too ? For me it's obvious invalid.

Regarding 'GROUP BY' - I am not sure it's on the test - not mentioned in objectives. (I may be wrong).

Thanks,
MZ

[Yevgen Bushuyev]

yes, it runs ok and returns the same product as passed to the parameter.

wbr, eugen.

[Mikalai Zaikin]

Hi !

Just a hint ...

Try to pass in not a product object reference, rather than some primitive, or simple string.

It must throw an exception at runtime, and in the stack trace you will be able to examine the actual SQL it uses for this Query.

Thanks,
MZ