Jsonb column property null check result in wrong SQL condition

[vndlczndr]

Hi,

I have a problem where newer versions of Npgsql for EF creates wrong SQL query conditions. See the example below.

Minimal working example:

.NET:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;

namespace NpgSqlPoc {

    class Program {

        static void Main(string[] args) {

            NpgsqlConnection.GlobalTypeMapper.UseJsonNet();
            DatabaseContext context = new DatabaseContext();

            List<Entity> res = context.Entities.Where(x => x.JsonColumn.StringValue == null).ToList();

            Console.ReadLine();
        }
    }

    public class DatabaseContext : DbContext {
        public DbSet<Entity> Entities { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {

            optionsBuilder.UseNpgsql("Host=*****;Database=TEMP2;Username=postgres;Password=*****");
            //optionsBuilder.UseLoggerFactory(LoggerFactory.Create(x => x.AddDebug())); // 3.1.11
            //optionsBuilder.LogTo(message => Debug.WriteLine(message)); // 5.0.X
            optionsBuilder.EnableDetailedErrors();
            optionsBuilder.EnableSensitiveDataLogging();
        }
    }

    public class Entity {

        [Key]
        public int Key { get; set; }

        [Column(TypeName = "jsonb")]
        public JsonColumn JsonColumn { get; set; }
    }

    public class JsonColumn {

        public string StringValue { get; set; }
    }
}

SQL:

CREATE TABLE "public"."Entities" (
  "JsonColumn" jsonb NOT NULL,
  "Key" int4 NOT NULL DEFAULT nextval('"Entities_Key_seq"'::regclass)
)
;
ALTER TABLE "public"."Entities" OWNER TO "postgres";

-- ----------------------------
-- Primary Key structure for table Entities
-- ----------------------------
ALTER TABLE "public"."Entities" ADD CONSTRAINT "Entities_pkey" PRIMARY KEY ("Key");

Resulting SQL query using Npgsql.EntityFrameworkCore.PostgreSQL version 3.1.11:

Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT e."Key", e."JsonColumn"
FROM "Entities" AS e
WHERE (e."JsonColumn"->>'StringValue' IS NULL)

Resulting SQL query using Npgsql.EntityFrameworkCore.PostgreSQL version 5.0.X:

Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT e."Key", e."JsonColumn"
FROM "Entities" AS e
WHERE FALSE

Please note the "WHERE FALSE" condition, that results in an empty result set regardless of the real table data.

PostgreSQL version: 10.15

.NET version: 5.0

Any thoughts?

Best Regards,
Vendel

(this is my first ticket, please tell if there is a problem with it)

[roji]

@VendelCzinder thanks, found the issue and submitted a fix for 5.0.3. The issue is perfect, contains all the info :)

[roji]

Note, this is very similar to #1683, but that one is for arrays whereas this one is for JSON.