.. _version_5.9.10:

==============
Version 5.9.10
==============

Released on 2025-02-12.

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

    We recommend that you upgrade to the latest 5.8 release before moving to
    5.9.10.

    A rolling upgrade from 5.8.x to 5.9.10 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.9.0` release notes for a full list of changes in the
5.9 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 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 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 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.
