DQL query not returning all results

Hi, I am implementing a simple query and my DQL editor comes back with a set of over 23,000 results. The problem is that the resultset does not include all the documents that adhere to the criteria. Is there perhaps a limit size on the number of results that can be returned?

More specifically the query I enter is the following:

SELECT r_object_id FROM pkt_document(ALL) WHERE ((a_status='VALID' OR (a_status='WITHDRAWN' AND pkt_is_latest_withdrawn=true)) AND pkt_acl_name IN (select pkt_acl_name from pkt_acl_lookup where pkt_acl_label LIKE 'PKT Level 1%') AND CABINET('/Top Node',DESCEND)) AND pkt_display_to_portal = true.

I have identifed a document with r_object_id='090082f181d39956', that is not included in the this resultset.

To prove that the document should be returned I modify the query to add AND r_object_id='090082f181d39956'

This query returns the document, however the document is not included among the results of the first query above. Any insights would be much appreciated.

Find more posts tagged with

Comments

KSMatson

Try this,

SELECT r_object_id FROM pkt_document(ALL) 
WHERE a_status in ('VALID', 'WITHDRAWN')
AND pkt_is_latest_withdrawn = True
AND pkt_display_to_portal = True 
AND pkt_acl_name IN (select pkt_acl_name 
from pkt_acl_lookup where pkt_acl_label LIKE 'PKT Level 1%') 
AND FOLDER('/Top Node', DESCEND);

Dump the object here if you still dont see it.

Run this query as well to check if the object actually meets the criteria

SELECT r_object_id, acl_name, i_folder_id, a_status, 
pkt_is_latest_withdrawn, pkt_display_to_portal
FROM pkt_document(ALL) where r_object_id = '090082f181d39956';

whats the depth of the folder hierarchy where this object is linked, I think there was a bug with one of the CS versions on how many folders are queried when the DESCEND predicate was used. I dont remember the upper bound on it. What version of CS are you using?

cwildgoose

Thank you for your response. The folder structure is 7 levels deep, and will need to check the version of the CS. I did notice that if I append ENABLE (RETURN TOP 30000) then I get more than the original 23,000 result. But still I have yet to find the document id i identrified in the previous post.

KSMatson

The only other thing that I can think off is the permission issue, if the object permission doesn't allow browse on it then it wont show up in the resultset. But again if a superuser account is being used to run the query then by default the superuser has browse permission on every object in the docbase.

It is indeed puzzling that if the document actually meets the criteria and is being excluded from the resultset.