MySQL :: HeatWave User Guide :: 10.6 Limitations

Functions:

Bit functions and operators.
COALESCE() as a JOIN predicate.
Window functions.
Full-text search functions.
XML, JSON, Spatial, and other domain specific functions.
Encryption and compression functions.
Loadable Functions.
LAST_INSERT_ID()
FOUND_ROWS()
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.
The EXTRACT() function with a temporal interval unit.
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:
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 10.1, “Supported Data Types”.

Data types:

BINARY
BIT
BLOB
JSON
VARBINARY
Spatial data types. See Spatial Data Types.
The DECIMAL type with precision greater than 18 with 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 the following:
- 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.

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 10.3, “Supported SQL Modes”.

Other:

Multiple instances of COUNT(DISTINCT value), SUM(DISTINCT value), AVG(DISTINCT value) expressions in a query are permitted only if they specify the same value.
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.
A mix of UNION and UNION ALL at the same level in a query.
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.
Concurrent processing of queries. HeatWave processes one query at a time. Concurrently issued queries are queued internally and processed in the order that they arrive.
Partial query offload. Queries are offloaded to HeatWave entirely or not at all. 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.
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.