MySQL HeatWave で動作の制限・制約がある SQL 文 (1) 変更の伝搬に関するもの

[こちら](https://qiita.com/hmatsu47/items/8f202eef64ea57e7d948)のシリーズの続きです。

https://qiita.com/hmatsu47/items/8f202eef64ea57e7d948

https://qiita.com/hmatsu47/items/0979f877ad596cf3cf67

https://qiita.com/hmatsu47/items/4c61ce90adcdbbea74e9

https://qiita.com/hmatsu47/items/230d345e481701a5d735

MySQL HeatWave で実行できない・実行結果に差異が生じる SQL 文がいくつかあるようですので、これから試していきます。

## [MySQL HeatWave User Guide](https://dev.mysql.com/doc/heatwave/en/) に記載の制限・制約事項

- **[2.16 Limitations](https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations.html)（MySQL HeatWave User Guide）**

https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations.html

今回は、最初の

- **[2.16.1 Change Propagation Limitations](https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html)（MySQL HeatWave User Guide）**

を確認します。

:::note warn
2023/7/30 時点（MySQL バージョン 8.1.0-u1-cloud）の情報です。
今後、結果などが変わる可能性があります。
:::

## 1. 外部キーの`CASCADE`（`UPDATE`・`DELETE`）

### テーブル作成・テストデータ挿入

こんな感じで、親・子のテーブルを作成します。

```sql
mysql> CREATE DATABASE fk_test;
Query OK, 1 row affected (0.01 sec)

mysql> USE fk_test;
Database changed
mysql> CREATE TABLE fk_p (id INT NOT NULL, val INT NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE fk_c (id INT NOT NULL, p_id INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (p_id) REFERENCES fk_p (id) ON UPDATE CASCADE ON DELETE CASCADE);
Query OK, 0 rows affected (0.02 sec)
```

こちらにデータを挿入します（以下は挿入後の内容）。

```sql
mysql> SELECT * FROM fk_p ORDER BY id;
+----+-----+
| id | val |
+----+-----+
|  1 | 100 |
|  2 | 110 |
|  3 | 150 |
|  4 | 150 |
|  5 | 170 |
|  6 | 170 |
|  7 | 210 |
|  8 | 210 |
|  9 | 280 |
| 10 | 300 |
+----+-----+
10 rows in set (0.00 sec)

mysql> SELECT * FROM fk_c ORDER BY id;
+----+------+
| id | p_id |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    2 |
|  4 |    5 |
|  5 |    5 |
|  6 |    5 |
|  7 |    7 |
|  8 |    7 |
|  9 |    9 |
| 10 |   10 |
+----+------+
10 rows in set (0.00 sec)
```

### HeatWave 有効化・データロード

セカンダリエンジンとして HeatWave を有効化します。

```sql
mysql> ALTER TABLE fk_p SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE fk_p SECONDARY_LOAD;
Query OK, 0 rows affected (0.26 sec)

mysql> ALTER TABLE fk_c SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE fk_c SECONDARY_LOAD;
Query OK, 0 rows affected (0.20 sec)
```

HeatWave へのデータロードを確認します。

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
+-----+-----+------+
| pid | val | cid  |
+-----+-----+------+
|   1 | 100 |    1 |
|   2 | 110 |    2 |
|   2 | 110 |    3 |
|   3 | 150 | NULL |
|   4 | 150 | NULL |
|   5 | 170 |    4 |
|   5 | 170 |    5 |
|   5 | 170 |    6 |
|   6 | 170 | NULL |
|   7 | 210 |    7 |
|   7 | 210 |    8 |
|   8 | 210 | NULL |
|   9 | 280 |    9 |
|  10 | 300 |   10 |
+-----+-----+------+
14 rows in set (0.10 sec)
```

### `DELETE`実行

`UPDATE`・`DELETE`の両方を確認するため、トランザクションを有効にしつつ、まずは`CASCADE`でよく使うであろう、`DELETE`から試してみます。

```sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM fk_p WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
```

`DELETE`そのものは成功したので、あらためて HeatWave 側で SQL 文を流してみると…

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
ERROR 3889 (HY000): Secondary engine operation failed. Reason: "use_secondary_engine is FORCED but query could not be executed in secondary engine".
```

エラー（`ERROR 3889 (HY000): Secondary engine operation failed.`）になりました。

:::note warn
**2023/10/14 追記：**
2023/10/12 時点（MySQL バージョン 8.1.0-u4-cloud）で確認したところ、**親テーブル（`fk_p`）の`id=5`に対応する子テーブル（`fk_c`）の`id=3・4・5`が HeatWave 側で削除されず残ったままになる一方で、エラーが出なくなりました。**

データの不整合に気付かないままになる恐れがあるので厄介ですね。

https://speakerdeck.com/hmatsu47/mysql-heatwave-limitations-and-dms-replication?slide=7
:::

念のため MySQL DB 側で実行してみると、

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=OFF) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
+-----+-----+------+
| pid | val | cid  |
+-----+-----+------+
|   1 | 100 |    1 |
|   2 | 110 |    2 |
|   2 | 110 |    3 |
|   3 | 150 | NULL |
|   4 | 150 | NULL |
|   6 | 170 | NULL |
|   7 | 210 |    7 |
|   7 | 210 |    8 |
|   8 | 210 | NULL |
|   9 | 280 |    9 |
|  10 | 300 |   10 |
+-----+-----+------+
11 rows in set (0.01 sec)
```

親テーブルの`id=5`が正常に削除されていて、エラーも出ません。

### `UPDATE`実行

一旦`ROLLBACK` します。

```sql
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
+-----+-----+------+
| pid | val | cid  |
+-----+-----+------+
|   1 | 100 |    1 |
|   2 | 110 |    2 |
|   2 | 110 |    3 |
|   3 | 150 | NULL |
|   4 | 150 | NULL |
|   5 | 170 |    4 |
|   5 | 170 |    5 |
|   5 | 170 |    6 |
|   6 | 170 | NULL |
|   7 | 210 |    7 |
|   7 | 210 |    8 |
|   8 | 210 | NULL |
|   9 | 280 |    9 |
|  10 | 300 |   10 |
+-----+-----+------+
14 rows in set (0.03 sec)
```

戻っていますね。

一応、`UPDATE`についても試してみます。

```sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE fk_p SET id = 11 WHERE id = 7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT /*+ SET_VAR(use_secondary_engine=OFF) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
+-----+-----+------+
| pid | val | cid  |
+-----+-----+------+
|   1 | 100 |    1 |
|   2 | 110 |    2 |
|   2 | 110 |    3 |
|   3 | 150 | NULL |
|   4 | 150 | NULL |
|   5 | 170 |    4 |
|   5 | 170 |    5 |
|   5 | 170 |    6 |
|   6 | 170 | NULL |
|   8 | 210 | NULL |
|   9 | 280 |    9 |
|  10 | 300 |   10 |
|  11 | 210 |    7 |
|  11 | 210 |    8 |
+-----+-----+------+
14 rows in set (0.00 sec)
```

MySQL DB では普通に実行できますが、

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ p.id pid, p.val, c.id cid FROM fk_p p LEFT JOIN fk_c c ON p.id = c.p_id ORDER BY p.id, c.id;
ERROR 3889 (HY000): Secondary engine operation failed. Reason: "use_secondary_engine is FORCED but query could not be executed in secondary engine".
```

HeatWave ではやっぱりエラーになります。

## 2. `time_zone`を`SYSTEM`（`+00:00`）以外に設定したときの`TIMESTAMP`列`UPDATE`

### Configurations に設定追加・テスト DB 作成

MySQL HeatWave on AWS を使い、Configurations で`time_zone`を`Asia/Tokyo`に設定して試してみます。

:::note warn
**2023/10/14 追記：**
2023/10/12 時点（MySQL バージョン 8.1.0-u4-cloud）で確認したところ、**`time_zone`を`Asia/Tokyo`形式で指定すると HeatWave 側でエラーが出るようになりました。**

**`+09:00`形式で指定する必要があります。**

https://speakerdeck.com/hmatsu47/mysql-heatwave-limitations-and-dms-replication?slide=22
:::

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/3efa1266-14d3-2ec6-f117-ee8d7d68bbd7.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/123899/3645e4bc-c7f9-09fd-a3f9-73ff9fea5018.png)
```sql
mysql> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| Asia/Tokyo  |
+-------------+
1 row in set (0.00 sec)
```

### テーブル作成・テストデータ投入

テーブルを作ります。

```sql
CREATE DATABASE ts_test;
USE ts_test;
CREATE TABLE ts_test (id INT NOT NULL, val int NOT NULL, ts TIMESTAMP NOT NULL, PRIMARY KEY(id));
```

以下のストアドプロシージャを作成して、テストデータを投入します。

```sql
DELIMITER //

DROP PROCEDURE IF EXISTS ts_insert;
CREATE PROCEDURE ts_insert(IN record_count INT, IN start_datetime DATETIME)
BEGIN
  DECLARE cur_id INT DEFAULT 1;
  DECLARE cur_datetime DATETIME DEFAULT '2001-01-01 00:00:00';
  SET cur_datetime = start_datetime;

  loop1: LOOP
    INSERT INTO ts_test SET id = cur_id, val = cur_id, ts = cur_datetime;
    SET cur_id = cur_id + 1;
    SET cur_datetime = cur_datetime + interval 1 minute;

    IF cur_id > record_count then
      LEAVE loop1;
      END IF;
  END LOOP loop1;
END;
//

DELIMITER ;
```

データを投入します。

```sql
mysql> CALL ts_insert(1000000, '1990-01-01 00:00:00');
```

:::note
トランザクションを効かせるのを忘れたので、処理に時間が掛かってしまい、クライアントがタイムアウトしてしまいました。
:::

データ投入後にインデックスを作成します。

```sql
mysql> ALTER TABLE ts_test ADD INDEX idx_ts(ts, val);
Query OK, 0 rows affected (4.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

### MySQL DB で実行（`UPDATE`前）

まずはセカンダリエンジン（HeatWave）がない状態で実行してみます。

```sql
mysql> SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2612756160 |
|  4789001520 |
|  6531861600 |
|  8710179120 |
| 10326549600 |
| 12631356720 |
| 14624086320 |
| 16049685600 |
| 18545263920 |
| 19844373600 |
| 22466441520 |
| 24459171120 |
| 23804948160 |
| 28251785520 |
| 29237781600 |
| 32172963120 |
| 33032469600 |
| 36094140720 |
| 38086870320 |
| 38755605600 |
| 37855661760 |
+-------------+
21 rows in set (1.04 sec)
```

### HeatWave 有効化・HeatWave で実行（`UPDATE`前）

HeatWave を有効化します。

```sql
mysql> ALTER TABLE ts_test SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ts_test SECONDARY_LOAD;
Query OK, 0 rows affected (1.96 sec)
```

ロードが完了し、先ほどの SQL 文の`EXPLAIN`を表示してみると、HeatWave で実行されることがわかります。

```sql
mysql> EXPLAIN FORMAT=TREE SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts)\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: year(ts_test.ts), month(ts_test.ts)  (cost=1202..221e+6 rows=183822)
    -> Group aggregate: sum(ts_test.val)  (cost=1066..196e+6 rows=183822)
        -> Filter: (ts_test.ts between '1990-02-01 00:00:00' and '1991-10-28 23:59:59')  (cost=95.9..88.1e+6 rows=919111)
            -> Table scan on ts_test in secondary engine RAPID  (cost=0..0 rows=919111)

1 row in set, 1 warning (0.02 sec)
```

HeatWave で実行してみると、

```sql
mysql> SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2612756160 |
|  4789001520 |
|  6531861600 |
|  8710179120 |
| 10326549600 |
| 12631356720 |
| 14624086320 |
| 16049685600 |
| 18545263920 |
| 19844373600 |
| 22466441520 |
| 24459171120 |
| 23804948160 |
| 28251785520 |
| 29237781600 |
| 32172963120 |
| 33032469600 |
| 36094140720 |
| 38086870320 |
| 38755605600 |
| 37855661760 |
+-------------+
21 rows in set (0.15 sec)
```

差異がないことがわかります。

### データ（`TIMESTAMP`列）更新

データ（`TIMESTAMP`列）を更新します。

```sql
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE ts_test SET ts = ts + INTERVAL 12 HOUR;
Query OK, 1000000 rows affected (8.99 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

mysql> SELECT * FROM ts_test WHERE id = 1;
+----+-----+---------------------+
| id | val | ts                  |
+----+-----+---------------------+
|  1 |   1 | 1990-01-01 12:00:00 |
+----+-----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM ts_test WHERE id = 1000000;
+---------+---------+---------------------+
| id      | val     | ts                  |
+---------+---------+---------------------+
| 1000000 | 1000000 | 1991-11-26 22:39:00 |
+---------+---------+---------------------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.08 sec)
```

全行 12 時間後ろにずらしています。

### MySQL DB で実行（`UPDATE`後）

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=OFF) */ SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2583725760 |
|  4756860720 |
|  6500757600 |
|  8678038320 |
| 10295445600 |
| 12599215920 |
| 14591945520 |
| 16018581600 |
| 18513123120 |
| 19813269600 |
| 22434300720 |
| 24427030320 |
| 23775917760 |
| 28219644720 |
| 29206677600 |
| 32140822320 |
| 33001365600 |
| 36061999920 |
| 38054729520 |
| 38724501600 |
| 37826631360 |
+-------------+
21 rows in set (1.13 sec)
```

集計対象のデータ行が前にずれた分、集計対象行が全体的に前にずれて（`val`の値が小さい行が集計対象になる）、集計値が小さくなっているのがわかります。

### HeatWave で実行（`UPDATE`後）

```sql
mysql> SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2605498560 |
|  4780966320 |
|  6524085600 |
|  8702143920 |
| 10318773600 |
| 12623321520 |
| 14616051120 |
| 16041909600 |
| 18537228720 |
| 19836597600 |
| 22458406320 |
| 24451135920 |
| 23797690560 |
| 28243750320 |
| 29230005600 |
| 32164927920 |
| 33024693600 |
| 36086105520 |
| 38078835120 |
| 38747829600 |
| 37848404160 |
+-------------+
21 rows in set (0.09 sec)
```

差異が生じてしまいました。

:::note warn
**2023/10/14 追記：**
2023/10/12 時点（MySQL バージョン 8.1.0-u4-cloud）で確認したところ、**`time_zone`を`+09:00`形式で指定した状態で差異が出なくなりました。**

https://speakerdeck.com/hmatsu47/mysql-heatwave-limitations-and-dms-replication?slide=22
:::
**~~外部キーの`CASCADE`（`UPDATE`・`DELETE`）のときとは違い~~エラーにならない** ので、むしろ厄介ですね。

### HeatWave にデータを再ロードする

一旦 HeatWave のデータをアンロードし、ロードし直します。

```sql
mysql> ALTER TABLE ts_test SECONDARY_UNLOAD;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE ts_test SECONDARY_LOAD;
Query OK, 0 rows affected (1.98 sec)
```

対象 SQL 文が HeatWave で実行されることを`EXPLAIN`で確認しつつ再実行すると、正しい結果になりました。

```sql
mysql> EXPLAIN FORMAT=TREE SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts)\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: year(ts_test.ts), month(ts_test.ts)  (cost=1202..221e+6 rows=183822)
    -> Group aggregate: sum(ts_test.val)  (cost=1066..196e+6 rows=183822)
        -> Filter: (ts_test.ts between '1990-02-01 00:00:00' and '1991-10-28 23:59:59')  (cost=95.9..88.1e+6 rows=919111)
            -> Table scan on ts_test in secondary engine RAPID  (cost=0..0 rows=919111)

1 row in set, 1 warning (0.02 sec)

mysql> SELECT SUM(val) FROM ts_test WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2583725760 |
|  4756860720 |
|  6500757600 |
|  8678038320 |
| 10295445600 |
| 12599215920 |
| 14591945520 |
| 16018581600 |
| 18513123120 |
| 19813269600 |
| 22434300720 |
| 24427030320 |
| 23775917760 |
| 28219644720 |
| 29206677600 |
| 32140822320 |
| 33001365600 |
| 36061999920 |
| 38054729520 |
| 38724501600 |
| 37826631360 |
+-------------+
21 rows in set (0.10 sec)
```

### `DATETIME`列で実行してみると

問題は発生しませんでした。

```sql
mysql> CREATE TABLE ts_test2 (id INT NOT NULL, val int NOT NULL, ts DATETIME NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO ts_test2 SELECT id, val, CONCAT(ts, '+00:00') ts FROM ts_test ORDER BY id;
Query OK, 1000000 rows affected (5.34 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ts_test2 WHERE id = 1;
+----+-----+---------------------+
| id | val | ts                  |
+----+-----+---------------------+
|  1 |   1 | 1990-01-01 00:00:00 |
+----+-----+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| Asia/Tokyo  |
+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=TREE SELECT SUM(val) FROM ts_test2 WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BYYEAR(ts), MONTH(ts)\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: year(ts_test2.ts), month(ts_test2.ts)  (cost=1202..221e+6 rows=183822)
    -> Group aggregate: sum(ts_test2.val)  (cost=1066..196e+6 rows=183822)
        -> Filter: (ts_test2.ts between '1990-02-01 00:00:00' and '1991-10-28 23:59:59')  (cost=95.9..88.1e+6 rows=919111)
            -> Table scan on ts_test2 in secondary engine RAPID  (cost=0..0 rows=919111)

1 row in set, 1 warning (0.02 sec)

mysql> SELECT SUM(val) FROM ts_test2 WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);

+-------------+
| SUM(val)    |
+-------------+
|  2612756160 |
|  4789001520 |
|  6531861600 |
|  8710179120 |
| 10326549600 |
| 12631356720 |
| 14624086320 |
| 16049685600 |
| 18545263920 |
| 19844373600 |
| 22466441520 |
| 24459171120 |
| 23804948160 |
| 28251785520 |
| 29237781600 |
| 32172963120 |
| 33032469600 |
| 36094140720 |
| 38086870320 |
| 38755605600 |
| 37855661760 |
+-------------+
21 rows in set (0.09 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE ts_test2 SET ts = ts + INTERVAL 12 HOUR;
Query OK, 1000000 rows affected (9.21 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.09 sec)

mysql> EXPLAIN FORMAT=TREE SELECT SUM(val) FROM ts_test2 WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BYYEAR(ts), MONTH(ts)\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: year(ts_test2.ts), month(ts_test2.ts)  (cost=4.77e+6..100e+6 rows=21)
    -> Group aggregate: sum(ts_test2.val)  (cost=4.77e+6..100e+6 rows=21)
        -> Filter: (ts_test2.ts between '1990-02-01 00:00:00' and '1991-10-28 23:59:59')  (cost=96.4..88.1e+6 rows=914400)
            -> Table scan on ts_test2 in secondary engine RAPID  (cost=0..0 rows=914400)

1 row in set, 1 warning (0.02 sec)

mysql> SELECT SUM(val) FROM ts_test2 WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);

+-------------+
| SUM(val)    |
+-------------+
|  2583725760 |
|  4756860720 |
|  6500757600 |
|  8678038320 |
| 10295445600 |
| 12599215920 |
| 14591945520 |
| 16018581600 |
| 18513123120 |
| 19813269600 |
| 22434300720 |
| 24427030320 |
| 23775917760 |
| 28219644720 |
| 29206677600 |
| 32140822320 |
| 33001365600 |
| 36061999920 |
| 38054729520 |
| 38724501600 |
| 37826631360 |
+-------------+
21 rows in set (0.09 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=OFF) */ SUM(val) FROM ts_test2 WHERE ts BETWEEN '1990-02-01 00:00:00' AND '1991-10-28 23:59:59' GROUP BY YEAR(ts), MONTH(ts) ORDER BY YEAR(ts), MONTH(ts);
+-------------+
| SUM(val)    |
+-------------+
|  2583725760 |
|  4756860720 |
|  6500757600 |
|  8678038320 |
| 10295445600 |
| 12599215920 |
| 14591945520 |
| 16018581600 |
| 18513123120 |
| 19813269600 |
| 22434300720 |
| 24427030320 |
| 23775917760 |
| 28219644720 |
| 29206677600 |
| 32140822320 |
| 33001365600 |
| 36061999920 |
| 38054729520 |
| 38724501600 |
| 37826631360 |
+-------------+
21 rows in set (0.90 sec)
```
---

**2023/8/6 追記：**
続きの記事を書きました。

- **[MySQL HeatWave で動作の制限・制約がある SQL 文 (2) データ型 (1)](https://qiita.com/hmatsu47/items/a9667762fb5ecdd66e75)**

https://qiita.com/hmatsu47/items/a9667762fb5ecdd66e75