MySQL で GREATEST 関数の引数に int unsigned なカラムを絡めようとしてハマった

## はじめに

MySQL において、int 型は以下のように定義されています。

https://dev.mysql.com/doc/refman/8.4/en/numeric-type-syntax.html

> A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

すなわち int unsigned にすると負の数が範囲外となります。

これに関して、GREATEST 関数を絡めたときの動作が自分の思っていたものと異なったため、それをまとめてみようと思います。

## 検証環境

- WSL2 on Windows
- Docker version: 26.1.1
- MySQL 8.4.0 on Docker（記事執筆時点の最新バージョンを使用します）

```text
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)
```

## 人の名前と年齢を管理するテーブル

人の名前と年齢を管理することを考えます。

テーブル定義

```sql
create table people (id int primary key auto_increment, name varchar(10), age int unsigned);
```

```text
mysql> create table people (id int primary key auto_increment, name varchar(10), age int unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> desc people;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | YES  |     | NULL    |                |
| age   | int unsigned | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
```

### 5 歳の Jane さんを投入

このテーブルに 5 歳の Jane さんという人を投入します。

```sql
insert into people (name, age) value ('Jane', 5);
```

投入できました。

```text
mysql> insert into people (name, age) value ('Jane', 5);
Query OK, 1 row affected (0.01 sec)

mysql> select * from people;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jane |    5 |
+----+------+------+
1 row in set (0.00 sec)
```

では、もう一人、-2 歳の John さんという人を投入します。

```sql
insert into people (name, age) value('John', -2);
```

年齢が範囲外だと言われ、投入できませんでした。これは `age` カラムが unsigned であり、負の値は範囲外であるためです。

```text
mysql> insert into people (name, age) value('John', -2);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
```

### Jane さんを 0 歳まで若返らせる

次に、投入された Jane さんを 1 歳ずつ若返らせます。ただし、0 歳よりも若返ることはでき無いものとします。

:::note alert
現実世界では原則、人の年齢を減らすことはできません。
:::

GREATEST 関数を使ってこれを表現することを考えます。

```sql
update people set age = greatest(age - 1, 0) where id = 1;
```

若返りに成功しました。

```text
mysql> update people set age = greatest(age - 1, 0) where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from people;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jane |    4 |
+----+------+------+
1 row in set (0.01 sec)
```

GREATEST 関数は、その引数に 2 つ以上の値が指定されると、最も大きい値を返します。

> With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for [LEAST()](https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#function_least).

https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#function_greatest

今回、 `greatest(age - 1, 0)` の部分は 5 - 1 = 4 と 0 のうち大きい値を返すので、4 となります。

あと 4 回同じ処理をしてみます。

```text
mysql> update people set age = greatest(age - 1, 0) where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update people set age = greatest(age - 1, 0) where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update people set age = greatest(age - 1, 0) where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update people set age = greatest(age - 1, 0) where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from people;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jane |    0 |
+----+------+------+
1 row in set (0.00 sec)
```

無事 Jane さんは 0 歳まで若返りました。

### さらに Jane さんを若返らせる

0 歳の時点からもう 1 歳若返らせます。しかし、年齢は 0 歳未満にはできません。  
しかし、`greatest(age - 1, 0)` とすれば -1 と 0 のうち大きい値ですから 0 になり、エラーも起きず値も更新されない（スルーされる）のではないかと考えられます。

実際、`greatest(-1, 0)` は 0 を返します。

```text
mysql> select greatest(- 1, 0) from people;
+------------------+
| greatest(- 1, 0) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
```

ですから、実行してみましょう。

```text
mysql> update people set age = greatest(age - 1, 0) where id = 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`people`.`age` - 1)'
```

`age - 1` が範囲外であると怒られてしまいました。
結果が範囲内の 0 であるとわかりきっていてもダメなのはなぜでしょう・・・ 😢

### 対処法

調べてみたら、こちらに同じ悩みの方がいらっしゃいました。

https://dba.stackexchange.com/questions/162855/why-is-the-value-despite-of-using-greatest-still-out-of-range

> Ok, found a solution by using SELECT GREATEST(0, CAST(posts AS SIGNED) - 1) > – 
> mgutt
> Feb 1, 2017 at 11:55

signed にキャストするとうまくいくようです。  
やってみましょう。

```sql
update people set age = greatest(cast(age as signed) - 1, 0) where id = 1;
```

できました 🎉

```text
mysql> update people set age = greatest(cast(age as signed) - 1, 0) where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from people;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jane |    0 |
+----+------+------+
1 row in set (0.00 sec)
```

unsigned は unsigned 型として明確に区別されていると思われます。だから unsigned である age と 1 の演算結果が unsigned の範囲外となり、エラーになったと考えられます。
そして、signed にキャストすることで -1 も範囲内となるため、エラーを回避することができました。

## まとめ

演算結果が同じ型の範囲内で閉じることを意識するのは大事なこと