ExecuteUpdate Produce Wrong Query when used with window function and join

[code-sujan]

Hello,
I am facing an issue while performing ExecuteUpdate When using with window function and join query.

Following are the sample entity

public class Product
{
    public long Id { get; set; }
    public string Name { get; set; }
    public long VatPercent { get; set; }
}

public class Unit
{
    public long Id { get; set; }
    public string Name { get; set; }
}

public class ProductUnitLink
{
    public long Id { get; set; }
    public virtual Product Product { get; set; }
    public long ProductId { get; set; }
    
    public virtual Unit Unit { get; set; }
    public long UnitId { get; set; }
}

public class ProductCp
{
    public long Id { get; set; }
    public DateTime Date { get; set; }
    public virtual Product Product { get; set; }
    public long ProductId { get; set; }
    public virtual Unit Unit { get; set; }
    public long UnitId { get; set; }
    
    public decimal Rate { get; set; }
}

public class PurchaseOrder
{
    public long Id { get; set; }
    public virtual Product Product { get; set; }
    public long ProductId { get; set; }
    public virtual Unit Unit { get; set; }
    public long UnitId { get; set; }
    public long Quantity { get; set; }
    public decimal LastCp { get; set; }
    public decimal LastCpWithoutVat { get; set; }
    public decimal NetAmount { get; set; }
}

Code To fetch LastCp Info using Window Function

 var lastCpQuery = from item in context.ProductUnitLinks
            from cp in context.ProductCps.Include(x => x.Product)
                .Where(x => x.ProductId == item.ProductId && x.UnitId == item.UnitId)
                .OrderByDescending(x => x.Date)
                .Take(1)
            select new LastCpDto
            {
                ProductId = item.ProductId,
                UnitId = item.UnitId,
                Rate = cp.Rate,
                RateWithoutVat = cp.Rate > 0 ? Math.Round((cp.Product.VatPercent > 0) ? (cp.Rate - (cp.Product.VatPercent/100 * cp.Rate)) : cp.Rate, 3) : 0
            };

Works fine while fetching the data producing correct SQL Query

var orderQueryWithJoin = from item in context.PurchaseOrders
            join cp in lastCpQuery on new { item.ProductId, item.UnitId } equals new { cp.ProductId, cp.UnitId }
            select new
            {
                item,
                Rate = cp.Rate,
                RateWithoutVat = cp.RateWithoutVat
            };
        var list = orderQueryWithJoin.ToList(); // This works fetching rate info

SELECT p."Id", p."LastCp", p."LastCpWithoutVat", p."NetAmount", p."ProductId", p."Quantity", p."UnitId", t1."Rate", CASE
          WHEN t1."Rate" > 0.0 THEN round(CASE
              WHEN p2."VatPercent" > 0 THEN t1."Rate" - (CAST((p2."VatPercent" / 100) AS numeric) * t1."Rate")
              ELSE t1."Rate"
          END, 3)
          ELSE 0.0
      END AS "RateWithoutVat"
      FROM "PurchaseOrders" AS p
      INNER JOIN (
          SELECT p0."ProductId", p0."UnitId", t0."ProductId" AS "ProductId0", t0."Rate"
          FROM "ProductUnitLinks" AS p0
          INNER JOIN (
              SELECT t."ProductId", t."Rate", t."UnitId"
              FROM (
                  SELECT p1."ProductId", p1."Rate", p1."UnitId", ROW_NUMBER() OVER(PARTITION BY p1."ProductId", p1."UnitId" ORDER BY p1."Date" DESC) AS row
                  FROM "ProductCps" AS p1
              ) AS t
              WHERE t.row <= 1
          ) AS t0 ON p0."ProductId" = t0."ProductId" AND p0."UnitId" = t0."UnitId"
      ) AS t1 ON p."ProductId" = t1."ProductId" AND p."UnitId" = t1."UnitId"
      INNER JOIN "Products" AS p2 ON t1."ProductId0" = p2."Id"

Fails during ExecuteUpdate producing wrong SQL query

orderQueryWithJoin.ExecuteUpdate(setters =>
            setters.SetProperty(x => x.item.LastCp, x => x.Rate)
                .SetProperty(x => x.item.LastCpWithoutVat, x => x.RateWithoutVat)
                .SetProperty(x => x.item.NetAmount, x => x.item.Quantity * x.Rate)
            );

UPDATE "PurchaseOrders" AS p
      SET "LastCp" = p."Quantity"::numeric * t1."Rate",
          "LastCpWithoutVat" = CASE
              WHEN t1."Rate" > 0.0 THEN round(CASE
                  WHEN p2."VatPercent" > 0 THEN t1."Rate" - (CAST((p2."VatPercent" / 100) AS numeric) * t1."Rate")
                  ELSE t1."Rate"
              END, 3)
              ELSE 0.0
          END,
          "LastCp" = t1."Rate"
      FROM (
          SELECT p0."ProductId", p0."UnitId", t0."ProductId" AS "ProductId0"
          FROM "ProductUnitLinks" AS p0
          INNER JOIN (
              SELECT t."ProductId", t."UnitId"
              FROM (
                  SELECT p1."ProductId", p1."UnitId", ROW_NUMBER() OVER(PARTITION BY p1."ProductId", p1."UnitId" ORDER BY p1."Date" DESC) AS row
                  FROM "ProductCps" AS p1
              ) AS t
              WHERE t.row <= 1
          ) AS t0 ON p0."ProductId" = t0."ProductId" AND p0."UnitId" = t0."UnitId"
      ) AS t1
      INNER JOIN "Products" AS p2 ON t1."ProductId0" = p2."Id"
      WHERE p."ProductId" = t1."ProductId" AND p."UnitId" = t1."UnitId";

Stack traces

Npgsql.PostgresException (0x80004005): 42703: column t1.Rate does not exist

      POSITION: 70
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)  
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteNonQuery()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.<>c.<NonQueryResult>b__13_0(DbContext _, ValueTuple`3 state)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.NonQueryResult(RelationalQueryContext relationalQueryContext, RelationalCommandCache relationalCommandCache, Type contextType, Comman
dSource commandSource, Boolean threadSafetyChecksEnabled)
        Exception data:
          Severity: ERROR
          SqlState: 42703
          MessageText: column t1.Rate does not exist
          Position: 70
          File: parse_relation.c
          Line: 3611
          Routine: errorMissingColumn

Produced query is missing column Rate, RateWithoutVat in select, resulting in error i.e. column Rate does not exist.
Also this issue only arises when including Product while fetching LastCp

var lastCpQuery = from item in context.ProductUnitLinks
            from cp in context.ProductCps.Include(x => x.Product)

It works fine when Include is removed.

var lastCpQuery = from item in context.ProductUnitLinks
                from cp in context.ProductCps.Where(x => x.ProductId == item.ProductId && x.UnitId == item.UnitId)
                    .OrderByDescending(x => x.Date)
                    .Take(1)
                select new LastCpDto
                {
                    ProductId = item.ProductId,
                    UnitId = item.UnitId,
                    Rate = cp.Rate,
                };

var orderQueryWithoutJoin = from item in context.PurchaseOrders
            join cp in lastCpQuery on new { item.ProductId, item.UnitId } equals new { cp.ProductId, cp.UnitId }
            select new
            {
                item,
                Rate = cp.Rate
            };
        orderQueryWithoutJoin.ExecuteUpdate(setters =>
            setters.SetProperty(x => x.item.LastCp, x => x.Rate)
                .SetProperty(x => x.item.NetAmount, x => x.item.Quantity * x.Rate)
        );

UPDATE "PurchaseOrders" AS p                                                                                     
      SET "NetAmount" = p."Quantity"::numeric * t1."Rate",                                                                                                                     
          "LastCp" = t1."Rate"                                                                                                                                                 
      FROM (                                                                                                                                                                   
          SELECT p0."Id", p0."ProductId", p0."UnitId", t0."Id" AS "Id0", t0."Date", t0."ProductId" AS "ProductId0", t0."Rate", t0."UnitId" AS "UnitId0"                        
          FROM "ProductUnitLinks" AS p0                                                                                                                                        
          INNER JOIN (                                                                                                                                                         
              SELECT t."Id", t."Date", t."ProductId", t."Rate", t."UnitId"                                                                                                     
              FROM (                                                                                                                                                           
                  SELECT p1."Id", p1."Date", p1."ProductId", p1."Rate", p1."UnitId", ROW_NUMBER() OVER(PARTITION BY p1."ProductId", p1."UnitId" ORDER BY p1."Date" DESC) AS row
                  FROM "ProductCps" AS p1                                                                                                                                      
              ) AS t                                                                                                                                                           
              WHERE t.row <= 1                                                                                                                                                 
          ) AS t0 ON p0."ProductId" = t0."ProductId" AND p0."UnitId" = t0."UnitId"                                                                                             
      ) AS t1                                                                                                                                                                  
      WHERE p."ProductId" = t1."ProductId" AND p."UnitId" = t1."UnitId" ;

Link to Project to reproduce error

Repo With Error

Include provider and version information

EF Core version: 7.0.11
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 7.0
Operating system: Window 10
IDE: JetBrains Rider

[ajcvickers]

Note for triage: still repros with RC2 on both SQL Server and PostgreSQL.

SQL Server SQL:

fail: 10/12/2023 12:04:23.962 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      UPDATE [p]
      SET [p].[NetAmount] = CAST([p].[Quantity] AS decimal(18,2)) * [t1].[Rate],
          [p].[LastCpWithoutVat] = CASE
              WHEN [t1].[Rate] > 0.0 THEN ROUND(CASE
                  WHEN [p2].[VatPercent] > CAST(0 AS bigint) THEN [t1].[Rate] - CAST([p2].[VatPercent] / CAST(100 AS bigint) AS decimal(18,2)) * [t1].[Rate]
                  ELSE [t1].[Rate]
              END, 3)
              ELSE 0.0
          END,
          [p].[LastCp] = [t1].[Rate]
      FROM [PurchaseOrders] AS [p]
      INNER JOIN (
          SELECT [p0].[ProductId], [p0].[UnitId], [t0].[ProductId] AS [ProductId0]
          FROM [ProductUnitLinks] AS [p0]
          INNER JOIN (
              SELECT [t].[ProductId], [t].[UnitId]
              FROM (
                  SELECT [p1].[ProductId], [p1].[UnitId], ROW_NUMBER() OVER(PARTITION BY [p1].[ProductId], [p1].[UnitId] ORDER BY [p1].[Date] DESC) AS [row]
                  FROM [ProductCps] AS [p1]
              ) AS [t]
              WHERE [t].[row] <= 1
          ) AS [t0] ON [p0].[ProductId] = [t0].[ProductId] AND [p0].[UnitId] = [t0].[UnitId]
      ) AS [t1] ON [p].[ProductId] = [t1].[ProductId] AND [p].[UnitId] = [t1].[UnitId]
      INNER JOIN [Products] AS [p2] ON [t1].[ProductId0] = [p2].[Id]

[roji]

Thanks for checking @ajcvickers.

I don't have time for a deep investigation at the moment, but some chances of this being a dup of #31407. Let's keep this open and I'll prioritize looking at this along with #31407 once things calm down.

[roji]

Confirmed that this fails on 8.0.0 with SQL Server, but works with the latest daily build (9.0.0-alpha.1.24054.14).

[roji]

Duplicate of #31407