• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Enthuware question on inner join

 
Ralph Jaus
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is a one-to-many relationship between the entities Student
and Presentation (a student can have many presentations).

Student S1 refers to Presentations P1 and P2
Student S2 refers to Presentations P3 and P4

What's the size of the collection returned by the following query:

select s from Student s inner join s.presentations p

The given answer is: 2

Enthu's explanation: Since there is an inner join between Student and Presentation, it just selects all the
Students for which there is a presentation. Since there are 2 such Students, it returns a collection of size 2.

But to my understanding an inner join forms the cartesian product of the involved tables. So the join
should yield the table

S1 P1
S1 P2
S2 P3
S2 P4

Then, if the Students are selected the result should be S1, S1, S2, S2. So the answer would be 4. I also tried
this scenario with glassfish/toplink and the result was indeed 4.

What do you think ?
 
Raf Szczypiorski
Ranch Hand
Posts: 383
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Enthuware is right - it will perform the inner join, and since the select clause returns only s, it will return only students that have at least one presentation, and it either returns the presentation collection or not (you don't tell us if it is lazy). If you wanted to return 4 results:
S1 P1
S1 P2
S2 P3
S2 P4
the query would have to be: select s, p from Student s inner join s.presentations p. Are you sure you checked the very same scenario on Glassfish?
 
Amandeep Singh
Ranch Hand
Posts: 850
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Inner join never form's cartesian product.
 
Ralph Jaus
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

thanks for your replies. I tried again, but I didn't get the desired result. My output always consists of 4 objects. The details are Tables:Query:
SQL generated by toplink: Query result: The result is the same whether table Presentation contains a foreign key constraint on Student.ID or not.

Has anybody an idea why it doesn't return just the 2 students ? Thanks.

 
Paul Anilprem
Enthuware Software Support
Ranch Hand
Posts: 3819
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ralph,
I just ran the code and I am getting 2 rows. The code that I am running is given along with EJBPlus question bank (in Toy Application). The only difference that I see in the code that you've given here is that you are using private List<Presentation> presentations; while the code in the question (and in the sample program) is using private Collection<Presentation> presentations; But that shouldn't be an issue.

The sql that is being generated is:

SELECT DISTINCT t0.ID, t0.LASTNAME, t0.ROLLNUMBER, t0.FIRSTNAME, t0.SCORE FROM STUDENT t0, PRESENTATION t1 WHERE (t1.PRESENTER_ID = t0.ID)

Can you please tell me the version of the app server you are using?

Just realized that there is another difference in the query that you are using and the one given in question:
SELECT s from Student s INNER JOIN s.presentations p <-- INNER is missing in your query or is it just a typo here?

thanks,
Paul.
 
Ralph Jaus
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

I tried again with "inner" but that had no effect (I think it's optional anyway).

I'm using Glassfish version 9.1_02 (build b04-fcs).

Maybe it's an versional problem, but I don't really belive that. Because joins will surely be
frequently used in practise and I guess the problem would have been found / fixed years ago.
But who knows.

Anyway, thanks to showing me your generated SQL, I know how it should work. That's sufficient.

Thank you very much.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic