MySQL HeatWave で動作の制限・制約がある SQL 文 (2) データ型 (2)

こちらの記事の続きです。

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

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

前回に引き続き、データ型の制限です。

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

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

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

## 5. 空間データ型

PostgreSQL + PostGIS と比べて検索などが遅い印象があるので、HeatWave で速くなってくれれば…と思わなくはないですが、HeatWave の趣旨から外れる（と思う）ので対象外、というのはわかります。

テストは省略します。

## 6. 整数部の精度が 18 桁を超える数値（`ABS()`式の演算を除く）

試してみます。

### 19 桁の`DECIMAL`型（整数部 18 桁 + 小数部 1 桁）で試してみる

MySQL DB でテーブルを作成し、データを追加します。

```sql
mysql> CREATE TABLE dec_test1 (id INT NOT NULL AUTO_INCREMENT, val DECIMAL(19, 1) NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dec_test1 SET val = 123456789012345678.9;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dec_test1 SET val = 234567890123456789;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dec_test1 SET val = 345678901234567890.1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dec_test1;
+----+----------------------+
| id | val                  |
+----+----------------------+
|  1 | 123456789012345678.9 |
|  2 | 234567890123456789.0 |
|  3 | 345678901234567890.1 |
+----+----------------------+
3 rows in set (0.00 sec)
```

これを HeatWave にロードして`SELECT`します。

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

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

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ * FROM dec_test1;
+----+----------------------+
| id | val                  |
+----+----------------------+
|  1 | 123456789012345678.9 |
|  2 | 234567890123456789.0 |
|  3 | 345678901234567890.1 |
+----+----------------------+
3 rows in set (0.02 sec)
```

特に問題ありませんでした。

### 20 桁の`DECIMAL`型（整数部 19 桁 + 小数部 1 桁）で試してみる

```sql
mysql> CREATE TABLE dec_test2 (id INT NOT NULL AUTO_INCREMENT, val DECIMAL(20, 1) NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dec_test2 SET val = 1234567890123456789;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dec_test2 SET val = 2345678901234567890.1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dec_test2 SET val = 3456789012345678901.2;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM dec_test2;
+----+-----------------------+
| id | val                   |
+----+-----------------------+
|  1 | 1234567890123456789.0 |
|  2 | 2345678901234567890.1 |
|  3 | 3456789012345678901.2 |
+----+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ * FROM dec_test2;
ERROR 3889 (HY000): Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
```

エラー（`ERROR 3889 (HY000): Secondary engine operation failed. No secondary engine defined for at least one of the query tables.`）が出ました。

エラーメッセージがちょっと意味不明ですね（内部でどのように処理しているのか垣間見ることができる？）。

:::note warn
**2023/10/14 追記：**
2023/10/12 時点（MySQL バージョン 8.1.0-u4-cloud）で確認したところ、エラーが出ず正常に表示されるようになりました。

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

### 20 桁の`DECIMAL`型（整数部 18 桁 + 小数部 2 桁）で試してみる

過程は省略しますが、

```sql
mysql> CREATE TABLE dec_test3 (id INT NOT NULL AUTO_INCREMENT, val DECIMAL(20, 2) NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)
（中略）
mysql> SELECT * FROM dec_test3;
+----+-----------------------+
| id | val                   |
+----+-----------------------+
|  1 | 123456789012345678.90 |
|  2 | 234567890123456789.01 |
|  3 | 345678901234567890.12 |
+----+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ * FROM dec_test3;
+----+-----------------------+
| id | val                   |
+----+-----------------------+
|  1 | 123456789012345678.90 |
|  2 | 234567890123456789.01 |
|  3 | 345678901234567890.12 |
+----+-----------------------+
3 rows in set (0.02 sec)
```
エラーは出ませんでした。

公式マニュアルの「Decimal values」をどちらの意味で捉えるのか迷いましたが、**整数部が 18 桁を超えるとエラー** になるようなので、「`DECIMAL`型の値」ではなく「整数値」という意味ですね。

### `ABS()`の除外については

よくわかりませんでした。

```sql
mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ id, ABS(val) FROM dec_test2;
ERROR 3889 (HY000): Secondary engine operation failed. No secondary engine defined for at least one of the query tables.
```

結果ではなく、計算の過程で整数部が 19 桁以上になっても大丈夫（だけど結果は 18 桁以内じゃないとエラー）という意味なのでしょうか？

## 7. `ENUM`のうち、公式マニュアルに「サポート対象」と書かれていないもの

[公式マニュアル](https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html)の表現が、

> - ENUM type columns as part of a UNION or non-top level UNION ALL SELECT list or as a JOIN key, except when used inside a supported expression.
> - ENUM type support is limited to:

（個別の列挙項目は省略）

という、**何がサポート対象外なのかよくわからない** 書き方だったので非常に困りました。

### とりあえず`MIN()`で試してみる

列挙項目に、

> - COUNT(), SUM(), and AVG() aggregation functions on ENUM columns.

があったので、`AVG()`と`MIN()`で挙動がどうなるのか試してみます。

まずは MySQL DB で本来の動作を確認します。

```sql
mysql> CREATE TABLE enum_test (id INT NOT NULL AUTO_INCREMENT, num_u5 ENUM('one', 'two', 'three', 'four', 'five') NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO enum_test SET num_u5 = 'one';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO enum_test SET num_u5 = 'two';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO enum_test SET num_u5 = 'three';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT AVG(num_u5) FROM enum_test;
+-------------+
| AVG(num_u5) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(num_u5) FROM enum_test;
+-------------+
| MIN(num_u5) |
+-------------+
| one         |
+-------------+
1 row in set (0.00 sec)
```

`AVG()`は平均をインデックス値（数値）で、`MIN()`は文字列値そのものを返すようです。

HeatWave にロードして試してみます。

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

mysql> ALTER TABLE enum_test SECONDARY_LOAD;
Query OK, 0 rows affected (0.22 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ AVG(num_u5) FROM enum_test;
+-------------+
| AVG(num_u5) |
+-------------+
|           2 |
+-------------+
1 row in set (0.02 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ MIN(num_u5) FROM enum_test;
ERROR 3889 (HY000): Secondary engine operation failed. Reason: "Aggregation over ENUM is only supported for SUM, AVG, and COUNT".
```

`MIN()`では無事（？）エラー（`ERROR 3889 (HY000): Secondary engine operation failed. Reason: "Aggregation over ENUM is only supported for SUM, AVG, and COUNT".`）が出ました。

## 8. 厳密な SQL モード以外での時間（日付）型

結論を先に書いておくと、**どう制限されているのかわかりませんでした。**

### `sql_mode`を空にして`DATETIME`型の列を扱う

デフォルトでは、

```sql
mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE dt_test (id INT NOT NULL AUTO_INCREMENT, dt DATETIME NOT NULL, PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO dt_test SET dt = '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'dt' at row 1
```

`sql_mode`で厳密モードが指定されているので、`sql_mode`を空にして厳密モードを解除します（結果、何も指示をしなくても`0000-00-00`のような、本来なら無効な日付を指定可能に）。

```sql
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO dt_test SET dt = '2023-08-14 21:16:22';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dt_test SET dt = '2023-08-14 21:18:03';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO dt_test SET dt = '0000-00-00 00:00:00';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM dt_test;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2023-08-14 21:16:22 |
|  2 | 2023-08-14 21:18:03 |
|  3 | 0000-00-00 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)
```

HeatWave にロードして試すと、

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

mysql> ALTER TABLE dt_test SECONDARY_LOAD;
Query OK, 0 rows affected (0.22 sec)

mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ * FROM dt_test;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2023-08-14 21:16:22 |
|  2 | 2023-08-14 21:18:03 |
|  3 | 0000-00-00 00:00:00 |
+----+---------------------+
3 rows in set (0.02 sec)
```

エラーにな…りませんでした。

## データ型の制限についての結論

公式マニュアルの情報は、

- 複数のページを見ないと全体像を把握できない
- 書かれている内容をどう解釈したら良いかがわかりづらいものがある

ので、個人的には **サポートへの問い合わせ** をお勧めします。

（「ちょっとややこしいぞ」という点をイメージできるように…というのが記事を書いた目的、ということで）

---

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

- **[MySQL HeatWave で動作の制限・制約がある SQL 文 (3) 関数・オペレータ](https://qiita.com/hmatsu47/items/70084f1cced2e49ad8e0)**

https://qiita.com/hmatsu47/items/70084f1cced2e49ad8e0