You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Jakob Braeuchi <jb...@gmx.ch> on 2003/12/27 11:46:30 UTC
wrong column read due to orderBy
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
Re: wrong column read due to orderBy
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi all,
fyi SAPDB show the same behaviour as MySQL :(
jakob
Jakob Braeuchi wrote:
> hi all,
>
> the problem seems to by MySql specific. additional tests with hsqldb
> return correct values.
>
> 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
Re: wrong column read due to orderBy
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi all,
the problem seems to by MySql specific. additional tests with hsqldb
return correct values.
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
Re: wrong column read due to orderBy
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: wrong column read due to orderBy
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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