db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: wrong column read due to orderBy
Date Tue, 30 Dec 2003 19:31:30 GMT
hi all,

i fixed this problem using a column alias.

but i think adding additional columns for sorting and grouping is not 
the best thing to do. i also had to fix an issue with report queries 
caused by additional columns. so we need another solution for 1.1 !

jakob

Jakob Braeuchi wrote:
> hi all,
> 
> i do have a solution using a column alias in 
> SqlQueryStatement#ensureColumns. the resulting sql looks like:
> 
> SELECT
>     A0.T1_COL,
>     A0.TABLE2_ID,
>     A0.FOO,
>     A0.TABLE1_ID,
>     A1.FOO *as ojb_col_4*
> FROM
>     table1 A0
> INNER JOIN
>     table2 A1 ON A0.TABLE2_ID=A1.TABLE2_ID
> ORDER BY 5
> 
> this works with MySQL, hsql and SAPDB.
> 
> 
> jakob
> 
> 
> Jakob Braeuchi wrote:
> 
>> hi all,
>>
>> terry brick reported an interesting problem ( 
>> http://article.gmane.org/gmane.comp.jakarta.ojb.user/11033 ). in this 
>> case the the orderBy-column (A1.FOO) has the same name as a 
>> select-column (A0.FOO) but it's from another table:
>>
>> SELECT
>>     A0.T1_COL,
>>     A0.TABLE2_ID,
>>     A0.FOO,
>>     A0.TABLE1_ID,
>>     *A1.FOO*
>> FROM
>>     table1 A0
>> INNER JOIN
>>     table2 A1
>> ON
>>     A0.TABLE2_ID=A1.TABLE2_ID
>> ORDER BY 5
>>
>> the orderBy-column was added to the select-columns in 
>> SqlQueryStatement#ensureColumns.
>>
>> when reading the columns from the retrieved row 
>> RowReaderDefaultImpl#readObjectArrayFrom uses the *name* of the row to 
>> get it's value, and in case of MySQL the value of A1.FOO is read !
>>
>> imo there are several solutions to avoid this problem:
>>
>> 1.) not adding the orderBy-Column to the select-columns
>> 2.) when adding the orderBy-Column to the select-columns use a column 
>> alias (ie. A1.FOO as ORDER_FOO)
>> 3.) using column index to read value from the row.
>>
>> any ideas, preferences ?
>>
>> i can reproduce the problem with MySQL but i've not yet tried it with 
>> other dbms.
>>
>> jakob
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message