2.18.3 Functions and Operator Limitations

• Bit functions and operators.

• COALESCE() as a JOIN predicate.

• CONVERT_TZ() with named time zones before MySQL 8.4.0. Only datetime values are supported. Rewrite queries that use named time zones with equivalent datetime values. For example:

  mysql> SELECT CONVERT_TZ(O_ORDERDATE, 'UTC','EST') FROM tpch.orders;

  Rewrite as:

  mysql> SELECT CONVERT_TZ(O_ORDERDATE, '+00:00','-05:00') FROM tpch.orders;

• Only UNIX_TIMESTAMP() and FROM_UNIXTIME() functions support a timezone value specified as an offset from UTC in the form of [H]H:MM and prefixed with a + or -. For example:

  mysql> SET time_zone = timezone;

  For information about time zone offsets, see MySQL Server Time Zone Support.

• Full-text search functions.

• XML, Spatial, and other domain specific functions.

• The following JSON functions:

  • JSON_ARRAY_APPEND()

  • JSON_ARRAY_INSERT()

  • JSON_INSERT()

  • JSON_MERGE()

  • JSON_MERGE_PATCH()

  • JSON_MERGE_PRESERVE()

  • JSON_REMOVE()

  • JSON_REPLACE()

  • JSON_SCHEMA_VALID()

  • JSON_SCHEMA_VALIDATION_REPORT()

  • JSON_SET()

  • JSON_TABLE()

• Loadable Functions.

• GROUP_CONCAT() with:

  • Dictionary-encoded string columns, RAPID_COLUMN=ENCODING=SORTED. See Section 2.14.2, “Dictionary Encoding”.

  • WITH ROLLUP

• A CASE control flow operator or IF() function that contains columns not within an aggregation function and not part of the GROUP BY key.

• String functions and operators on columns that are not VARLEN encoded. See Section 2.7.1, “Encoding String Columns”.

• The AVG() aggregate function with enumeration and temporal data types.

• The following aggregate functions with the enumeration data type:

  • STD()

  • STDDEV()

  • STDDEV_POP()

  • STDDEV_SAMP()

  • SUM()

  • VARIANCE()

  • VAR_POP()

  • VAR_SAMP()

  With the exception of SUM(), the same aggregate functions within a semi-join predicate due to the nondeterministic nature of floating-point results and potential mismatches. For example, the following use is not supported:

  mysql> SELECT FROM A WHERE a1 IN (SELECT VAR_POP(b1) FROM B);

  The same aggregate functions with numeric data types other than those supported by HeatWave. See Section 2.10, “Supported Data Types”.

• WEEK(date[,mode]) does not support the default_week_format system variable.

  To use the mode argument, the mode value must be defined explicitly.