Hi,
Sorry for the late reply but i have been ill the last week.
Jakob indicated that with my patch applied, still duplicate objects were
being returned in the resultset. I had a look at the testcase
(testOrderByJoined2) and i can confirm this.
The trick is that you have to execute a distinct query. But... Jakob
also indicated that HSQLDB than gives a sql syntax error, and i can
confirm this.
The case is that:
SELECT A0.X, A0.Y, A0.Z FROM T0 AS A0 JOIN T1 as A1 ORDER BY A1.Q
is a correct statement according to HSQLDB, but
SELECT DISTINCT A0.X, A0.Y, A0.Z FROM T0 AS A0 JOIN T1 as A1 ORDER BY
A1.Q
is incorrect, then it suddenly requires the orderby collumns in the
resultset like
SELECT DISTINCT A0.X, A0.Y, A0.Z, A1.Q FROM T0 AS A0 JOIN T1 as A1
ORDER BY A1.Q
but THAT is what creates the duplicate records in the resultset.
Imho i consider this to be an inconsistency/bug in HSQLDB. I do not
think it is correct that the rdbms requires me to have specific columns
in the resultset actually affecting the queryresult.
I believe that MySQL does NOT have this problem, my guess is that loads
of other rdbms's do not either. So it now has become a platform specific
problem. I extended my patch making the query generation regarding the
inclusion of the orderby columns platform specific.
What did i do?
- added method to Platform interface : boolean requiresOrderbyColumns();
- added method to PlatformDefaultImpl class : boolean
requiresOrderbyColumns() returning true;
- added method to PlatformMySQLImpl class : boolean
requiresOrderbyColumns() returning false;
- in SqlQueryStatement declared m_platform to be protected instead of
private
- in SqlQueryStatement added addJoinColumn methods (as in earlier patch)
- in SqlQueryStatement added ensureJoinColumns method (as in earlier
patch)
- in SqlQueryStatement modified method appendOrderByClause to use real
names instead
of resultset column indexes in orderby clause (as in earlier patch)
- in SqlSelectStatement modified method buildStatement:
// iBanx patch - start
if(m_platform.requiresOrderbyColumns())
{
ensureColumns(orderByFields, columnList, stmt);
}
else
{
ensureJoinColumns(orderByFields, columnList);
}
// iBanx patch - end
Todo : implement requiresOrderbyColumns method in other rbms specific
Platform implementations returning false if it the rdbms does NOT
require the orderby columns to be in the resultset.
This patch ensures correct sql syntax and builds a query that does not
return duplicate objects when the database supports the required sql
syntax. Of course, it has to be a 'distinct' query!
So, running the tests in QueryTest, on an HSQLDB they will fail, on a
MySSQL db they will pass.
I included modified files in the zip. Modification are marked with "//
iBanx patch - start" and
"// iBanx patch - end".
I also suggested another solution, modifying RsIterator so that the
'next' and 'hasNext'
methods filter out the duplicate records in the resultset. But this will
not work when the
duplicate records/objects are spread over multiple pages of the
resultset, since only duplicates
in one page would be filtered out. So this is not a good solution.
Will this new patch work, and if so, will it be applied in 1.0.5 and
1.1?
Roger Janssen
iBanx
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Friday, June 23, 2006 9:54 PM
To: OJB Users List
Cc: Selders, Bart
Subject: Re: Bug : duplicate objects in resultset : Modify RsIterator
class?
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]
------------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
