Azure / Azure/data-api-builder

[Bug]: 500 `InvalidOperationException` on cached REST read when result is empty

Open
#3,704 1 comment 0 reactions 1 assignee Claimed by @souvikghosh04 View on GitHub
cri rest
Dominant language
C#
Stars
1.5k
Forks
370
Avg merge
3d 17h
Merged PRs (30d)
8

Description

## Environment

- **DAB:** `Microsoft.DataApiBuilder 2.0.9+17ae3aa8414514918b627c777026efbc135a57e9`
- **Database:** Azure SQL (mssql)
- **API:** REST, `host.mode: development`, anonymous read
- **Also reported failing on:** 1.7.92, 2.0.8 (a commenter notes 1.7.93 worked)

## Summary

When entity/runtime **caching is enabled** and a REST read returns **zero rows**, DAB returns **HTTP 500** with `System.InvalidOperationException` during response serialization. With caching disabled, the identical requests correctly return `200 {"value":[]}`. The failure is in the cache code path, not the query itself.

## Repro

Minimal config — caching enabled globally and on the entity:

```json
{
"runtime": {
"rest": { "enabled": true, "path": "/api" },
"cache": { "enabled": true, "ttl-seconds": 5 }
},
"entities": {
"Todo": {
"source": { "object": "dbo.Todos", "type": "table" },
"rest": { "enabled": true },
"cache": { "enabled": true, "ttl-seconds": 5 },
"permissions": [ { "role": "anonymous", "actions": [ { "action": "read" } ] } ]
}
}
}
```

Requests that produce an empty result set:

```
GET /api/Todo?$filter=Id eq -99999 # list read, zero matches
GET /api/Todo/Id/-99999 # by-PK read, no row
```

Both return **HTTP 500**. (An empty base table / a view that returns no rows reproduces identically.)

## Expected

`200` with an empty payload, matching the non-cached behavior:

```json
{ "value": [] }
```

## Actual

`500` with:

```json
{ "error": { "code": "UnexpectedError", "message": "While processing your request the server ran into an unexpected error.", "status": 500 } }
```

Server-side exception:

```text
System.InvalidOperationException: Operation is not valid due to the current state of the object.
at System.Text.Json.JsonElement.CheckValidInstance()
at System.Text.Json.JsonElement.WriteTo(Utf8JsonWriter writer)
at System.Text.Json.Serialization.Converters.JsonElementConverter.Write(...)
at System.Text.Json.Serialization.Converters.NullableConverter`1.Write(...)
...
at System.Text.Json.JsonSerializer.SerializeToUtf8Bytes[TValue](TValue value, JsonSerializerOptions options)
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ParseResultIntoJsonDocument(Nullable`1 result) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 446
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.GetResultInCacheScenario(RuntimeConfig runtimeConfig, SqlQueryStructure structure, String queryString, String dataSourceName, IQueryExecutor queryExecutor, EntityCacheLevel cacheEntryLevel) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 440
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(SqlQueryStructure structure, String dataSourceName, Boolean isMultipleCreateOperation) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 339
at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(FindRequestContext context) in /_/src/Core/Resolvers/SqlQueryEngine.cs:line 192
at Azure.DataApiBuilder.Core.Services.RestService.DispatchQuery(...) in /_/src/Core/Services/RestService.cs:line 238
...
at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(...) in /_/src/Service/Controllers/RestController.cs:line 278
```

## Root cause

The failure is in the **cache read path**: `SqlQueryEngine.GetResultInCacheScenario` → `SqlQueryEngine.ParseResultIntoJsonDocument(JsonElement? result)` at `src/Core/Resolvers/SqlQueryEngine.cs:440–446`.

When the DB returns no rows, the result value is a **default/`Undefined` `JsonElement?`** (no backing `JsonDocument`). `ParseResultIntoJsonDocument` then calls `JsonSerializer.SerializeToUtf8Bytes(...)` on it, which invokes `JsonElement.WriteTo` → `CheckValidInstance()`, and that throws `InvalidOperationException` because the `JsonElement` has no valid backing document.

The non-cached path does not hit this: it detects the empty result (`IQueryExecutor` logs `Did not return any rows in the JSON result.`) and returns an empty `value` collection. The cache scenario is missing that empty/null guard before serialization.

Notes from instrumentation (FusionCache debug logs):
- The exception fires on **both cache miss (first request)** and **cache hit**, because `GetResultInCacheScenario` is used whenever caching is enabled — the empty value is even serialized/stored and then fails again on read-back.
- Applies to both **list** (`FOR JSON PATH`) and **by-PK** (`FOR JSON PATH, WITHOUT_ARRAY_WRAPPER`) reads.

## Control (caching disabled)

Same two requests plus a populated control, cache off:

```text
GET /api/Todo?$filter=Id eq -99999 -> 200 {"value":[]}
GET /api/Todo/Id/-99999 -> 200 {"value":[]}
GET /api/Todo -> 200 {"value":[ ...rows... ]}
```

Server log for the empty cases: `Did not return any rows in the JSON result.` then `HTTP ... 200`. This isolates the defect to the caching path.

## Suggested fix direction

In `GetResultInCacheScenario` / `ParseResultIntoJsonDocument`, guard the null/empty (`JsonValueKind.Undefined`) case before `SerializeToUtf8Bytes`, returning the same empty result the non-cached path produces (empty array for list, no-row/`404`-style handling for by-PK). A regression test with caching enabled against a zero-row read (both list and PK) would cover this.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.