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

Aperta
  • Qualcosa non va?
#4,091 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

  • #4102 di @CaffeinatedCoder — chiusa senza merge

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
35/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Ferma
Stack tecnologico
csharp, postgresql, sql
Ambito
database

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
C#
Stelle
1.8k
Fork
268
Merge medio
2h 35m
PR unite (30g)
136

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di npgsql/efcore.pg

Tutte le issue di npgsql/efcore.pg

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.