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()
ASSIGNED
andUNSIGNED
on temporal values. For supportedCAST()
operations, see Section 2.11.3, “Cast Functions and Operators”.COALESCE()
as aJOIN
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 asSELECT 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()
andLEAST()
functions with temporal data type columns.A
CASE
control flow operator orIF()
function that contains columns not within an aggregation function and not part of theGROUP 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(
does not support thedate[,mode
])default_week_format
system variable. To use themode
argument, themode
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 aUNION
or non-top levelUNION ALL
SELECT
list or as aJOIN
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()
, andAVG()
aggregation functions onENUM
columns. The functions operate on the numeric index value, not the associated string value.CAST(
. The numeric index value is cast, not the associated string value.enum_col
AS {[N]CHAR [(X)]|SIGNED|UNSIGNED|FLOAT|DOUBLE|DECIMAL [(M,N)]})CAST(
is supported only in theenum_col
) AS {[N]CHAR}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
andtimestamp
variable settings are not passed to HeatWave when queries are offloaded.A
time_zone
setting other thanSYSTEM
(+00:00
) is not supported when propagating changes to tables containingTIMESTAMP
columns. Propagating changes in this scenario causes incorrect data to be stored inTIMESTAMP
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
andEXISTS
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 thanDATETIME
,TIMESTAMP
, andDATE
.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 theDUPSWEEDOUT
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 theON
clause.
-
Index and optimizer hints. See Index Hints, and Optimizer Hints.
Semijoin strategies other than
FIRSTMATCH
. MySQL attempts to enforce theFIRSTMATCH
strategy and ignores all other semijoin strategies specified explicitly as subquery optimizer hints. However, MySQL may still select theDUPSWEEDOUT
semijoin strategy duringJOIN
order optimization, even if an equivalent plan could be offered using theFIRSTMATCH
strategy. (A plan that uses theDUPSWEEDOUT
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 inGROUP 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 anORDER BY
orLIMIT
clause, between different column types, between dictionary-encoded columns, or betweenENUM
columns.UNION
queries with or without anORDER BY
orLIMIT
clause, between different column types, between dictionary-encoded columns, or betweenENUM
columns.UNION
andUNION ALL
subqueries with or without anORDER BY
orLIMIT
clause, between different column types, between dictionary-encoded columns, betweenENUM
columns, or specified in anIN
orEXISTS
clause. Comparison predicates,
GROUP BY
,JOIN
, and so on, if the key column isDOUBLE 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 supportsCREATE TABLE ... SELECT
andINSERT ... SELECT
statements where only theSELECT
portion of the operation is offloaded to HeatWave. See Section 2.3, “Running Queries”.)SET timezone =
, with thetimezone
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 theUNIX_TIMESTAMP()
andFROM_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 thanSYSTEM
(+00:00
) is not supported when propagating changes to tables containingTIMESTAMP
columns. Propagating changes in this scenario causes incorrect data to be stored inTIMESTAMP
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 tablet1
(x < 7
) followed by a consecutive filter on tablett1
(tt1.x > 5
) in theWHERE
clause. -
UNION ALL
statements inside derived table expressions or common table expressions. For example, the following query, which defines aUNION ALL
clause withinWITH
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 inSUPER_READ_ONLY
mode. MySQL Server is placed inSUPER_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 disableSUPER_READ_ONLY
mode, refer to the Health Monitor documentation, in the MySQL Database Service Guide.
-