You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Janssen, Roger" <ro...@ibanx.nl> on 2006/06/22 09:38:19 UTC
RE: Bug : duplicate objects in resultset : Modify RsIterator class?
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:jbraeuchi@gmx.ch]
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: org.hsqldb.Expression@1566a6c 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 <ro...@ibanx.nl> 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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
*************************************************************************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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Bug : duplicate objects in resultset : Modify RsIterator class?
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: org.hsqldb.Expression@c6eff5 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:jbraeuchi@gmx.ch] 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: org.hsqldb.Expression@1566a6c 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 <ro...@ibanx.nl> 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: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>> *************************************************************************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: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Bug : duplicate objects in resultset : Modify RsIterator class?
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: org.hsqldb.Expression@c6eff5 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:jbraeuchi@gmx.ch]
> 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: org.hsqldb.Expression@1566a6c 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 <ro...@ibanx.nl> 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: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
> *************************************************************************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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org