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

Offen
  • Stimmt etwas nicht?
#4,091 1 Kommentar 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

  • #4102 von @CaffeinatedCoder — ohne Merge geschlossen

Bewertung

Schwierigkeit
3/5
Geschätzter Aufwand
1-2 Tage
Anfängerfreundlichkeit
35/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Veraltet
Tech-Stack
csharp, postgresql, sql
Bereich
database

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

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.

Vorherrschende Sprache
C#
Sterne
1.8k
Forks
268
Ø Merge
2 Std. 35 Min.
Gemergte PRs (30 T.)
136

Entwicklungsumgebung

Dieses Projekt bietet weder Dev-Container noch Dockerfile noch Beitragsleitfaden – die Einrichtung liegt bei Ihnen. Beginnen Sie mit der README; die allgemeinen Schritte stehen in unserem Leitfaden für den ersten Beitrag.

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus npgsql/efcore.pg

Alle Issues in npgsql/efcore.pg

Ähnliche Issues

Weitere Issues zu C#

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.