sql: decide on JSON ordering before 23.2 release

[rafiss]

#101260 was created for the following reason:

Currently, #97928 and #99275 are responsible for laying out
a lexicographical ordering for JSON columns to be forward
indexable in nature. This ordering is based on the rules
posted by Postgres and is defined here: #99849

However, Postgres currently sorts the empty JSON array
before any other JSON values. An issue has been opened:
postgresql.org/message-id/17873-826fdc8bbcace4f1%40postgresql.org

Thus, this PR intends on replicating this behaviour until
the issue has been identified and resolved by Postgres.

However, @mgartner says:

Before this is merged, we'd like to check the SQL 2023 spec which reportedly defines the ordering of JSON values. If the ordering in the spec does not follow this empty array inconsistency of Postgres, we're probably fine with sticking with the current ordering. If the spec defines an ordering completely different than Postgres, we'll have to reconsider our options. This needs to be done before v23.2 is shipped.

This issue tracks that decision, and is a release blocker, since it will be a headache to change the ordering after the release goes out.

Epic: CRDB-24501

Jira issue: CRDB-29144

[mgartner]

Thanks for creating this issue. This is currently waiting on me to examine the SQL:2023 spec and see if it details the ordering of JSON values.

[rytaft]

@rafiss @mgartner Can we call this a GA-blocker instead of release-blocker?

[mgartner]

Empty JSON Array Comparison

The 2023 SQL spec (private copy) specifies the high level ordering of JSON values as:

null < scalar < array < object

Where scalar is a number, string, boolean, or datetime. There's no mention of special behavior for an empty array. The language is quite specific:

— An SQL/JSON null value compares less than any SQL/JSON scalar;
— An SQL/JSON scalar compares less than any SQL/JSON array;
— An SQL/JSON array compares less than any SQL/JSON object;

Therefore, Postgres's ordering of an empty JSON array before all other JSON values is inconsistent with the spec.

With the support of the 2023 spec, we have some justification to diverge from Postgres and NOT merge #101260. The spec's behavior is more intuitive than Postgres's. And I believe this is a bug in Postgres, though we've had no responses on the bug report and I think it's unlikely they'll be motivated to fix it anytime soon. I'd love to hear opinions from the SQL Foundations and Migrations team cc @rafiss @otan.

General JSON Array Comparison

There's another notable divergence in JSON array comparison behavior. The spec states:

— An SQL/JSON array A compares less than another SQL/JSON array B, if one of the following is true:

• The cardinality of A is less than the cardinality of B and the elements at the same array index in both SQL/JSON arrays compare equal for all elements of A;
• There exists an element E in A that compares less than the corresponding element in B and each element that precedes E in A compares equal to the corresponding element in B;

The Postgres docs highlight different comparison behavior with JSON arrays:

Array with n elements > array with n - 1 elements

For example, according to the 2023 spec, [1, 2, 3] is less than [1, 4] (via the second bullet point), but [1, 2, 3] is greater than [1, 4] in Postgres because the former has more elements than the latter.

Our current implementation matches Postgres's behavior here. I think it would be far more disruptive to users for Postgres to fix this inconsistency compared to the empty array inconsistency, so I think it's very unlikely they will ever change this behavior. For that reason, matching Postgres and diverging from the spec seems safe in this case.

[rafiss]

From the SQL Foundations perspective, I feel comfortable diverging from PG on this (with some public documentation somewhere). we now have enough 3rd party tooling support so we can handle this difference externally.

The Migrations view may be different. @otan has told me about some tools that verify migrations from PG down to some details such as ordering, so he may have more perspective.

[otan]

I'd much rather match PG by default for reasons rafi mentions. The question I have is whether PG plans to change their implementation.

[mgartner]

Ok, let's mimic Postgres behavior exactly then. I'll go ahead and review and merge #101260.