Columns listed multiple times in select clause

[Matt Mastrangelo <mm...@x2dev.com>]

Hello,

I'm having a problem with a SQL statement returned by a call to 
org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl#getPreparedSelectStatement(...).  
The SELECT statement returned lists all the columns referenced in the 
ORDER BY clause twice in the SELECT clause.

Here is what appears to be the causing the problem:

In 
org.apache.ojb.broker.accesslayer.sql.SqlSelectStatement#getStatement(), 
ensureColumns is called if the query contains an ORDER BY or GROUP BY 
clause.  The ensureColumns method attempts to make sure that any fields 
referenced in an ORDER BY or GROUP BY are included in the SELECT 
clause.  A list of class attribute names retrieved by a call to 
appendListOfColumnsForSelect is passed to ensureColumns.

The problem seems to be that ensureColumns is expecting a list of 
database column names, not class attribute names.  It checks the list to 
see if the field in the ORDER BY/GROUP BY exists, and if not, it adds it 
to the SELECT clause.  But because the list contains attribute names and 
not database column names, none of the fields are found in the passed 
list. Hence, all fields in ORDER BY/GROUP BY clauses are being appended 
to the SELECT clause, even if they are already present.

This causes problems in some databases, such as Microsoft SQL Server, 
where the same field cannot be present more than once in the SELECT 
clause.  This doesn't cause a problem in other databases, such as 
Interbase/Firebird, that allow columns to appear multiple times.

Has this problem been reported before, and if so, are there any known 
workarounds? 

Thanks for your help.

Matt Mastrangelo



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

Re: Columns listed multiple times in select clause

[Jakob Braeuchi <jb...@gmx.ch>]

hi matt,

i can not reproduce the behaviour you mentioned in your post.
ensureColumns expects a list of attributeNames _not_ dbColumns.

please post your query and the resulting sql.

jakob

Matt Mastrangelo wrote:
> Hello,
> 
> I'm having a problem with a SQL statement returned by a call to 
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl#getPreparedSelectStatement(...).  
> The SELECT statement returned lists all the columns referenced in the 
> ORDER BY clause twice in the SELECT clause.
> 
> Here is what appears to be the causing the problem:
> 
> In 
> org.apache.ojb.broker.accesslayer.sql.SqlSelectStatement#getStatement(), 
> ensureColumns is called if the query contains an ORDER BY or GROUP BY 
> clause.  The ensureColumns method attempts to make sure that any fields 
> referenced in an ORDER BY or GROUP BY are included in the SELECT 
> clause.  A list of class attribute names retrieved by a call to 
> appendListOfColumnsForSelect is passed to ensureColumns.
> 
> The problem seems to be that ensureColumns is expecting a list of 
> database column names, not class attribute names.  It checks the list to 
> see if the field in the ORDER BY/GROUP BY exists, and if not, it adds it 
> to the SELECT clause.  But because the list contains attribute names and 
> not database column names, none of the fields are found in the passed 
> list. Hence, all fields in ORDER BY/GROUP BY clauses are being appended 
> to the SELECT clause, even if they are already present.
> 
> This causes problems in some databases, such as Microsoft SQL Server, 
> where the same field cannot be present more than once in the SELECT 
> clause.  This doesn't cause a problem in other databases, such as 
> Interbase/Firebird, that allow columns to appear multiple times.
> 
> Has this problem been reported before, and if so, are there any known 
> workarounds?
> Thanks for your help.
> 
> Matt Mastrangelo
> 
> 
> 
> ---------------------------------------------------------------------
> 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