.. _version_4.2.0:

=============
Version 4.2.0
=============

Released on 2020-07-07.

.. NOTE::

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

    We recommend that you upgrade to the latest 4.1 release before moving to
    4.2.0.

    A rolling upgrade from 4.1.7+ to 4.2.0 is supported.

    Before upgrading, you should `back up your data`_.

.. _back up your data: https://crate.io/docs/crate/reference/en/latest/admin/snapshots.html

.. rubric:: Table of Contents

.. contents::
   :local:


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

- Changed the logic how the ``array_unique`` :ref:`scalar function
  <scalar-functions>` infers the argument types.  Previously if the arguments
  had a different type it used the type of the first argument. For example::

    cr> select array_unique(['1'], [1.0, 2.0]);
    +---------------------------------+
    | array_unique(['1'], [1.0, 2.0]) |
    +---------------------------------+
    | ["1", "1.0", "2.0"]             |
    +---------------------------------+

  This logic has been changed to instead use a type precedence logic to be
  consistent with how other :ref:`functions <gloss-function>` behave::

    cr> select array_unique(['1'], [1.0, 2.0]);
    +------------+
    | [1.0, 2.0] |
    +------------+
    | [1.0, 2.0] |
    +------------+

- Bulk ``INSERT INTO ... VALUES (...)`` statements do not throw an exception
  any longer when one of the bulk operations fails. The result of the execution
  is only available via the ``results`` array represented by a row count for
  each bulk operation.

- Numeric literals fitting into the ``integer`` range are now treated as
  ``integer`` literals instead of ``bigint`` literals. Thus a statement like
  ``select 1`` will return an ``integer`` column type. This shouldn't be an
  issue for most clients as the ``HTTP`` endpoint uses ``JSON`` for
  serialization and PostgreSQL clients usually use a typed ``getLong``.

  A consequence of this change is that arithmetic with literals used in
  statements will also happen within the ``integer`` range, and it can lead to
  an ``integer overflow`` if the result does not fit into the ``integer``
  range. For example, a calculation like ``365 * 24 * 60 * 60 * 1000`` needs to
  be explicitly cast into the ``bigint`` range to avoid an ``integer
  overflow``. This can be done like this: ``365::bigint * 24 * 60 * 60 *
  1000``. If working with timestamps it is recommended to instead use the more
  readable ``interval`` type instead: ``'1 year'::interval``.

- In the PostgreSQL Wire Protocol, ``ReadyForQuery`` messages now contain the
  ``IN TRANSACTION`` or ``FAILED TRANSACTION`` indicator based on previous
  ``BEGIN`` and ``COMMIT`` SQL statements. Before this change, the status
  always defaulted to ``IDLE``. This change may have the side-effect that an
  explicit ``rollback`` call in a client library will result in an unsupported
  ``ROLLBACK`` statement.


Deprecations
============

- The ``index.warmer.enabled`` setting has been deprecated and doesn't have any
  effect anymore.


Changes
=======


Administration
--------------

- The JavaScript :ref:`user-defined function <user-defined-functions>` language
  is now enabled by default in the CrateDB enterprise edition.

- Added the :ref:`optimizer <conf-session-optimizer>` session setting to
  configure query optimizer rules.

- Include the bundled version of ``OpenJDK`` (14.0.1+7) into the ``CrateDB``
  built. It means that ``CrateDB`` doesn't rely on ``JAVA_HOME`` of the host
  system any longer.

- Increased the default interval to detect ``keystore`` or ``truststore``
  changes to five minutes.

- Added a ``tables`` column to the :ref:`sys.snapshots <sys-snapshots>` table
  which lists the fully qualified name of all tables contained within the
  snapshot.

- Limit the output of ``COPY FROM RETURN SUMMARY`` in the presence of errors to
  display up to 50 ``line_numbers`` to avoid buffer pressure at clients and to
  improve readability.


SQL Standard and PostgreSQL compatibility improvements
------------------------------------------------------

- Added :ref:`scalar function <scalar-functions>` :ref:`CURRENT_TIME
  <scalar-current_time>`, that returns the system's time as microseconds since
  midnight UTC, at the time the SQL statement is handled. The actual return
  type is the new data type :ref:`timetz <type-timestamp-with-tz>`.

- Added new type :ref:`time with time zone <type-timestamp-with-tz>`, a.k.a ``timetz``,
  which is to be used as return type for :ref:`time related functions
  <scalar-date-time>` such as the future ``current_time``.

- Added the :ref:`type-oidvector` data type which is used in some
  :ref:`postgres-pg_catalog` tables.

- Added the :ref:`type-regproc` alias data type that is used to reference
  :ref:`functions <gloss-function>` in the :ref:`postgres-pg_catalog` tables.

- Added the :ref:`varchar(n) and character varying(n) <data-type-varchar>`
  types, where ``n`` is an optional length limit.

- Added the :ref:`server_version_num <conf-session-server_version_num>` and
  :ref:`server_version <conf-session-server_version>` read-only session
  settings.

- Added the :ref:`pg_catalog.pg_proc <postgres-pg_catalog>` table.

- Added the :ref:`pg_catalog.pg_range <postgres-pg_catalog>` table.

- Added the :ref:`pg_catalog.pg_enum <postgres-pg_catalog>` table.

- Added the :ref:`information_schema.character_sets <character_sets>` table.

- Added :ref:`postgres-pg_type` columns: ``typbyval``, ``typcategory``,
  ``typowner``, ``typisdefined``, ``typrelid``, ``typndims``, ``typcollation``,
  ``typinput``, ``typoutput``, and ``typndefault`` for improved PostgreSQL
  compatibility.

- Added support for ``JOIN USING``, e.g. ``SELECT * FROM t1 JOIN t2 USING
  (col)``, an alternative to ``JOIN ON``, when the column name(s) are the same
  in both relations.

- Added entries for primary keys to ``pg_class`` and ``pg_index`` table.

- Added support for :ref:`record subscript <sql-record-subscript>` syntax as
  alternative to the existing :ref:`object subscript <sql-object-subscript>`
  syntax.

- Added support for using columns of type ``long`` inside :ref:`subscript
  expressions <sql-subscripts>` (e.g., ``array_expr[column]``).

- Made :ref:`generate_series <table-functions-generate-series>` addressable by
  specifying the ``pg_catalog`` schema explicitly. So, for example, both
  ``generate_series(1, 2)`` and ``pg_catalog.generate_series(1, 2)`` are valid.

- Added support for the PostgreSQL notation to refer to array types. For
  example, it is now possible to use ``text[]`` instead of ``array(test)``.

- Added support for ``GROUP BY`` operations on analysed columns of type
  ``text``.


Functions and operators
~~~~~~~~~~~~~~~~~~~~~~~

- Fixed :ref:`arithmetics <arithmetic>` containing a non-floating numeric
  column type and a floating literal which resulted wrongly in a non-floating
  return type.

- Replaced the ``Nashorn`` JavaScript engine with ``GraalVM`` for JavaScript
  :ref:`user-defined functions <user-defined-functions>`. This change upgrades
  ``ECMAScript`` support from ``5.1`` to ``10.0``.

- Added the :ref:`chr <scalar-chr>` :ref:`scalar function <scalar-functions>`.

- Added :ref:`length <scalar-length>` and :ref:`repeat <scalar-repeat>` scalar
  functions.

- Added the :ref:`array_agg <aggregation-array-agg>` :ref:`aggregation function
  <aggregation-functions>`.

- Added the :ref:`trunc <scalar-trunc>` scalar function.

- Added the :ref:`now <scalar-now>` scalar function.

- Added a ``mod`` alias for the :ref:`modulus <scalar-modulus>` function for
  improved PostgreSQL compatibility.

- Added the :ref:`atan2 <scalar-atan2>` trigonometric scalar function.

- Added the :ref:`exp <scalar-exp>` scalar function.

- Added the :ref:`degrees <scalar-degrees>` and :ref:`radians <scalar-radians>`
  scalar functions.

- Added support for using :ref:`table functions <table-functions>` with more
  than one column within the select list part of a ``SELECT`` statement.

- Added the :ref:`cot <scalar-cot>` trigonometric scalar function.

- Added the :ref:`pi <scalar-pi>` scalar function.

- Added a ``ceiling`` alias for the :ref:`ceil <scalar-ceil>` function for
  improved PostgreSQL compatibility.

- Added the :ref:`encode(bytea, format) <scalar-encode>` and :ref:`decode(text,
  format) <scalar-decode>` scalar functions.

- Added the :ref:`ascii <scalar-ascii>` scalar function.

- Added the :ref:`obj_description(integer, text) <scalar-obj_description>` scalar
  function for improved PostgreSQL compatibility.

- Added the :ref:`format_type(integer, integer) <scalar-format_type>` scalar function
  for improved PostgreSQL compatibility.

- Added the :ref:`version() <scalar-version>` system information scalar function.


New statements and clauses
--------------------------

- Extended the supported syntax for :ref:`SET TRANSACTION
  <ref-set-transaction>`.

- Added the :ref:`DISCARD <discard>` statement.

- Added the :ref:`CHECK <check_constraint>` constraint syntax, which specifies
  that the values of certain columns must satisfy a :ref:`boolean expression
  <sql-literal-value>` on insert and update.

- Introduced new optional ``RETURNING`` clause for :ref:`INSERT <sql-insert>`
  and :ref:`UPDATE <ref-update>` to return specified values from each row
  written.


Performance improvements
------------------------

- Optimized ``<column> IS NOT NULL`` queries.
