You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Bertrand Delacretaz <bd...@apache.org> on 2005/09/12 17:37:14 UTC
Group by having causes "inner hasNext was false" error
Hi,
I'm using a GROUP BY HAVING query (using addGroupBy and
setHavingCriteria to a QueryByCriteria), and depending on the data I
get an "inner hasNext was false" error.
IIUC the problem is due to the SELECT COUNT [2] query used by OJB to
find out how many objects to retrieve, which in some cases finds more
objects that the main query retrieves, causing the RsIterator to fail
as it believes there are more objects to retrieve but finds the end of
the result set.
The main query does a
GROUP BY A0.DOCUMENT_ID
HAVING count(*) >= 1
which is not included in the SELECT COUNT query, so in some cases the
count is higher than the actual number of records found by the select
query, which causes the error.
The interesting java statements are:
q.addGroupBy(DocStoreDocument.COLUMN_DOCUMENT_ID);
final Criteria having = new Criteria();
having.addGreaterOrEqualThan("count(*)", new
Integer(dpQueries.size()));
q.setHavingCriteria(having);
And the mapping declarations are found below [3].
Am I doing something wrong in the way I build the query, or is there a
know workaround to this problem? Maybe a "don't care about the exact
SELECT COUNT value" option would be the easiest? I know nothing about
OJB internals though, this might be silly.
BTW I'm new here, I'm a Cocoon committer, using OJB regularly but
mostly for simple stuff, so I didn't have questions unti now ;-)
Thanks for any help!
-Bertrand
Here are SQL queries, dumped by spying on the mysql 3306 port. It's a
simple document/document-property structure with a 1-N relationship.
[1] Main query
SELECT
A0.STATE,A0.LAST_MODIFIED,A0.CREATION_DATE,A0.TEXT,A0.CONTENT_SOURCE,A0.
DOCUMENT_ID,A0.THEME,A0.TITLE,A0.OJB_VERSION,A0.CONTENT_AREA,A0.MEDIALIS
T,A0.REF_DATE,A0.ROLE,A0.XML_TEXT,REF_DATE
as ojb_col_14
FROM DOCUMENT A0
LEFT OUTER JOIN DOCPROPERTY A1 ON A0.DOCUMENT_ID=A1.DOCUMENT_ID
WHERE (( ROLE = 'story')
AND ((A1.NAME = 'broadcast.date') AND A1.VALUE LIKE '196%'))
AND (ROLE<>'site.block')
GROUP BY A0.DOCUMENT_ID
HAVING count(*) >= 1
ORDER BY 15 DESC
[2] The query that counts the objects IIUC. As the GROUP BY HAVING is
missing (but the statement wouldn't work with it, right?) the count can
be higher than what [1] finds:
SELECT count(*) FROM DOCUMENT A0 LEFT OUTER JOIN DOCPROPERTY A1 ON
A0.DOCUMENT_ID=A1.DOCUMENT_ID
WHERE (( ROLE = 'story')
AND ((A1.NAME = 'broadcast.date') AND A1.VALUE LIKE '196%'))
AND (ROLE<>'site.block')
[3] And here are the mappings:
<class-descriptor class="ch.nouvo.cms.docstore.DocStoreDocument"
table="DOCUMENT">
<field-descriptor name="documentId" column="DOCUMENT_ID"
jdbc-type="CHAR" primarykey="true"/>
<field-descriptor name="role" column="ROLE"
jdbc-type="VARCHAR"/>
<field-descriptor name="contentSource" column="CONTENT_SOURCE"
jdbc-type="VARCHAR"/>
<field-descriptor name="contentArea" column="CONTENT_AREA"
jdbc-type="VARCHAR"/>
<field-descriptor name="theme" column="THEME"
jdbc-type="VARCHAR"/>
<field-descriptor name="state" column="STATE"
jdbc-type="VARCHAR"/>
<field-descriptor name="refDate" column="REF_DATE"
jdbc-type="TIMESTAMP"/>
<field-descriptor name="creationDate" column="CREATION_DATE"
jdbc-type="TIMESTAMP"/>
<field-descriptor name="lastModified" column="LAST_MODIFIED"
jdbc-type="TIMESTAMP"/>
<field-descriptor name="title" column="TITLE"
jdbc-type="VARCHAR"/>
<field-descriptor name="text" column="TEXT"
jdbc-type="VARCHAR"/>
<field-descriptor name="xmlText" column="XML_TEXT"
jdbc-type="VARCHAR"/>
<field-descriptor name="medialist" column="MEDIALIST"
jdbc-type="VARCHAR"/>
<!-- version field maintained by OJB, for optimistic locking -->
<field-descriptor
name="persistentStorageVersion"
column="OJB_VERSION"
jdbc-type="INTEGER"
locking="true"
/>
<!-- map the collection of DocumentProperty objects -->
<collection-descriptor
name="properties"
collection-
class="org.apache.ojb.broker.util.collections.RemovalAwareList"
element-class-ref="ch.nouvo.cms.docstore.DocumentProperty"
orderby="name"
sort="ASC"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<inverse-foreignkey field-ref="documentId"/>
</collection-descriptor>
</class-descriptor>
<!-- DocumentProperty class mapping -->
<class-descriptor class="ch.nouvo.cms.docstore.DocumentProperty"
table="DOCPROPERTY">
<field-descriptor
name="propertyId"
column="PROP_ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor name="documentId" column="DOCUMENT_ID"
jdbc-type="CHAR"/>
<field-descriptor name="name" column="NAME" jdbc-type="CHAR"/>
<field-descriptor name="index" column="PROP_INDEX"
jdbc-type="INTEGER"/>
<field-descriptor name="value" column="VALUE"
jdbc-type="VARCHAR"/>
</class-descriptor>
[SOLVED] : Group by having causes "inner hasNext was false" error
Posted by Bertrand Delacretaz <bd...@apache.org>.
Le 12 sept. 05 à 17:37, Bertrand Delacretaz a écrit :
> ...I'm using a GROUP BY HAVING query (using addGroupBy and
> setHavingCriteria to a QueryByCriteria), and depending on the data
> I get an "inner hasNext was false" error.
> IIUC the problem is due to the SELECT COUNT [2] query used by OJB
> to find out how many objects to retrieve, which in some cases finds
> more objects that the main query retrieves, causing the RsIterator
> to fail as it believes there are more objects to retrieve but finds
> the end of the result set...
I went back to this old problem, and fixed it simply by specifiying
that my MySQL driver is JDBC 3.0 capable, by adding
<jdbc-connection-descriptor ... jdbc-level="3.0">
in my ojb-repository.xml
Without this, OJB does a SELECT COUNT request to get the size of the
result set, and the generated SELECT was incorrect when my main query
includes a HAVING count(*) >= N clause.
Debugging the RsIterator class allowed me to see exactly what was
happening and fix my (stupid) mistake.
Just thought I'd send this info here so that others might be spared
the pain ;-)
-Bertrand
Re: Group by having causes "inner hasNext was false" error
Posted by Carlos Chávez <cc...@agssa.net>.
Hi Bertrand.
Better Use ReportQueryByCriteria and getReportQueryIteratorByQuery for that.
every element in the Iterator is a object[].
Cheers.
Carlos Chávez.
Bertrand Delacretaz wrote:
> Hi,
>
> I'm using a GROUP BY HAVING query (using addGroupBy and
> setHavingCriteria to a QueryByCriteria), and depending on the data I
> get an "inner hasNext was false" error.
>
> IIUC the problem is due to the SELECT COUNT [2] query used by OJB to
> find out how many objects to retrieve, which in some cases finds more
> objects that the main query retrieves, causing the RsIterator to fail
> as it believes there are more objects to retrieve but finds the end
> of the result set.
>
> The main query does a
>
> GROUP BY A0.DOCUMENT_ID
> HAVING count(*) >= 1
>
> which is not included in the SELECT COUNT query, so in some cases the
> count is higher than the actual number of records found by the select
> query, which causes the error.
>
> The interesting java statements are:
>
> q.addGroupBy(DocStoreDocument.COLUMN_DOCUMENT_ID);
> final Criteria having = new Criteria();
> having.addGreaterOrEqualThan("count(*)", new
> Integer(dpQueries.size()));
> q.setHavingCriteria(having);
>
> And the mapping declarations are found below [3].
>
> Am I doing something wrong in the way I build the query, or is there
> a know workaround to this problem? Maybe a "don't care about the
> exact SELECT COUNT value" option would be the easiest? I know nothing
> about OJB internals though, this might be silly.
>
> BTW I'm new here, I'm a Cocoon committer, using OJB regularly but
> mostly for simple stuff, so I didn't have questions unti now ;-)
>
> Thanks for any help!
> -Bertrand
>
>
>
> Here are SQL queries, dumped by spying on the mysql 3306 port. It's a
> simple document/document-property structure with a 1-N relationship.
>
> [1] Main query
> SELECT
> A0.STATE,A0.LAST_MODIFIED,A0.CREATION_DATE,A0.TEXT,A0.CONTENT_SOURCE,A0.
> DOCUMENT_ID,A0.THEME,A0.TITLE,A0.OJB_VERSION,A0.CONTENT_AREA,A0.MEDIALIS
> T,A0.REF_DATE,A0.ROLE,A0.XML_TEXT,REF_DATE
> as ojb_col_14
> FROM DOCUMENT A0
> LEFT OUTER JOIN DOCPROPERTY A1 ON A0.DOCUMENT_ID=A1.DOCUMENT_ID
> WHERE (( ROLE = 'story')
> AND ((A1.NAME = 'broadcast.date') AND A1.VALUE LIKE '196%'))
> AND (ROLE<>'site.block')
> GROUP BY A0.DOCUMENT_ID
> HAVING count(*) >= 1
> ORDER BY 15 DESC
>
>
> [2] The query that counts the objects IIUC. As the GROUP BY HAVING is
> missing (but the statement wouldn't work with it, right?) the count
> can be higher than what [1] finds:
>
> SELECT count(*) FROM DOCUMENT A0 LEFT OUTER JOIN DOCPROPERTY A1 ON
> A0.DOCUMENT_ID=A1.DOCUMENT_ID
> WHERE (( ROLE = 'story')
> AND ((A1.NAME = 'broadcast.date') AND A1.VALUE LIKE '196%'))
> AND (ROLE<>'site.block')
>
> [3] And here are the mappings:
> <class-descriptor class="ch.nouvo.cms.docstore.DocStoreDocument"
> table="DOCUMENT">
> <field-descriptor name="documentId" column="DOCUMENT_ID"
> jdbc-type="CHAR" primarykey="true"/>
> <field-descriptor name="role" column="ROLE"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="contentSource"
> column="CONTENT_SOURCE" jdbc-type="VARCHAR"/>
> <field-descriptor name="contentArea" column="CONTENT_AREA"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="theme" column="THEME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="state" column="STATE"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="refDate" column="REF_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="creationDate" column="CREATION_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="lastModified" column="LAST_MODIFIED"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="title" column="TITLE"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="text" column="TEXT"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="xmlText" column="XML_TEXT"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="medialist" column="MEDIALIST"
> jdbc-type="VARCHAR"/>
>
> <!-- version field maintained by OJB, for optimistic locking -->
> <field-descriptor
> name="persistentStorageVersion"
> column="OJB_VERSION"
> jdbc-type="INTEGER"
> locking="true"
> />
>
> <!-- map the collection of DocumentProperty objects -->
> <collection-descriptor
> name="properties"
> collection-
> class="org.apache.ojb.broker.util.collections.RemovalAwareList"
> element-class-ref="ch.nouvo.cms.docstore.DocumentProperty"
> orderby="name"
> sort="ASC"
> auto-retrieve="true"
> auto-update="true"
> auto-delete="true"
> >
> <inverse-foreignkey field-ref="documentId"/>
> </collection-descriptor>
>
> </class-descriptor>
>
> <!-- DocumentProperty class mapping -->
> <class-descriptor class="ch.nouvo.cms.docstore.DocumentProperty"
> table="DOCPROPERTY">
> <field-descriptor
> name="propertyId"
> column="PROP_ID"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> />
>
> <field-descriptor name="documentId" column="DOCUMENT_ID"
> jdbc-type="CHAR"/>
> <field-descriptor name="name" column="NAME" jdbc-type="CHAR"/>
> <field-descriptor name="index" column="PROP_INDEX"
> jdbc-type="INTEGER"/>
> <field-descriptor name="value" column="VALUE"
> jdbc-type="VARCHAR"/>
> </class-descriptor>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Group by having causes "inner hasNext was false" error
Posted by Bertrand Delacretaz <bd...@apache.org>.
Le 12 sept. 05, à 18:45, Antonio Gallardo a écrit :
> ...I think the clausule : [HAVING count(*) >= 1 ] creates unnecessary
> processing..
Hi Antonio,
In my case I need it in the query - usally not with the value 1, but
with higher values which are generated dynamically depending on the
query - for example for finding
all DOCUMENT
which have a DOCPROPERTY with value=x,name=y
AND a DOCPROPERTY with value=z name=t
-Bertrand
Re: Group by having causes "inner hasNext was false" error
Posted by Antonio Gallardo <ag...@agssa.net>.
Bertrand Delacretaz wrote:
> Hi,
>
> I'm using a GROUP BY HAVING query (using addGroupBy and
> setHavingCriteria to a QueryByCriteria), and depending on the data I
> get an "inner hasNext was false" error.
>
> IIUC the problem is due to the SELECT COUNT [2] query used by OJB to
> find out how many objects to retrieve, which in some cases finds more
> objects that the main query retrieves, causing the RsIterator to fail
> as it believes there are more objects to retrieve but finds the end
> of the result set.
>
> The main query does a
>
> GROUP BY A0.DOCUMENT_ID
> HAVING count(*) >= 1
Hi:
I think the clausule : [HAVING count(*) >= 1 ] creates unnecessary
processing.
Best Regards,
Antonio Gallardo.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org