Check for null does not work anymore

Question

Hi guys,

Since the GA release of DocumentDB it seems that the check for null properties in a query does not work.

Here is an example that used to return values, but now it doesn't: 

SELECT * FROM WebStoreCustomers c where c.azureSearchSyncDateTime != null

We know for a fact that we have documents that contain values for the azureSearchSyncDateTime property.

How can we change our query syntax to fix this problem?

Thanks!

Answer 1

Hello,

I just tried, @playground (http://www.documentdb.com/sql/demo), with the following query:

SELECT f.id,
 f.description,
 f.tags,
 f.foodGroup
FROM foods f
WHERE f.foodGroup = "Snacks" and f.id = "19015" and f.tags!=null

It return me the document. Do you think this is a valid test?

I will run some more tests with Datetime in mind to check if this is related to DateTime type in particular.

Thanks.

Meer Alam

---

Meer Al - MSFT

Answer 2

I also tested creating a simple document as:

{
    "id": "Test 1",
    "OrderDate": {
      "Date": "2014-09-15T23: 14: 25.7251173Z",
      "Epoch": 1408318702
    },
    "ShipDate": {
      "Date": "2014-09-30T23: 14: 25.7251173Z",
      "Epoch": 1408318702
    }

}

Then I ran the query:

SELECT * FROM c where c.ShipDate !=null and it return me the document

If I change this to:

SELECT * FROM c where c.ShipDate=null , then no document is returned.

In my test, thus, it seems it is working as expected.

Can you share a sample document from your collection, so I can test this out further?

Thanks.

---

Meer Al - MSFT

Answer 3

Hi, thanks for the quick reply.

i have seen strange behavior in the query explorer.

this is a document example i expect to find with the mentioned query:

{

"emailAddress": "[e-mail]",
    "numberOfOrders": 0,
    "currency": null,
    "totalRevenue": "0.00",
    "customerSince": "2015-04-04T00:00:00",
    "updateDate": "2015-04-04T04:00:57.5652718Z",
    "customerData": "{\r\n  \"naam\": \" Wiltenburg\",\r\n  \"email_address\": \"[e-mail]\",\r\n  \"customer_since\": \"2015-04-04\",\r\n  \"order_count\": 0,\r\n  \"total_spent\": \"0.00\",\r\n  \"telefoon\": \"0627525347\",\r\n  \"mobiel\": \"\",\r\n  \"straat\": \"Zwier Regelinkstraat\",\r\n  \"huisnummer\": \"13\",\r\n  \"postcode\": \"3421 BZ\",\r\n  \"woonplaats\": \"Oudewater\"\r\n}",
    "webStorePlatformId": 32123,
    "retentionGridNumberOfOrders": null,
    "retentionGridLastOrderDateTime": null,
    "platformUpdateDate": null,
    "azureSearchSyncDateTime": "2015-04-04T04:00:56.909Z",
    "azureSearchSyncInProgress": false,
    "personId": null,
    "name": null,
    "phoneNumber": null,
    "numberOfConversations": 0,
    "numberOfOpenConversations": 0,
    "lastGivenRating": null,
    "twitterName": null,
    "webStorePlatformName": null,
    "sequenceNumber": 0,
    "DatabaseId": 0,
    "subscriptionId": 33484,
    "id": "7e0abbb3-8b45-432b-9ad9-e09a958703f3",
    "creationDateTime": "2015-04-04T04:00:57.5652718Z",
    "_rid": "cltRAPqyzAAk+Q0AAAAAAA==",
    "_ts": 1428120056,
    "_self": "dbs/cltRAA==/colls/cltRAPqyzAA=/docs/cltRAPqyzAAk+Q0AAAAAAA==/",
    "_etag": "\"000046b4-0000-0000-0000-551f61f80000\"",
    "_attachments": "attachments/" 

}

the strange thing is that i CAN find this document when i use this query:

SELECT * FROM c where c.subscriptionId = 33484 and c.azureSearchSyncDateTime != null and c.azureSearchSyncInProgress = false

the only difference is that i added c.subscriptionId = 33484 to the query.

Answer 4

Hello Elmar,

Thanks for the information.

Strange, it works for me. I tried the following query after adding the document to my test collection

SELECT * FROM c where c.azureSearchSyncDateTime!=null

From your original query:

SELECT * FROM WebStoreCustomers c where c.azureSearchSyncDateTime != null

, can you try removing WebStoreCustomers to see if behaves differently?

Thanks.

---

Meer Al - MSFT

Answer 5

Hello Elmar,

One more thought. Possible that we did not have index on azureSearchSyncDateTime field?

Thanks.

---

Meer Al - MSFT

Answer 6

After offline discussion, we determined the root cause was that the query mentioned returned the correct results, but they were in subsequent pages that were not fetched automatically by Elmar's stored procedure.

In this case, the problem was not immediately apparent since the query requires a scan, and no results could be retrieved within the first page's execution time.

To avoid reading incomplete results, when executing queries inside stored procedures, please check for additional results using response.continuation like the example shown here: https://code.msdn.microsoft.com/windowsazure/Azure-DocumentDB-NET-Code-6b3da8af/view/SourceCode#content