Why DISTINCT with OBJECT in QL?

[B.Sathish]

Why do we need DISTINCT with OBJECT?

SELECT DISTINCT OBJECT (m) FROM MovieSchema m

would be the same as

SELECT OBJECT (m) FROM MovieSchema m

because this query would return a collection of Movie Component interface references which means a collection of movie entities. Each movie entity is unique, right ???

I understand if you use DISTINCT with cmp field type :

SELECT DISTINCT m.title FROM MovieSchema m

because there might be the same titles in the Collection returned (assuming 2 movies can have the same title).

Could someone pl tell me whether my understanding is right?

[Peer Reynders]

I would tend to agree with you.

I do however cringe a bit here:

Originally posted by B Sathish:
would be the same as

I'd prefer produces the same result as - because they are certainly not the same. Never use DISTINCT if you know that your results will be unique anyway. DISTINCT will inject the DISTINCT keyword into the SQL query which means that the RDBMS will waste time to try to eliminate duplicates - and depending on the size of the result set that could be a considerable amount of time.

However SELECT DISTINCT OBJECT (m) is necessary with a more complicated (Finder Query) where the same object may appear more than once according the criteria specified by the WHERE clause (and you don't care for looking at the same entity twice) as in:

SELECT [B]DISTINCT[/B] OBJECT(d) FROM DirectorSchema d, MovieSchema m 
    WHERE (m.genre IN ('Action','Sci-fi')) 

based on the table on page 400 in HFEJB Movie 12 would bring up director 42 once as would Movie 11, so DISTINCT would eliminate the second reference to the Director 42 object. So

SELECT OBJECT(d) FROM DirectorSchema d, MovieSchema m 
    WHERE (m.genre IN ('Action','Sci-fi')) 

would not produce the same results.
[ October 28, 2005: Message edited by: Peer Reynders ]

[B.Sathish]

Thanks Peer, I agree with you, in fact I realized this before reading your message. But in the example you have given, I think the query should be :

SELECT DISTINCT OBJECT(d) FROM DirectorSchema d, IN (d.movies) m WHERE (m.genre IN ('Action','Sci-fi'))

and not

SELECT DISTINCT OBJECT(d) FROM DirectorSchema d, MovieSchema m WHERE (m.genre IN ('Action','Sci-fi'))

because you want to search only those movies which are in each director's collection of movies. Let me know what you feel

[Peer Reynders]

SELECT DISTINCT OBJECT(d) FROM DirectorSchema d, IN (d.movies) m WHERE (m.genre IN ('Action','Sci-fi')) 

looks right. I guess my previous SQL experience did me a disservice in this particular case, as I was simply thinking tables.