Is it true that work item that combine multiple conditions ... run each condition on the full set of items?
Hello there,
our dev org will switch from our home-grown tool to RTC 4.02 next month;
and todays education sessions raised some eyebrows. What we heard (or better: what we understood) is:
a) when queries combine multiple condition, each condition is evaluated separately on the FULL set of items in the database
b) the individual result sets are subject to "internal limitation"; meaning: if the query would actually result 10 000 items, RTC just returns a smaller subset (like 1000, 2000, or so) of these 10 000.
c) intersection happens in the end, using the "limited" results from step "b)"
So, theoretically it could happen that:
- condition 1 matches 10 000 items
- condition 2 matches 50 items (all of them matching cond1, too)
If "condition 1" is now reduced to just 1000 items ... and those 1000 contain only 5 out of the 50 for cond2 ... the final result would be 5 items; and not 50. That would be like ... very bad.
Can someone with real insight confirm (or hopefully refute) these thoughts?
our dev org will switch from our home-grown tool to RTC 4.02 next month;
and todays education sessions raised some eyebrows. What we heard (or better: what we understood) is:
a) when queries combine multiple condition, each condition is evaluated separately on the FULL set of items in the database
b) the individual result sets are subject to "internal limitation"; meaning: if the query would actually result 10 000 items, RTC just returns a smaller subset (like 1000, 2000, or so) of these 10 000.
c) intersection happens in the end, using the "limited" results from step "b)"
So, theoretically it could happen that:
- condition 1 matches 10 000 items
- condition 2 matches 50 items (all of them matching cond1, too)
If "condition 1" is now reduced to just 1000 items ... and those 1000 contain only 5 out of the 50 for cond2 ... the final result would be 5 items; and not 50. That would be like ... very bad.
Can someone with real insight confirm (or hopefully refute) these thoughts?
Accepted answer
Hi Edwin,
Although I cannot really confirm on the sequence in your question, I believe the limit is generally applied after the intersection/union (and/or). An exception might be with the 'fulltext' condition (which seems to be evaluated separately)
I'd like to share my test results below:
I changed the following 2 settings in CCM Admin > Advanced properties:
- Full Text Merge Limit ==> 5
- Maximum Query Result Set Size ==> 10
According to the above settings, I expect the max # of work items returned by a query to be 10. If a 'fulltext' is used in my query, it will return 5 work items.
In a project area, I have 11 defects and 4 tasks. All work items have the word 'Defect' in the summary field.
1. I ran a query to list all work items (no condition) ==> Got 10 of 15 work items
2. I added a 'FullText' condition and search on 'Defect' ==> Got 5 work items (the Full Text Merge Limit of 5 is applied here)
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA'))
3. I removed the 'FullText' condition, and added 'type' to filter for defect type only ==> Got 10 defect work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.WORK_ITEM_TYPE = ?)
4. I ran a query with both full text = 'defect' AND type = 'defect' ==> Got 5 work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA') and (t1.WORK_ITEM_TYPE = ?))
5. I ran a query with both full text = 'defect' OR type = 'defect' ==> Got 10 work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA') or (t1.WORK_ITEM_TYPE = ?)))
6. I ran a query with both full text = 'defect' AND type = 'task' ==> Got NO work item. This is because all the work items returned from the fulltext condition are of 'defect' type.
7. a query with type = 'defect' and 'created by' = current user ==> Unlike 'fulltext' condition, the conditions in this case are evaluated together in a single query.
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.WORK_ITEM_TYPE = ?) and (t1.CREATOR_ITEM_ID = ?))
Although I cannot really confirm on the sequence in your question, I believe the limit is generally applied after the intersection/union (and/or). An exception might be with the 'fulltext' condition (which seems to be evaluated separately)
I'd like to share my test results below:
I changed the following 2 settings in CCM Admin > Advanced properties:
- Full Text Merge Limit ==> 5
- Maximum Query Result Set Size ==> 10
According to the above settings, I expect the max # of work items returned by a query to be 10. If a 'fulltext' is used in my query, it will return 5 work items.
In a project area, I have 11 defects and 4 tasks. All work items have the word 'Defect' in the summary field.
1. I ran a query to list all work items (no condition) ==> Got 10 of 15 work items
2. I added a 'FullText' condition and search on 'Defect' ==> Got 5 work items (the Full Text Merge Limit of 5 is applied here)
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA'))
3. I removed the 'FullText' condition, and added 'type' to filter for defect type only ==> Got 10 defect work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.WORK_ITEM_TYPE = ?)
4. I ran a query with both full text = 'defect' AND type = 'defect' ==> Got 5 work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA') and (t1.WORK_ITEM_TYPE = ?))
5. I ran a query with both full text = 'defect' OR type = 'defect' ==> Got 10 work items
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.ITEM_ID in('_kIwjAIXJEeKlGtXVZ5cngw', '_jgWtUI_cEeKuZOtxyMUbNA', '_zosp4I_cEeKuZOtxyMUbNA', '_0ncKEo_cEeKuZOtxyMUbNA', '_HKqGQI_gEeKuZOtxyMUbNA') or (t1.WORK_ITEM_TYPE = ?)))
6. I ran a query with both full text = 'defect' AND type = 'task' ==> Got NO work item. This is because all the work items returned from the fulltext condition are of 'defect' type.
7. a query with type = 'defect' and 'created by' = current user ==> Unlike 'fulltext' condition, the conditions in this case are evaluated together in a single query.
select distinct t1.ITEM_ID, t1.STATE_ID from MODEL.WORK_ITEM t1 where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t1.WORK_ITEM_TYPE = ?) and (t1.CREATOR_ITEM_ID = ?))
