.. _version_5.10.1:

==============
Version 5.10.1
==============

Released on 2025-02-13.

.. NOTE::

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

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

    A rolling upgrade from 5.9.x to 5.10.1 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`_.

.. _back up your data: https://crate.io/docs/crate/reference/en/latest/admin/snapshots.html
.. _inserting the data into a new table: https://crate.io/docs/crate/reference/en/latest/admin/system-information.html#tables-need-to-be-recreated

.. rubric:: Table of contents

.. contents::
   :local:

See the :ref:`version_5.10.0` release notes for a full list of changes in the
5.10 series.

Fixes
=====

- Fixed a recovery issue that led to records of a table with an ``OBJECT``
  sub-column being part of a standalone ``FULLTEXT`` index to be never visible
  for ``MATCH`` queries. This issue cannot be mitigated and a full re-index of
  the data is required.

- Fixed an issue that would lead to errors when attempting to select a
  sub-column from a virtual table or view participating in a join, e.g.::

    SELECT vw1.col2['arr']
    FROM (SELECT * FROM tbl1) as vw1
    INNER JOIN tbl2 on vw1.col1 = tbl2.col1
    LIMIT 10;

- Fixed a regression from ``5.10.0`` that could get ``COPY FROM`` statements to
  get stuck if used with ``RETURN SUMMARY``

- Fixed a regression from ``5.10.0`` that caused a ``ColumnUnknownException``
  when accessing a child of an object literal from an aliased table.

- Fixed an issue that could lead to a ``Scale of numeric must be less than the
  precision`` error when using the ``AVG`` aggregation in a way that involved
  values of type ``NUMERIC``.

- Fixed an issue that could cause data loss if increasing the number of shards
  using ``ALTER TABLE ... SET (number_of_shards = ?)`` while having other
  allocation settings in place that prevent the shard allocation.

- Fixed an issue that could cause duplicate records to be returned when
  filtering on a table by PRIMARY KEY, using query parameters, and binding the
  same value to them, e.g.::

    SELECT * FROM tbl WHERE pk_col = ? OR pk_col = ?
    -- Bind the same value to both query parameters

- Fixed a handshake version compatibility issue that causes a node with a
  higher major version (e.g. ``6.x``) to fail to join a cluster of version
  < :ref:`version_5.10.1` (rolling upgrade scenario).

- Fixed an issue that caused an error when using a PreparedStatement and
  selecting an expression, involving a parameter and a column used in
  ``ORDER BY`` and query had a ``LIMIT`` clause. Example.::

    SELECT a, b + ? as sum FROM tbl ORDER BY sum LIMIT 10

- Fixed an issue that caused an error when using a correlated sub-query and
  a normal subquery on the same level together with an additional sub-query on
  an outer level. Example::

    WITH tbl AS (SELECT 1 as x)
    SELECT (
      SELECT x FROM tbl
      WHERE t.x = tbl.x
        AND tbl.x IN (SELECT x FROM unnest([1]))
    ),
    (SELECT y FROM unnest([2]) as t2(y))
    FROM tbl t

- Fixed an issue that caused a planner exception when using a query containing
  an ``ORDER BY`` clause on top of a ``UNION`` clause, while the ordering column
  is not part of the most-top select items. Example::

    SELECT id FROM (
        SELECT id, other_id, name FROM users
        UNION ALL
        SELECT id, other_id, name FROM users
        ) u
    ORDER BY name

- Fixed an issue that would lead to wrong results when using
  :ref:`MIN <aggregation-min>` or :ref:`MAX <aggregation-max>` aggregations on
  :ref:`numeric <type-numeric>` values. Previously, the values where implicitly
  casted to ``STRING`` and the aggregations where using alphanumeric ordering,
  instead of numeric ordering.

- Fixed an issue that led to ``ArithmeticException`` when using
  :ref:`AVG <aggregation-avg>` with ``NUMERIC`` type and result was an infinite
  fraction, like 1/3.

- Fixed an issue that would lead to returning a default name for
  :ref:`PRIMARY KEY constraint<primary_key_constraint>` in
  :ref:`information_schema_key_column_usage` and ``pg_class`` tables even if a
  custom name was explicitly provided during table creation.

- Fixed an issue that would lead to returning a different default name for
  :ref:`PRIMARY KEY constraint<primary_key_constraint>` in
  :ref:`information_schema_key_column_usage` and ``pg_class`` tables,
  ``<table_name>_pk`` and ``<table_name>_pkey`` respectively, when a custom
  name is not explicitly provided during table creation.

- Fixed an issue that would cause :ref:`array_position<scalar-array_position>`
  to return wrong results when used on a column with NULL values in the
  ``WHERE`` combined with a ``NOT`` predicate. e.g.::

    SELECT * FROM tbl WHERE NOT array_position(string_array_col, 'foo');

- Fixed a regression introduced with :ref:`version_5.6.0` that caused any
  partitioned table contained inside the snapshot to be restored, if not exists,
  by the :ref:`sql-restore-snapshot` statement when only a concrete table was
  specified as to be restored. Only the partitioned table definition was falsely
  restored, but not the the actual data.

- Fixed a regression introduced with :ref:`version_5.10.0` that would lead to
  wrong results for ``LEFT JOIN`` queries when the join condition has a
  :ref:`CASE<scalar-case-when-then-end>` with an equality condition inside.
  Previously, the optimizer would use :ref:`Hash Join<available-join-algo_hash>`
  instead of :ref:`Nested Loop<available-join-algo_nl>` leading to wrong
  results. e.g.::

    SELECT * FROM tlb
    LEFT JOIN (SELECT 2 AS col2 FROM tbl) AS tbl2
      ON (CASE t0.c0 WHEN sub0.col2 THEN t0.c1 ELSE t0.c1 END)
