.. _version_5.2.0:

=============
Version 5.2.0
=============

Released on 2023-01-12.

.. NOTE::

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

    We recommend that you upgrade to the latest 5.1 release before moving to
    5.2.0.

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

.. _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:


Breaking Changes
================

- Removed support for the ``CRATE_INCLUDE`` environment variable from the
  ``bin/crate`` start script.
  Configuration of CrateDB should happen via the ``crate.yml``, the
  ``CRATE_HEAP_SIZE`` environment variable and optionally ``CRATE_JAVA_OPTS``.

- Removed support for the ``-d`` and ``-p`` options from the ``bin/crate`` start
  script. It's recommended to run CrateDB either via a container runtime like
  Docker, or via a service manager like ``systemd`` where these options are not
  required.

- Subtraction of timestamps was returning their difference in milliseconds, but
  with result type ``TIMESTAMP`` which was wrong and led to issues with several
  PostgreSQL compliant clients. Instead of just fixing the result type, and
  change it to ``LONG``, the subtraction of timestamps was changed to return an
  ``INTERVAL`` and be 100% compliant with PostgreSQL behaviour.

  Before::

    SELECT '2022-12-05T11:22:33.123456789'::timestamp - '2022-11-21T10:11:22.0012334'::timestamp;
    +-----------------------+
    | 1213871122::timestamp |
    +-----------------------+
    |            1213871122 |
    +-----------------------+


  After::

    SELECT '2022-12-05T11:22:33.123456789'::timestamp - '2022-11-21T10:11:22.0012334'::timestamp;
    +------------------------------+
    | 'PT337H11M11.122S'::interval |
    +------------------------------+
    | 337:11:11.122                |
    +------------------------------+

  To use the previous behaviour, timestamps can simply be cast to longs before
  subtracting them::

    SELECT (ts_end::long - ts_start::long) FROM test

  Alternatively, epoch can be extracted from the result of the subtraction::

    SELECT EXTRACT(epoch FROM ts_end - ts_start) FROM test


Changes
=======

SQL Statements
--------------

- Added support for adding multiple columns in a single
  :ref:`ALTER TABLE ADD COLUMN <sql-alter-table-add-column>` statement.

- Extended the syntax for ``CREATE VIEW`` to allow parenthesis surrounding the
  query.


SQL Standard And PostgreSQL Schema Compatibility
------------------------------------------------

- Bumped the version of PostgreSQL wire protocol to ``11`` since ``10`` has been
  deprecated.

- Added :ref:`has_database_privilege <scalar-has-database-priv>` scalar function
  which checks whether user (or current user if not specified) has specific
  privilege(s) for the database.

- Added a :ref:`datestyle <conf-session-datestyle>` session setting that shows
  the display format for date and time values. Only the ``ISO`` style is
  supported. Optionally provided pattern conventions for the order of date
  parts (Day, Month, Year) are ignored.

- Added support for ``SCROLL`` and backward movement to cursors. See
  :ref:`DECLARE <sql-declare>` and :ref:`FETCH <sql-fetch>`.

- Added the :ref:`MAX_BY <aggregation-max_by>` and :ref:`MIN_BY
  <aggregation-min_by>` aggregation functions

- Added support for :ref:`bit operators <bit-operators>` on integral and
  ``BIT`` types.

- Added support for dollar quoted strings,
  see :ref:`String Literal <string_literal>` for further details.

- ``cancel`` messages sent from a client via the PostgreSQL wire protocol are
  now internally forwarded to other nodes to support setups with load-balancers.

- Added support for :ref:`SUM() <aggregation-sum>` aggregations on
  :ref:`INTERVAL type <type-interval>`. e.g.::

    SELECT SUM(tsEnd - tsStart) FROM test


Scalar Functions
----------------

- Added the :ref:`concat(object, object) <scalar-concat-object>` scalar function
  which combines two objects into a new object containing the union of their
  first level properties, taking the second object's values for duplicate
  properties.

- Added the :ref:`parse_uri(text) <scalar-parse_uri>` scalar function which
  parses a valid URI string into an ``object`` containing the URI components,
  making it easier to query them.

- Added the :ref:`parse_url(text) <scalar-parse_url>` scalar function which
  parses a valid URL string into an ``object`` containing the URL components,
  including parsed query parameters, making it easier to query them.

- Added support for :ref:`EXTRACT(field FROM interval) <scalar-extract>`.
  e.g.::

    SELECT EXTRACT(MINUTE FROM INTERVAL '49 hours 127 minutes')


Performance Improvements
------------------------

- Improve performance of
  :ref:`snapshots related operations <snapshot-restore>`.

Administration and Operations
-----------------------------

- Added ``attributes`` column to :ref:`sys.nodes <sys-nodes>` table to expose
  :ref:`custom node settings <conf-node-attributes>`.

- Exposed the ``require``, ``include`` and ``exclude`` ``routing.allocation``
  settings per partition within
  :ref:`information_schema.table_partitions <is_table_partitions>`.

- Updated to Admin UI 1.24.1, which added Italian translations, updated some
  dependency packages across the board, and its tool chain.
