空テーブルに対する固定値SELECTのFROM有無でハマった件

しょうもないことでハマってしまったので自分の反省を覚えとくための小ネタです。

• 検証環境: MariaDB 10.4.13

前提

とある履歴テーブルにおいて、現在時刻よりX分以内に履歴があった場合はスキップ、
なかった場合は履歴レコードを追加する、というようなロジックがありました。

-- こんな履歴テーブル定義
CREATE TABLE hoge_history (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  label varchar(100) NOT NULL,
  timestamp timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (id)
);

-- こんな感じのSQLで履歴を入れてた
INSERT INTO hoge_history (label)
SELECT CONCAT('exec: ', NOW()) AS label
FROM hoge_history 
WHERE NOT EXISTS (
	SELECT 1 FROM hoge_history WHERE timestamp >= SUBTIME(NOW(), '00:15;00') 
)
LIMIT 1;

事象

開発用の好きに使っていいDBを、一度空っぽにして新しく作り直したところ、
履歴レコードが一切入らずエラーになってしまった。
運用が始まっているプロダクトコードでは起こった事のない事象。

原因・解決

FROM句 が悪さしてた。

INSERT INTO hoge_history (label)
SELECT CONCAT('exec: ', NOW()) AS label
-- ↓ここ↓
FROM hoge_history 
WHERE NOT EXISTS (
	SELECT 1 FROM hoge_history WHERE timestamp >= SUBTIME(NOW(), '00:15:00') 
)
LIMIT 1;

一度DBを空っぽにしたため hoge_history テーブルは空っぽの状態になっていた。
SQL内でSELECT結果として固定値を出力していたとしても、
FROM句があるときはテーブルの中身に引きずられる。たとえば…

MariaDB [test]> SELECT COUNT(1) FROM example;
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 AS const FROM example;
Empty set (0.00 sec)

MariaDB [test]> SELECT 1 AS const;
+-------+
| const |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

といった感じに、固定値で何の問題もなく出力できる結果も、空テーブルを FROM に持つかどうかで結果が変わる。
なので、WHERE句の結果がTRUEになったとしてもSELECTの結果がEmptyになってしまい、意図通りにINSERTされなかった。

っていうか、SELECTの挙動考えれば当たり前だったね。

MariaDB [test]> SELECT 1 AS const FROM example WHERE name = 'hoge';
Empty set (0.00 sec)

MariaDB [test]> SELECT 1 AS const FROM example LIMIT 1;
Empty set (0.00 sec)

MariaDB [test]> INSERT INTO example(id, name) VALUES(1, 'hoge');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> INSERT INTO example(id, name) VALUES(2, 'fuga');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT COUNT(1) FROM example;
+----------+
| COUNT(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 AS const FROM example;
+-------+
| const |
+-------+
|     1 |
|     1 |
+-------+
2 rows in set (0.00 sec)

MariaDB [test]> SELECT 1 AS const;
+-------+
| const |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 AS const FROM example WHERE name = 'hoge';
+-------+
| const |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT 1 AS const FROM example LIMIT 1;
+-------+
| const |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

FROMのテーブルに入っているデータから、を前提にして出力を考えてて、
そこに固定値しかないといっても処理としては関係ないのであった。

ということで、シンプルにFROM句を外す。
LIMIT 1 も FROM なしで固定値を出す分には影響ないから外してもよさそう。

INSERT INTO hoge_history (label)
SELECT CONCAT('exec: ', NOW()) AS label
WHERE NOT EXISTS (
	SELECT 1 FROM hoge_history WHERE timestamp >= SUBTIME(NOW(), '00:15:00') 
);

実にしょうもないことで、めちゃくちゃ焦った……。