.. _version_5.3.2:

=============
Version 5.3.2
=============

Released on 2023-05-24.

.. WARNING::

    CrateDB 5.3.x versions up to :ref:`5.3.3 <version_5.3.3>` (excluding)
    contain a critical bug which can lead to data corruption/loss when using
    a column definition with a number data type and disabled index
    (``INDEX OFF``).
    It is not recommended to use those versions, use CrateDB >=
    :ref:`5.3.3 <version_5.3.3>` instead.

.. NOTE::

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

    We recommend that you upgrade to the latest 5.2 release before moving to
    5.3.1.

    A rolling upgrade from 5.2.x to 5.3.2 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.3.0` release notes for a full list of changes in the
5.3 series.

Fixes
=====

- Fixed an issue that would exclude empty partitioned tables from being listed
  in :ref:`sys.snapshots <sys-snapshots>`.

- Fixed a regression introduced in 5.3.0 that prevented the evaluation of
  ``DEFAULT`` clauses on children of ``OBJECT`` columns if the object was
  missing entirely from an ``INSERT INTO`` statement.

- Improved error message when providing
  :ref:`DEFAULT clause <sql-create-table-default-clause>` for columns of type
  ``OBJECT``.

- Fixed a regression introduced in 5.3.0 that could lead to ``INSERT INTO``
  statements with a ``ON CONFLICT`` clause to mix up values and target columns,
  leading to validation errors or storing the wrong values in the wrong columns.

- Fixed an issue that :ref:`LIKE and ILIKE <sql_dql_like>` operators would
  produce wrong results when the ``?`` is used in the pattern string, e.g.::

    SELECT * FROM tbl WHERE q ILIKE '%.com?apiPath%'

- Fixed an issue that would cause all tables within a
  :ref:`Snapshot <snapshot-restore>` to be restored, when trying to restore an
  empty partitioned table, e.g.::

    RESTORE SNAPSHOT repo1.snap1 TABLE empty_parted

- Fixed an issue with :ref:`Azure repositories<sql-create-repo-azure>`, which
  could lead to wrong results for queries to ``sys.snapshots``, create snapshots
  to a wrong repository, or drop snapshots from a wrong repository when more
  than 1 repositories are configured to the same CrateDB cluster.

- Fixed an issue that could lead to queries to become stuck instead of failing
  with a circuit breaker error if a node is under memory pressure.

- Improved an optimization rule to enable index lookups instead of table scans
  in more cases. This is a follow up to a fix in 5.2.7 which fixed a regression
  introduced in 5.2.3.

- Fixed an issue that caused ``DROP TABLE IF EXISTS`` to wrongly return ``1``
  row affected or ``SQLParseException`` (depending on user privileges), when
  called on an existent schema, a non-existent table and with the ``crate``
  catalog prefix, e.g.::

    DROP TABLE IF EXISTS crate.doc.non_existent_table

- Improved output representation of timestamp subtraction, by normalizing to
  bigger units, but no further than days, to be consistent with PostgreSQL
  behavior. e.g::

    SELECT '2022-12-05T11:22:33.123456789+05:30'::timestamp - '2022-12-03T11:22:33.123456789-02:15'::timestamp

  previously would return: ``PT40H15M`` and now returns: ``P1DT16H15M``.

- Improved error message for :ref:`date_bin <date-bin>` scalar function when the
  first argument of :ref:`INTERVAL data type <type-interval>` contains month
  and/or year units.

- Fixed an issue that allowed inserting a non-array value into a column that is
  dynamically created by inserting an empty array, ultimately modifying the type
  of the column. The empty arrays will be convert to ``nulls`` when queried. For
  example::

    CREATE TABLE t (o OBJECT);
    INSERT INTO t VALUES ({x=[]});
    INSERT INTO t VALUES ({x={}});  /* this is the culprit statement, inserting an object onto an array typed column */
    SHOW CREATE TABLE t;
    +-----------------------------------------------------+
    | SHOW CREATE TABLE doc.t                             |
    +-----------------------------------------------------+
    | CREATE TABLE IF NOT EXISTS "doc"."t" (              |
    |    "o" OBJECT(DYNAMIC) AS (                         |
    |       "x" OBJECT(DYNAMIC)  /* an array type modified to an object type */
    SELECT * FROM t;
    +-------------+
    | o           |
    +-------------+
    | {"x": {}}   |
    | {"x": null} |  /* an empty array converted to null */
    +-------------+

- Fixed an issue that caused ``AssertionError`` to be thrown when referencing
  previous relations, not explicitly joined, in an join condition, e.g.::

    SELECT * FROM t1
    CROSS JOIN t2
    INNER JOIN t3 ON t3.x = t1.x AND t3.y = t2

- Fixed an issue that caused the default expressions on columns of type
  ``GEO_SHAPE`` to be ignored on writes.

- Fixed a race condition issue while concurrently accessing S3 repositories
  with different settings, e.g. by queries against ``sys.snapshots``.

- Fixed an issue in a mixed cluster scenario that may cause incoming writes
  written on a node < ``5.3.0`` to fail when replicated to a node >= ``5.3.0``.
