Bob,
I did a quick test. This is a bug.
Cayenne suppresses DISTINCT when doing queries that include CLOB/BLOB
columns in the result, as this doesn't work on many databases. This
part is expected (DISTINCT on CLOBs actually works on Postgres, but
this is beyond the point). Later knowing that DISTINCT was suppressed
it applies "quick" DISTINCT logic in-memory, by excluding rows with
duplicate PK columns. However in doing that it ignores the fact that
ResultSet contains a join of two tables. This is wrong. I'll log a
bug in Jira with a test case to prove it.
Thanks
Andrus
On Dec 13, 2005, at 9:24 PM, bob schellink wrote:
> Hello list,
>
> Running Window 2k, Cayenne 1.2M8, Postgres 8.0, using cayenne-cmd-
> app as an example.
>
> I am trying out the new prefetch stuff and might have found a issue.
>
> I changed the Main.java class findGallery() method to use a joint
> prefetch
>
> private Gallery findGallery(String galleryPattern) {
> String likePattern = "%" + galleryPattern + "%";
> Expression qual = ExpressionFactory.likeIgnoreCaseExp
> ("galleryName",
> likePattern);
> SelectQuery query = new SelectQuery(Gallery.class, qual);
>
> query.addPrefetch ("paintingArray").setSemantics
> (PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
> List galleries = ctxt.performQuery(query);
> Gallery gallery = (Gallery) galleries.get(0);
>
> System.out.println("Paintings in Gallery: " +
> gallery.getPaintingArray ().size());
> ....
>
> Here is the query that gets executed :
>
> INFO QueryLogger: SELECT DISTINCT t0.GALLERY_NAME, t0.GALLERY_ID,
> t1.ESTIMATED_PRICE, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.PAINTING_ID
> FROM GALLERY t0, PAINTING t1 WHERE t0.GALLERY_ID = t1.GALLERY_ID
> AND (t0.GALLERY_NAME ILIKE ?) [bind: '%Gallery1%'] - prepared in 31
> ms.
>
> The number of paintings in the gallery is 3 in my case. That is fine.
>
> However if I alter the database gallery.name column from type
> varchar to
> text, and rerun this application, the number of paintings is 1.
>
> And there is a little difference between the two queries :
>
> INFO QueryLogger: SELECT t0.GALLERY_NAME, t0.GALLERY_ID,
> t1.ESTIMATED_PRICE, t1.PAINTING_TITLE, t1.ARTIST_ID, t1.PAINTING_ID
> FROM GALLERY t0, PAINTING t1 WHERE t0.GALLERY_ID = t1.GALLERY_ID
> AND (t0.GALLERY_NAME ILIKE ?) [bind: '%Gallery1%'] - prepared in 47
> ms.
>
> Notice that in the second query there is no DISTINCT in the select.
>
> BTW with DISJOINT_PREFETCH_SEMANTICS everything works fine in both
> cases.
>
> Is this is a known issue or am I missing something, like a setting
> somewhere?
>
> Kind Regards,
>
> bob
>
>
This archive was generated by hypermail 2.0.0 : Wed Dec 14 2005 - 02:06:52 EST