Enthuware question on inner join

[Ralph Jaus]

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]

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]

Inner join never form's cartesian product.

[Ralph Jaus]

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

@Entity
public class Student
{
  @Id 
  private int id;
  private String name;

  @OneToMany(mappedBy="student", cascade=CascadeType.ALL)
  private List<Presentation> presentations; 

  // plus getter and setter methods 
} 

@Entity
public class Presentation
{
  @Id 
  private int id;
  private String name;

  @ManyToOne
  private Student student;

  // plus getter and setter methods 
} 

Tables:

Student:             ID   NAME 
                    --------------                  
                     1    S1 
                     2    S2 


Presentation:         ID   NAME   STUDENT_ID 
                    ---------------------------
                      1    P1     1
                      2    P2     1
                      3    P3     2
                      4    P4     2 

Query:

String qString = "select s from Student s join s.presentations p";
Query query = entityManager.createQuery(qString);

List list = query.getResultList();
for (Object o: list)
{
  Student s = (Student)o;
  System.out.println( s.getId() + "  " + s.getName() );
}

SQL generated by toplink:

SELECT t0.ID, t0.NAME FROM STUDENT t0, PRESENTATION t1 WHERE (t1.STUDENT_ID = t0.ID) 

Query result:

ID    NAME 
-------------
1     S1
1     S1 
2     S2
2     S2 
 

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]

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]

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.