sql: implement json compare #43123
Description
While working on sqlsmith, we discovered that the json type doesn't implement the < and <= (and > and >=) operators. There's a json.Compare method, so I went ahead and just added it to the list, assuming that it had been merely forgotten. I wrote a test to generate some json stuff and compare them, and ran it against postgres. The results differed, and should probably be fixed before we add this feature to postgres.
WITH
t (c)
AS (
VALUES
('1'::JSONB),
('true'::JSONB),
('[]'::JSONB),
('{}'::JSONB)
)
SELECT
a.c, b.c, a.c < b.c, a.c <= b.c
FROM
t AS a, t AS b
ORDER BY
a.c, b.c;
cockroach:
c | c | ?column? | ?column?
+------+------+----------+----------+
1 | 1 | false | true
1 | [] | true | true
1 | true | true | true
1 | {} | true | true
[] | 1 | false | false
[] | [] | false | true
[] | true | false | false
[] | {} | true | true
true | 1 | false | false
true | [] | true | true
true | true | false | true
true | {} | true | true
{} | 1 | false | false
{} | [] | false | false
{} | true | false | false
{} | {} | false | true
postgres:
c | c | ?column? | ?column?
------+------+----------+----------
[] | [] | f | t
[] | {} | t | t
[] | 1 | t | t
[] | true | t | t
{} | [] | f | f
{} | {} | f | t
{} | 1 | f | f
{} | true | f | f
1 | [] | f | f
1 | {} | t | t
1 | 1 | f | t
1 | true | t | t
true | [] | f | f
true | {} | t | t
true | 1 | f | f
true | true | f | t
Although the orders differ here (which is weird?), the output also differs. Take the 1, [] case, where postgres says false, false and cockroach says true, true.
Jira issue: CRDB-5297
Metadata
Metadata
Assignees
Labels
Type
Projects
Status