Mailing-List: contact ojb-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "OJB Developers List" <ojb-dev@db.apache.org>
Message-ID: <54721.10.0.0.1.1090355340.squirrel@10.0.0.1>
Date: Tue, 20 Jul 2004 14:29:00 -0600 (CST)
Subject: Re: [ReportQueryByCriteria] - Error in SQL generated when using    
       GROUP      BY sentence ?
From: Carlos =?iso-8859-1?Q?Ch=E1vez?= <cchavez@agssa.net>
To: "OJB Developers List" <ojb-dev@db.apache.org>
Reply-To: cchavez@agssa.net
User-Agent: SquirrelMail/1.4.3a-0.f1.1
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal


 Hi Jakob.

Jakob Braeuchi Escribio :-)
> hi carlos,
>
> i have a solution that works for hsqldb and mysql.
>
> if the orderBy- or groupByFiled is found in the list of selected fields
> it's
> index is used otherwise it's name is used:
>
> SELECT A0.ID,A0.FIRSTNAME,count(*) FROM PERSON A0 WHERE A0.FIRSTNAME LIKE
> ?
> GROUP BY 1,2
  This do not work in postgresql
>
> SELECT max(A0.ID) FROM PERSON A0 GROUP BY A0.LASTNAME,A0.FIRSTNAME
>
> could you please verify whether group by index also is working for
> postgresql.

  group by index do not work in postgresql. work fine for order by but not
  for group by.

  Cheers,
  Carlos Ch�vez.

>
> jakob
>
> Carlos Ch�vez schrieb:
>
>>  Hi jakob.
>>
>> Jakob Braeuchi Escribio :-)
>>
>>>hi carlos,
>>>
>>>this is caused by the call to ensureColumns in
>>>SqlSelectStatement#getStatement
>>>
>>>...
>>>             groupByFields = query.getGroupBy();
>>>             if (groupByFields != null && !groupByFields.isEmpty())
>>>             {
>>>//                ensureColumns(groupByFields, columnList, stmt);
>>>             }
>>>...
>>>
>>>if you comment this line (nr. 221) the sql should look ok.
>>>
>>>imo we could drop this call completely, but before i do so, i'll have to
>>>run all
>>>the testcases.
>>>we use the same procedure for orderby as well, because we
>>>order
>>>by column-position and not by column-name. may be we could drop this as
>>>well ?
>>
>>
>>   i think we can drop this in both case.
>>
>>   if the particular implementation of a database need this, we can do
>> this
>>   for the database only.
>>
>>   In case of postgresql i test this and we do not need what the column
>>   in the "group by" an "order by" are part of the columns of the result.
>>
>>   Cheers
>>   Carlos Ch�vez.
>>
>>
>>>hth
>>>jakob
>>>
>>>Carlos Ch�vez schrieb:
>>>
>>>
>>>>  Hi to all,
>>>>
>>>>  I write the next code :
>>>>
>>>>  Criteria criterio = new Criteria();
>>>>  criterio.addEqualTo("fam_id", famId);
>>>>  criterio.addEqualTo("prod_id", prodId);
>>>>  ReportQueryByCriteria subQuery = new
>>>>ReportQueryByCriteria(Detallecompra.class, criterio);
>>>>  subQuery.setAttributes(new String[] { "max(facc_id)" });
>>>>  subQuery.addGroupBy(new String[] { "fam_id", "prod_id" });
>>>>
>>>>  if the SQL generated above i want to use like a subquery,
>>>>  OJB send me the next message of error:
>>>>
>>>>org.apache.ojb.broker.PersistenceBrokerSQLException:
>>>>java.sql.SQLException: ERROR: Subselect must have only one field
>>>>
>>>> Then, I was asking myself why this happened, if only set one
>>>> attribute:
>>>> subQuery.setAttributes(new String[] { "max(facc_id)" });
>>>>
>>>> I found that the attributes of GROUP BY are added to sentence SQL,
>>>> although these do not appear in the result
>>>>
>>>> the SQL generated is:
>>>> SELECT max(A0.FACC_ID),A0.FAM_ID as ojb_col_1,A0.PROD_ID as ojb_col_2
>>>> FROM DETALLECOMPRA A0 WHERE (A0.FAM_ID = ?) AND A0.PROD_ID = ?
>>>> GROUP BY A0.FAM_ID,A0.PROD_ID
>>>>
>>>> but i think the correct is :
>>>> SELECT max(A0.FACC_ID)
>>>> FROM DETALLECOMPRA A0 WHERE (A0.FAM_ID = ?) AND A0.PROD_ID = ?
>>>> GROUP BY A0.FAM_ID,A0.PROD_ID
>>>>
>>>> because the attributes A0.FAM_ID,A0.PROD_ID are not indicated in the
>>>> sentences: subQuery.setAttributes(new String[] { "max(facc_id)" });
>>>> there are part of the GROUP BY but not the setAttributes sentences
>>>>
>>>> there is some reason for this behavior or this is an error ?
>>>>
>>>> IMO this is an error.
>>>>
>>>> i am using ojb 1.0.0.
>>>>
>>>> Cheers,
>>>>
>>>
>>>---------------------------------------------------------------------
>>>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
>


-- 
Carlos Ch�vez

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