11.2.1.9 Other Limitations

Most non-default DB System SQL modes.

For a list of supported SQL modes, see Section 5.2, “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.

Cursors inside stored programs are not supported before MySQL 9.0.0, see: Cursors.

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;

Primary keys with column prefixes.

Virtual generated columns.

Queries that are executed as part of a trigger.

Queries that call a stored function are not supported before MySQL 9.5.0.

As of MySQL 9.5.0, queries that call a stored function are supported with the following limitations:

The query runs with forced secondary engine only:
```
SET use_secondary_engine=forced;
```
The function definition must not contain IF or ELSE statements.
The function definition must not contain CREATE, INSERT, ALTER, UPDATE, or DELETE statements.
In the function definition, the SQL SECURITY characteristic must be set to INVOKER. It must not be set to DEFINER For more information, see The SQL SECURITY Characteristic.
The function must use the same sql_mode variable setting as the current session.
The function must use the same Connection Character Set and Collation System Variables variables setting as the current session.
Condition handlers are not supported on the secondary engine. For more information, see Condition Handling.
Stored function must not be a part of a prepared statement. For more information, see Prepared Statements.
Only the following statements must be used in the function definition:
- SET statement: the SET statement must use only local variables. The set value can either be an expression or a subquery.
- SELECT ... INTO statement: only local variable can be used.
- RETURN statement: The RETURN statement must be followed by an expression that does not contain a subquery. If you need to use a subquery, set a local variable to use the subquery.

Stored functions for MySQL HeatWave can be defined as shown below:

CREATE [DEFINER = username] FUNCTION
	[IF NOT EXISTS] function_name ([func_parameter[,...]])
	RETURNS datatype [characteristic ...] routine_body
func_parameter:
	param_name datatype
datatype:
	Any valid MySQL data type
characteristic:  {
	COMMENT 'string'	
	| LANGUAGE SQL 
	| [NOT] DETERMINISTIC 
	| { CONTAINS SQL | READS SQL DATA} }
SQL SECURITY INVOKER
routine_body:
	SQL routine with supported statements

For example:

CREATE FUNCTION get_earliest_departure(from_airport_id SMALLINT)
RETURNS DATETIME
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
    DECLARE earliest_departure DATETIME;

    SELECT MIN(departure)
      INTO earliest_departure
      FROM flight
     WHERE `from` = from_airport_id;

    RETURN earliest_departure;
END

Queries that are executed as part of a stored program.

Queries that are part of a multi-statement transaction.

Materialized views are not supported for versions earlier than MySQL 9.5.0. See Section 5.4.7, “Query Views” for information on non-materialized views. As of MySQL 9.5.0, materialized views are supported. See Section 5.4.8, “Query Materalized Views”.

You cannot run queries for materialized views that project DICTIONARY encoded columns.

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 DB System by default.

MySQL HeatWave supports CREATE TABLE ... SELECT and INSERT ... SELECT statements where only the SELECT portion of the operation is offloaded to MySQL HeatWave. See Section 5.4, “Run Queries”.

Named time zones are not supported before MySQL 8.4.0.

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 MySQL 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, see Resolving SUPER_READ_ONLY and OFFLINE_MODE Issue in the MySQL HeatWave on OCI Service Guide.