11.6 Limitations

This section lists functionality that is not supported by HeatWave. It is not an exhaustive list with respect to data types, functions, operators, and SQL modes. For data types, functions, operators, and SQL modes that are supported by HeatWave, see Section 11.1, “Supported Data Types”, Section 11.2, “Supported Functions and Operators”, and Section 11.3, “Supported SQL Modes”. If a particular data type, function, operator, or SQL mode does not appear in those tables and lists, it should be considered unsupported.

• Functions:

  • Bit functions and operators.

  • CAST() AS SIGNED and UNSIGNED on temporal values. For supported CAST() operations, see Section 11.2.3, “Cast Functions and Operators”.

  • COALESCE() as a JOIN predicate.

  • Full-text search functions.

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

  • Encryption and compression functions.

  • Loadable Functions.

  • GREATEST() and LEAST() functions with temporal data type columns.

  • 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 3.3, “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 undeterministic nature of floating-point results and potential mismatches. For example, the following use is not supported:

    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 11.1, “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.

• Data types:

  • Spatial data types. See Spatial Data Types.

  • Decimal values with a precision greater than 18 in expression operations, with the exception of ABS() expression operations.

  • ENUM type columns as part of a UNION or non-top level UNION ALL SELECT list or as a JOIN key, except when used inside a supported expression.

  • ENUM type support is limited to:

    • Comparison with string or numeric constants, and other numeric, non-temporal expressions (numeric columns, constants, and functions with a numeric result).

    • Comparison operators (<, <=, <=>, =, >=, >, and BETWEEN) with numeric arguments.

    • Comparison operators (=, <=>, and <>) with string constants.

    • enum_col IS [NOT] {NULL|TRUE|FALSE}

    • The IN() function in combination with numeric arguments (constants, functions, or columns) and string constants.

    • COUNT(), SUM(), and AVG() aggregation functions on ENUM columns. The functions operate on the numeric index value, not the associated string value.

    • CAST(enum_col AS {[N]CHAR [(X)]|SIGNED|UNSIGNED|FLOAT|DOUBLE|DECIMAL [(M,N)]}). The numeric index value is cast, not the associated string value.

    • CAST(enum_col) AS {[N]CHAR} is supported only in the SELECT list and when it is not nested in another expression.

• Character sets and collations:

  • The gb18030_chinese_ci character set and collation.

• Variables:

  • time_zone and timestamp variable settings are not passed to HeatWave when queries are offloaded.

  • The sql_select_limit as a global variable. It is only supported as a session variable.

• JOIN types:

  • Antijoins, with the exception of supported IN and EXISTS antijoin variants listed below.

  • Implicit casting (query cast injection) of the YEAR type to other types. It can only be joined with itself.

  • Implicit casting (query cast injection) of the VARCHAR type to types other than DATETIME, TIMESTAMP, and DATE.

  • Temporal to numeric implicit casting (query cast injection). Therefore, temporal types cannot be joined with numeric types.

  • EXISTS semijoins and antijoins are supported in the following variants only:

    • SELECT ... WHERE ... EXISTS (...)

    • SELECT ... WHERE ... EXISTS (...) IS TRUE

    • SELECT ... WHERE ... EXISTS (...) IS NOT FALSE

    • SELECT ... WHERE ... NOT EXISTS (...) IS FALSE

    • SELECT ... WHERE ... NOT EXISTS (...) IS NOT TRUE

    Depending on transformations and optimizations performed by MySQL, other variants of EXISTS semijoins may or may not be offloaded.

  • IN semijoins and antijoins other than the following variants:

    • SELECT ... WHERE ... IN (...)

    • SELECT ... WHERE ... IN (...) IS TRUE

    • SELECT ... WHERE ... NOT IN (...) IS FALSE

    Depending on transformations and optimizations performed by MySQL, other variants of IN semijoins may or may not be offloaded.

  • A query with a supported semijoin or antijoin condition may be rejected for offload due to how MySQL optimizes and transforms the query.

  • Semijoin and antijoin queries use the best plan found after evaluating the first 10000 possible plans, or after investigating 10000 possible plans since the last valid plan. The plan evaluation count is reset to zero after each derived table, after an outer query, and after each subquery. The plan evaluation limit is required because the DUPSWEEDOUT join strategy, which is not supported by HeatWave, may be used as a fallback strategy by MySQL during join order optimization (for related information, see FIRSTMATCH). The plan evaluation limit prevents too much time being spent evaluating plans in cases where MySQL generates numerous plans that use the DUPSWEEDOUT semijoin strategy.

  • Outer join queries without an equality condition defined for the two tables.

• Index and optimizer hints. See Index Hints, and Optimizer Hints.

  Semijoin strategies other than FIRSTMATCH. MySQL attempts to enforce the FIRSTMATCH strategy and ignores all other semijoin strategies specified explicitly as subquery optimizer hints. However, MySQL may still select the DUPSWEEDOUT semijoin strategy during JOIN order optimization, even if an equivalent plan could be offered using the FIRSTMATCH strategy. (A plan that uses the DUPSWEEDOUT semijoin strategy would produce incorrect results if executed on HeatWave.)

  For general information about subquery optimizer hints, see Subquery Optimizer Hints.

• SQL modes:

  • Most non-default MySQL DB System SQL modes. For a list of supported SQL modes, see Section 11.3, “Supported SQL Modes”.

• Other:

  • The WITH ROLLUP modifier in GROUP BY clauses in the following cases:

    • In queries that contain distinct aggregations.

    • In queries that contain duplicate GROUP BY keys.

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

  • UNION ALL queries with an ORDER BY or LIMIT clause, between different column types, between dictionary-encoded columns, or between ENUM columns.

    UNION queries with or without an ORDER BY or LIMIT clause, between different column types, between dictionary-encoded columns, or between ENUM columns.

    UNION and UNION ALL subqueries with or without an ORDER BY or LIMIT clause, between different column types, between dictionary-encoded columns, between ENUM columns, or specified in an IN or EXISTS clause.

  • Comparison predicates, GROUP BY, JOIN, and so on, if the key column is DOUBLE PRECISION.

  • Type conversion on relational data. For example, SELECT CONCAT(2, L_COMMENT) from LINEITEM; is not supported.

  • Queries with an impossible WHERE condition (queries known to have an empty result set). For example, the following query is not offloaded:

    SELECT AVG(c1) AS value FROM t1 WHERE c1 IS NULL;

  • Querying of YEAR type data using expressions and other functions. For example, the following queries are not offloaded:

    SELECT YEAR(d) + 1 FROM t1;
     
    SELECT YEAR(d) + c1 FROM t1; # where c1 is an integer column

  • String operations involving columns with different collations.

  • Explicit partition selection. See Partition Selection.

  • Primary keys with column prefixes.

  • Virtual generated columns.

  • Queries that are executed as part of a trigger.

  • Queries that call a stored program.

  • Queries that are executed as part of a stored program.

  • Queries that are part of a multi-query transaction.

  • Views.

    CREATE VIEW ... AS SELECT queries. Setting use_secondary_engine=FORCED does not cause the statement to fail with an error. The statement is executed on the MySQL Database Service instance regardless of the use_secondary_engine setting.

  • Partial query offload for regular SELECT queries. If all elements of the query are supported, the entire query is offloaded; otherwise, the query is executed on the MySQL DB System by default. (HeatWave supports CREATE TABLE ... SELECT and INSERT ... SELECT statements where only the SELECT portion of the operation is offloaded to HeatWave. See Chapter 6, Running Queries.)

  • SET timezone = timezone, with the timezone value specified as a an offset from UTC in the form of [H]H:MM and prefixed with a + or - is supported only by the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions. Named time zones are not supported. For information about time zone offsets, see MySQL Server Time Zone Support.

  • Row widths in intermediate and final query results that exceed 4MB in size. A query that exceeds this row width limit is not offloaded to HeatWave for processing.