Azure / Azure/data-api-builder

[Bug]: groupBy doesn't work when an alias is defined for a field

Abierto
#3,672 0 comentarios 1 reacción 1 asignado Reclamado por @souvikghosh04 Ver en GitHub
bug cri mcp-server triage
Lenguaje dominante
C#
Estrellas
1.5k
Forks
370
Merge medio
3 d 22 h
PR fusionados (30 d)
9

Descripción

### What happened?

Given this table in a SQL Server database:

```
CREATE TABLE dbo.Products
(
id INT NOT NULL PRIMARY KEY,
name NVARCHAR(1000) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
category NVARCHAR(1000) NOT NULL
)

insert into dbo.Products(id, name, price, category) values(1, 'Milk', 1.99, 'milk');
insert into dbo.Products(id, name, price, category) values(2, 'Apple', 2.39, 'fruit');
insert into dbo.Products(id, name, price, category) values(3, 'Kiwi', 2.99, 'fruit');
insert into dbo.Products(id, name, price, category) values(4, 'Onions', 1.29, 'vegetable');
```

And this entities definition in dab-config.json, first without an alias:

```
"entities": {
"Products": {
"source": {
"object": "dbo.Products",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "Products",
"plural": "Products"
}
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}
```

If i run this query:

```graphql
query Products {
products {
groupBy(fields: [category]) {
fields {
category
}
aggregations {
sum(field: price)
}
}
}
}
```

I get this result, the sum of prices grouped by category, as expected:

```json
{
"data": {
"products": {
"groupBy": [
{
"fields": {
"category": "fruit"
},
"aggregations": {
"sum": 5.38
}
},
{
"fields": {
"category": "milk"
},
"aggregations": {
"sum": 1.99
}
},
{
"fields": {
"category": "vegetable"
},
"aggregations": {
"sum": 1.29
}
}
]
}
}
}
```

Everything good so far. But now i want to add an alias for the `category` column to `cat`. Therefore i add a `fields` definition like this:

```json
"entities": {
"Products": {
"source": {
"object": "dbo.Products",
"type": "table"
},
"graphql": {
"enabled": true,
"type": {
"singular": "Products",
"plural": "Products"
}
},
"fields": [
{
"name": "category",
"alias": "cat"
}
],
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}
```

When i now try to run the modified query:

```graphql
query Products {
products {
groupBy(fields: [cat]) {
fields {
cat
}
aggregations {
sum(field: price)
}
}
}
}
```

... I get this error:

```json
{
"errors": [
{
"message": "Invalid column name 'cat'.",
"path": [
"products"
],
"extensions": {
"code": "DatabaseOperationFailed"
}
}
],
"data": null
}
```

I also doesn't work if i use the original column name `category`.
I tested with CLI tool (dab) and docker containers with version 1.7.93 and 2.0.8. Same error every time.

### Version

1.7.93, 2.0.8

### What database are you using?

Azure SQL

### What hosting model are you using?

Container Apps

### Which API approach are you accessing DAB through?

GraphQL

### Relevant log output

```Text
dab-1 | dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
dab-1 | 40228e5f-f4f8-42aa-8e68-afbe9a5ba9fe Executing query: SELECT TOP 100 [table0].[cat] AS [category] ,sum([table0].[price]) AS [sum] FROM [dbo].[Products] AS [table0] WHERE 1 = 1 GROUP BY [table0].[category] FOR JSON PATH, INCLUDE_NULL_VALUES
dab-1 | fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
dab-1 | 40228e5f-f4f8-42aa-8e68-afbe9a5ba9fe Query execution error due to:
dab-1 | Invalid column name 'cat'.
dab-1 | Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'cat'.
dab-1 | at Microsoft.Data.SqlClient.SqlCommand.<>c.b__195_0(Task`1 result)
dab-1 | at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
dab-1 | at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
dab-1 | --- End of stack trace from previous location ---
dab-1 | at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
dab-1 | at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
dab-1 | --- End of stack trace from previous location ---
dab-1 | 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 /_/src/Core/Resolvers/QueryExecutor.cs:line 296
```

### Code of Conduct

- [x] I agree to follow this project's Code of Conduct

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.