Incorrect checking of JSONB null values

[rogerfar]

When I run this query:

dataContext.Users.Where(m => m.MetaData.DatabaseAccess != null && m.MetaData.DatabaseAccess.Any()).ToListAsync();

It generates this query:

SELECT a."Id", a."AccessFailedCount", a."ConcurrencyStamp", a."Email", a."EmailConfirmed", a."ForceAuthenticate", a."IsActive", a."LockoutEnabled", a."LockoutEnd", a."ManagerId", a."Mobile", a."Name", a."NormalizedEmail", a."NormalizedUserName", a."Office", a."OfficeExt", a."PasswordHash", a."PhoneNumber", a."PhoneNumberConfirmed", a."SecurityStamp", a."TwoFactorEnabled", a."UserName", a."EmploymentMetaData", a."MetaData"
FROM "AspNetUsers" AS a
WHERE EXISTS (
    SELECT 1
    FROM ROWS FROM (jsonb_to_recordset(a."MetaData" -> 'DatabaseAccess') AS (
        "Instance" text,
        "Permission" integer
    )) WITH ORDINALITY AS d)

This is incorrect because if the values MetaData.DatabaseAccess is null, postgres cannot query it:

22023: cannot call jsonb_to_recordset on a non-array

But, the statement m.MetaData.DatabaseAccess != null is ignored, there is no explicit null check.

Important to note is that if you do in postgres:

SELECT *
FROM "AspNetUsers" AS a
WHERE
"MetaData" -> 'DatabaseAccess' IS NOT NULL

It WILL return items that are null, it will only filter out items that do not have the key on the MetaData column set.

I believe the appropriate way to null check a value is:

SELECT *
FROM "AspNetUsers" AS a
WHERE
jsonb_typeof(a."MetaData" -> 'DatabaseAccess') != 'null'

Which makes the initial complete query:

SELECT *
FROM "AspNetUsers" AS a
WHERE
jsonb_typeof(a."MetaData" -> 'DatabaseAccess') != 'null' AND
EXISTS (
    SELECT 1
    FROM ROWS FROM (jsonb_to_recordset(a."MetaData" -> 'DatabaseAccess') AS (
        "Instance" text,
        "Permission" integer
    )) WITH ORDINALITY AS d)

[rogerfar]

This is the model I used:

public class User : IdentityUser<Guid>, IBaseModel<User>
{
    public String Name { get; set; } = null!;

    public UserMetaData MetaData { get; set; } = new();
}

public class UserMetaData
{
   public List<UserMetaDataDatabaseAccess>? DatabaseAccess { get; set; } = [];
}

public class UserMetaDataDatabaseAccess
{
    public required String Instance { get; set; }
    public required DatabasePermission Permission { get; set; }
}

        modelBuilder.Entity<User>(user =>
        {
            user.Property(m => m.Name)
                .HasMaxLength(250);

            user.OwnsOne(m => m.MetaData, m =>
            {
                m.ToJson();
                m.OwnsMany(u => u.DatabaseAccess);
            });
        });

[roji]

Thanks, I agree this looks like a bug, but I can't see this happening with the minimal repro below. Can you help out by tweaking it to make it fail as it does for you?

Attempted repro

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

context.Users.Add(new User
{
    Name = "John Doe",
    MetaData = new UserMetaData() { DatabaseAccess = null }
});
await context.SaveChangesAsync();

context.ChangeTracker.Clear();

_ = context.Users.Where(m => m.MetaData.DatabaseAccess != null && m.MetaData.DatabaseAccess.Any()).ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<User> Users { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(user =>
        {
            user.OwnsOne(m => m.MetaData, m =>
            {
                m.ToJson();
                m.OwnsMany(u => u.DatabaseAccess);
            });
        });
    }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;

    public UserMetaData MetaData { get; set; } = new();
}

public class UserMetaData
{
   public List<UserMetaDataDatabaseAccess>? DatabaseAccess { get; set; } = [];
}

public class UserMetaDataDatabaseAccess
{
    public required string Instance { get; set; }
}

[rogerfar]

Try running this query:

await _dbContext.Users.Where(m => m.MetaData.Access.Any()).ToListAsync();
await _dbContext.Users.Where(m => m.MetaData != null && m.MetaData.Access.Any()).ToListAsync();

Both of these give this error:

22023: argument of jsonb_to_recordset must be an array of objects

It runs this query:

      SELECT u."Id", u."MetaData"
      FROM "Users" AS u
      WHERE (u."MetaData") IS NOT NULL AND EXISTS (
          SELECT 1
          FROM ROWS FROM (jsonb_to_recordset(u."MetaData" -> 'Access') AS (
              "Level" integer,
              "Name" text
          )) WITH ORDINALITY AS a)

I have a repo here: https://github.com/rogerfar/npgsql-reproduction