Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 2.0Mb
PDF (A4) - 2.0Mb


HeatWave User Guide  /  ...  /  Functions and Operator Limitations

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.

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

  • Loadable Functions.

  • GROUP_CONCAT() with:

  • 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.7.1, “Encoding String Columns”.

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

  • The following aggregate functions with the enumeration data type:

    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.