2.16.3 Functions and Operator Limitations

• Bit functions and operators.

• CAST() AS SIGNED and UNSIGNED on temporal values.

  For supported CAST() operations, see Section 2.12.3, “Cast Functions and Operators”.

• COALESCE() as a JOIN predicate.

• CONVERT_TZ() with named time zones such as MET or Europe/Amsterdam.

  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

• Full-text search functions.

• XML, JSON, Spatial, and other domain specific functions.

• Loadable Functions.

• GROUP_CONCAT() with:

  • ORDER BY

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

  • WITH ROLLUP

  GROUP_CONCAT() is only supported in strict mode.

• GREATEST() and LEAST() functions with the YEAR type.

  GREATEST() and LEAST() do not support comparison between temporal and non-temporal types.

  DATE, DATETIME, TIME, and TIMESTAMP support was added in MySQL 8.0.30.

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

• Date functions on the YEAR type.

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

• In some cases, comparison functions with a mixture of string and non-string arguments due to HeatWave returning incorrect results.

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

• The following aggregate functions with enumeration, string, and temporal data types:

  • STD()

  • STDDEV()

  • STDDEV_POP()

  • STDDEV_SAMP()

  • SUM()

  • VAR_POP()

  • VAR_SAMP()

  • VARIANCE()

  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.