UPDATE [dbo].[todos] WITH(UPDLOCK) SET [dbo].[todos].[title] = @param2, [dbo].[todos].[completed] = @param3, [dbo].[todos].[owner_id] = @param4, [dbo].[todos].[custom] = @param5, [dbo].[todos].[created_on] = @param6, [dbo].[todos].[position] = @param7, [dbo].[todos].[created_on_date] = @param8 OUTPUT Inserted.[id] AS [tid], Inserted.[title] AS [title], Inserted.[completed] AS [isDone], Inserted.[custom] AS [custom], Inserted.[owner_id] AS [visibility], Inserted.[created_on] AS [created_on], Inserted.[position] AS [position], Inserted.[created_on_date] AS [created_on_date] WHERE [dbo].[todos].[id] = @param1 AND ([owner_id] = @param0)
IF @@ROWCOUNT = 0 BEGIN;
INSERT INTO [dbo].[todos] ([id], [title], [completed], [owner_id]) OUTPUT Inserted.[id] AS [tid], Inserted.[title] AS [title], Inserted.[completed] AS [isDone], Inserted.[custom] AS [custom], Inserted.[owner_id] AS [visibility], Inserted.[created_on] AS [created_on], Inserted.[position] AS [position], Inserted.[created_on_date] AS [created_on_date] VALUES (@param1, @param2, @param3, @param4)END
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
Request starting HTTP/1.1 PUT https://localhost:5001/api/todo/tid/3a67cc45-1234-4e66-9fa2-549c37d56eab application/json 80
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
Executing endpoint 'Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service)'
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
Route matched with {action = "Upsert", controller = "Rest"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.IActionResult] Upsert(System.String) on controller Azure.DataApiBuilder.Service.Controllers.RestController (Azure.DataApiBuilder.Service).
dbug: Azure.DataApiBuilder.Service.AuthenticationHelpers.ClientRoleHeaderAuthenticationMiddleware[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: Request authentication state: Anonymous.
dbug: Azure.DataApiBuilder.Service.AuthenticationHelpers.ClientRoleHeaderAuthenticationMiddleware[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: The request will be executed in the context of Anonymous role
dbug: Azure.DataApiBuilder.Service.Resolvers.IQueryExecutor[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: Executing query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION; UPDATE [dbo].[todos] WITH(UPDLOCK) SET [dbo].[todos].[title] = @param2, [dbo].[todos].[completed] = @param3, [dbo].[todos].[owner_id] = @param4, [dbo].[todos].[custom] = @param5, [dbo].[todos].[created_on] = @param6, [dbo].[todos].[position] = @param7, [dbo].[todos].[created_on_date] = @param8 OUTPUT Inserted.[id] AS [tid], Inserted.[title] AS [title], Inserted.[completed] AS [isDone], Inserted.[custom] AS [custom], Inserted.[owner_id] AS [visibility], Inserted.[created_on] AS [created_on], Inserted.[position] AS [position], Inserted.[created_on_date] AS [created_on_date] WHERE [dbo].[todos].[id] = @param1 AND ([owner_id] = @param0) IF @@ROWCOUNT = 0 BEGIN; INSERT INTO [dbo].[todos] ([id], [title], [completed], [owner_id]) OUTPUT Inserted.[id] AS [tid], Inserted.[title] AS [title], Inserted.[completed] AS [isDone], Inserted.[custom] AS [custom], Inserted.[owner_id] AS [visibility], Inserted.[created_on] AS [created_on], Inserted.[position] AS [position], Inserted.[created_on_date] AS [created_on_date] VALUES (@param1, @param2, @param3, @param4) END; COMMIT TRANSACTION
fail: Azure.DataApiBuilder.Service.Resolvers.IQueryExecutor[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: The column "created_on_date" cannot be modified because it is either a computed column or is the result of a UNION operator.
fail: Azure.DataApiBuilder.Service.Resolvers.IQueryExecutor[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_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.Service.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args)
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: The column "created_on_date" cannot be modified because it is either a computed column or is the result of a UNION operator.
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
d40453b8-c3e5-4c30-ac2f-1c101588330e: at Azure.DataApiBuilder.Service.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](TConnection conn, String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args)
at Azure.DataApiBuilder.Service.Resolvers.QueryExecutor`1.<>c__DisplayClass19_0`1.<<ExecuteQueryAsync>b__0>d.MoveNext()
--- 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.Service.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](String sqltext, IDictionary`2 parameters, Func`3 dataReaderHandler, HttpContext httpContext, List`1 args)
at Azure.DataApiBuilder.Service.Resolvers.SqlMutationEngine.PerformUpsertOperation(IDictionary`2 parameters, RestRequestContext context)
at Azure.DataApiBuilder.Service.Resolvers.SqlMutationEngine.ExecuteAsync(RestRequestContext context)
at Azure.DataApiBuilder.Service.Services.RestService.ExecuteAsync(String entityName, Operation operationType, String primaryKeyRoute)
at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, Operation operationType)
info: Microsoft.AspNetCore.Mvc.Infrastructure.SystemTextJsonResultExecutor[1]
Executing JsonResult, writing value of type '<>f__AnonymousType0`1[[<>f__AnonymousType1`3[[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=6.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], Azure.DataApiBuilder.Service, Version=0.6.13.0, Culture=neutral, PublicKeyToken=null]]'.
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
Executed action Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service) in 22.5888ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
Executed endpoint 'Azure.DataApiBuilder.Service.Controllers.RestController.Upsert (Azure.DataApiBuilder.Service)'
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
Request finished HTTP/1.1 PUT https://localhost:5001/api/todo/tid/3a67cc45-1234-4e66-9fa2-549c37d56eab application/json 80 - 500 - application/json;+charset=utf-8 23.3078ms
What happened?
If there is a calculated column in a table, for example:
then when doing an UPSERT the operation will fail with the following error:
{ "error": { "code": "DatabaseOperationFailed", "message": "The column \u0022created_on_date\u0022 cannot be modified because it is either a computed column or is the result of a UNION operator.", "status": 500 } }the generated SQL, in fact, should not do any operation on the calculated colum, but instead it tries to update/insert it:
Version
0.6.13
What database are you using?
Azure SQL
What hosting model are you using?
Local (including CLI)
Which API approach are you accessing DAB through?
REST
Relevant log output
Code of Conduct