hi roger,
with your path applied the testcase
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);
// check duplicates
Set resultsAsSet = new HashSet(results);
assertEquals(resultsAsSet.size(), results.size());
}
still returns duplicates ???
jakob
Jakob Braeuchi schrieb:
hi roger,
i ran this new testcase in QueryTest:
/**
* test OrderBy joined column. Read all Projects
*/
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);
}
and it causes the following failure with hsqldb
* Can't prepare statement:
* sql statement was 'SELECT DISTINCT A0.ID,A0.TITLE,A0.DESCRIPTION FROM
PROJECT A0 INNER JOIN PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID INNER
JOIN PERSON A2 ON A1.PERSON_ID=A2.ID ORDER BY A2.LASTNAME'
* Exception message is [ORDER BY item should be in the SELECT DISTINCT
list: [EMAIL PROTECTED] in statement [SELECT DISTINCT
A0.ID,A0.TITLE,A0.DESCRIPTION FROM PROJECT A0 INNER JOIN PERSON_PROJECT
A1 ON A0.ID=A1.PROJECT_ID INNER JOIN PERSON A2 ON A1.PERSON_ID=A2.ID
ORDER BY A2.LASTNAME]]
* Vendor error code [-71]
* SQL state code [37000]
the original testcase testOrderByJoined() produces the following sql
with your patch:
SELECT A0.ID,A0.TITLE,A0.DESCRIPTION FROM PROJECT A0 INNER JOIN
PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID INNER JOIN PERSON A2 ON
A1.PERSON_ID=A2.ID WHERE A0.TITLE LIKE 'testOrderByJoined1151088454187'
ORDER BY A0.TITLE,A2.LASTNAME,A2.FIRSTNAME
and although A2.LASTNAME,A2.FIRSTNAME are not in the select, hsqldb is
happy. the problem is the DISTINCT.
jakob
Janssen, Roger schrieb:
Hi,
If that is the case (I do not get this result with the OJB testsuite and
my patch and testcase, or don't you guys use hsql anymore for the
tests?), then probably some modification has to be made to RsIterator
class. The next and hasNext methods must make sure that you do NOT get a
duplicate object in the resultset. For the next-method (return the next
not-yet-returned object), this would not be problem, I have my doubts
about the hasNext method (hasNext should be true if there is a next
object AND it has not been returned yet). This implies that paging
(pagination) will also work again, because this is also affected by the
duplicate-records bug.
The problem is though, that with these appearent contraints by some
databases, I do not know how the paging implementation using native SQL
is going to work, since I can limit the returned records to say 10, but
if there are 5 duplicates in there, it is not the result expected (By
the way, is the paging implementation using native SQL on the roadmap
for OJB 1.1? We really need it because performance with queries on large
resultsets are killing our apps.).
Another alternative is that the SQL query generation implementation
regarding this issue could be db platform specific, and just accept the
fact that with some platforms you get duplicate objects.
Roger Janssen
iBanx
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED] Sent: Wednesday, June
21, 2006 10:13 PM
To: OJB Users List
Subject: Re: Bug : duplicate objects in resultset : Why add orderby
columns to resultset?
hi all,
this is what hsql says when the orderby column is not in the select :(
Caused by: java.sql.SQLException: ORDER BY item should be in the SELECT
DISTINCT list: [EMAIL PROTECTED] in statement [SELECT
DISTINCT A0.ID,A0.TITLE,A0.DESCRIPTION FROM PROJECT A0 INNER JOIN
PERSON_PROJECT A1 ON A0.ID=A1.PROJECT_ID INNER JOIN PERSON A2 ON
A1.PERSON_ID=A2.ID ORDER BY A2.LASTNAME]
hth
jakob
Thomas Dudziak schrieb:
On 6/19/06, Janssen, Roger <[EMAIL PROTECTED]> wrote:
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?
As far as I remember, there was a problem a couple of months ago that
for one database all orderby columns had to be selected columns as
well (though I don't remember offhand which database it was, though).
Tom
---------------------------------------------------------------------
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]
*************************************************************************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]
