Hi, Diving deeper into the OJB code I see that the ensureColumns method in fact does two things: (1) add the columns to the returnable set of columns in the resultset (2) adds the columns to a list of columns to be used in force a join
(2) is okay for orderby's, but (1) is not. If for orderby's (1) was left out, would that be a problem? Roger Janssen iBanx -----Original Message----- From: Janssen, Roger [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 5:32 PM To: OJB Users List Subject: RE: Bug : duplicate objects in resultset : Why add orderby columns to resultset? Hi, I found the following in OJB code: For Version 1.0.4: In class SqlSelectStatement in method protected String buildStatement(): orderByFields = query.getOrderBy(); columnList = ensureColumns(orderByFields, columnList, stmt); For Version 1.0 RC6 (which we are still using... sorry) In class SqlSelectStatement in method public String getStatement(): if (orderByFields != null && !orderByFields.isEmpty()) { orderByColumnNumbers = ensureColumns(orderByFields, columnList, stmt); } The above code raises the question why these columns are added when these columns are only appearing in the orderby? This is the root of the problem, that is causing multiple resultsets for one and the same object to be returned. OJBIterator implementations do not filter out the duplicates, and I do not think that that is the right way to go, how do you implement paging for that matter, especially when native SQL will be used implementing paging? Is their a specific reason why these columns are added? Roger Janssen iBanx -----Original Message----- From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Friday, June 16, 2006 9:11 PM To: OJB Users List Subject: Re: Bug : duplicate objects in resultset hi roger, the following works for me. i use a set instead of a collection: public void testOrderByJoined2() { Criteria crit = new Criteria(); QueryByCriteria q = QueryFactory.newQuery(Project.class, crit, true); q.addOrderByAscending("persons.lastname"); Collection results = broker.getCollectionByQuery(q); assertNotNull(results); ManageableHashSet resultsMgmt = (ManageableHashSet) broker.getCollectionByQuery(RemovalAwareSet.class, q); assertNotNull(resultsMgmt); // compare with count int count = broker.getCount(q); assertEquals(resultsMgmt.size(), count); } hth jakob Janssen, Roger schrieb: > Hi, > > Thanx for the confirmation. > > Is it necessary to add the orderby column to the returned columns in > the resultset, when the orderby column is a column of a joined table? > This is what causes this behaviour. I personnaly do not think so, > since for instantiating the objects, it is not required to be in the resultset. > > Example: > > OJB generated SQL (A1.VALUE is added to columns in resultset): > > SELECT DISTINCT A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS,A1.VALUE as > ojb_col_49 > FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_ABSTRACTATTRIBUTE > A1 ON A0.ID=A1.OID WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER > BY 4 > > What it (could) should look like, and gives the correct results (no > duplicates in resultset): > > SELECT DISTINCT A0.ID, A0.ORG_CONTEXT,A0.ENABLED,A0.ENTITY_CLASS > FROM IBANX_LOG_EVENT A0 INNER JOIN IBANX_LOG_EVENT_AA A1 ON > A0.ID=A1.OID > > WHERE (( A1.NAME = ?) AND (A1.VALUE LIKE ?)) ORDER BY A1.VALUE > > If you agree that this is the problem, is their a quick patch I can > apply? > > Thanx. > > Roger Janssen > iBanx > > -----Original Message----- > From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 15, 2006 10:13 PM > To: OJB Users List > Cc: Selders, Bart > Subject: Re: Bug : duplicate objects in resultset > > hi roger, > > i can confirm this problem. > this simple testcase shows the duplicates: > > /** > * test OrderBy joined column > */ > public void testOrderByJoined2() > { > Criteria crit = new Criteria(); > QueryByCriteria q = QueryFactory.newQuery(Project.class, crit); > q.addOrderByAscending("persons.lastname"); > > Collection results = broker.getCollectionByQuery(q); > assertNotNull(results); > > // compare with count > int count = broker.getCount(q); > assertEquals(results.size(), count); > > Set resultsSet = new HashSet(results); > assertEquals(resultsSet.size(), results.size()); > } > > jakob > > > > ********************************************************************** > ***T he information contained in this communication is confidential > and is intended solely for the use of the individual or entity to whom > it is addressed.You should not copy, disclose or distribute this > communication without the authority of iBanx bv. iBanx bv is neither > liable for the proper and complete transmission of the information has > been maintainedn or that the communication is free of viruses, > interceptions or interference. > > If you are not the intended recipient of this communication please > returnt he communication to the sender and delete and destroy all copies. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]