Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: support ordering by JSON (pq: unable to encode table key: *tree.DJSON) #35706

Closed
knz opened this issue Mar 13, 2019 · 11 comments · Fixed by #99275 or #101932
Closed

sql: support ordering by JSON (pq: unable to encode table key: *tree.DJSON) #35706

knz opened this issue Mar 13, 2019 · 11 comments · Fixed by #99275 or #101932
Assignees
@Shivs11
Labels
A-sql-optimizer SQL logical planning and optimizations. A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) docs-done docs-known-limitation T-sql-queries SQL Queries Team X-anchored-telemetry The issue number is anchored by telemetry references.

Comments

@knz
Copy link
Contributor

knz commented Mar 13, 2019 •
edited by rytaft
Loading

Currently it's not possible to order a table by a JSON column or create a primary index on a JSON column.

This is because JSON does not have a valid lexicographic ordering.
This is incidentally the issue underlying #24436 and #35260.

for reference, PostgreSQL supports it.

See also #35730

Epic: CRDB-24501
Jira issue: CRDB-4564
@knz knz added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-optimizer SQL logical planning and optimizations. X-anchored-telemetry The issue number is anchored by telemetry references. labels Mar 13, 2019
This was referenced Mar 14, 2019
Open
Closed
Closed
@jordanlewis jordanlewis changed the title sql: support ordering by JSON sql: support ordering by JSON (pq: unable to encode table key: *tree.DJSON) May 7, 2019
@jordanlewis jordanlewis mentioned this issue May 7, 2019
Closed
@jordanlewis jordanlewis mentioned this issue Feb 19, 2020
Merged
@Bessonov Bessonov mentioned this issue Apr 10, 2020
Closed
@knz
Copy link
Contributor Author

knz commented Apr 28, 2020

@rohany @RaduBerinde I think the recent key encoding for arrays will solve this issue?

@knz knz mentioned this issue Apr 28, 2020
Merged
@rohany
Copy link
Contributor

rohany commented Apr 28, 2020

No, the key encoding for arrays will just be for arrays -- i think json ordering is a different beast.

@jlinder jlinder added the T-sql-queries SQL Queries Team label Jun 16, 2021
@rafiss rafiss mentioned this issue Sep 10, 2021
Closed
@rafiss rafiss mentioned this issue Nov 2, 2021
Merged
@vy-ton
Copy link
Contributor

vy-ton commented May 16, 2022

This was on a deprecated project board. It can go in the longer-term backlog. For reference, it's in the top 10 on unimplemented feature by cluster count

@jordanlewis jordanlewis mentioned this issue Nov 18, 2022
Open
@awoods187
Copy link
Contributor

I'm putting this back into triage based on how high it appears in the unimplemented query list cc @rytaft @mgartner

@mgartner
Copy link
Collaborator

@awoods187 have we heard directly from customers that this is a strongly desired feature? I can't think of a legitimate case where ordering by JSON would be useful or required. Perhaps some ORMs are doing it for reasons.

@knz
Copy link
Contributor Author

knz commented Nov 28, 2022

one reason i could imagine would be to make the output deterministic in tests

Shivs11 added a commit to Shivs11/cockroach that referenced this issue Apr 7, 2023
@Shivs11
sql: enabling forward indexes and ORDERBY on JSONB columns
cc619a7 
Currently, cockroachdb#97928 outlines the scheme for JSONB encoding
and decoding for forward indexes. However, the PR doesn't
enable this feature to our users. This current PR aims
to allow forward indexes on JSONB columns. The presence
of a lexicographical ordering, as described in cockroachdb#97928,
shall now allow primary and secondary indexes on JSONB
columns along with the ability to use ORDER BY filter
in their queries.

Additionally, JSON values consist of decimal numbers
and containers, such as Arrays and Objects, which can
contain these decimal numbers. In order to preserve
the values after the decimal, JSONB columns are now
required to be composite in nature. This shall enable
such values to be stored in both the key and the value
side of a K/V pair in hopes of receiving the exact value.

Fixes: cockroachdb#35706

Release note (sql change): This PR adds support for enabling
forward indexes and ordering on JSON values.

Epic: CRDB-24501
craig bot pushed a commit that referenced this issue Apr 11, 2023
@Shivs11 @miraradeva @msbutler
Merge #99275 #100942 #101220
1abff58 
99275: sql: enabling forward indexes and ORDERBY on JSONB columns r=celiala a=Shivs11

Currently, #97928 outlines the scheme for JSONB encoding
and decoding for forward indexes. However, the PR doesn't
enable this feature to our users. This current PR aims
to allow forward indexes on JSONB columns. The presence
of a lexicographical ordering, as described in #97928,
shall now allow primary and secondary indexes on JSONB
columns along with the ability to use `ORDER BY` filter
in their queries.

Additionally, JSON values consist of decimal numbers
and containers, such as Arrays and Objects, which can
contain these decimal numbers. In order to preserve
the values after the decimal, JSONB columns are now
required to be composite in nature. This shall enable
such values to be stored in both the key and the value
side of a K/V pair in hopes of receiving the exact value.

Fixes: #35706

Release note (sql change): This PR adds support for enabling
forward indexes and ordering on JSON values.

Epic: [CRDB-24501](https://cockroachlabs.atlassian.net/browse/CRDB-24501)

100942: kvserver: add metrics to track snapshot queue size r=kvoli a=miraradeva

Previously, we had metrics to track the number of snapshots waiting in
the snapshot queue; however, snapshots may be of different sizes, so it
is also helpful to track the size of all snapshots in the queue. This
change adds the following metrics to track the total size of all
snapshots waiting in the queue:

    range.snapshots.send-queue-bytes
    range.snapshots.recv-queue-bytes

Informs: #85528
Release note (ops change): Added two new metrics,
range.snapshots.(send|recv)-queue-bytes, to track the total size of all
snapshots waiting in the snapshot queue.

101220: roachtest: prevent shared mutable state across c2c roachtest runs r=benbardin a=msbutler

Previously, all `c2c/*` roachtests run with `--count` would provide incomprehensible results because multiple roachtest runs of the same test would override each other's state. Specifically, the latest call of `test_spec.Run()`, would override the `test.Test` harness, and `syncedCluster.Cluster` used by all other tests with the same registration.

This patch fixes this problem by moving all fields in `replicationSpec` that are set during test execution (i.e. a `test_spec.Run` call), to a new `replicationDriver` struct. Now, `replicationSpec` gets defined during test registration and is shared across test runs, while `replicationDriver` gets set within a test run.

Epic: None
Release note: None

Co-authored-by: Shivam Saraf <[email protected]>
Co-authored-by: Mira Radeva <[email protected]>
Co-authored-by: Michael Butler <[email protected]>
@craig craig bot closed this as completed in 5cdb625 Apr 11, 2023
@Shivs11 Shivs11 mentioned this issue Apr 20, 2023
Merged
Shivs11 added a commit to Shivs11/cockroach that referenced this issue Apr 21, 2023
@Shivs11
sql: adding version gates for changes related to JSON forward indexes
5e32b80 
Previously, cockroachdb#99275 and cockroachdb#97298 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.

Fixes: cockroachdb#35706
Shivs11 added a commit to Shivs11/cockroach that referenced this issue Apr 24, 2023
@Shivs11
sql: adding version gates for changes related to JSON forward indexes
e287ba9 
Previously, cockroachdb#99275 and cockroachdb#97298 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.

Fixes: cockroachdb#35706
Shivs11 added a commit to Shivs11/cockroach that referenced this issue Apr 26, 2023
@Shivs11
sql: adding version gates for changes related to JSON forward indexes
5ee49ff 
Previously, cockroachdb#99275 and cockroachdb#97298 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.

Fixes: cockroachdb#35706
Shivs11 added a commit to Shivs11/cockroach that referenced this issue Apr 26, 2023
@Shivs11
sql: adding version gates for changes related to JSON forward indexes
676c402 
Previously, cockroachdb#99275 and cockroachdb#97298 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.

Fixes: cockroachdb#35706
rharding6373 pushed a commit to Shivs11/cockroach that referenced this issue May 15, 2023
@Shivs11 @rharding6373
sql: adding version gates for changes related to JSON forward indexes
a7ecb81 
Previously, cockroachdb#99275 and cockroachdb#97928 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Fixes: cockroachdb#35706

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.
rharding6373 pushed a commit to Shivs11/cockroach that referenced this issue May 15, 2023
@Shivs11 @rharding6373
sql: adding version gates for changes related to JSON forward indexes
1f534a6 
Previously, cockroachdb#99275 and cockroachdb#97928 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Fixes: cockroachdb#35706

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.
rharding6373 pushed a commit to Shivs11/cockroach that referenced this issue May 15, 2023
@Shivs11 @rharding6373
sql: adding version gates for changes related to JSON forward indexes
30fc318 
Previously, cockroachdb#99275 and cockroachdb#97928 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Fixes: cockroachdb#35706

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.
rharding6373 pushed a commit to Shivs11/cockroach that referenced this issue May 15, 2023
@Shivs11 @rharding6373
sql: adding version gates for changes related to JSON forward indexes
33a903e 
Previously, cockroachdb#99275 and cockroachdb#97928 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: CRDB-24501

Fixes: cockroachdb#35706

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.
craig bot pushed a commit that referenced this issue May 22, 2023
@Shivs11
Merge #101932
a1b8517 
101932: sql: adding version gates for changes related to JSON forward indexes r=fqazi a=Shivs11

Previously, #99275 and #97928 were responsible for laying the
foundations of a JSON lexicographical order as well as enabling JSON
forward indexes in CRDB. However, these commits did not account for
version gates. In a given cluster, all of its nodes are required to be
at a certain version number for creating JSON forward indexes as well as
for ordering JSON columns.  Thus, this PR aims to enable version gates
for ensuring all nodes in a given cluster meet this requirement.

Epic: [CRDB-24501](https://cockroachlabs.atlassian.net/browse/CRDB-24501)

Fixes: #35706

Release note (sql change): This PR adds version gates which requires all
nodes in a given cluster to have a minimum binary version number which
in turn is required for creating forward indexes on JSON columns and for
ordering JSON columns.

Co-authored-by: Shivam Saraf <[email protected]>
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
@jkomyno jkomyno mentioned this issue Nov 15, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Shivs11 Shivs11

Labels
A-sql-optimizer SQL logical planning and optimizations. A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) docs-done docs-known-limitation T-sql-queries SQL Queries Team X-anchored-telemetry The issue number is anchored by telemetry references.
Projects
SQL Queries
Archived in project
Milestone
No milestone