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

Ouverte
  • Quelque chose ne va pas ?
#4,091 1 commentaire 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

  • #4102 par @CaffeinatedCoder — fermée sans fusion

Évaluation

Difficulté
3/5
Temps estimé
1-2 jours
Accessibilité débutants
35/100
Type d'issue
Bug
Clarté
Clairement spécifiée
Activité
À l'abandon
Stack technique
csharp, postgresql, sql
Domaine
database

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

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.

Langage dominant
C#
Étoiles
1.8k
Forks
268
Merge moyen
2 h 35 min
PR mergées (30 j)
136

Préparer son environnement

Ce projet ne fournit ni conteneur de développement, ni Dockerfile, ni guide de contribution : l'installation est à votre charge. Commencez par son README, et consultez notre guide de la première contribution pour les étapes générales.

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de npgsql/efcore.pg

Toutes les issues de npgsql/efcore.pg

Issues similaires

Plus d'issues C#

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.