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: [ReportQueryByCriteria] - Error in SQL generated when using GROUP BY sentence ?
Date Wed, 21 Jul 2004 09:22:27 GMT
hi carlos,

you were right ! by removing ensureColumns groupBy- and orderBy-columns were not 
used to build the joins. this is fixed now. i added a simple testcase 
QueryTest#testOrderByJoined.

thanks
jakob

Jakob Braeuchi schrieb:

> hi carlos,
> 
> this is strange, because all testcases pass without a problem.
> but i'll check it asap.
> 
> jakob
> 
> Carlos Chávez schrieb:
> 
>>  Hi Jakob.
>>  Thanks for the answer.
>>
>>  But now a found the next problem with the next code:
>>
>>  QueryByCriteria query = new QueryByCriteria(Product.class, criteria);
>>  query.addOrderBy("family.fam_name", true);
>>  query.addOrderBy("prod_name", true);
>>
>>  This code generate the next error:
>> org.apache.ojb.broker.PersistenceBrokerException:
>> org.apache.ojb.broker.PersistenceBrokerSQLException:
>> java.sql.SQLException: ERROR: La relación "a1" no existe (The relation
>> "a1" does not exist)
>>
>>  I found this is why the SQL is generated wrong, the SQL is:
>>
>>  SELECT AO.PROD_ID, AO.PROD_NAME, <list of other fields>
>>  FROM PRODUCT A0
>>  ORDER BY A1.FAM_NAME, 2
>>
>>  in this SQL is missing the clause INNER JOIN to the relationship FAMILY
>>  where the alias is A1.
>>
>>  The sentences "query.addOrderBy("family.fam_name", true);" is not 
>> resolve
>>  correct.
>>
>>  The same problem is happen with the group by.
>>
>>  I think the correct SQL maybe is:
>>  SELECT AO.PROD_ID, AO.PROD_NAME, <list of other fields>
>>  FROM PRODUCT A0 INNER JOIN FAMILY A1 ON A0.FAM_ID = A1.FAM_ID
>>  ORDER BY A1.FAM_NAME, 2
>>
>>  Cheers,
>>  Carlos Chávez.
>>
>> Jakob Braeuchi Escribio :-)
>>
>>> hi carlos,
>>>
>>> i just commited a fix to the groupBy-problem. please have a look at it.
>>>
>>> thanks
>>> jakob
>>>
>>> Carlos Chávez schrieb:
>>>
>>>
>>>> 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
>>>>>
>>>>
>>>>
>>>>
>>> ---------------------------------------------------------------------
>>> 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