[Bug]: MCP orderby takes incompatible shapes in read_records and aggregate_records, and the mismatch returns an opaque UnexpectedError

[WMOlsen]

What happened?

The two MCP DML tools accept orderby in mutually incompatible shapes. Passing one tool's shape to the other returns UnexpectedError with no indication of what was wrong, so the caller has no way to discover the correct form from the error.

aggregate_records expects a bare direction string. It sorts by the aggregated value.

argument	result
orderby: "desc"	OK, groups sorted by aggregated value descending
orderby: "asc"	OK
orderby: "COLUMN_NAME"	InvalidArguments: Argument 'orderby' must be either 'asc' or 'desc' when provided. Got: 'COLUMN_NAME'. — clear and actionable
orderby: ["desc"]	UnexpectedError: Unexpected error occurred in AggregateRecordsTool. — opaque

read_records expects an array of column specifications.

argument	result
orderby: ["COLUMN_NAME desc"]	OK
orderby: "COLUMN_NAME desc"	UnexpectedError: Unexpected error occurred in ReadRecordsTool. — opaque
orderby: "COLUMN_NAME"	same

So the shape that is correct in one tool is an opaque failure in the other, in both directions.

Why this matters more for MCP than for REST

The consumer is a language model reading describe_entities output and the tool input schemas. Having learned ["COLUMN desc"] from read_records, it will use that in aggregate_records and get a failure that names neither the parameter nor the expected form. There is no discovery path from the error back to the working call.

Asks

1. Return an actionable message for the shape mismatch in both tools, as aggregate_records already does for the column-name case. Naming the expected shape would be enough.
2. Consider accepting both shapes, or aligning them.

Two related observations

aggregate_records silently ignores orderby when groupby is absent, rather than rejecting it. A caller asking for sorted output gets unsorted output and a success response.

Grouped results are returned ordered by the aggregated value, and there is no way to order by the group key, because orderby accepts only a direction. For a groupby on a date or period column this means the caller cannot ask for chronological order and has to sort client-side. Given that filtering on a date-typed column is currently broken (#3760), groupby on the date is the only way to reach a single period, which makes the inability to order by that key more limiting than it would otherwise be.

Version

2.1.3-rc

What database are you using?

Azure SQL

What hosting model are you using?

Azure Container Apps

Which API approach are you accessing DAB through?

MCP

[aaronburtle]

Thanks for the detailed reproduction. We confirmed the error-handling problem.

The two tools currently use different ordering contracts because they sort different things:

read_records orders individual records by one or more fields: "orderby": ["COLUMN_NAME desc"].
aggregate_records orders groups by their computed aggregate value: "orderby": "desc" or "asc". Grouped results default to descending order.
Both contracts are advertised through tools/list, but you’re right that an incorrect shape should produce an actionable error rather than UnexpectedError.

Without groupby, aggregation returns a single aggregate result, so ordering is intentionally ignored. That behavior was introduced in #3294 to resolve #3279.

Ordering grouped results by their group keys, including chronological ordering, is not currently supported. That is a useful enhancement to consider separately from the validation fix; the date-filter problem remains separate under #3760.

I've prepared a fix that returns InvalidArguments identifying orderby, its expected shape, and an example. It also handles malformed array members while preserving the existing ordering contracts. You can track progress here: #3816