2.19.3 Functions and Operator Limitations

• As of MySQL 9.2.2, the maximum output width of the following functions is 65,332 bytes:

  • JSON_ARRAYAGG()

  • JSON_OBJECTAGG()

  • JSON_ARRAY()

  • JSON_OBJECT()

  • JSON_EXTRACT()

  • GROUP_CONCAT()

  • SPACE() when the input is a floating point.

  • SOUNDEX()

  • UNCOMPRESS()

  • WEIGHT_STRING()

• 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.

• COUNT(NULL) in cases where it is used as an input argument for non-aggregate operators.

• All known limitations for COUNT(DISTINCT) also apply to HLL().

• Full-text search functions.

• XML, Spatial, and other domain specific functions.

• The following JSON functions:

  • JSON_TABLE() queries that include a LEFT JOIN clause require a primary key column specified for the base or intermediate table. Without a primary key column, these queries are not offloaded.

• Loadable Functions.

• GROUP_CONCAT() with:

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

  • As of MySQL 9.0.1-u1, the GROUP_CONCAT() function supports CUBE and ROLLUP non-primitive GROUP BY transformation options.

  • As of MySQL 9.0.1-u1 COUNT(DISTINCT) function supports CUBE, ROLLUP and WITH ROLLUP options.

• COUNT(DISTINCT) with:

  • As of MySQL 9.0.1-u1 COUNT(DISTINCT) function supports CUBE, ROLLUP and WITH ROLLUP options.

• 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.8.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.11, “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.