Filtering by Dates with Table Service no longer working in 3.25.0

[thornerygrecianms]

Which service(blob, file, queue, table) does this issue concern? Table

Which version of the Azurite was used? 3.25.0

Where do you get Azurite? (npm, DockerHub, NuGet, Visual Studio Code Extension) GitHub and Docker

What's the Node.js version? 9.8.1

What problem was encountered? When filtering with dates to the table service using the Microsoft.Azure.Cosmos.Table library no results are returned with 3.25.0 which used to work with previous versions

Steps to reproduce the issue?

This sample C# code demonstrates the issue, running this in 3.24.0 will output results, in 3.25.0 it returns nothing.

_using Microsoft.Azure.Cosmos.Table;

namespace TestTableCreateAndList

{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            // Create a CloudStorageAccount object
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse("UseDevelopmentStorage=true");

            // Create a CloudTableClient object
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

            // Get a reference to the table
            CloudTable table = tableClient.GetTableReference("TestTable");

            table.CreateIfNotExists();

            var dataTime = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

            TestEntity testEntity = new TestEntity { MyDate = dataTime, RowKey= "1", PartitionKey= "2" };

            TableOperation insertOrReplaceOperation = TableOperation.InsertOrReplace(testEntity);

            // Execute the operation
            TableResult result = await table.ExecuteAsync(insertOrReplaceOperation);

            string filter = TableQuery.GenerateFilterConditionForDate("MyDate", QueryComparisons.Equal, dataTime);          
            
            // This does work, using GenerateFilterCondition
            // string filter = TableQuery.GenerateFilterCondition("MyDate", QueryComparisons.Equal, dataTime.ToString());              

            // Create a TableQuery object with the filter
            TableQuery<TestEntity> query = new TableQuery<TestEntity>().Where(filter);

            // Execute the query            
            TableQuerySegment<TestEntity> resultSegment = await table.ExecuteQuerySegmentedAsync(query, null);
            
            // Display the result
            foreach (TestEntity entity in resultSegment.Results)
            {
                Console.WriteLine(entity.RowKey);
            }
        }
    }

    public class TestEntity : TableEntity
    {
        public DateTime MyDate { get; set; }
    }
}_

If possible, please provide the debug log using the -d parameter, replacing <pathtodebuglog> with an appropriate path for your OS, or review the instructions for docker containers:

In the debug logs we see this.

It seems that the filter has datetime before the filter value: MyDate eq datetime'1970-01-01T00:00:00.0000000Z', it works if filter value: MyDate eq '1970-01-01T00:00:00.0000000Z' when using GenerateFilterCondition

_v24

2023-07-25T15:27:47.511Z 59b40689-be99-403c-87e3-b67d6e3f1dfc info: HandlerMiddleware: DeserializedParameters={"options":{"queryOptions":{"filter":"MyDate eq datetime'1970-01-01T00:00:00.0000000Z'"},"requestId":"78656609-32d7-4248-8cbf-517dddbd6379","dataServiceVersion":"3.0;"},"version":"2017-07-29"}
2023-07-25T15:27:47.514Z 59b40689-be99-403c-87e3-b67d6e3f1dfc debug: TableHandler:queryEntities() Raw response string is "{\"odata.metadata\":\"http://127.0.0.1:10002/devstoreaccount1/$metadata#Tables/@Element\",\"value\":[{\"odata.etag\":\"W/\\\"datetime'2023-07-25T15%3A27%3A47.4012873Z'\\\"\",\"PartitionKey\":\"2\",\"RowKey\":\"1\",\"[email protected]\":\"Edm.DateTime\",\"MyDate\":\"1970-01-01T00:00:00.0000000Z\",\"Timestamp\":\"2023-07-25T15:27:47.4012873Z\"}]}"

v25

23-07-25T15:34:35.033Z 2d7e6abe-6f10-4eba-a55f-9b401db19ef2 info: HandlerMiddleware: DeserializedParameters={"options":{"queryOptions":{"filter":"MyDate eq datetime'1970-01-01T00:00:00.0000000Z'"},"requestId":"9920f2f4-c6d5-4b18-bc0a-1db351c3486e","dataServiceVersion":"3.0;"},"version":"2017-07-29"}
2023-07-25T15:34:35.036Z 2d7e6abe-6f10-4eba-a55f-9b401db19ef2 debug: TableHandler:queryEntities() Raw response string is "{\"odata.metadata\":\"http://127.0.0.1:10002/devstoreaccount1/$metadata#Tables/@Element\",\"value\":[]}"_

Please be sure to remove any PII or sensitive information before sharing!
The debug log will log raw request headers and bodies, so that we can replay these against Azurite using REST and create tests to validate resolution.

Have you found a mitigation/solution? No as we depend on locked SDKs making these calls, however using GenerateFilterCondition as a string does work, it´s just when you use GenerateFilterConditionForDate. We´ve not checked for GenerateFilterConditionForInt etc.

[Slater-1]

Seeing a similar issue with date ranges on v3.25.0.

Seeded Data:
daterange-repro.csv

Query:

[XiaoningLiu]

v3.25.0 includes refactor changes on Table entity query implementation #1975. @notheotherben can you help take a look?

[notheotherben]

Looking into this now, I suspect the issue here is the micro-second precision (which isn't reflected in the type coercion happening during evaluation). I'll see what we can do to get that resolved.

[notheotherben]

#2077 should resolve this, running the above repro locally I get the following results (which appear to be valid).

Big thank you to @thornerygrecianms and @Slater-1 for the comprehensive bug report here, it made tracking down the issue a lot easier than it would otherwise have been.

[thornerygrecianms]

Thanks for you help with this @notheotherben @EmmaZhu have pulled the latest version and everything is now working as expected.