Index on contenttype column breaks REST query on ContentTypeId

%3CLINGO-SUB%20id%3D%22lingo-sub-1147381%22%20slang%3D%22en-US%22%3EIndex%20on%20contenttype%20column%20breaks%20REST%20query%20on%20ContentTypeId%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1147381%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20REST%20query%20on%20a%20list%20with%20a%20filter%20on%20contentypeid%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fportal%2F_api%2FWeb%2FLists%2FgetByTitle%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fportal%2F_api%2FWeb%2FLists%2FgetByTitle%3C%2FA%3E%3CSPAN%3E('MyForms')%2FItems%3F%24filter%3Dstartswith(ContentTypeId%2C'0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF')%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20query%20has%20always%20worked.%20Our%20list%20now%20has%20more%20than%20500%20items%20and%20now%20the%20query%20fails%20and%20returning%20zero%20items.%26nbsp%3B%3CSPAN%3EWe%20have%20an%20expiration%20policy%20on%20our%20contenttypes.%20The%20expiration%20policy%20timer%20job%20has%20created%20an%20index%20on%20the%20contenttype%20column.%20When%20I%20remove%20this%20index%20my%20query%20works%20again%20with%20500%20or%20more%20items.%20sadly%20the%20index%20is%20recreated%20on%20contenttype%20when%20the%20expiration%20policy%20timer%20job%20runs.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20does%20an%20index%20on%20contenttype%20break%20my%20REST%20query%20when%20there%20are%20500%20items%20or%20more%3F%20When%20I%20have%20499%20items%20the%20query%20still%20works%20even%20with%20the%20index%20on%20contentype%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%23%23%23%23%20Update%20from%20analyzing%26nbsp%3Bthe%20ULS%20log%20%23%23%23%23%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20do%20a%20trace%20I%20see%20that%20my%20REST%20query%20is%20translated%20to%20the%20following%20CAML%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3CQUERY%3E%3CWHERE%3E%3CBEGINSWITH%3E%3CFIELDREF%20name%3D%22ContentTypeId%22%3E%3C%2FFIELDREF%3E%3CVALUE%20type%3D%22Text%22%3E0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF%3C%2FVALUE%3E%3C%2FBEGINSWITH%3E%3C%2FWHERE%3E%3C%2FQUERY%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20query%20fails%20when%20I%20execute%20this%20from%20CSOM.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%20%20%20%20%20%20%20%20%20%20%20%20using%20(ClientContext%20ctx%20%3D%20new%20ClientContext(%22http%3A%2F%2Fd-ccp-portal%22))%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Web%20web%20%3D%20ctx.Web%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List%20list%20%3D%20web.Lists.GetByTitle(%22Beheerde%20documenten%20Forms%202%22)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20var%20q%20%3D%20new%20CamlQuery()%20%7B%20ViewXml%20%3D%20%22%3CVIEW%3E%3CQUERY%3E%3CWHERE%3E%3CBEGINSWITH%3E%3CFIELDREF%20name%3D%22ContentTypeId%22%3E%3C%2FFIELDREF%3E%3CVALUE%20type%3D%22Text%22%3E0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF%3C%2FVALUE%3E%3C%2FBEGINSWITH%3E%3C%2FWHERE%3E%3C%2FQUERY%3E%3C%2FVIEW%3E%22%20%7D%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20var%20r%20%3D%20list.GetItems(q)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ctx.Load(r)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ctx.ExecuteQuery()%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Er%20%3D%200%3C%2FP%3E%3CP%3EThe%20problem%20seems%20to%20be%20the%20value%20type.%20When%20I%20replace%20my%20CAML%20query%20like%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3CQUERY%3E%3CWHERE%3E%3CBEGINSWITH%3E%3CFIELDREF%20name%3D%22ContentTypeId%22%3E%3C%2FFIELDREF%3E%3CVALUE%20type%3D%22ContentTypeId%22%3E0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF%3C%2FVALUE%3E%3C%2FBEGINSWITH%3E%3C%2FWHERE%3E%3C%2FQUERY%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20do%20get%20all%20my%20items%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%20%20%20%20%20%20%20%20%20%20%20%20using%20(ClientContext%20ctx%20%3D%20new%20ClientContext(%22http%3A%2F%2Fd-ccp-portal%22))%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Web%20web%20%3D%20ctx.Web%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List%20list%20%3D%20web.Lists.GetByTitle(%22Beheerde%20documenten%20Forms%202%22)%3B%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20var%20q2%20%3D%20new%20CamlQuery()%20%7B%20ViewXml%20%3D%20%22%3CVIEW%3E%3CQUERY%3E%3CWHERE%3E%3CBEGINSWITH%3E%3CFIELDREF%20name%3D%22ContentTypeId%22%3E%3C%2FFIELDREF%3E%3CVALUE%20type%3D%22ContentTypeId%22%3E0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF%3C%2FVALUE%3E%3C%2FBEGINSWITH%3E%3C%2FWHERE%3E%3C%2FQUERY%3E%3C%2FVIEW%3E%22%20%7D%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20var%20r2%20%3D%20list.GetItems(q2)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ctx.Load(r2)%3B%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ctx.ExecuteQuery()%3B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%20the%20problem%20seems%20to%20be%20that%20REST%20doesn't%20set%20the%20correct%20%22value%20type%22%20when%20you%20do%20a%20%24filter%20on%20startswith(ContenTypeId)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20the%20index%20on%20my%20list%20also%20has%20influence%20I%20don't%20really%20know.%20Because%20when%20there%20is%20no%20index%20on%20contenttype%20the%20first%20query%20also%20works%20fine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1147381%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAPIs%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

We have a REST query on a list with a filter on contentypeid:

 

http://portal/_api/Web/Lists/getByTitle('MyForms')/Items?$filter=startswith(ContentTypeId,'0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF')

 

This query has always worked. Our list now has more than 500 items and now the query fails and returning zero items. We have an expiration policy on our contenttypes. The expiration policy timer job has created an index on the contenttype column. When I remove this index my query works again with 500 or more items. sadly the index is recreated on contenttype when the expiration policy timer job runs.

 

Why does an index on contenttype break my REST query when there are 500 items or more? When I have 499 items the query still works even with the index on contentype

 

#### Update from analyzing the ULS log ####

 

When I do a trace I see that my REST query is translated to the following CAML query:

 

 

 

<Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='Text'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query>

 

 

 

This query fails when I execute this from CSOM.

 

 

 

            using (ClientContext ctx = new ClientContext("http://d-ccp-portal"))
            {
                Web web = ctx.Web;
                List list = web.Lists.GetByTitle("Beheerde documenten Forms 2");

                var q = new CamlQuery() { ViewXml = "<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='Text'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query></View>" };
                var r = list.GetItems(q);                ctx.Load(r);                ctx.ExecuteQuery();
            }

 

 

 

 

r = 0

The problem seems to be the value type. When I replace my CAML query like so:

 

 

 

<Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query>

 

 

 

 

I do get all my items

 

 

 

            using (ClientContext ctx = new ClientContext("http://d-ccp-portal"))
            {
                Web web = ctx.Web;
                List list = web.Lists.GetByTitle("Beheerde documenten Forms 2");

                var q2 = new CamlQuery() { ViewXml = "<View><Query><Where><BeginsWith><FieldRef Name='ContentTypeId' /><Value Type='ContentTypeId'>0x010101008801EC40791D47909E988DEB1A1B9EFD006D679758256E403D9B766451FDA53DBF</Value></BeginsWith></Where></Query></View>" };
                var r2 = list.GetItems(q2);                ctx.Load(r2);                ctx.ExecuteQuery();
            }

 

 

 

 

So the problem seems to be that REST doesn't set the correct "value type" when you do a $filter on startswith(ContenTypeId)

 

Why the index on my list also has influence I don't really know. Because when there is no index on contenttype the first query also works fine.

0 Replies