Re: BUGREPORT : OJB 1.0.5rc1 : UserAliases in OrderBy clauses are not replaced by the generated aliases for the joins resulting in illegal SQL statements

Hi Roger,

Janssen, Roger wrote:
> Hi Armin,
>
> Just to make it absolutely clear so we talk about the same thing;
>
> When I execute a reportquery, I expect to get records back, and if I get
> duplicate records back because of the joins I created, well that is
> fine. But when I execute a 'normal' query, and OJB returns instantiated
> objects, I would like to get a list without duplicated records. 
>
> If this just is not possible, so be it.

You can get duplicate objects back too if the order-by clause contains 
fields which are not part of the output columns (class fields). It's a 
general behavior of OJB.

For example, if I modify #ensureColumnsOrderBy and specify a order-by 
field/column which is not part of the output columns we get:

1.)
SELECT DISTINCT A0.ID_,A0.TITLE_,A0.VERSION_
FROM SAMPLE_BOOK A0
INNER JOIN SAMPLE_REVIEW A1 ON A0.ID_=A1.FK_BOOK
WHERE (A0.ID_ < A1.ID_) AND
A0.TITLE_ LIKE '%_testOrderBy_2_1204304719937_%'
ORDER BY A1.ID_ DESC

This doesn't work with maxDB and oracle (I suppose other DB too). As 
workaround OJB always adds the missing columns as "hidden" output 
columns. With the unmodified #ensureColumnsOrderBy method OJB will generate:

2.)
SELECT DISTINCT A0.ID_,A0.TITLE_,A0.VERSION_,A1.ID_ as ojb_col_4
FROM SAMPLE_BOOK A0
INNER JOIN SAMPLE_REVIEW A1 ON A0.ID_=A1.FK_BOOK
WHERE (A0.ID_ < A1.ID_) AND
A0.TITLE_ LIKE '%_testOrderBy_2_1204305513406_%'
ORDER BY 4 DESC

I don't know if the order-by clause in the 1. example makes sense, in 
the 2. example the returned result set makes sense (with ordered A1.ID_ 
column and duplicated A0.ID_,A0.TITLE_,A0.VERSION_ columns for each 
match) but the result returned by OJB ignore the ojb_col_4 and only 
returns A0.ID_,A0.TITLE_,A0.VERSION_.

> It is always nice to have something configurable :) ... But adding a new
> method to invoke different behaviour on different databases, I don't
> know if that's what we all want... I am not eager to write database
> dependent code... So if it is not unavoidable to prevent duplicate
> records... I can live with that..

If we make it configurable you can choice which behavior of OJB you 
prefer (1. doesn't work with all DB, or 2. works for all DB but return 
unexpected duplicates).

regards,
Armin

> Roger Janssen
>
> -----Original Message-----
> From: Armin Waibel [mailto:[EMAIL PROTECTED] 
> Sent: Friday, February 29, 2008 4:56 PM
> To: OJB Users List
> Subject: Re: BUGREPORT : OJB 1.0.5rc1 : UserAliases in OrderBy clauses
> are not replaced by the generated aliases for the joins resulting in
> illegal SQL statements
>
> Hi Roger,
>
> Janssen, Roger wrote:
> > Hi Armin,
> >
> > Tested what you described and the ordering seems to work in our setup 
> > as well.
> >
> > What does not work however is that I get duplicate items returned. For
>
> > every match in the right-handside of the 1:N relation, I get the same 
> > object returned. Even if I execute a distinct query.
>
> This behavior is intended by OJB. OJB adds all "unknown fields/columns" 
> to the query statement. See SqlQueryStatement#ensureColumnsOrderBy.
>
> If you change this method in the same way like #ensureColumnsGroupBy
> works, the fields won't be added and you won't get duplicate items.
>
> This works with e.g. mysql but doesn't work for e.g. maxDB, oracle -
> they always expect an output column in the order-by clause.
> So, I don't know how to solve this issue? Should we make this
> configurable e.g. Query.addMissingOrderByColumns(boolean enable)?
>
>
> > Also the setPathOuterJoin functionality does not work. Inner joins 
> > keep being generated. After this latest info from you I tried the same
>
> > with the setPathOuterJoin :
> >
> >         query.setPathOuterJoin("myAlias");
> >
> > And
> >
> >       query.setPathOuterJoin("myAlias.collectionAttr");
> >
> > But no luck.
>
> ok I will try to setup some tests too.
>
> regards,
> Armin
>
> > Greetings,
> >
> > Roger Janssen
> > iBanx
> >
> > -----Original Message-----
> > From: Armin Waibel [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, February 27, 2008 2:51 PM
> > To: OJB Users List
> > Subject: Re: BUGREPORT : OJB 1.0.5rc1 : UserAliases in OrderBy clauses
>
> > are not replaced by the generated aliases for the joins resulting in 
> > illegal SQL statements
> >
> > Hi Roger,
> >
> > I start work on OJB-137 (your issue) and noticed while "playing" with 
> > order-by tests that the following notations seems to work (class Book 
> > has a 1:n relation to class Review):
> >
> > Criteria c1 = new Criteria()
> >          .addEqualTo("reviews.summary", "3_review" + name); 
> > c1.setAlias("alias_1"); Criteria c2 = new Criteria()
> >          .addEqualTo("reviews.summary", "4_review" + name); 
> > c2.setAlias("alias_2"); c1.addAndCriteria(c2); ReportQueryByCriteria q
>
> > = QueryFactory.newReportQuery(Sample.Book.class,
> > c1, true);
> > q.setAttributes(new String[]{"id", "title","reviews.id"}); 
> > q.addOrderByDescending("alias_2.reviews.id");
> >
> > If I prefix the path expression with the user alias it seems to work 
> > ("alias_2.reviews.id" instead of "alias_2.id"). Did you tried this
> too?
> > regards,
> > Armin
> >
> >
> > Armin Waibel wrote:
> > > Hi Roger,
> > >
> > > I create two new "user-alias" related bug reports:
> > >
> > > http://issues.apache.org/jira/browse/OJB-137
> > >
> > > https://issues.apache.org/jira/browse/OJB-139
> > >
> > > OJB-137 reflects your issue. Locally I fixed OJB-139 (easier to fix 
> > > then
> > > 137) and start work on OJB-137 (seems more complex to fix).
> > >
> > > regards,
> > > Armin
> > >
> > > Armin Waibel wrote:
> > > > Hi Roger,
> > > >
> > > > now I get your point (I'm a bit slow on the uptake ;-)). I start 
> > > > writing many new "order by" tests to isolate the problem and to make
>
> > > > sure that changes don't have unrequested side-effects.
> > > >
> > > >  > itself was not stable enough for us). In fact, I helped 
> > > > implementing  > this feature in OJB a long time ago, and I believe 
> > > > the documentation  > on  > the OJB site is the documentation I once 
> > > > sent to you guys.
> > > >
> > > > You are right, I found your example in the query documentation. But 
> > > > I
> > > > can't find a test in the OJB test-suite. I think this is the reason 
> > > > why this feature got lost between 1.0.rc6 and now (the 
> > > > criteria/query
> > > > stuff was reworked/improved since 1.0rc6).
> > > >
> > > > My new tests show another bug when using a user alias on a 1:n 
> > > > relation with table-per-subclass inheritance - but this is another 
> > > > story. After finish test writing, I will do my best to find a patch 
> > > > for your problem and keep you up-to-date.
> > > >
> > > > regards,
> > > > Armin
> > > >
> > > > Janssen, Roger wrote:
> > > > > Hi,
> > > > >
> > > > > The management summary answer to your question "is it important to 
> > > > > support alias-names in order by and having clause" is : Yes, it is 
> > > > > really really really important!!!
> > > > >
> > > > > So now for some background information explaining our situation.
> > > > >
> > > > > We implemented a concept to support non-modelled (abstract)
> > attributes.
> > > > > We need to support many different complex forms and datasets for 
> > > > > our
> > > > > customers. The deviation between customers is huge, so modifying 
> > > > > our
> > > > > domain model (and thus our pojos and thus out database tables) for 
> > > > > every customer implementation is not an option. We need to have a 
> > > > > stable core domain model.
> > > > >
> > > > > So all our objects in our domain model have a collection property 
> > > > > that is capable of holding a collection of abstract-attribute 
> > > > > instances, it's a standard 1:N relation which OJB supports. 
> > > > > Abstract
> > > > > attribute classes are classes implementing name-value tuples, the 
> > > > > name of the attribute, and the value of the attribute. So adding 
> > > > > new
> > > > > attributes to specific objectclass instances implies we just have 
> > > > > to
> > > > > add abstract attribute instances to the collection and OJB will 
> > > > > store them in the apropriate table in the database. We do not have 
> > > > > to modify our domain model by adding new java properties to the 
> > > > > affected classes and adding new columns to the affected tables.
> > > > >
> > > > > So querying for values of object properties, represented by 
> > > > > abstract
> > > > > attributes, we need to create clauses combining the name/value 
> > > > > properties of the abstract attribute instances.
> > > > >
> > > > > So an example query with two selection criteria based on abstract 
> > > > > attributes of an object could be:
> > > > >
> > > > > SELECT * FROM PERMIT AS A0 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS
> > > > > A1 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS A2 WHERE A0.ID=A1.ID AND
>
> > > > > A0.ID=A2.ID AND
> > > > >       (A1.NAME='<name of attribute 1> AND A1.VALUE='<value of 
> > > > > attribute
> > > > > 1>') AND
> > > > >       (A2.NAME='<name of attribute 2> AND A2.VALUE='<value of 
> > > > > attribute
> > > > > 2>');
> > > > >
> > > > > Since we need to work with bounded name/value pairs, we need to 
> > > > > bind
> > > > > them together using a alias, in this example the aliases are A1 and
> > A2.
> > > > > So if we want to order the resultset on such an abstract attribute 
> > > > > value the query would look like:
> > > > >
> > > > > SELECT * FROM PERMIT AS A0 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS
> > > > > A1 INNER JOIN PERMIT_ABSTRACT_ATTRIBUTE AS A2 WHERE A0.ID=A1.ID AND
>
> > > > > A0.ID=A2.ID AND
> > > > >       (A1.NAME='<name of attribute 1> AND A1.VALUE='<value of 
> > > > > attribute
> > > > > 1>') AND
> > > > >       (A2.NAME='<name of attribute 2> AND A2.VALUE='<value of 
> > > > > attribute
> > > > > 2>')
> > > > > ORDER BY A2.VALUE;
> > > > >
> > > > > We need to use the alias to link the orderby to the proper join, of
>
> > > > > which there are two, to the same join table (the abstract attribute
>
> > > > > table)!
> > > > >
> > > > > This is rather common functionality, currently supported in all our
>
> > > > > applications, supported by OJB 1.0rc6 that we use (the 1.0 release 
> > > > > itself was not stable enough for us). In fact, I helped 
> > > > > implementing
> > > > > this feature in OJB a long time ago, and I believe the 
> > > > > documentation
> > > > > on the OJB site is the documentation I once sent to you guys.
> > > > >
> > > > > The upcoming 1.0.5 release has some features that we have been 
> > > > > waiting for, for several years. We have been able to postpone 
> > > > > improvements in our applications for this long, but we cannot do 
> > > > > this any longer. So we really need these new features 1.0.5 
> > > > > implements, but we need the user-alias in the orderby to work as 
> > > > > well, otherwise we will loose a lot of functionality and our
> > customers will not accept that.
> > > > > Some of the required features are the native limit-clause 
> > > > > implementations, the reference descriptor implementation without 
> > > > > using foreign-keys (I requested this feature for more then a year 
> > > > > ago myself), etc., etc..
> > > > > I hope you now have an understanding of how we use this feature, 
> > > > > and
> > > > > that we cannot afford to loose it.
> > > > >
> > > > > Greetings,
> > > > >
> > > > > Roger Janssen
> > > > > iBanx
> > > > > *******************************************************************
> > > > > *
> > > > > *****
> > > > >
> > > > > The 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 maintained nor that the communication is free of viruses, 
> > > > > interceptions or interference.
> > > > > If you are not the intended recipient of this communication please 
> > > > > return the 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]
> >
> >
> >
> > ---------------------------------------------------------------------
> > 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]