SQL error when using taskState in deployments API

execution

(mike) #1

Hi,

In version 2018.3.11, calling the “/api/deployments” endpoint with a taskState filter causes a HTTP500 and a SQL error to be returned in the response.

{
  "ErrorMessage": "Error while executing SQL command in transaction 'https://[redacted]/api/deployments?taskState=Success 80ec267cb5d34b0e936a1d08840c5368': The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\nIncorrect syntax near 'ALIAS_GENERATED_1'.\r\nThe command being executed was:\r\nSELECT COUNT(*)\r\nFROM (\r\n    SELECT *\r\n    FROM dbo.[Deployment]\r\n    ORDER BY [Created] DESC\r\n) ALIAS_GENERATED_2\r\nINNER JOIN (\r\n    SELECT *\r\n    FROM dbo.[ServerTask]\r\n    WHERE ([State] = @state)\r\n) ALIAS_GENERATED_1\r\nON ALIAS_GENERATED_2.[TaskId] = ALIAS_GENERATED_1.[Id]\r\nSQL Error 1033 - The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\nIncorrect syntax near 'ALIAS_GENERATED_1'.",
  "FullException": "Error while executing SQL command in transaction 'https://octopus.services.kingsway.asos.com/api/deployments?taskState=Success 80ec267cb5d34b0e936a1d08840c5368': The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\nIncorrect syntax near 'ALIAS_GENERATED_1'.\r\nThe command being executed was:\r\nSELECT COUNT(*)\r\nFROM (\r\n    SELECT *\r\n    FROM dbo.[Deployment]\r\n    ORDER BY [Created] DESC\r\n) ALIAS_GENERATED_2\r\nINNER JOIN (\r\n    SELECT *\r\n    FROM dbo.[ServerTask]\r\n    WHERE ([State] = @state)\r\n) ALIAS_GENERATED_1\r\nON ALIAS_GENERATED_2.[TaskId] = ALIAS_GENERATED_1.[Id]\r\nSystem.Exception\r\n   at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable`1 commandTimeoutSeconds)\r\n   at Nevermore.QueryBuilder`2.Count()\r\n   at Nevermore.QueryBuilder`2.ToList(Int32 skip, Int32 take, Int32& totalResults)\r\n   at Octopus.Server.Web.Api.Actions.DeploymentsQueryResponder.ExecuteRegistered()\r\n   at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context)\r\n   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)\r\n   at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x)\r\n   at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context)\r\n\r\n--Inner Exception--\r\nSQL Error 1033 - The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.\r\nIncorrect syntax near 'ALIAS_GENERATED_1'.\r\nSystem.Data.SqlClient.SqlException\r\n   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n   at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass11_0.<ExecuteScalarWithRetry>b__0()\r\n   at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)\r\n   at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable`1 commandTimeoutSeconds)"
}

For example:

  • /api/deployments?projects=projects-111&environments=Environments-2222&taskState=Success&take=1
  • /api/deployments?taskState=Success

Removing the “taskState=Success” from both urls gives a valid response instead of an error.

For convenience, the sql error in the above response is:

The ORDER BY clause is invalid in views, inline functions, derived tables,
subqueries, and common table expressions, unless TOP, OFFSET or
FOR XML is also specified.

Incorrect syntax near 'ALIAS_GENERATED_1'

SELECT COUNT(*)
FROM (
    SELECT *
    FROM dbo.[Deployment]
    ORDER BY [Created] DESC
) ALIAS_GENERATED_2
INNER JOIN (
    SELECT *
    FROM dbo.[ServerTask]
    WHERE ([State] = @state)
) ALIAS_GENERATED_1
ON ALIAS_GENERATED_2.[TaskId] = ALIAS_GENERATED_1.[Id]

Cheers,

Mike


(Tom Williams) #2

Hi Mike,

Thanks for reaching out. Sorry to hear you hit this issue with Octopus.
I have created a public issue for this item here. We have actually already fixed this issue and it will go out in one of our releases next week.

Thanks again for bringing this issue to our attention. Please let me know if there is anything else I can help with.

Kind Regards,
Tom W


(mike) #3

Thanks Tom.

I’ll keep an eye out for the release.

Cheers,

Mike


(system) #5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.