Version 5.10.0

Released on 2025-01-21.

Note

If you are upgrading a cluster, you must be running CrateDB 4.0.2 or higher before you upgrade to 5.10.0.

We recommend that you upgrade to the latest 5.9 release before moving to 5.10.0.

A rolling upgrade from 5.9.x to 5.10.0 is supported. Before upgrading, you should back up your data.

Warning

Tables that were created before CrateDB 4.x will not function with 5.x and must be recreated before moving to 5.x.x.

You can recreate tables using COPY TO and COPY FROM or by inserting the data into a new table.

Table of contents

Breaking Changes

  • Accessing a key of an object type expression of a sub select may fail now with an ColumnUnknownException even if the key is present in the evaluated expressions value. This is related to the change to make error handling of DYNAMIC and STRICT objects more consistent across all cases. One popular case affected by this is a sub-select with a JSON to OBJECT cast where the JSON structure is not visible during analysis. For example:

    SELECT myobj['x'] FROM (SELECT '{"x":1}'::OBJECT myobj) t;

    will now throw a ColumnUnknownException. This can be solved by:

  • defining the inner types while casting:

    SELECT myobj['x'] FROM (SELECT '{"x":1}'::OBJECT AS (x INT) myobj) t;

  • changing the column policy to IGNORED:

    SELECT myobj['x'] FROM (SELECT '{"x":1}'::OBJECT(IGNORED) myobj) t;

  • or by disabling the error on unknown object keys for DYNAMIC objects:

    SET SESSION error_on_unknown_object_key = false;

Deprecations

None

Changes

SQL Statements

  • CREATE TABLE no longer persists default values for all available table options but instead only stores the explicitly defined options. As a result, SHOW CREATE TABLE will only show the explicitly defined options in the WITH clause. An exception are the column_policy and number_of_replicas options, which always show up. To get a complete view of the effective options you can query the information_schema.tables table.

  • Added support for GROUP BY ALL clause which allows grouping by all output columns that are not aggregate functions without explicitly listing them, e.g.:

    SELECT department, title, AVG(salary) as avg_salary
FROM employees
GROUP BY ALL;

    This is equivalent to:

    SELECT department, title, AVG(salary) as avg_salary
FROM employees
GROUP BY department, title;

  • Added support for the ALTER SERVER statement to change the options of an existing foreign server.

SQL Standard and PostgreSQL Compatibility

  • Added CHAR and “CHAR” to the list of non-reserved keywords e.g. to allow using it as an alias in a query:

    SELECT 'foo' AS "CHAR";

Data Types

Scalar and Aggregation Functions

None

Performance and Resilience Improvements

  • Added hash-join execution for left/right-outer-equi-joins. This improves performance for left/right-outer-join with an equi-join condition significantly with the cost of higher memory consumption e.g.:

    SELECT * FROM t1 LEFT JOIN t2 OM t1.id = t2.id;

    This optimization can be disabled, with the session settings:

    SET rewrite_left_outer_join_to_hash_join = false
SET rewrite_right_outer_join_to_hash_join = false

    Note that these settings are experimental, and may change in the future.

  • Reduced storage requirements for new tables and partitions. CrateDB no longer permanently retains a complete json representation of a row, and instead always retrieves data from other data structures where they exist. Columns that can not be perfectly reconstructed from other data structures, for example arrays of numeric data which are sorted and deduplicated by the built-in column store, are stored separately in a more efficient format.

    The json representation of a row is still stored initially as part of the transaction log for a shard. It will be removed naturally by merging as indexing continues once the row has been successfully replicated to all shards, or it can be removed explicitly by an OPTIMIZE operation after indexing to a table or partition has completed.

  • Lowered the default max_concurrency and queue_size Overload Protection values, this should help cluster stability without slowing down operations.

Administration and Operations

  • Added caching for public keys retrieved from JWK endpoints for JWT authentication. See JWT authentication method for more details.

  • Added node settings for the JWT Based Authentication allowing to provide global values for the JWT properties.

  • Improved error handling for unknown object keys of DYNAMIC or STRICT objects. For example:

    CREATE TABLE tbl (obj OBJECT(STRICT) AS (x INT));
SELECT obj['unknown'] FROM (SELECT obj FROM tbl) AS t;

    now throws a ColumnUnknownException as expected.

  • Updated statement_timeout setting to also account for parsing, analysis and planning phases.

  • Added a policy sub-column to column_details in the information_schema.columns table.

Client interfaces

  • Added an :error payload to failed bulk responses issued over the :ref`interface-http` containing details of the error that caused the bulk operation to fail.