.. _version_5.6.3:

=============
Version 5.6.3
=============


Released on 2023-03-22.

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

    We recommend that you upgrade to the latest 5.5 release before moving to
    5.6.3.

    A rolling upgrade from 5.5.x to 5.6.3 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.6.0` release notes for a full list of changes in the
5.6 series.

Fixes
=====

- Fixed an issue in filter-push-down for joins which prevented constant join
  conditions to be pushed-down in hash-joins.

- Fixed an issue that could cause ``SELECT`` statements on virtual tables or
  views and a ``WHERE`` clause to run with an expensive execution plan and not
  utilize the table's indices due to the ``merge_filter_and_collect`` optimizer
  rule not being applied.

- Fixed an issue that could cause ``SELECT`` statements to ignore a ``WHERE``
  clause if it involved views or virtual tables, and unused columns containing a
  window function.

- Fixed an issue that could cause ``SHOW CREATE TABLE`` on a missing table to
  mention similarly named tables, despite the user not having any permissions on
  those tables.

- Fixed an issue that caused wrong results to be returned for queries for which
  the ``WHERE`` clause includes a :ref:`PK <primary_key_constraint>` column and
  a non-:ref:`PK <primary_key_constraint>` column under an ``OR`` operator,
  e.g.::

    SELECT * FROM tbl WHERE pk_col = 1 OR other_col = 'foo'

- Fixed several issues with PostgreSQL style :ref:`INTERVALS <type-interval>`:

  - Duplicate definitions now raise an error::

     SELECT '1 year 2 years'::INTERVAL

  - Units next to values are now supported without whitespace separation::

     SELECT '1year 3day'::INTERVAL

  - Weeks were ignored if days were also present in the string::

     SELECT '2 weeks 3 days'::INTERVAL

  - Made the parsing of the interval units strict. Before using units like
    ``yearrr`` was allowed, now it raises an error.

  - Made the interval units case-insensitive. The following used to raise an
    error::

      SELECT '2 WEEKS'::INTERVAL

- Fixed an issue that caused ``CrateDB`` to fail to notify client applications
  connecting via :ref:`PostgreSQL Wire Protocol <interface-postgresql>` that
  :ref:`standard_conforming_strings <conf-session-standard_conforming_strings>`
  is set to ``on`` which caused the clients to treat all query strings as non
  standard conforming.

- Fixed ``NullPointerException`` thrown when joining tables with ``USING``
  clause which contains columns not existing in either or both tables.

- Fixed an issue that caused an inner join with a ``WHERE`` clause containing
  a ``CASE`` expression to return invalid results.

- Fixed an issue that caused an inner join query to throw an
  ``UnsupportedFeatureException`` when the join condition contains columns from
  more than one table and the ``WHERE`` clause contains an expression better
  suited as the join condition e.g.::

    SELECT * FROM t1 INNER JOIN t2 ON (t1.a = t2.b + t1.a) WHERE t1.a = t2.b + 1
