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,
> 
> When you have two object classes, Permit and AbstractAttribute and
> Permit holds a collection of AbstractAttributes (using a collection
> descriptor for the abstractattribute collection on the Permit in the
> mapping file), we can create a query like below:
> 
> 
> 		Criteria crit1 = new
> Criteria().addEqualTo("allAbstractAttributes.name", "aaTest");
>             UserAlias ua = new UserAlias("myAlias");
>             crit1.setAlias(ua);
>             Criteria crit2 = new
> Criteria().addLike("allAbstractAttributes.value", "*");
>             crit2.setAlias(ua);
>             crit1.addAndCriteria(crit2);
> 
>             QueryByCriteria query = QueryFactory.newQuery(Permit.class,
> crit1, true);
> 
>             query.addOrderBy("myAlias.value", true);
> 
>             Collection c = pb.getCollectionByQuery(query);
> 

You are right, this doesn't work with 1.0.5rc1 (with 1.0.4 it doesn't 
work too). Is this the correct code? AFAIK this query work without an 
alias (my local test show the same generated sql with and without alias):

Criteria crit1 = new Criteria().addEqualTo("allAbstractAttributes.name", 
"aaTest");
Criteria crit2 = new Criteria().addLike("allAbstractAttributes.value", 
"*").addAndCriteria(crit2);

QueryByCriteria query = QueryFactory.newQuery(Permit.class,crit1, true);
query.addOrderBy("allAbstractAttributes.value", true);

I setup some new query tests with order by clause but I can't think out 
a useful test using an alias in the order by clause. Fixing this "bug" 
won't be easy (you already notice that ;-)), so I want be sure that it 
is important to support alias-names in oder by and having clause.

regards,
Armin

> 
> In this query I use a useralias to reference a specific join in my
> orderby clause. However the generated SQL looks something like:
> 
>             SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
>             WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
>             ORDER BY myAlias.value
> 
> So we observe that the defined useralias on the join is not processed
> within the orderby clause, and I get a wrong SQL statement throwing an
> SQL exception. I would have expected a query like:
> 
>             SELECT .... FROM PERMIT A0 INNER JOIN ABSTRACTATTRIBUTE A1
>             WHERE A0.ID == A1.OID AND A1.NAME = ? AND A1.VALUE LIKE '%'
>             ORDER BY A1.VALUE
> 
> Again, I did some OJB code hacking. I came up with a patch, but.... I
> have the feeling it might be done better/smarter/more efficient and
> maybe even implemented in some other location in the code.... But the
> SQL generation code was not quite that transparent regarding this
> aspect, so just analyse my patch and see what you do with it.
> 
> In the class SqlSelectStatement, in the method protected String
> buildSqlString(), there is the following code:
> 
>     ...
>     ...
>     groupByFields = query.getGroupBy();
>     ensureColumnsGroupBy(groupByFields, columnList);
> 
>     orderByFields = query.getOrderBy();
>     columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
>     ...	
>     ...
> 
> In here the groupByFields and the orderByFields contain the lists of
> attributes like the user specified them, so with the useraliases. These
> need to be replaced with the generated alias before the
> ensurecolumn-methods are called. So I implemented a method
> replaceUserAlias doing exactly that and I call this method before the
> call to the ensurecolumns-methods.
> 
> The code then becomes:
> 
>     ...
>     ...
>     groupByFields = query.getGroupBy();
>     // start - iBanx patch
>     replaceUserAlias(groupByFields, whereCrit);
>     // end - iBanx patch
>     ensureColumnsGroupBy(groupByFields, columnList);
> 
>     orderByFields = query.getOrderBy();
>     // start - iBanx patch
>     replaceUserAlias(orderByFields, whereCrit);
>     // end - iBanx patch
>     columnList = ensureColumnsOrderBy(orderByFields, columnList, stmt);
> 
>     ...
>     ...
> 
> The method implementation is:
> 
>     // start - iBanx patch
>     /**
>      * Replaces any useralias reference in the fieldlist with the
> corresponding generated alias of the join releated to the
>      * whereCrit clause.
>      *
>      * @param fields
>      * @param whereCrit
>      */
>     private void replaceUserAlias(List fields, Criteria whereCrit)
>     {
>         // defensive programming preventing NPE's
>         if((getRoot()!=null) && (whereCrit!=null) &&
> (whereCrit.getUserAlias()!=null) && (fields!=null))
>         {
>             // continue when we have a join
>             // -- test it like this because the iterateJoins() method
> throes NPE when joins equals null
>             if(getRoot().joins != null)
>             {
>                 // iterate over all the joins to check for useraliases
>                 Iterator theJoins = getRoot().iterateJoins();
>                 if(theJoins!=null)
>                 {
>                     while(theJoins.hasNext())
>                     {
>                         Join j = (Join)theJoins.next();
>                         if(j.right!=null)
>                         {
>                             // iterate over all the fields in the list
> that might contain useraliases
>                             for(int i = 0;i < fields.size();i++)
>                             {
>                                 FieldHelper fh =
> (FieldHelper)fields.get(i);
>                                 // if the field has a useralias....
> replecae it with the generated alias
>  
> if(fh.name.startsWith(whereCrit.getUserAlias().getName() + "."))
>                                 {
>                                     // generate new fieldname with the
> generated alias
>                                     String fhname = j.right.alias +
> fh.name.substring(whereCrit.getUserAlias().getName().length());
>                                     // remove the 'old' field from the
> list
>                                     fields.remove(i);
>                                     // instantiate a new fieldhelper
> with the new proper name
>                                     FieldHelper nfh = new
> FieldHelper(fhname, fh.isAscending);
>                                     // insert it into the list at the
> same location
>                                     fields.add(i, nfh);
>                                 }
>                             }
>                         }
>                     }
>                 }
>             }
>         }
>     }
>     // end - iBanx patch
> 
> I have tested this patch for the given orderby-scenario, not with a
> useralias in the groupby clause. This patch seems to work for me in this
> case, but like I said... Some of you guys probably have a better fix for
> 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]
> 
>