MySQLのLAG()とかLEAD()に ERROR 1690 (22003): BIGINT UNSIGNED value is out of range と言われたら

TL;DR

• sql_mode に NO_UNSIGNED_SUBTRACTION を追加してから実行する

---

たとえば、 performance_schema.events_statements_summary_by_digest の結果を延々とため込んでいるようなテーブルがあるとするじゃろ？

sum_rows_examined は累計値なので、グラフにする時なぞは前回との差分を取りたくなるので、MySQL 8.0からようやく使えるようになった LAG なぞ使うではないか。

mysql> WITH base AS
    -> (
    ->   SELECT
    ->     digest,
    ->     sum_rows_examined - LAG(sum_rows_examined) OVER w AS diff_exam,
    ->     last_update,
    ->     TIMESTAMPDIFF(second, LAG(last_update) OVER w, last_update) AS diff_time
    ->   FROM ps_digest_info
    ->   WINDOW w AS (PARTITION BY digest ORDER BY seq)
    -> )
    -> SELECT digest, last_update, diff_exam / diff_time, diff_time FROM base;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`admintool`.`ps_digest_info`.`sum_rows_examined` - `tmp_field_5`)'

エラった(´･ω･｀)

これは再起動でもはさんだのか、 sum_rows_examined - LAG(sum_rows_examined) OVER w (これが tmp_field_5 の正体) が負になるところがあると BIGINT UNSIGNED の範囲に収まらないから…ってこうなる。

確かに sum_rows_examined は BIGINT UNSIGNED で定義していたのでこれを BIGINT SIGNED にデータ型を変えてやれば動くようにはなるんだけど、必ずしもそうできるわけでもない。

mysql> show create table ps_digest_info\G
*************************** 1. row ***************************
       Table: ps_digest_info
Create Table: CREATE TABLE `ps_digest_info` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ipaddr` varchar(15) NOT NULL,
  `port` smallint(5) unsigned NOT NULL,
  `schema_name` varchar(255) NOT NULL,
  `digest` varchar(255) NOT NULL,
  `digest_text` text NOT NULL,
  `count_star` bigint(20) unsigned NOT NULL,
  `sum_rows_examined` bigint(20) unsigned NOT NULL,
  `sum_rows_sent` bigint(20) unsigned NOT NULL,
..

MySQLは何故か整数の加減算の時に「片方が符号無しの場合は結果の型を符号無しにしようとする」という謎なポリシー(?)があって、それに違反すると出るエラーが ERROR 1690 (22003): BIGINT UNSIGNED value is out of range .

このポリシーを曲げさせて「結果の型が負になりそうなら符号ありにキャストする」ようにするのが NO_UNSIGNED_SUBTRACTION

mysql> SET sql_mode = CONCAT(@@sql_mode, ',NO_UNSIGNED_SUBTRACTION');
Query OK, 0 rows affected (0.00 sec)

mysql> WITH base AS  (    SELECT       digest,       sum_rows_examined - LAG(sum_rows_examined) OVER w AS diff_exam,      last_update,       TIMESTAMPDIFF(second, LAG(last_update) OVER w, last_update) AS diff_time    FROM ps_digest_info     WINDOW w AS (PARTITION BY digest ORDER BY seq))  SELECT digest, last_update, diff_exam / diff_time, diff_time FROM base;
+----------------------------------+---------------------+-----------------------+-----------+
| digest                           | last_update         | diff_exam / diff_time | diff_time |
+----------------------------------+---------------------+-----------------------+-----------+
|                                  | 2019-06-25 10:30:08 |                  NULL |      NULL |
|                                  | 2019-06-25 18:30:08 |               26.7571 |     28800 |
|                                  | 2019-07-02 10:30:08 |               68.2178 |    576000 |
..

結果が返ってくるようになった。良かった良かった。

​