[Bug]: aggregate_records (MCP) generates an invalid default ORDER BY SUM(<raw column>) against the grouped derived table on dwsql (Microsoft Fabric Warehouse) → Invalid column name

What happened?

Summary

When calling the MCP aggregate_records tool with a numeric aggregate (sum/avg/min/max) plus groupby against a dwsql data source (Microsoft Fabric Warehouse), DAB emits a query whose outer default ORDER BY re-applies the aggregate over the pre-aggregation raw column (e.g. ORDER BY SUM([alias].[amount]) DESC). The outer query's FROM is the grouped derived table, which only exposes the group-by columns and the aliased aggregate ([sum_amount]), not the raw column [amount]. SQL Server therefore fails name resolution with:

Msg 207, Level 16, State 1: Invalid column name 'amount'.

The inner grouped subquery is correct; only the auto-generated outer ORDER BY is wrong. As a result, sum/avg aggregations via MCP are unusable on Fabric Warehouse.

Environment

• Data API builder: 2.0.9 (Microsoft.DataApiBuilder 2.0.9) — also reproduced on 2.0.8
• Data source database-type: dwsql
• Backend: Microsoft Fabric Warehouse (SQL analytics endpoint, *.datawarehouse.fabric.microsoft.com)
• Surface: MCP SQL Server (runtime.mcp), tool aggregate_records
• Entity: a view (dbo.my_view) with key-fields defined
• OS: Windows 11

Configuration (relevant excerpt)

All object names, column names, and connection-string names below are synthetic and minimized for reproduction. The permission setting is also a minimal sample for reproduction only.

{
  "data-source": {
    "database-type": "dwsql",
    "connection-string": "@env('my-connection-string')",
    "options": { "set-session-context": false }
  },
  "runtime": {
    "mcp": {
      "enabled": true,
      "path": "/mcp",
      "dml-tools": {
        "describe-entities": true,
        "read-records": true,
        "aggregate-records": true
      }
    }
  },
  "entities": {
    "my_view": {
      "source": { "object": "dbo.my_view", "type": "view", "key-fields": ["id"] },
      "permissions": [ { "role": "anonymous", "actions": [ { "action": "read" } ] } ]
    }
  }
}

Repro steps

1. Point DAB (database-type: dwsql) at a Fabric Warehouse and expose a view with a numeric column (here amount) and a string column (category).
2. Start DAB: dab start --LogLevel Debug.
3. Call the MCP aggregate_records tool:

{
  "method": "tools/call",
  "params": {
    "name": "aggregate_records",
    "arguments": {
      "entity": "my_view",
      "function": "sum",
      "field": "amount",
      "groupby": ["category"]
    }
  }
}

4. The call fails with InternalServerError: Invalid column name 'amount'.

Note: passing an explicit orderby (e.g. ["category asc"]) does not change the generated SQL — the broken default ORDER BY SUM(<raw column>) DESC is still emitted, so there is no client-side workaround.

Actual generated SQL (from --LogLevel Debug)

SELECT COALESCE('['+STRING_AGG('{'+N'"category":' + ISNULL('"'+STRING_ESCAPE([category],'json')+'"','null')+', '+N'"sum_amount":' + ISNULL(STRING_ESCAPE(CONVERT(NVARCHAR(MAX), [sum_amount]),'json'),'null')+'}',', ')+']','[]')
FROM (
    SELECT TOP 101
        [dbo_my_view].[category] AS [category],
        sum([dbo_my_view].[amount]) AS [sum_amount]
    FROM [dbo].[my_view] AS [dbo_my_view]
    WHERE 1 = 1
    GROUP BY [dbo_my_view].[category]
) AS [dbo_my_view]
ORDER BY SUM([dbo_my_view].[amount]) DESC   -- <-- invalid: [amount] is not a column of the derived table

The derived table aliased [dbo_my_view] only exposes [category] and [sum_amount]. The outer ORDER BY SUM([dbo_my_view].[amount]) references the raw, pre-aggregation column [amount], which does not exist at that scope → Invalid column name 'amount'.

Expected behavior

The default ordering should reference the projected aggregate alias, not re-aggregate the raw column in the outer scope:

) AS [dbo_my_view]
ORDER BY [sum_amount] DESC

(Equivalently, DAB should either order by the aliased aggregation output or omit the default ORDER BY when the client did not request one.)

Additional context / scoping

• read_records on the same entity and the same column (SELECT amount ...) works correctly — the column and permissions are fine.
• aggregate_records with function: "count", field: "*" works (it does not reference a raw measure column).
• Only the numeric aggregates (sum/avg/min/max) with groupby fail, and the failure is entirely in the auto-generated outer ORDER BY.
• Running the intended query by hand against the Warehouse (grouped subquery + ORDER BY [sum_amount]) succeeds, confirming the issue is the generated SQL, not the data/schema/permissions.
• This looks related to the dwsql query builder's default order-by generation for aggregations (cf. previous fix "failing MCP aggregate_records tool caused by groupby default value", #3294).

Error detail

fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
      Query execution error due to:
Invalid column name 'amount'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'amount'.
   at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](...) in /_/src/Core/Resolvers/QueryExecutor.cs:line 296
Error Number: 207, State: 1, Class: 16

Version

2.0.9

What database are you using?

Azure SQL （dwsql（Microsoft Fabric Warehouse））

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

MCP

Code of Conduct

• I agree to follow this project's Code of Conduct