Re: Group by having causes "inner hasNext was false" error

Mon, 12 Sep 2005 09:08:06 -0700 

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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to