U4-6934 - v7.3.0-beta3 ListView sorting by updateDate throws exception

Created by Jeavon Leopold 07 Aug 2015, 14:45:24 Updated by Shannon Deminick 08 Oct 2015, 12:39:15

Relates to: U4-6310

In 7.3-beta3 we have a custom list view which orders by last edited, if you click the "last edited" in the list view to reverse the order it throws this exception

Received an error from the server Failed to retrieve children for content item 1152

Ambiguous column name 'updateDate'.

EXCEPTION DETAILS:

System.Data.SqlClient.SqlException: Ambiguous column name 'updateDate'. STACKTRACE:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 235 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at Umbraco.Core.Persistence.Database.d__71.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Umbraco.Core.Persistence.Database.Page[T](Int64 page, Int64 itemsPerPage, String sql, Object[] args) at Umbraco.Core.Persistence.Database.Page[T](Int64 page, Int64 itemsPerPage, Sql sql) at Umbraco.Core.Persistence.Repositories.VersionableRepositoryBase2.GetPagedResultsByQuery[TDto,TContentBase](IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, Tuple2 nodeIdSelect, Func2 processQuery, String orderBy, Direction orderDirection, Func1 defaultFilter) at Umbraco.Core.Persistence.Repositories.ContentRepository.GetPagedResultsByQuery(IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, String orderBy, Direction orderDirection, String filter) at Umbraco.Core.Services.ContentService.GetPagedChildren(Int32 id, Int64 pageIndex, Int32 pageSize, Int64& totalChildren, String orderBy, Direction orderDirection, String filter) at Umbraco.Web.Editors.ContentController.GetChildren(Int32 id, Int32 pageNumber, Int32 pageSize, String orderBy, Direction orderDirection, String filter) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Controllers.ApiControllerActionInvoker.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()

2 Attachments

Comments

Bjarne Fyrstenborg 10 Aug 2015, 08:49:55

I think this issue have been there for some time.


Jeavon Leopold 10 Aug 2015, 08:52:21

@Bjarne.Fyrstenborg not for me, this site is currently v7.2.8 and it works fine, upgrade to v7.3.0-beta3 and it breaks


Bjarne Fyrstenborg 10 Aug 2015, 10:45:27

@crumpled_jeavon I have just tested with a clean 7.2.8 install and I also see the issue there.

Steps to reproduce:

  • Upload some files or images to media section
  • Delete a couple of them
  • Go to recycle bin
  • Now if you sort by "last edited" date you should get the error: "Failed to retrieve children for media item -21".

But it doesn't seem to happen for recycle bin in content section nor the listview for e.g. Blog node in Fanoe starterkit.


Jeavon Leopold 10 Aug 2015, 10:48:06

@Bjarne.Fyrstenborg ok, so maybe it now happens for content also in v7.3....


Bjarne Fyrstenborg 10 Aug 2015, 11:46:56

@crumpled_jeavon yes, the issue for recycle bin still happens in 7.3.0 beta3 ... and now also for recycle bin listview in content and listview for e.g. Blog node in Fanoe starterkit, which just use the default listview config.

The errors:

  • Media -> Recycle Bin: Failed to retrieve children for content item -20
  • Content -> Recycle Bin: Failed to retrieve children for media item -21
  • Content -> Blog: Failed to retrieve children for content item 1075

1075 is the id for Blog node.


Sebastiaan Janssen 12 Aug 2015, 09:02:50

Error is as follows. I would change https://github.com/umbraco/Umbraco-CMS/blob/dev-v7/src/Umbraco.Core/Persistence/Repositories/ContentRepository.cs#L785 And prepend the orderBy with "umbracoNode." but I don't know the implications for doing that and ordering by things that might not be in umbraocNode. Need @Shandem to take a look.

at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 235 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at Umbraco.Core.Persistence.Database.d__651.MoveNext() in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\PetaPoco.cs:line 781 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Umbraco.Core.Persistence.Database.Fetch[T](String sql, Object[] args) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\PetaPoco.cs:line 619 at Umbraco.Core.Persistence.Database.Page[T](Int64 page, Int64 itemsPerPage, String sql, Object[] args) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\PetaPoco.cs:line 726 at Umbraco.Core.Persistence.Database.Page[T](Int64 page, Int64 itemsPerPage, Sql sql) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\PetaPoco.cs:line 734 at Umbraco.Core.Persistence.Repositories.VersionableRepositoryBase2.GetPagedResultsByQuery[TDto,TContentBase](IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, Tuple2 nodeIdSelect, Func2 processQuery, String orderBy, Direction orderDirection, Func1 defaultFilter) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\Repositories\VersionableRepositoryBase.cs:line 314 at Umbraco.Core.Persistence.Repositories.ContentRepository.GetPagedResultsByQuery(IQuery1 query, Int64 pageIndex, Int32 pageSize, Int64& totalRecords, String orderBy, Direction orderDirection, String filter) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Persistence\Repositories\ContentRepository.cs:line 783 at Umbraco.Core.Services.ContentService.GetPagedChildren(Int32 id, Int64 pageIndex, Int32 pageSize, Int64& totalChildren, String orderBy, Direction orderDirection, String filter) in D:\Dev\Umbraco-CMS\src\Umbraco.Core\Services\ContentService.cs:line 522 at Umbraco.Web.Editors.ContentController.GetChildren(Int32 id, Int32 pageNumber, Int32 pageSize, String orderBy, Direction orderDirection, String filter) in D:\Dev\Umbraco-CMS\src\Umbraco.Web\Editors\ContentController.cs:line 166 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()


Sebastiaan Janssen 12 Aug 2015, 09:14:57

As a temporary workaround (and PRO tip): Go into the listview, remove the last edited column to sort on, then in the default sort choose last edited and descending.. Tada! That one actually works.


Rik Helsen 08 Oct 2015, 12:36:52

is this actually fixed as it sais in the sidebar? i only see a (useful) workaround?


Shannon Deminick 08 Oct 2015, 12:39:08

Have you tested it?


Shannon Deminick 08 Oct 2015, 12:39:15

Yes, see revision: fc8b0f18226a5150a317bb9dbe9aae0a9f6cd90f


Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.3.0

Due in version: 7.3.0

Sprint:

Story Points:

Cycle: