DQL Select returns several rows for repeatable values

I am using Documentum Developer Edition 6.6. I have run the following DQL:

select "r_object_id", "r_modify_date", "r_version_label","i_position" ,"object_name" from "dm_document" where FOLDER (ID('0bde75d18000cfa4')) and "r_object_type"='dm_document' order by "r_modify_date" asc, "i_position" desc

I expected: the DQL will return one row for each dm_documentum object. I remember – my earlier requests with this DQL did it – one roц for each document. But today I see: for some of the dm_document objects only one row returned; whereas for other dm_document objects several rows are returned per object! Like the following:

09de75d18000d514 7/28/2011 3:41 PM 1.0,CURRENT -1,-2 CORPORATE TAX DODGING FOR FUN AND PROFIT.mp3
09de75d18000d515 7/28/2011 3:41 PM 1.0 -1 DOWNWARD MOBILITY IN A RISING ECONOMY.mp3
...
09de75d18000d515 7/28/2011 3:41 PM CURRENT -2 DOWNWARD MOBILITY IN A RISING ECONOMY.mp3

In other words – for the 09de75d18000d514 one row (with repeatable “r_version_label" and "i_position" as arrays) was returned; whereas for another document 09de75d18000d515 the repeatable properties were returned as separate rows.

Why is that? For me, this looks like a bug – because of the documents 09de75d18000d514 and 09de75d18000d515 have no essential differences; they are just usual dm_document instances, nothing more.

And the more important question is: what can I do? I see the problem disappears if I remove the “"i_position" desc” from the DQL – then each dm_object is returned as single row. But I needed this “"i_position" desc” to have “r_version_label" sorted in accordance to corresponding values of the "i_position" (each item of “r_version_label" array corresponds to an item of the "i_position" array that contains its “position number”).
Maybe this my assumption – that Documentum should order the “r_version_label" accordingly to the "i_position" because of I specified “"i_position" desc” – was wrong? If so, now I see the only way to cope with this:
- I use the DQL without the “"i_position" desc”
- My software (it uses DFS) will sort the repeatable values (like “r_version_label") items itself, making assumption that Documentum will bring their items in reverse order.

Can I rely on the assumption that 'Documentum will bring items of th erepeatable properties in reverse order'? This is what I see in Documentum results; but I cannot find a formal confirmation for this in Documentum docs.

Find more posts tagged with

Comments

GlenLantz

I have also found the following: the DQL works OK if I add "r_object_id" into the ORDER BY:

Maybe this will be the solution?

michal.malczewski

Hi,

Did you try executing your queries using something very basic (idql32 / samson)? Are you sure no ENABLE(RETURN_TOP ****) is applied anywhere to the query? It might not be your case, but since 6.5 the RETURN_TOP behaves different to what it used to - see "Change in the way Content Server processes DQL hints for RETURN_TOP" in release notes for details.

You said you were using DFS - I have no clue what DFS does, but I can imagine it applies some hints on the DQL queries - what you are getting is a ROW_BASED result. I'd give setting return_top_results_row_based=F (and restarting!) a try.

Kind regards

GlenLantz

"Did you try executing your queries using something very basic" - I used DQL Editor from Documentum Administrator. It gives exacrtly the same results as execution of the same DQL query using DFS.

"Are you sure no ENABLE(RETURN_TOP ****) is applied anywhere to the query" - no, I do not use the 'hints'; that my DQL query was exactly as I posted in the discussion. So this problem has no relation to possible ussues of the RETURN_TOP ****

What is worst - thi smay be kind of 'jumping' bug that appears only sometimes. I tell this because earlier that DQL query did not duplicate the rows - I remember this well. But later - on the same instance of Documentum Server - it began the duplicating.

Tord

Hi Glen,

When you order by internal attributes you are in murky waters and your mileage may vary so I would suggest you don't use the i_position attribute in your order by clause nor in your select clause. The r_version_label values will be returned in a consistent manner without the i_position stuff.

However, if you are going to do that, I suggest you order by the object_id _before_ the i_position and you should see consistent results. (In fact, the results are consistent now as well since your order by forces multiple rows to from the same object to not come in sequence.

In other words, don't do this, but if you have to, use this query instead:

  SELECT r_object_id,          r_modify_date,          r_version_label,         i_position,         object_name     FROM dm_document    WHERE FOLDER (ID('0bde75d18000cfa4'))      AND r_object_type = 'dm_document'  ORDER BY r_modify_date ASC,          r_object_id ASC,         i_position DESC

Thanks

--Tord

GlenLantz

"When you order by internal attributes you are in murky waters "

OK, suppose I do not use the ordering by "i_position" at all. My DQL will look like this:

select "r_object_id", "r_modify_date", "r_version_label","i_position" ,"object_name" from "dm_document" where FOLDER (ID('0bde75d18000cfa4')) and "r_object_type"='dm_document' order by "r_modify_date" asc, "r_object_id" asc

But anyway, my application wants items of the repeatable values ("r_version_label", "authors" etc ) to be ordered in the same way they are ordered when I see them in Documentum WebTop. My application could order them itself, after teh DQL query brings the results. But for this it has to be sure: items of a repeatable value will be returned in certain order (ascending, descending - does not matter). In the experiments I have made I see: they are returned ordered by ascending of the "i_position" - in other words, in reverse order (relatively to the order I see in Documentum WebTop). As I said, my application can re-order them itself, without the DQL - no problem here. But the question is: is this behaviour (that items of a repeatable value will be returned ordered by i_position asc) guaranteed for me? I have not seen this behaviour specified in the docs - this is what I worry about.

Tord

Well, I think you misunderstood me. If you don't use the "i_position ASC" part you don't need to add the "r_object_id ASC" before it since that part is only needed if you add the ordering on the i_position attribute to keep all the corresponding object rows together.

So, either your query is this (no i_position, no ordering on r_object_id)

  SELECT r_object_id,          r_modify_date,          r_version_label,         object_name     FROM dm_document    WHERE FOLDER (ID('0bde75d18000cfa4'))      AND r_object_type = 'dm_document'  ORDER BY r_modify_date ASC

Or this (ordering on i_position, but first ordering on r_object_id to avoid object fragmentation):

  SELECT r_object_id,          r_modify_date,          r_version_label,         i_position,         object_name     FROM dm_document    WHERE FOLDER (ID('0bde75d18000cfa4'))      AND r_object_type = 'dm_document'  ORDER BY r_modify_date ASC,          r_object_id ASC,         i_position DESC

But like I wrote, I would not rely on an internal attribute since they are not part of the public "api".

Now, your other question if the order of the repeatable values is guaranteed to be as per i_position asc. I can't find an official answer, but I would at least assume that the values are delivered in a consistent manner.

GlenLantz

"but I would at least assume that the values are delivered in a consistent manner" - me too :-) ; I just hoped that someone would point me a place in the docs I missed - a place with explicit assertion about their order

Tord

Well I couldn't find it anywhere. If I find out I'll update this thread.

Thanks

--Tord