LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@hmatsu47

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

Last updated at Posted at 2023-08-14

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

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

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

5. 空間データ型

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

テストは省略します。

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

試してみます。

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

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

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します。

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 桁）で試してみる

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.）が出ました。

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

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 桁）で試してみる

過程は省略しますが、

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()の除外については

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

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のうち、公式マニュアルに「サポート対象」と書かれていないもの

公式マニュアルの表現が、

  • 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 で本来の動作を確認します。

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 にロードして試してみます。

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型の列を扱う

デフォルトでは、

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のような、本来なら無効な日付を指定可能に）。

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 にロードして試すと、

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 追記：
続きの記事を書きました。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?