MySQL :: MySQL HeatWave User Guide

MySQL HeatWave User Guide / HeatWave / Limitations

2.16 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 2.9, “Supported Data Types”, Section 2.11, “Supported Functions and Operators”, and Section 2.10, “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 2.11.3, “Cast Functions and Operators”.
- COALESCE() as a JOIN predicate.
- CONVERT_TZ() with named timezones such as 'MET' or 'Europe/Amsterdam'. Only datetime values are supported. A query that uses named timezones can be rewritten using equivalent datetime values; for example, SELECT CONVERT_TZ(O_ORDERDATE, 'UTC','EST') FROM tpch.orders can be rewritten as SELECT CONVERT_TZ(O_ORDERDATE, '+00:00','-05:00') FROM tpch.orders.
- MySQL Enterprise Data Masking and De-Identification and De-Identification Functions
- 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 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:
  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 2.9, “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.
- A time_zone setting other than SYSTEM (+00:00) is not supported when propagating changes to tables containing TIMESTAMP columns. Propagating changes in this scenario causes incorrect data to be stored in TIMESTAMP columns, leading to incorrect query results.
- 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.
- Some outer join queries with IN ... EXISTS sub-queries (semi-joins) in the ON clause.
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 2.10, “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.
- Materialized views. Only nonmaterialized views are supported. See Using Views.
- UNION queries executed on a view.
- 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 Section 2.3, “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.
- A time_zone setting other than SYSTEM (+00:00) is not supported when propagating changes to tables containing TIMESTAMP columns. Propagating changes in this scenario causes incorrect data to be stored in TIMESTAMP columns, leading to incorrect query results.
- 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.
- Consecutive filter operations on derived tables. For example, the following query is not supported:
```
SELECT * FROM (SELECT * FROM t1 WHERE x < 7) tt1,
              (SELECT * FROM t1 WHERE x < y) tt2
WHERE  tt1.x > 5 and tt1.x = tt2.x;
```
  The query uses a filter for table tt1 in the table scan of table t1 (x < 7) followed by a consecutive filter on table tt1 (tt1.x > 5) in the WHERE clause.
- UNION ALL statements inside derived table expressions or common table expressions. For example, the following query, which defines a UNION ALL clause within WITH clause, is not supported.
```
WITH 
   cte1 AS (SELECT a, b FROM table1 UNION ALL (SELECT a, b FROM table2 )), 
   cte2 AS (SELECT c, d FROM table2) 
SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;
```
- Recursive common table expressions.
- Operations involving ALTER TABLE such loading, unloading, or recovering data when MySQL Server is running in SUPER_READ_ONLY mode. MySQL Server is placed in SUPER_READ_ONLY mode when MySQL Server disk space drops below a set amount for a specific duration. For information about thresholds that control this behavior and how to disable SUPER_READ_ONLY mode, refer to the Health Monitor documentation, in the MySQL Database Service Guide.

PREV HOME UP NEXT