sql: excluding json field with -> operator incorrectly returns values where the key doesn't exist

[timgraham]

Describe the problem

A SQL clause like WHERE NOT (("jsontest"."value" -> 'j') = 'null') unexpectedly (unlike PostgreSQL) returns values where 'j' doesn't exist. Instead, it should return only values where 'j' exists and isn't null.

To Reproduce

CREATE TABLE jsontest (
    value json
);
INSERT INTO jsontest VALUES ('{}');
INSERT INTO jsontest VALUES ('{"a": 1}');
INSERT INTO jsontest VALUES ('{"j": null}');
INSERT INTO jsontest VALUES ('{"j": 1}');
SELECT * FROM jsontest WHERE NOT (("jsontest"."value" -> 'j') = 'null');

Expected behavior

Expected result (as PostgreSQL):

{"j": 1}

Actual results:

   value
------------
  {}
  {"j": 1}
  {"a": 1}

Environment:

• CockroachDB version 20.1.0

Django 3.1 (to be released in August 2020) adds support for JSONField and this issue came up in a failing test.

[timgraham]

This also applies to values besides 'null'. For example, given the same setup as in the issue description:

SELECT * FROM jsontest WHERE NOT (("jsontest"."value" ->'a') = '1');

gives no results on PostgreSQL but 3 results on CockroachDB:

     value
---------------
  {}
  {"j": null}
  {"j": 1}

[jordanlewis]

This is an interesting issue - the problem is that the optimizer reduces things of the form

SELECT json->key = value

to

SELECT value @> '{key: value}'

which isn't valid when json->key isn't present. @> returns false when there's something not present, but ->key returns null.