Multiple NotInCriteria producing incorrect results

Question

Might be doing something stupid but basically I am not getting the results I would expect when using multiple NotInCriteria in a query. I only ever seem to have the one of the criteria excluded.

The code I am using to create the keywords to exclude part is as follows.

using (TaxonomyFactory tf = new TaxonomyFactory())
{
    foreach (string excludeKeyword in keywordsToExclude)
    {
        Keyword selectedKeyword = tf.GetTaxonomyKeyword(excludeKeyword);
        TaxonomyKeywordCriteria aKeywordCriteria = new 
                                         TaxonomyKeywordCriteria(selectedKeyword.TaxonomyUri,
                                                                 selectedKeyword.KeywordUri, 
                                                                 false);
        exclCiteria.Add(new NotInCriteria(aKeywordCriteria));
    }
}

Then add that to some other criteria and run

criteria.AddRange(exclCiteria);
Criteria queryCriteria = new AndCriteria(criteria.ToArray());

I have completed a stack trace on the cd_core log to see what query was actually being produce/run and found the following

2014-06-18 16:49:30,533 DEBUG SQL - select distinct itemmeta0_.PUBLICATION_ID as col_0_0_, itemmeta0_.ITEM_REFERENCE_ID as col_1_0_, itemmeta0_.ITEM_TYPE as col_2_0_, custommeta2_.KEY_DATE_VALUE as col_3_0_ 

from ITEMS itemmeta0_, COMPONENT componentm1_ inner join ITEMS componentm1_1_ on componentm1_.PUBLICATION_ID=componentm1_1_.PUBLICATION_ID and componentm1_.ITEM_REFERENCE_ID=componentm1_1_.ITEM_REFERENCE_ID, CUSTOM_META custommeta2_ 

where itemmeta0_.ITEM_TYPE=? and itemmeta0_.PUBLICATION_ID=? and (itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=? or itemmeta0_.ITEM_REFERENCE_ID=componentm1_.ITEM_REFERENCE_ID and itemmeta0_.PUBLICATION_ID=componentm1_.PUBLICATION_ID and componentm1_.SCHEMA_ID=?) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta3_.ITEM_REFERENCE_ID from ITEMS itemmeta3_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey4_ where itemmeta3_.ITEM_REFERENCE_ID=relatedkey4_.ITEM_REFERENCE_ID and itemmeta3_.PUBLICATION_ID=relatedkey4_.PUBLICATION_ID and relatedkey4_.KEYWORD_ID=? and relatedkey4_.TAXONOMY_ID=? and relatedkey4_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta5_.ITEM_REFERENCE_ID from ITEMS itemmeta5_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey6_ where itemmeta5_.ITEM_REFERENCE_ID=relatedkey6_.ITEM_REFERENCE_ID and itemmeta5_.PUBLICATION_ID=relatedkey6_.PUBLICATION_ID and relatedkey6_.KEYWORD_ID=? and relatedkey6_.TAXONOMY_ID=? and relatedkey6_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta7_.ITEM_REFERENCE_ID from ITEMS itemmeta7_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey8_ where itemmeta7_.ITEM_REFERENCE_ID=relatedkey8_.ITEM_REFERENCE_ID and itemmeta7_.PUBLICATION_ID=relatedkey8_.PUBLICATION_ID and relatedkey8_.KEYWORD_ID=? and relatedkey8_.TAXONOMY_ID=? and relatedkey8_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta9_.ITEM_REFERENCE_ID from ITEMS itemmeta9_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey10_ where itemmeta9_.ITEM_REFERENCE_ID=relatedkey10_.ITEM_REFERENCE_ID and itemmeta9_.PUBLICATION_ID=relatedkey10_.PUBLICATION_ID and relatedkey10_.KEYWORD_ID=? and relatedkey10_.TAXONOMY_ID=? and relatedkey10_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta11_.ITEM_REFERENCE_ID from ITEMS itemmeta11_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey12_ where itemmeta11_.ITEM_REFERENCE_ID=relatedkey12_.ITEM_REFERENCE_ID and itemmeta11_.PUBLICATION_ID=relatedkey12_.PUBLICATION_ID and relatedkey12_.KEYWORD_ID=? and relatedkey12_.TAXONOMY_ID=? and relatedkey12_.PUBLICATION_ID=?)) 

and (itemmeta0_.ITEM_REFERENCE_ID not in  (select distinct itemmeta13_.ITEM_REFERENCE_ID from ITEMS itemmeta13_, ITEM_CATEGORIES_AND_KEYWORDS relatedkey14_ where itemmeta13_.ITEM_REFERENCE_ID=relatedkey14_.ITEM_REFERENCE_ID and itemmeta13_.PUBLICATION_ID=relatedkey14_.PUBLICATION_ID and relatedkey14_.KEYWORD_ID=? and relatedkey14_.TAXONOMY_ID=? and relatedkey14_.PUBLICATION_ID=?)) 

and custommeta2_.PUBLICATION_ID=itemmeta0_.PUBLICATION_ID and custommeta2_.ITEM_ID=itemmeta0_.ITEM_REFERENCE_ID and custommeta2_.ITEM_TYPE=itemmeta0_.ITEM_TYPE and custommeta2_.KEY_NAME=? order by custommeta2_.KEY_DATE_VALUE DESC

2014-06-18 16:49:30,533 TRACE IntegerType - binding '16' to parameter: 1
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 2
2014-06-18 16:49:30,533 TRACE IntegerType - binding '98' to parameter: 3
2014-06-18 16:49:30,533 TRACE IntegerType - binding '103' to parameter: 4
2014-06-18 16:49:30,533 TRACE IntegerType - binding '8563' to parameter: 5
2014-06-18 16:49:30,533 TRACE IntegerType - binding '100' to parameter: 6
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 7
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 8
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 9
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 10
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 11
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 12
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 13
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 14
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 15
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 16
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 17
2014-06-18 16:49:30,533 TRACE IntegerType - binding '22' to parameter: 18
2014-06-18 16:49:30,533 TRACE IntegerType - binding '7224' to parameter: 19
2014-06-18 16:49:30,533 TRACE IntegerType - binding '55' to parameter: 20
2014-06-18 16:49:30,549 TRACE IntegerType - binding '22' to parameter: 21
2014-06-18 16:49:30,549 TRACE IntegerType - binding '7224' to parameter: 22
2014-06-18 16:49:30,549 TRACE IntegerType - binding '55' to parameter: 23
2014-06-18 16:49:30,549 TRACE IntegerType - binding '22' to parameter: 24
2014-06-18 16:49:30,549 TRACE StringType - binding 'Date' to parameter: 25

For some reason the same keyword is being searched for in the not in statements i.e. params 7,10,13,16,19 and 22 are all the same. I have checked that the keywords in the keywords to exclude list are all correct and more importantly different but for some reason they are all passed through as the same keyword. Am I doing something stupid or is this a known issue. Any help would be greatly appreciated.

Thanks!

Answer 1

Thought I would post this as it may help someone else if you run into this problem. Have received the following response from SDL support who solved the issue.

I have investigated further and observed that the code shared in previous notes was failing to include NotInCriteria when more than one keyword is used in excludeKeywords array. This was because the inner subquery goes on with multiple cross join due to AndCriteria, which can be corrected by using Orcriteria.

Following is the corrected code sample, which was tested and working fine as expected:

int publicationId = 12;
string categoryUri = "tcm:12-32-512";
string[] excludeKeywords = new String[] { "tcm:12-172-1024", "tcm:12-178-1024" };
Criteria includeCriteria = new PublicationCriteria(publicationId);
List<Criteria> excludeCriteria = new List<Criteria>();
foreach (String keywordUri in excludeKeywords)
{
    excludeCriteria.Add(new TaxonomyKeywordCriteria(categoryUri, keywordUri, true));
}
Criteria excludeAnd = new OrCriteria(excludeCriteria.ToArray());
Criteria finalCriteria = new AndCriteria(includeCriteria, new NotInCriteria(excludeAnd));
Query myQuery = new Query(finalCriteria);
string[] itemURIs = myQuery.ExecuteQuery();