sql: implement json compare

[maddyblue]

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

[maddyblue]

@justinj do you have memories about why this is? For example

cockroach/pkg/util/json/json_test.go

Line 73 in 1c54631

// In Postgres's sorting rules, the empty array comes before everything (even null),

says that we know we differ from postgres. Do you recall a technical reason why fixing this would be hard?

[justinj]

I don't think there's a technical reason, I didn't do it because I thought it would be tricky/error prone (but possible) and not that helpful, but it's caused problems so if someone has the bandwidth to do it it would probably be worthwhile.

[knz]

The pg docs have an entry somewhere that details the json compare algorithm. If we add the operator we should follow that doc to the letter.

[knz]

There's also an entry about array compares.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[michae2]

[triage] @mgartner to verify that this is now fixed

[mgartner]

Comparison of JSON values was added in #99275 and adjusted to match Postgres in #101260, both of which will be included in v23.2.