Azure / Azure/data-api-builder
[Bug]: GraphQL Filter input for MSSQL -> DateTimeOffset without offset is assumed current timezone instead of UTC.
- Ngôn ngữ chính
- C#
- Star
- 1.5k
- Fork
- 370
- Merge trung bình
- 3 ngày 22 giờ
- Pull request đã merge (30 ngày)
- 9
Mô tả
### What happened?
DateTimeOffset values in GraphQL filters for MSSQL backing DB's has the value not converted to UTC:
```graphql
query testqueryTime{
supportedTypes(filter: { datetimeoffset_types: {lt: "9999-12-31T23:59:59.9999999"}}){
items{
typeid
datetimeoffset_types
}
}
}
```
```json
{
"errors": [
{
"message": "Failed to convert parameter value from a String to a DateTimeOffset.",
"locations": [
{
"line": 48,
"column": 3
}
],
"path": [
"supportedTypes"
],
"extensions": {
"message": "Failed to convert parameter value from a String to a DateTimeOffset.",
"stackTrace": "..."
}
}
]
}
```
This is because SqlClient coerces the provided string as follows:
https://github.com/dotnet/SqlClient/blob/55f48c57a9b2a200c266b8739a2a477f974ab41d/src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/SqlParameter.cs#L2295C19-L2298C22
```csharp
else if ((currentType == typeof(string)) && (destinationType.SqlDbType == SqlDbType.DateTimeOffset))
{
value = DateTimeOffset.Parse((string)value, (IFormatProvider)null);
}
```
See console output to demonstrate that IFormatProvider value is null and string value is what client passes in.
In docs, if no offset is provided, then the DateTimeOffset is resolved as current time zone. So my time zone is UTC-8 which makes `9999-12-31T23:59:59.9999999` overflow. This was not caught in pipelines because pipelines are UTC.
https://learn.microsoft.com/en-us/dotnet/api/system.datetimeoffset.parse?view=net-8.0#system-datetimeoffset-parse(system-string-system-iformatprovider):~:text=00%3A00%20AM.-,If%20%3COffset%3E%20is%20missing%2C%20its%20default%20value%20is%20the%20offset%20of%20the%20local%20time%20zone.,-%3COffset%3E%20can%20represent
> If is missing, its default value is the offset of the local time zone.
The applicable test is
```csharp
[DataRow(DATETIMEOFFSET_TYPE, "lt", "'9999-12-31 23:59:59.9999999'", "\"9999-12-31 23:59:59.9999999\"", "<",
DisplayName = "777 datetimeoffset type filter and orderby test with lt operator and max value for datetimeoffset.")]
public async Task QueryTypeColumnFilterAndOrderByDateTime(string type, string filterOperator, string sqlValue, string gqlValue, string queryOperator)
```
However, the following works as expected where UTC specifier Z is added.
```graphql
query testqueryTime{
supportedTypes(filter: { datetimeoffset_types: {lt: "9999-12-31T23:59:59.9999999Z"}}){
items{
typeid
datetimeoffset_types
}
}
}
```
Proposed Fix:
- A fix would be a breaking change (but critical?) because FILTER input is not resolved as UTC whereas Mutation input (I believe is? needs confirmation).
- Requires a behavioral feature flag to run conversion code for datetimeoffset value that interprets no offset specified == UTC.
### Version
1.1.7
### What database are you using?
Azure SQL
### What hosting model are you using?
Local (including CLI)
### Which API approach are you accessing DAB through?
GraphQL
### Relevant log output
```Text
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
f3773651-3c70-43b5-8189-01d5243b8797 Executing query: SELECT TOP 100 [table0].[id] AS [typeid], [table0].[datetimeoffset_types] AS [datetimeoffset_types] FROM [dbo].[type_table] AS [table0] WHERE [table0].[datetimeoffset_types] < @param1 ORDER BY [table0].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES
fail: Azure.DataApiBuilder.Service.Startup[0]
A GraphQL request execution error occurred.
System.FormatException: Failed to convert parameter value from a String to a DateTimeOffset.
---> System.FormatException: The UTC representation of the date '9999-12-31T23:59:59.9999999' falls outside the year range 1-9999.
at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles, TimeSpan& offset)
at System.DateTimeOffset.Parse(String input, IFormatProvider formatProvider, DateTimeStyles styles)
at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
--- End of inner exception stack trace ---
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__195_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, String dataSourceName, List`1 args) in C:\\Documents\Dev\dabdev\src\Core\Resolvers\QueryExecutor.cs:line 251
at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.<>c__DisplayClass23_0`1.<b__0>d.MoveNext() in C:\Users\\Documents\Dev\dabdev\src\Core\Resolvers\QueryExecutor.cs:line 193
```
### Code of Conduct
- [X] I agree to follow this project's Code of Conduct
Hướng dẫn đóng góp
Hướng nghiên cứu
Bắt đầu với test case QueryTypeColumnFilterAndOrderByDateTime cho giá trị DateTimeOffset tối đa và kiểm tra đường dẫn bộ lọc GraphQL cung cấp tham số của nó cho MSSQL. So sánh hành vi khi có và không có hậu tố UTC, bao gồm cả yêu cầu feature-flag được đề xuất. Công việc được xem là hoàn tất khi test áp dụng đạt cho giá trị không có offset mà không phụ thuộc vào múi giờ UTC của máy.
Do mô hình lập chỉ mục viết ra từ nội dung của issue.
Đánh giá
- Công nghệ
- azure, csharp, graphql, sql
- Lĩnh vực
- api, databases, testing
- Loại issue
- Lỗi
- Độ khó
- 4/5
- Thời gian dự kiến
- 3-5 ngày
- Mức độ hoạt động
- Đình trệ
- Độ rõ ràng
- Khá rõ ràng
- Mức phù hợp với người mới
- 35/100