To all concerned,
I had a surprising issue with one of my clients using the CMIS integration when they tried to pull back values in a folder selecting on at least one repeating attribute in the WHERE clause and also specifying an "ORDER BY" clause. Here's the scenario. The following folder has 20 documents in it. The following 4 queries should all have the same # of returned records. However, when I specify at least one repeating attribute in the SELECT clause AND I specify a non repeating, non object id attribute in the ORDER BY clause, strange things happen:
| Case | Query | Results |
|---|
Pull back values without: - ORDER BY Clause
| SELECT cmis:objectId, cmis:lastModificationDate, keywords FROM cmis:document where cmis:parentId='0b01f22880035184' | |
Pull back without: - Repeating attribute "keywords" in SELECT clause
| SELECT cmis:objectId, cmis:lastModificationDate FROM cmis:document where cmis:parentId='0b01f22880035184' ORDER BY cmis:lastModificationDate DESC | 20 records |
Pull back WITH - Repeating attribute "keywords" in SELECT clause
- ORDER BY cmis:lastModificationDate
| SELECT cmis:objectId, cmis:lastModificationDate, keywords FROM cmis:document where cmis:parentId='0b01f22880035184' ORDER BY cmis:lastModificationDate DESC | 27 records |
Pull back WITH - Repeating attribute "keywords" in SELECT clause
- ORDER BY cmis:lastModificationDate
- Additional cmis:objectId in ORDER BY
| SELECT cmis:objectId, cmis:lastModificationDate, keywords FROM cmis:document where cmis:parentId='0b01f22880035184' ORDER BY cmis:lastModificationDate DESC, cmis:objectId | 20 records |
I have mixed results by specifying other singluar ORDER BY clauses with repeating attributes. After using DFC Trace and catching the DQL representation, I find the same issue with the DQL statement:
DQL
SELECT r_object_id, r_modify_date, keywords
FROM dm_document WHERE ANY i_folder_id = '0b01f22880035184' ORDER BY r_modify_date DESC
Returns the 27 records from before. When I use the "Translate to SQL" feature in DA's DQL editor, I find:
SQL
select all
xtrac_ria_10034.r_object_id,
xtrac_ria_10034.r_modify_date,
dm_repeating.keywords
from
dm_document_sp dm_document,
dm_document_rp dm_repeating
where (dm_document.r_object_id in (select dm_document from dm_sysobject_r where i_folder_id='0b01f22880035184'))
and (dm_document.i_has_folder = 1 and dm_document.i_is_deleted = 0)
and dm_repeating.r_object_id=dm_document.r_object_id
order by dm_document.r_modify_date desc
Returns 40 records. All Documents are CURRENT so they each have 2 records in their respective repeating attribute view.
It leads me to believe that the DQL takes the non normalized SQL results and fashions them and "ORDER"s them in a way that isn't clear to me. If I was doing a straight up DQL query, I could use the keywords ENABLE(ROW_BASED) at the end of the DQL to get the 20 rows I desire.
What is causing this seemingly sporadic "normalization" between SQL and DQL and is there a fixpack or some way to address this without altering the CMIS query as it stands to date?
