CosmosDB SQL where clause with numerical or boolean values giving weird results

Kholofelo 66

Hi,

I have a weird issue with a very simple CosmosBD query. See the below query.

Basically, I have a simple query as follows:

select * from c where c.redeemed=1

This query for some reason returns results with items with redeemed as 0.

Any help would be appreciated.

Kholofelo 66 Reputation points

2020-10-29T02:42:09.92+00:00

Just to mention. Querying with strings is fine, but I'm seeing this particular issue when using booleans or integers.
Anurag Sharma 17,606 Reputation points

2020-10-29T05:05:35.07+00:00

Hi @Kholofelo , welcome to Microsoft Q&A forum.

Would it be possible for you to share couple of documents to reproduce it? That would help us in understanding the real cause.

Meanwhile when I tried by creating few documents with integers it worked fine.
Kholofelo 66 Reputation points

2020-10-29T09:49:53.593+00:00

Hi @AnuragSharma-MSFT

The issue mysteriously disappeared after a few hours (I had not changed the data). My redeemed field was eventually a boolean but I changed it to an integer to try and fix this issue. I Just now reverted back to boolean and I'm having the same issue:

Kholofelo 66

an example document:

{
        "dateCreated": "2020-10-28T23:20:45.1422932+00:00",
        "dateRedeemed": "0001-01-01T00:00:00+00:00",
        "userRedeemed": null,
        "pointsGroup": null,
        "redeemed": false,
        "data": {
            "voucherCode": "T2XYH-8666A-XXXXX-8G28U",
            "provider": {
                "name": "example",
                "id": "b654c045-5fdc-42d1-b178-478fb708814a",
                "instructionsLink": "https://example.com"
            }
        },
        "partitionKey": "voucher",
        "type": "loyalty_voucher",
        "id": "7c488347-8194-42f6-a360-759d8943132a"
    }

Kholofelo 66

One extra piece of information.

I'm using optimistic concurrency control on these document. It seems that is doing some weird stuff to the document.

Here is an example of where I'm using optimistic concurrency control:

  await client.ReplaceDocumentAsync(doc.SelfLink, doc, new RequestOptions
                        {
                            AccessCondition = new AccessCondition
                            {
                                Condition = doc.ETag,
                                Type = AccessConditionType.IfMatch
                            }
                        });

After this is done, the document starts behaving weridly as described.

Anurag Sharma 17,606 Reputation points

2020-10-29T14:11:08.4+00:00

Hi @Kholofelo , thanks for the info. I am reproducing the issue with the extra information provided and will get back to you on this.

1 answer

Kholofelo 66

Hi @AnuragSharma-MSFT

The issue suddenly disappeared (which is extremely worrying) so I switched back my data to using booleans and now I have the same issue again.

See below an example document:

    {  
            "dateCreated": "2020-10-28T23:20:45.1422932+00:00",  
            "dateRedeemed": "0001-01-01T00:00:00+00:00",  
            "userRedeemed": null,  
            "pointsGroup": null,  
            "redeemed": false,  
            "data": {  
                "voucherCode": "T2XYH-8666A-33F3P-XXXXX",  
                "provider": {  
                    "name": "some-name,  
                    "id": "b654c045-5fdc-42d1-b178-478fb708814a",  
                    "instructionsLink": "https://example.com"  
                }  
            },  
            "partitionKey": "voucher",  
            "type": "loyalty_voucher"  
        }

This gives the same weird issue:

Share via

CosmosDB SQL where clause with numerical or boolean values giving weird results

1 answer

Your answer