[Bug]: GraphQL Query failure when mappings exist for relationship fields

[severussundar]

What happened?

When there exists mapping for relationship fields, GraphQL query fails. This happens for all relationship types.

Config File

Entity : Book

"mappings": {
        "id": "identifier"
  },

Authors relationship

"authors": {
          "cardinality": "many",
          "target.entity": "Author",
          "source.fields": [
            "identifier"
          ],
          "target.fields": [
            "id"
          ],
          "linking.object": "book_author_link",
          "linking.source.fields": [
            "book_id"
          ],
          "linking.target.fields": [
            "author_id"
          ]
        }

Review Relationship

        "reviews": {
          "cardinality": "many",
          "target.entity": "Review",
          "source.fields": ["identifier"],
          "target.fields": ["book_id"],
          "linking.source.fields": [],
          "linking.target.fields": []
        }

Query 1

{
  books {
    items {
      identifier
      title
      authors {
        items {
          id
          name
          birthdate
        }
      }
    }
  }
}

Query 2

{
  books{
    items{
      identifier
      title
      reviews{
        items{
          book_id
          id
          content
        }
      }
    }
  }

}

Version/Branch:

• Version: 0.12.*
• Branch: Current Main

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
      53542528-a234-4c70-8aaa-e5944b2926b5 Executing query: SELECT TOP 100 [table0].[id] AS [identifier], [table0].[title] AS [title], JSON_QUERY (COALESCE([table1_subq].[data], '[]')) AS [authors] FROM [dbo].[books] AS [table0] OUTER APPLY (SELECT TOP 100 [table1].[id] AS [id], [table1].[name] AS [name], [table1].[birthdate] AS [birthdate] FROM [dbo].[authors] AS [table1] INNER JOIN [dbo].[book_author_link] AS [table5] ON [table5].[author_id] = [table1].[id] WHERE [table5].[book_id] = [table0].[identifier] ORDER BY [table1].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES) AS [table1_subq]([data]) WHERE 1 = 1 ORDER BY [table0].[id] ASC FOR JSON PATH, INCLUDE_NULL_VALUES
fail: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
      53542528-a234-4c70-8aaa-e5944b2926b5 Query execution error due to:
Invalid column name 'identifier'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'identifier'.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         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 C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\QueryExecutor.cs:line 160
      ClientConnectionId:11ea1f53-24d3-49d8-8118-95cc5117affd
      Error Number:207,State:1,Class:16
fail: Azure.DataApiBuilder.Service.Startup[0]
      A GraphQL request execution error occurred.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Invalid column name 'identifier'.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'identifier'.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         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 C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\QueryExecutor.cs:line 160
      ClientConnectionId:11ea1f53-24d3-49d8-8118-95cc5117affd
      Error Number:207,State:1,Class:16
         --- End of inner exception stack trace ---
         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 C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\QueryExecutor.cs:line 178
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.<>c__DisplayClass19_0`1.<<ExecuteQueryAsync>b__0>d.MoveNext() in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\QueryExecutor.cs:line 102
      --- End of stack trace from previous location ---
         at Polly.Retry.AsyncRetryEngine.ImplementationAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Func`5 onRetryAsync, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider, Boolean continueOnCapturedContext)
         at Polly.AsyncPolicy.ExecuteAsync[TResult](Func`3 action, Context context, CancellationToken cancellationToken, Boolean continueOnCapturedContext)
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, String dataSourceName, HttpContext httpContext, List`1 args) in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\QueryExecutor.cs:line 90
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(SqlQueryStructure structure, String dataSourceName) in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\SqlQueryEngine.cs:line 291
         at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(IMiddlewareContext context, IDictionary`2 parameters, String dataSourceName) in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Resolvers\SqlQueryEngine.cs:line 81
         at Azure.DataApiBuilder.Service.Services.ExecutionHelper.ExecuteQueryAsync(IMiddlewareContext context) in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Services\ExecutionHelper.cs:line 79
         at ResolverTypeInterceptor.<>c__DisplayClass5_1.<<-ctor>b__5>d.MoveNext() in C:\Users\shyamsundarj\Codes\new_clone\data-api-builder\src\Core\Services\ResolverTypeInterceptor.cs:line 23
      --- End of stack trace from previous location ---
         at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)
         at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)

Code of Conduct

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