`EF.Functions.Sum` over `TimeSpan`/`Duration`/`Period`: a filter on the group becomes `<> NULL`, and null checks on the result fold away

未關閉
  • 哪裡不對？
#4,091 1 則留言 0 個 reaction 已指派 0 人 在 GitHub 檢視

維護者通常 1 天內回覆

還沒有人認領這個 Issue。

  • #4102 來自 @CaffeinatedCoder —— 已關閉，未合併

評估

難度
3/5
預估耗時
1-2 天
新手友好度
35/100
Issue 類型
缺陷
描述清晰度
描述清楚
活躍度
停滯
技術堆疊
csharp, postgresql, sql
領域
database

研究方向

Read src/EFCore.PG/Query/Internal/NpgsqlSqlNullabilityProcessor.cs lines 418-430 where SUM over interval skips null-semantics. Visit the wrapped expression's components like the code below does and report it nullable. Add a test using the issue's GroupBy/Sum repro to verify the FILTER becomes IS NOT NULL.

由索引模型根據 Issue 內容生成。

描述

Environment
PostgreSQL 18.6 (postgres:18), session TimeZone Etc/UTC
.NET 10 runtime 10.0.11, Npgsql.EntityFrameworkCore.PostgreSQL(.NodaTime) 10.0.3, EF Core 10.0.4
.NET 11 runtime 11.0.0-rc.2, Npgsql.EntityFrameworkCore.PostgreSQL(.NodaTime) 11.0.0-rc.2, EF Core 11.0.0-rc.2
Also Npgsql 10.0.3 on both lines, NodaTime 3.2.2, macOS arm64

Both lines print the same output. main at 150011d has the same code.

context.Rows.GroupBy(r => r.Group).Select(g => new { g.Key, Sum = EF.Functions.Sum(g.Where(r => r.Duration != null).Select(r => r.Duration!.Value)) })

SELECT r."Group" AS "Key", sum(r."Duration") FILTER (WHERE r."Duration" <> NULL) AS "Sum"

<> NULL is never true, so the sum is NULL for every group. A Where(x => x.Sum == null) on top then becomes HAVING FALSE and returns nothing. TimeSpan behaves the same way. EF.Functions.Average is correct: FILTER (WHERE ... IS NOT NULL).

Repro 
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using NodaTime;

await using var context = new TestContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

context.Rows.AddRange(
    new Row { Group = 1, Duration = Duration.FromHours(1), Span = TimeSpan.FromHours(1) },
    new Row { Group = 1, Duration = null, Span = null },
    new Row { Group = 1, Duration = Duration.FromHours(2), Span = TimeSpan.FromHours(2) });
await context.SaveChangesAsync();

var durations = context.Rows.GroupBy(r => r.Group).Select(g => new { g.Key, Sum = EF.Functions.Sum(g.Where(r => r.Duration != null).Select(r => r.Duration!.Value)) });
Console.WriteLine($"Sum of non-null Durations: {(await durations.SingleAsync()).Sum?.ToString() ?? "NULL"} (.NET: 3 hours)");
Console.WriteLine($"... Where(Sum == null).Count(): {await durations.CountAsync(x => x.Sum == null)}");

var spans = context.Rows.GroupBy(r => r.Group).Select(g => new { g.Key, Sum = EF.Functions.Sum(g.Where(r => r.Span != null).Select(r => r.Span!.Value)) });
Console.WriteLine($"Sum of non-null TimeSpans: {(await spans.SingleAsync()).Sum?.ToString() ?? "NULL"} (.NET: 03:00:00)");

public class TestContext : DbContext
{
    public DbSet<Row> Rows => Set<Row>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"), o => o.UseNodaTime())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Row
{
    public int Id { get; set; }
    public int Group { get; set; }
    public Duration? Duration { get; set; }
    public TimeSpan? Span { get; set; }
}

Output, the same on both lines:

Sum of non-null Durations: NULL (.NET: 3 hours)
... Where(Sum == null).Count(): 0
Sum of non-null TimeSpans: NULL (.NET: 03:00:00)

The cause is the workaround in NpgsqlSqlNullabilityProcessor.cs#L418-L430. For SUM over an interval it returns the wrapped PgFunctionExpression as is, so:

  • its FILTER predicate never goes through null-semantics processing, and
  • nullable stays false from the COALESCE that base EF added, although the comment above it says the function returns a nullable value.

Visiting the wrapped expression's components (as the code below it does for the other types) and reporting it as nullable would fix both.

主要語言
C#
星號
1.8k
分支
268
平均合併
2 小時 35 分鐘
30 天內合併 PR
136

環境準備

這個專案沒有提供開發容器、Dockerfile 或貢獻指南，環境需要你自己搭建：先看它的 README，通用步驟見我們的新手貢獻指南。

從這裡開始

  1. 先讀完整個 Issue，再讀專案的貢獻指南。
  2. 在 Issue 下留言說明你要接手 —— 這能避免兩個人做同樣的事。
  3. Fork 儲存庫，在一個分支上完成修改。
  4. 送出 Pull Request，並在描述裡引用這個 Issue 編號。

npgsql/efcore.pg 的其他 Issue

查看 npgsql/efcore.pg 的全部 Issue

相似的 Issue

更多 C# Issue

把新 issue 寄到你的電子郵件信箱

精選適合新手參與的 GitHub issue 摘要。