Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties #34722

glenn2223 · 2024-09-20T10:22:01Z

Edit

Comment #34722 (comment) better reflects the updated subject of this issue. The original report is detailed below (for reference)

Original Issue Report

ℹ️ Somewhat related to #33826, but without the concurrent insert

As far as I'm aware, I've not done anything wrong. This isn't the end of the world either, as I've stopped using QuerySplittingBehavior.SplitQuery while building the options. As I know splitting isn't default, this won't be high on the scale of fixes - just wanted to raise it.

File a bug

I have a query that has .Skip(..), .Take(..) and .OrderBy(..) (definitely has the ordering, before you ask 😁). If I split the query, globally or with .AsSplitQuery(), some results are dropped leaving a sub-collection in the record empty. I know they're being dropped because the missing lines are there if I run the generated query directly against the DB.

Include your code

Here's a simplified piece of code for the query I'm running. The GetPredicate() call builds and stores a built predicate for repeat calls - just in case it's important/relevant, I make the predicate/expression using TopMarksDevelopment.ExpressionBuilder (nuget, github - only a mild self-plug 😂)

_siteContext
    .Products.AsSplitQuery()
    .Where(
        x => x.Baskets.AsQueryable().Any(GetPredicate())
    )
    .OrderByDescending(x =>
        x.Baskets.AsQueryable().Where(GetPredicate()).Sum(x => x.Quantity * x.SalePrice)
    )
    .Skip((int)Math.Max(PageSize, 5) * ((int)Math.Max(Pg, 1) - 1)) // Equates to 0
    .Take((int)Math.Max(PageSize, 5) + 1) // Equates to 51
    .Select(x => new SaleFields(x.Name, x.StockFigure, x.StockFigure * x.Price)
        {
            Id = x.Id,
            SectionId = x.CategoryId,
            SectionName = x.Category == null ? null : x.Category.WorkingName,
            Summaries = x
                .Baskets.AsQueryable()
                .Where(GetPredicate())
                .Select(b => new BasketSummary(
                    b.Quantity,
                    b.SalePrice
                ))
                .AsEnumerable(),
        }
    )
    .ToListAsync()

Include stack traces

N/A. It's not an exception that's thrown, just missing results

Include verbose output

First, sorry it's a mess

I took the console output, commented out irrelevant areas and added the declarations so it could be run in SQL SMS (if required)

I also wanted to mention; the second part of the split query returns 99 lines and the single query also returns 99 lines - so there's no disparity there

Split Query

/*
 * First Part (RETURNS 51 LINES)
 */
 -- Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (774ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
DECLARE @__p_0 INT ='0', @__p_1 INT ='51'; -- NOTE: Equivalent to the line above

SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice], [p].[pID]
FROM [products] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [cart] AS [c]
    LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
    WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
ORDER BY (
    SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
    FROM [cart] AS [c0]
    LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
    WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
/*info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (774ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice], [p].[pID]
      FROM [products] AS [p]
      WHERE EXISTS (
          SELECT 1
          FROM [cart] AS [c]
          LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
          WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
      ORDER BY (
          SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
          FROM [cart] AS [c0]
          LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
          WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY*/


/*
 * Second Part (RETURNS 99 LINES)
 */
/*Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (766ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']*/
-- NOTE: @__p_0, @__p_1 are the same so I don't re-set them
SELECT [t0].[cartQuantity], [t0].[cartProdPrice], [t].[pID]
FROM (
    SELECT [p].[pID], (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c]
    FROM [products] AS [p]
    WHERE EXISTS (
        SELECT 1
        FROM [cart] AS [c]
        LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
        WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
    ORDER BY (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
INNER JOIN (
    SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartProdID]
    FROM [cart] AS [c1]
    LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
    WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
) AS [t0] ON [t].[pID] = [t0].[cartProdID]
ORDER BY [t].[c] DESC, [t].[pID]
/*info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (766ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
      SELECT [t0].[cartQuantity], [t0].[cartProdPrice], [t].[pID]
      FROM (
          SELECT [p].[pID], (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c]
          FROM [products] AS [p]
          WHERE EXISTS (
              SELECT 1
              FROM [cart] AS [c]
              LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
              WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
          ORDER BY (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC
          OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
      ) AS [t]
      INNER JOIN (
          SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartProdID]
          FROM [cart] AS [c1]
          LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
          WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
      ) AS [t0] ON [t].[pID] = [t0].[cartProdID]
      ORDER BY [t].[c] DESC, [t].[pID]
*/

Single Query

/*
 * Only Part (RETURNS 99 LINES)
 */
-- Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1,247ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
DECLARE @__p_0 INT ='0', @__p_1 INT ='51'; -- NOTE: Equivalent to the line above

SELECT [t].[pName], [t].[pInStock], [t].[c], [t].[pID], [t0].[cartQuantity], [t0].[cartProdPrice], [t0].[cartID], [t0].[ordID]
FROM (
    SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice] AS [c], [p].[pID], (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c0]
    FROM [products] AS [p]
    WHERE EXISTS (
        SELECT 1
        FROM [cart] AS [c]
        LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
        WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
    ORDER BY (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
LEFT JOIN (
    SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartID], [o1].[ordID], [c1].[cartProdID]
    FROM [cart] AS [c1]
    LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
    WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
) AS [t0] ON [t].[pID] = [t0].[cartProdID]
ORDER BY [t].[c0] DESC, [t].[pID], [t0].[cartID]
/*info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1,247ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
      SELECT [t].[pName], [t].[pInStock], [t].[c], [t].[pID], [t0].[cartQuantity], [t0].[cartProdPrice], [t0].[cartID], [t0].[ordID]
      FROM (
          SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice] AS [c], [p].[pID], (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c0]
          FROM [products] AS [p]
          WHERE EXISTS (
              SELECT 1
              FROM [cart] AS [c]
              LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
              WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
          ORDER BY (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC
          OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
      ) AS [t]
      LEFT JOIN (
          SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartID], [o1].[ordID], [c1].[cartProdID]
          FROM [cart] AS [c1]
          LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
          WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
      ) AS [t0] ON [t].[pID] = [t0].[cartProdID]
      ORDER BY [t].[c0] DESC, [t].[pID], [t0].[cartID]*/

Include provider and version information

EF Core version: 8.0.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows 11 && Windows Server 2019
IDE: VS Code

I hope this is all helpful and if you need anything else, let me know

The text was updated successfully, but these errors were encountered:

roji · 2024-09-20T12:30:56Z

definitely has the ordering, before you ask

Note that just having an ordering isn't sufficient - the ordering has to be fully deterministic. The above query contains:

OrderByDescending(x => x.Baskets.AsQueryable().Where(GetPredicate()).Sum(x => x.Quantity * x.SalePrice))

So two rows could happen to have the same Quantity*SalePrice value, at which point the ordering is undefined and can vary. Try adding ThenBy() with the ID - that should make the ordering fully deterministic.

Beyond that, is the row dropping occuring while concurrent updates are happening to the table? If so, then that's probably expected - you may need to use a higher transaction isolation level to ensure consistent results across the split queries.

Otherwise, if you can reproduce this without any concurrent updates, then I'll need a minimal, runnable repro in order to investigate further.

glenn2223 · 2024-09-20T13:27:20Z

Note that just having an ordering isn't sufficient - the ordering has to be fully deterministic...

Damn, I should have known there'd be more to it

So two rows could happen to have the same Quantity*SalePrice value, at which point the ordering is undefined and can vary. Try adding ThenBy() with the ID - that should make the ordering fully deterministic.

Adding the Id filter to the code returns the missing row... BUT WHY!? Looking at all the other ordering that EF Core does, it always adds the deterministic id automatically - but, in this one subquery it doesn't.

Is this something that could be introduced easily? If so, is there a good place you can point me to? Maybe I can help, as I already having a little experience with expression-building (plug package again!! 😂)

SQL Changes

Diff comparison: SQL - Diffchecker.pdf

/*
 * First Part (RETURNS 51 LINES)
 */
 --Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (305ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
DECLARE @__p_0 INT ='0', @__p_1 INT ='51'; -- NOTE: Equivalent to the line above

SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice], [p].[pID]
FROM [products] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [cart] AS [c]
    LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
    WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
ORDER BY (
    SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
    FROM [cart] AS [c0]
    LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
    WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
/*info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (305ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[pName], [p].[pInStock], CAST([p].[pInStock] AS float) * [p].[pPrice], [p].[pID]
      FROM [products] AS [p]
      WHERE EXISTS (
          SELECT 1
          FROM [cart] AS [c]
          LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
          WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
      ORDER BY (
          SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
          FROM [cart] AS [c0]
          LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
          WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY*/


/*
 * Second Part (RETURNS 99 LINES)
 */
-- Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (582ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
-- NOTE: @__p_0, @__p_1 are the same so I don't re-set them
SELECT [t0].[cartQuantity], [t0].[cartProdPrice], [t].[pID]
FROM (
    SELECT [p].[pID], (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c]
    FROM [products] AS [p]
    WHERE EXISTS (
        SELECT 1
        FROM [cart] AS [c]
        LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
        WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
    ORDER BY (
        SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
        FROM [cart] AS [c0]
        LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
        WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
INNER JOIN (
    SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartProdID]
    FROM [cart] AS [c1]
    LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
    WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
) AS [t0] ON [t].[pID] = [t0].[cartProdID]
ORDER BY [t].[c] DESC, [t].[pID]
/*info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (582ms) [Parameters=[@__p_0='0', @__p_1='51'], CommandType='Text', CommandTimeout='30']
      SELECT [t0].[cartQuantity], [t0].[cartProdPrice], [t].[pID]
      FROM (
          SELECT [p].[pID], (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') AS [c]
          FROM [products] AS [p]
          WHERE EXISTS (
              SELECT 1
              FROM [cart] AS [c]
              LEFT JOIN [orders] AS [o] ON [c].[cartOrderID] = [o].[ordID]
              WHERE [p].[pID] = [c].[cartProdID] AND [c].[cartQuantity] > 0 AND ([c].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c].[cartMerchID]))) = N'' OR [c].[cartMerchID] = N'\\\TERM///') AND [o].[ordID] IS NOT NULL AND [o].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o].[ordID] IS NOT NULL AND CONVERT(date, [o].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o].[ordDate]) <= '2023-09-17T00:00:00.000')
          ORDER BY (
              SELECT COALESCE(SUM(CAST([c0].[cartQuantity] AS float) * [c0].[cartProdPrice]), 0.0E0)
              FROM [cart] AS [c0]
              LEFT JOIN [orders] AS [o0] ON [c0].[cartOrderID] = [o0].[ordID]
              WHERE [p].[pID] = [c0].[cartProdID] AND [c0].[cartQuantity] > 0 AND ([c0].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c0].[cartMerchID]))) = N'' OR [c0].[cartMerchID] = N'\\\TERM///') AND [o0].[ordID] IS NOT NULL AND [o0].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c0].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o0].[ordID] IS NOT NULL AND CONVERT(date, [o0].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o0].[ordDate]) <= '2023-09-17T00:00:00.000') DESC, [p].[pID]
          OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
      ) AS [t]
      INNER JOIN (
          SELECT [c1].[cartQuantity], [c1].[cartProdPrice], [c1].[cartProdID]
          FROM [cart] AS [c1]
          LEFT JOIN [orders] AS [o1] ON [c1].[cartOrderID] = [o1].[ordID]
          WHERE [c1].[cartQuantity] > 0 AND ([c1].[cartMerchID] IS NULL OR LOWER(LTRIM(RTRIM([c1].[cartMerchID]))) = N'' OR [c1].[cartMerchID] = N'\\\TERM///') AND [o1].[ordID] IS NOT NULL AND [o1].[ordStatus] > CAST(2 AS smallint) AND LOWER(LTRIM(RTRIM([c1].[cartProdName]))) LIKE N'%\\\TERM///%' AND 1 = 1 AND [o1].[ordID] IS NOT NULL AND CONVERT(date, [o1].[ordDate]) >= '2023-09-01T00:00:00.000' AND CONVERT(date, [o1].[ordDate]) <= '2023-09-17T00:00:00.000'
      ) AS [t0] ON [t].[pID] = [t0].[cartProdID]
      ORDER BY [t].[c] DESC, [t].[pID]*/

ascott18 · 2024-09-25T15:47:18Z

Is this not the same as #26808?

roji · 2024-09-25T22:25:28Z

Duplicate of #26808

roji · 2024-09-25T22:25:48Z

Good catch @ascott18, thanks... Closing as duplicate.

glenn2223 added the customer-reported label Sep 20, 2024

dario-l mentioned this issue Sep 20, 2024

UseTransaction() operator to automatically execute a query in a specific isolation level (e.g. split query) #34724

Open

roji changed the title ~~Split query drops rows returned by the DB query~~ Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties Sep 20, 2024

roji added type-enhancement area-query labels Sep 20, 2024

roji added this to the Backlog milestone Sep 20, 2024

This comment was marked as off-topic.

Sign in to view

roji marked this as a duplicate of #26808 Sep 25, 2024

roji closed this as not planned Won't fix, can't repro, duplicate, stale Sep 25, 2024

roji removed this from the Backlog milestone Sep 25, 2024

roji added the closed-duplicate label Sep 25, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties #34722

Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties #34722

glenn2223 commented Sep 20, 2024 •

edited

Loading

roji commented Sep 20, 2024

glenn2223 commented Sep 20, 2024

This comment was marked as off-topic.

This comment was marked as off-topic.

This comment was marked as off-topic.

This comment was marked as off-topic.

ascott18 commented Sep 25, 2024

roji commented Sep 25, 2024

roji commented Sep 25, 2024

Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties #34722

Consider ensuring that split query ordering is deterministic, either warning/throwing if not or injecting the primary key properties #34722

Comments

glenn2223 commented Sep 20, 2024 • edited Loading

Edit

Original Issue Report

File a bug

Include your code

Include stack traces

Include verbose output

Split Query

Single Query

Include provider and version information

roji commented Sep 20, 2024

glenn2223 commented Sep 20, 2024

This comment was marked as off-topic.

This comment was marked as off-topic.

This comment was marked as off-topic.

This comment was marked as off-topic.

ascott18 commented Sep 25, 2024

roji commented Sep 25, 2024

roji commented Sep 25, 2024

glenn2223 commented Sep 20, 2024 •

edited

Loading