5.4.2 SELECT Statement Clauses for MySQL HeatWave

This topic provides information related to the SELECT statement clauses supported in MySQL HeatWave and describes how to use the GROUP BY and QUALIFY clauses to group or filter the processing results.

Before You Begin

• Review the requirements.

• Connect to your DB System.

• Verify if the query is offloaded to MySQL HeatWave for processing.

SELECT STATEMENT Clauses

The following SELECT statement clauses are supported in the MySQL HeatWave secondary engine.

• GROUP BY clause

  • A CUBE modifier in addition to the WITH ROLLUP modifier. CUBE modifier is available only in secondary engine and is not supported in the DB System.

  • A ROLLUP modifier as a preferred alternative to the WITH ROLLUP modifier.

• QUALIFY clause is added between the WINDOW clause and the ORDER BY clause. QUALIFY clause is available only in secondary engine and is not supported in the DB System.

  In addition to constraints similar to the HAVING clause, the QUALIFY clause can also include predicates related to a window function.

  Similar to the HAVING clause, the QUALIFY clause can refer to aliases mentioned in the SELECT list.

  The QUALIFY clause requires the inclusion of at least one window function in the query. The window function can be part of any one of the following:

  • The SELECT column list.

  • A filter predicate of the QUALIFY clause.

• TABLESAMPLE clause is available only in secondary engine and is not supported in the DB System.

SELECT STATEMENT Syntax

The following SELECT statement syntax shows the clauses that are supported in the MySQL HeatWave secondary engine.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [STRAIGHT_JOIN]
    [SQL_BUFFER_RESULT]
    select_expr [, select_expr] ...
    [FROM table_references
      [PARTITION partition_list]
	  [TABLESAMPLE { SYSTEM | BERNOULLI } (sample_percentage)]]
    [WHERE where_condition]
    [GROUP BY {{col_name | expr | position}, ... [WITH ROLLUP]
	        | {CUBE | ROLLUP} ({col_name | expr | position}, ...)}]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [QUALIFY qualify_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

For a full explanation of the SELECT syntax, see SELECT Statement.

MySQL HeatWave uses a query optimization technique where some conditions from HAVING clause are moved earlier in the execution pipeline to reduce the amount of data processed. This helps reduce the intermediate result set sizes and identify data-related issues that might go unnoticed in MySQL. However, this optimization technique changes the order of operations performed as compared to a standard SQL execution of the SELECT statement on MySQL.

What's Next

Learn how to explain a query and check query execution time.