PostgresException: 22007: invalid input syntax for type interval: " days"

[pil0t]

In continue to #2888

Example code:

using Microsoft.EntityFrameworkCore;
using Npgsql;
using Xunit;

namespace DateOnlyNpgsql;

public class Linked
{
    public int Id { get; set; }
    public int Diff { get; set; }
}
public class TestEntity
{
    public int Id { get; set; }
    public DateOnly? Date { get; set; }
    public int Diff { get; set; }
    public Linked Linked { get; set; }
    public int? LinkedId { get; set; }
}


public class MyTest
{
    [Fact]
    public async Task PostgresExceptionExample()
    {
        var x = new DbCtx();
        await x.Database.EnsureDeletedAsync();
        await x.Database.EnsureCreatedAsync();
        var e = new TestEntity()
        {
            Id = 1,
            Date = DateOnly.Parse("2023-09-26")
        };
        x.TestEntities.Add(e);
        await x.SaveChangesAsync();

        var r1 = x.TestEntities
            .Select(x => new
            {
                x.Id,
                NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
            });
// PostgresException: 22007: invalid input syntax for type interval: " days"
        var a1 = r1.ToArray();
    }

}

public class DbCtx : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; } = null!;
    public DbSet<Linked> LinkedEntities { get; set; } = null!;
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.EnableSensitiveDataLogging();
        options.UseNpgsql(
            new NpgsqlConnection("Host=localhost;Uid=postgres;Pwd=mysecretpassword;Database=DummyDatabase"));
    }
}

From logs I can extract this:

SELECT t."Id", t."Date" + CAST((COALESCE(l."Diff"::text, '') || ' days') AS interval) AS "NextMonth"
FROM "TestEntities" AS t
LEFT JOIN "LinkedEntities" AS l ON t."LinkedId" = l."Id"

Looks like COALESCE(l."Diff"::text, '') should be replaced with COALESCE(l."Diff"::text, '0')

[dedalexij]

I have the same problem with the AddYears method

[roji]

Sorry for not responding to this earlier.

I am unable to reproduce this with the latest versions (EF Core 8.0.7, Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4). Using the following attempted repro:

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

var e = new TestEntity
{
    Id = 1,
    Date = DateOnly.Parse("2023-09-26"),
    Linked = new Linked
    {
        Diff = 8
    }
};
context.TestEntities.Add(e);
await context.SaveChangesAsync();

_ = context.TestEntities
    .Select(x => new
    {
        x.Id,
        NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
    })
    .ToArray();

public class BlogContext : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; } = null!;
    public DbSet<Linked> LinkedEntities { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
}

public class Linked
{
    public int Id { get; set; }
    public int Diff { get; set; }
}
public class TestEntity
{
    public int Id { get; set; }
    public DateOnly? Date { get; set; }
    public int Diff { get; set; }
    public Linked Linked { get; set; }
    public int? LinkedId { get; set; }
}

I can see the following SQL:

SELECT t."Id", t."Date" + l."Diff" AS "NextMonth"
FROM "TestEntities" AS t
LEFT JOIN "LinkedEntities" AS l ON t."LinkedId" = l."Id"

The COALESCE was probably removed at some point, making this work. @dedalexij or @pil0t, if you can submit an updated repro that shows this actually failing on the latest versions, I'd be happy to take another look.

[roji]

Closing as the problem isn't reproducible at this point.

[jamend]

I ran into this issue in 9.0. To reproduce it, make Linked.Diff nullable and don't assign it a value. Workaround: NextMonth = x.Linked.Diff == null ? null : x.Date.Value.AddDays(x.Linked.Diff)

[roji]

@jamend can you please post a console program showing this? As always, posting a partial snippet isn't sufficient - you're basically asking me to guess the context around it.

[jamend]

@roji In reproducing the issue I realized my use case is a bit different from the original (DateTime instead of DateOnly), so perhaps it has since been fixed for DateOnly, but I do have the issue with DateTime.

Note the main difference between the original repro and the one you posted is there is no related Linked entity, so x.Linked.Diff projects to null.

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

var e = new TestEntity()
{
    Id = 1,
    Date = DateTime.UtcNow
};
context.TestEntities.Add(e);
await context.SaveChangesAsync();

var r1 = context.TestEntities
    .Select(x => new
    {
        x.Id,
        NextMonth = x.Date.Value.AddDays(x.Linked.Diff)
    });
// PostgresException: 22007: invalid input syntax for type interval: " days"
var a1 = r1.ToArray();

public class BlogContext : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; } = null!;
    public DbSet<Linked> LinkedEntities { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
}

public class Linked
{
    public int Id { get; set; }
    public int? Diff { get; set; }
}
public class TestEntity
{
    public int Id { get; set; }
    public DateTime? Date { get; set; }
    public int Diff { get; set; }
    public Linked Linked { get; set; }
    public int? LinkedId { get; set; }
}

[roji]

Thanks, I can repro this (see below for a simplified repro using a single table). The issue occurs when a non-constant interval expression is generated via concatenation:

SELECT t."Id", t."Date" + CAST(COALESCE(t."Diff"::text, '') || ' days' AS interval) AS "NextMonth"
FROM "TestEntities" AS t

... and the number being concatenated is NULL.

Will reopen and use this issue to track the problem, as the original one is likely the same and did not provide a repro.

Minimal repro

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

var e = new TestEntity()
{
    Id = 1,
    Date = DateTime.UtcNow,
    Diff = null // Causes the issue
};
context.TestEntities.Add(e);
await context.SaveChangesAsync();

var r1 = context.TestEntities
    .Select(x => new
    {
        x.Id,
        NextMonth = x.Date.AddDays((double)x.Diff)
    })
    .ToArray(); // PostgresException: 22007: invalid input syntax for type interval: " days"

public class BlogContext : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging()
            .EnableDetailedErrors();
}

public class TestEntity
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public double? Diff { get; set; }
}

[jamend]

I was also able to reproduce it with DateOnly with a test case in GearsOfWarQueryNpgsqlTest:

[ConditionalTheory]
[MemberData(nameof(IsAsyncData))]
public virtual async Task Select_DateOnly_NullProjection_AddYears(bool async)
{
    await AssertQuery(
        async,
        ss => ss.Set<Mission>()
            // We filter out DateOnly.MinValue which maps to -infinity
            .Where(m => m.Date != DateOnly.MinValue)
            // AddYears(null)
            .Select(m => m.Date.AddYears(ss.Set<Mission>().FirstOrDefault(m2 => m2.Id == -1).Id)));

    AssertSql(
        """
@__MinValue_0='01/01/0001' (DbType = Date)

SELECT CAST(m."Date" + INTERVAL '3 years' AS date)
FROM "Missions" AS m
WHERE m."Date" <> @__MinValue_0
""");
}

[roji]

Root cause: https://github.com/dotnet/efcore/issues/36210