Azure / Azure/data-api-builder
[Bug]: groupBy doesn't work when an alias is defined for a field
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
### 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
Contributor guide
Assessment
This issue has not been assessed yet.