search

terrajobst / nquery

NQuery is a relational query engine written in C#. It allows you to execute a SELECT query against .NET objects.
MIT License
72 stars 20 forks source link

Group by in CTE causes ArgumentException #11

Open terrajobst opened 9 years ago

terrajobst commented 9 years ago

Ported from CodePlex

WITH D AS (
 SELECT e.EmployeeID,
   e.FirstName + ' ' + e.LastName AS FullName,
   TRIM(t.TerritoryDescription) AS Territory,
   TRIM(r.RegionDescription) AS Region
 FROM Employees e
    INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
    INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID
    INNER JOIN Region r ON r.RegionID = t.RegionID
), o AS (
 SELECT o.OrderID,
   o.OrderDate,
   o.EmployeeID,
   SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS BruttoPrice,
   SUM(od.Quantity * od.UnitPrice) AS NettoPrice
 FROM Orders o
    INNER JOIN [Order Details] od ON od.OrderID = o.OrderID
 GROUP BY o.OrderID, o.OrderDate, o.EmployeeID
)
SELECT D.FullName,
  D.Territory,
  D.Region,
  o.*
FROM D INNER JOIN o ON o.EmployeeID = D.EmployeeID

System.ArgumentException: IComparer (or the IComparable methods it relies upon) did not return zero when Array.Sort called x. CompareTo(x). x: 'System.Object[]'  x's type: 'Object[]' The IComparer: 'NQuery.Runtime.ExecutionPlan.SortIterator+RowComparer'.    at System.Collections.Generic.ArraySortHelper1.SwapIfGreaterWithItems[TValue](T[] keys, TValue[] values, IComparer1 comparer, Int32 a, Int32 b)    at System.Collections.Generic.ArraySortHelper1.QuickSort[TValue](T[] keys, TValue[] values, Int32 left, Int32 right, IComparer1 comparer)    at System.Collections.Generic.ArraySortHelper1.Sort[TValue](T[] keys, TValue[] values, Int32 index, Int32 length, IComparer1 comparer)    at System.Collections.Generic.ArraySortHelper1.Sort(T[] items, Int32 index, Int32 length, IComparer1 comparer)    at System.Array.Sort[T](T[] array, Int32 index, Int32 length, IComparer1 comparer)    at System.Collections.Generic.List1.Sort(Int32 index, Int32 count, IComparer1 comparer)    at System.Collections.Generic.List1.Sort(IComparer`1 comparer)    at NQuery.Runtime.ExecutionPlan.SortIterator.SortInput() in N:\Trunk\Src\NQuery\Execution Plan\SortIterator.cs:line 100    at NQuery.Runtime.ExecutionPlan.SortIterator.Read() in N:\Trunk\Src\NQuery\Execution Plan\SortIterator.cs:line 113    at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Read() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 25    at NQuery.Runtime.ExecutionPlan.StreamAggregateIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StreamAggregateIterator.cs:line 128    at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 20    at NQuery.Runtime.ExecutionPlan.HashMatchIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\HashMatchIterator.cs:line 41    at NQuery.Runtime.ExecutionPlan.StatisticsIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\StatisticsIterator.cs:line 20    at NQuery.Runtime.ExecutionPlan.ResultIterator.Open() in N:\Trunk\Src\NQuery\Execution Plan\ResultIterator.cs:line 13    at NQuery.Query.ExecuteDataTable() in N:\Trunk\Src\NQuery\API\Query.cs:line 132    at NQuery.Gui.QueryDocument.Execute() in N:\Trunk\Src\NQuery.Gui\QueryDocument.cs:line 196    at NQuery.Gui.QueryDocument.NQuery.Gui.IQueryHandler.Execute() in N:\Trunk\Src\NQuery.Gui\QueryDocument.cs:line 135    at NQuery.Gui.MainForm.executeToolStripMenuItem_Click(Object sender, EventArgs e) in N:\Trunk\Src\NQuery.Gui\MainForm.cs:line 282    at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)    at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)    at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)    at System.Windows.Forms.ToolStripMenuItem.ProcessCmdKey(Message& m, Keys keyData)    at System.Windows.Forms.ToolStripManager.ProcessShortcut(Message& m, Keys shortcut)    at System.Windows.Forms.Form.ProcessCmdKey(Message& msg, Keys keyData)    at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)    at TD.SandDock.DocumentContainer.ProcessCmdKey(Message& msg, Keys keyData)    at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)    at TD.SandDock.DockControl.ProcessCmdKey(Message& msg, Keys keyData)    at System.Windows.Forms.ContainerControl.ProcessCmdKey(Message& msg, Keys keyData)    at System.Windows.Forms.Control.PreProcessMessage(Message& msg)    at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)    at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)

dallmair commented 7 years ago

Works fine in v.Next (result also looks good to me).