azure-table-storageazure-tablequery

Max $filter comparisons in an Azure Table Query

This page (https://learn.microsoft.com/en-us/rest/api/storageservices/querying-tables-and-entities) says:

Note that no more than 15 discrete comparisons are permitted within a $filter string.

However in my experiments I have hit that limit and not had any side effects. For example, this is from Azure Storage Explorer:

Statistics for storageacct/table ("PartitionKey eq '1' or PartitionKey eq '2' or PartitionKey eq '3' or PartitionKey eq '4' or PartitionKey eq '5' or PartitionKey eq '6' or PartitionKey eq '7' or PartitionKey eq '8' or PartitionKey eq '9' or PartitionKey eq '10' or PartitionKey eq '11' or PartitionKey eq '12' or PartitionKey eq '13' or PartitionKey eq '14' or PartitionKey eq '15' or PartitionKey eq '16' or PartitionKey eq '17' or PartitionKey eq '18' or PartitionKey eq '19' or PartitionKey eq '20' or PartitionKey eq '21' or PartitionKey eq '22' or PartitionKey eq '23' or PartitionKey eq '24' or PartitionKey eq '25' or PartitionKey eq '26' or PartitionKey eq '27' or PartitionKey eq '28' or PartitionKey eq '29' or PartitionKey eq '30' or PartitionKey eq '31' or PartitionKey eq '32' or PartitionKey eq '33' or PartitionKey eq '34' or PartitionKey eq '35' or PartitionKey eq '36' or PartitionKey eq '37' or PartitionKey eq '38' or PartitionKey eq '39' or PartitionKey eq '40' or PartitionKey eq '41'"): 0 entities

Given the 15 comparison limit, I would expect this $filter to cause the request to fail.

In the case I needed to interpret "15 discrete comparisons" a certain way, I tried this query with various and/or combinations. It always succeeds.

Is this limitation from a previous generation of Azure Table APIs which does not exist anymore?

Are there any other limits on $filter? Such as maximum string length?

Thanks

** EDIT **

I have been experimenting some more on this. Assuming the Development Storage Emulator is the same as the real service, the number of comparison operators which can be used in a query is not a fixed amount. Here are some experiment results which gave successful results where when incrementing by one results in an error:

(PK==V) and ((RK==V) or (RK==V) ... 97x) // 98 comparisons, 97 non-PK comparisons
(PK==V and RK==V) or (PK==V and RK==V) ... 97x // 194 comparisons, 97 non-PK comparisons
(RK==V) or (RK==V) ... 98x // 98 comparisons, 98 non-PK comparisons
(PK==V) or (PK==V) ... 98x // 98 comparisons, 0 non-PK comparisons
(PK==V and RK==V and Prop=V) or (PK==V and RK==V and Prop=V) ... 93x // 279 comparisons, 186 non-PK comparisons

I am not sure what conclusion to draw from this. I can safely do (PK==V and RK==V) or'd 97 times, but I can do (RK==V) or'd 98 times. I have tested this with same values and also distinct values, as well as other comparison operators and not just equals.

With these results, how can one predictably know the server will return an error based on a query string?

And where does the number 15 come into play?

** EDIT **

I just tried all of my tests on a live storage account and found there was no maximum. In fact I was able to keep adding operators successfully until it started returning:

The remote server returned an error: (414) Request-URI Too Long.

So all of those random results I got from the storage emulator apparently don't apply to the live service. And also the 15 comparison limit does not exist anymore at all? (conjecture)

From trial and error, it looks like I start getting a 414 error when the full URI is about 32768 (32KB) characters long. That is a fully encoded URL including all other parameters, scheme, hostname, etc. I don't think there is a reliable way to precompute the exact URI length which would be produced by ExecuteQuery, so I suppose one could just split the request starting after about a 32500 character $filter string? And then don't expect it to work with the storage emulator...

Solution

  • I also tested it with REST API and Client Library. I also found that the 15 discrete comparisons limit does not exist. I can add lots of comparisons until reach the URL length limit. Here were what I tested.

    1. Table REST API, 1000+ comparisons of PK.
    2. Table REST API, 1000+ comparisons with different columns.
    3. Table Client Library, 1000+ comparisons of PK.
    4. Table Client Library, 1000+ comparisons with different columns.

    On Azure Table Client Library document, I haven't found the 15 discrete comparisons limit. The limit maybe out of date. Table​Query.​Filter​String Property

    You could post a comment on following document to get the official response from the document owner.

    Querying Tables and Entities