EJB-QL question

[jeff mutonho]

Page 434 HF EJB.
Can someone explain to me why answer B is correct?Which part of the query ensures that the orders returned have line items?Does the IN(o.lineItems) li
just mean "let li represent an individual line item in the Order Collection?

[jeff mutonho]

You can ignore my previous question.Mastering seems to explain this clearly.

[B.Sathish]

Jeff,
Can you please post what Mastering says regarding this. I too had this doubt and would like to understand how this works.

[jeff mutonho]

On page 573,Roman says

the variables. For example:
SELECT OBJECT(o)
FROM Order AS o, Customer AS c
The above query finds all orders if and only if there are any customers (which
do not need to be related to the Order objects). Even though we aren't using the
variable c anywhere else, we are still excluding orders if there are no cus�
tomers. By declaring a variable, you are constraining the domain of the query.
This is similar to the following SQL statement that returns all orders so long as
there are one or more records in the Customer table:
SELECT o.*
FROM Order o, Customer c

As the first line states , The above query finds all orders if and only if there are any customers (which do not need to be related to the Order objects)

That explain why

jeff mutonho

[jeff mutonho]

Meant , The "if and only if" explains why.

jeff mutonho

[B.Sathish]

Thanks for that Jeff, but somehow I feel a little squeamish about this...

[jeff mutonho]

Why?What is that you're not understanding?

[B.Sathish]

I understand it...just meant to say that this was not very intuitive for me.

[Peer Reynders]

Originally posted by jeff mutonho:
Does the IN(o.lineItems) li just mean "let li represent an individual line item in the Order Collection?

I kind of touched on that issue in HFEJB p.415: Comma in EJB-QL expression by referring to the EJB-QL language defintion.

Originally posted by B Sathish:
somehow I feel a little squeamish about this...

I think that queasy feeling is just a natural reaction to the tension resulting from the uneasy co-existence of the relational and object paradigm within EJB-QL.

[Peer Reynders]

This is similar to the following SQL Statement all orders so long as there are one or more records in the Customers Table:
SELECT o.*
FROM Order o, Customer c

And in which way is this explanation supposed to be helpful? Thats like saying multiplying any number by zero results in zero. In fact that query is pretty much useless as it produces a cartesian product. To make my point here are some examples from the pointbase database that comes with the Sun RI 1.4 J2EE server:

[I]Number of orders:[/I]
SQL> SELECT COUNT(o.Order_Num) FROM Order_Tbl o;
COUNT(o.Order_Num)
---------------------------------------
15
 
[I]Number of customers:[/I]
SQL> SELECT COUNT(c.Customer_Num) FROM Customer_Tbl c;
COUNT(c.Customer_Num)
---------------------------------------
13
 
SQL> [B]SELECT o.* FROM Order_Tbl o, Customer_Tbl c;[/B]
ORDER_NUM     CUSTOMER_NU   REP_NUM       PRODUCT_NUM   SALES_TAX_   QUANTI
-----------   -----------   -----------   -----------   ----------   ------
10398001      1             5001          980001        FL           10
[I]...another 194 rows of data elided...[/I]
195 Rows Selected.
 
[I]195 Rows, thats a cartesian product!!! i.e.: [/I]
 
SQL> SELECT COUNT(o.Order_Num) 
    FROM Order_Tbl o [B]CROSS JOIN[/B] Customer_Tbl c;
COUNT(o.Order_Num)
---------------------------------------
195
 
[I]This is more useful (all orders with a customer):[/I]
SQL> SELECT COUNT(o.Order_Num) 
    FROM Order_Tbl o [B]INNER JOIN[/B] Customer_Tbl c USING (Customer_Num);
COUNT(o.Order_Num)
---------------------------------------
15
 
[I]Variation: All Orders regardless if a customer is present:[/I]
SQL> SELECT COUNT(o.Order_Num) 
    FROM Order_Tbl o [B]LEFT OUTER JOIN[/B] Customer_Tbl c USING (Customer_Num);
COUNT(o.Order_Num)
---------------------------------------
15
 
[I]Variation: All Orders, including a separate row for each customer without orders[/I]
SQL> SELECT COUNT(c.Customer_Num) 
    FROM Order_Tbl o [B]RIGHT OUTER JOIN[/B] Customer_Tbl c USING (Customer_Num);
COUNT(c.Customer_Num)
---------------------------------------
16

[jeff mutonho]

This is similar to the following SQL Statement all orders so long as there are one or more records in the Customers Table:
SELECT o.*
FROM Order o, Customer c

The confusion came from how specifying " Customer c " introduces the condition that the results will depend on the existence of "one or more records in the Customers Table"

[Peer Reynders]

Originally posted by jeff mutonho:
" Customer c " introduces the condition that the results will depend on the existence of "one or more records in the Customers Table"

I'm objecting to the book presenting it as a special case. In SQL it is understood that if you don't specify a join condition the result will be the cartesian product of the (two, three, four,...) tables - which means every row "will be joined" with every other row. So if any one table contains zero rows - the result has no rows - big surprise. :roll:

The passage in the book (in this context) is silent on the fact that

SELECT OBJECT(o) FROM Order AS o, Customer AS c

will produce a cartesian product (see EJB Spec 11.3.5 p.238). Example: If there are 3 orders and 5 customers, that query will present each of the three orders to you five times.
Once you understand that it produces a cartesian product the behaviour they are pointing out is no longer "surprising". Instead they make it look like a special case just to avoid having to explain what a cartesian product is.

With

SELECT OBJECT(o) FROM Customer c, IN(c.orders) o 

you indicate that you wish to use the defined CMR between customer and order "as join condition", i.e.:

SELECT o.* FROM Customer c, Orders o WHERE c.customer_id = o.customer_id;
-- or using join syntax
SELECT o.* FROM Customer c INNER JOIN Orders o USING( customer_id );

[ November 02, 2005: Message edited by: Peer Reynders ]

[B.Sathish]

I am relieved now :

SELECT OBJECT(o) FROM Order AS o, Customer AS c

will give a cartesian product