Hacktoberfest 2026: những issue maintainer đã đánh dấu cho tháng Mười, đang mở và phù hợp người mới. Xem issue Hacktoberfest

[Bug]: smalldatetime column cannot be filtered via OData $filter - three literal forms, three different failurs

Đang mở
  • Có gì không ổn?
#3,760 1 bình luận 0 reaction 1 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

@aaronburtle đang làm issue này rồi.

Từ ngày 14/9/2026.

Đánh giá

Issue này chưa được đánh giá.

Mô tả

bug
What happened?

Filtering a smalldatetime column through the MCP DML tools fails for every literal form tried. Three forms were tested and each failed differently: one is rejected by the parser, one fails at parameter binding, and one fails with no type mapping. No form succeeded.

In the parameter-binding case the query itself is correct — DAB resolves the entity and fields and emits well-formed SQL with correct parameter placeholders. The failure occurs when binding the parameter value.

This was against on-premise SQL Server 2022.

Version

2.0.9

What database are you using?

Azure SQL

What hosting model are you using?

Custom Docker host

Which API approach are you accessing DAB through?

MCP

Relevant log output
### 1. `aggregate_records`, ISO 8601 with `Z` — `UnexpectedError`

Arguments:


{
  "entity": "ARAS_Charges_By_Day",
  "function": "count",
  "field": "*",
  "filter": "ChargeNumber eq '390100' and DateOfWork ge 2026-07-01T00:00:00Z and DateOfWork lt 2026-08-01T00:00:00Z",
  "groupby": ["Username"]
}


SQL generated (correct):


SELECT TOP 1001 [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[Username] AS [Username],
       count([dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[DateOfWork]) AS [count]
FROM [dbo].[ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT] AS [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT]
WHERE ((([ChargeNumber] = @param0) AND ([DateOfWork] >= @param1)) AND ([DateOfWork] < @param2))
GROUP BY [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[Username]
ORDER BY COUNT([dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[DateOfWork]) DESC
FOR JSON PATH, INCLUDE_NULL_VALUES


Server log:


fail: Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool[0]
      Unexpected error in AggregateRecordsTool.
      System.InvalidCastException: Failed to convert parameter value from a DateTimeOffset to a DateTime.
       ---> System.InvalidCastException: Object must implement IConvertible.
         at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
         at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
         --- End of inner exception stack trace ---
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](...) in /_/src/Core/Resolvers/QueryExecutor.cs:line 296
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](...) in /_/src/Core/Resolvers/QueryExecutor.cs:line 196
         at Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool.ExecuteAsync(JsonDocument arguments, IServiceProvider serviceProvider, CancellationToken cancellationToken) in src\Azure.DataApiBuilder.Mcp\BuiltInTools\AggregateRecordsTool.cs:line 269
warn: Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool[0]
      MCP Tool error UnexpectedError: Unexpected error occurred in AggregateRecordsTool.


Returned to the client:


{ "toolName": "aggregate_records", "status": "error",
  "error": { "type": "UnexpectedError", "message": "Unexpected error occurred in AggregateRecordsTool." } }


### 2. `read_records`, ISO 8601 without offset — `BadRequest`

Arguments:


{
  "entity": "ARAS_Charges_By_Day",
  "select": "Username,DateOfWork",
  "filter": "ChargeNumber eq '390100' and DateOfWork ge 2026-07-01T00:00:00 and DateOfWork lt 2026-08-01T00:00:00"
}


Returned to the client:


{ "toolName": "read_records", "status": "error",
  "error": { "type": "BadRequest",
    "message": "The DateTimeOffset text '2026-07-01T00:00:00' should be in format 'yyyy-MM-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range." } }


So the parser requires the offset that case 1 supplies — and that offset is what case 1 then fails to bind.

### 3. Date-only literal — no type mapping

Using an unquoted date with no time component (`DateOfWork ge 2026-07-01`), the error returned was:


No mapping exists from object type Microsoft.OData.Edm.Date to a known managed provider native type.


## Summary

| Literal form | Result |
|---|---|
| `2026-07-01T00:00:00Z` | `UnexpectedError` — `InvalidCastException`, `DateTimeOffset` → `DateTime` at `SqlParameter.CoerceValue` |
| `2026-07-01T00:00:00` | `BadRequest` — parser requires an offset |
| `2026-07-01` | No mapping from `Microsoft.OData.Edm.Date` to a provider native type |

## Expected behaviour

A `smalldatetime` column should be filterable. The parsed OData value should be converted to the CLR type matching the target column before binding.

Additionally, the case 1 failure reaches the MCP client as a bare `UnexpectedError` with no detail. If this is a value-conversion problem it is user-correctable, and a `BadRequest` naming the field and expected format would let a calling agent self-correct rather than retry blindly.

## Possibly related

- #2783 — `datetime2` columns are emitted in the generated OpenAPI as `string` with no format, and the reporter hit trouble applying date filters.
- #2268 — `SqlClient` coercion failure on date parameters (GraphQL/MSSQL). Different endpoint, same coercion layer.

## Workaround in use

An `int` day key on the view, filtered instead of the date column:


CONVERT(int, CONVERT(char(8), DateOfWork, 112)) AS DateKey
-- DateKey ge 20260701 and DateKey lt 20260801



### Code of Conduct

- [x] I agree to follow this project's Code of Conduct
Ngôn ngữ chính
C#
Star
1.5k
Fork
371
Merge trung bình
9 ngày 2 giờ
Pull request đã merge (30 ngày)
10

Chuẩn bị môi trường

Mở trong Codespaces

Khởi chạy dev container của dự án ngay trên trình duyệt, bằng tài khoản GitHub của bạn.

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của Azure/data-api-builder

Tất cả issue của Azure/data-api-builder

Issue tương tự

Thêm issue về C#

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.