2.17.8 Other Limitations

• Most non-default MySQL DB System SQL modes.

  For a list of supported SQL modes, see Section 2.11, “Supported SQL Modes”.

• The gb18030_chinese_ci character set and collation.

• 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 dictionary-encoded columns, or between ENUM columns.

  EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL, and UNION queries with or without an ORDER BY or LIMIT clause, between dictionary-encoded columns, or between ENUM columns.

  EXCEPT, EXCEPT ALL, INTERSECT, INTERSECT ALL, UNION and UNION ALL subqueries with or without an ORDER BY or LIMIT clause, 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.

• Queries with an impossible WHERE condition (queries known to have an empty result set).

  For example, the following query is not offloaded:

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

• 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-statement transaction.

• Materialized views.

  Only nonmaterialized views are supported. See Section 2.3.9, “Using Views”.

• 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”.

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

• Named time zones are not supported.

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

  mysql> 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.

• Recursive common table expressions.

• Operations involving ALTER TABLE such as 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 HeatWave on OCI Service Guide.