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 08:25:06 GMT
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


Mime
View raw message