2012年4月14日土曜日

NULL + 1 > 0 はTRUEかFALSEか

PHPだとTRUE。SQLではUNKNOWNのためTRUEではないのでFALSE。
$ php -r 'echo (null + 1) > 0 ? "TRUE" : "FALSE";'
TRUE

mysql> SELECT IF(NULL + 1 > 0, "TRUE", "FALSE");
+-----------------------------------+
| IF(NULL + 1 > 0, "TRUE", "FALSE") |
+-----------------------------------+
| FALSE                             |
+-----------------------------------+
1 row in set (0.00 sec)
3値論理であることを意識していないSQLを業務でも結構見る。NULLの取扱い方は難しい。
だから僕は、Nullがきらい。
基本的には、NOT NULLにしてnullは排除するように気をつけている。
(例外的に、必須でないけどUNIQUEなカラムは仕方ないのでNULL許すこともあるが)
でも、NOT NULLでNULLを排除していてもNULLは出てくるので困る。そう、外部結合。
NULLを完全に排除なんてできないので、nullについてちゃんと意識してSQLを書こうという今回の教訓。

とりあえず以上。


一応、表題の件を考慮しない失敗例は示しておく。

まず下記のようなユーザ、テスト1結果、テスト２結果テーブルにデータがあるとする。
テスト1とテスト２は1つのテーブルで管理したほうがいいとか、そういうのはナシ。
mysql> SELECT id, name FROM users;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Wilcke      |
|  2 | Bishop      |
|  3 | Clostermann |
|  4 | Lucchini    |
|  5 | Yeager      |
|  6 | Hartmann    |
|  7 | Barkhorn    |
|  8 | Litvyak     |
|  9 | Juutilainen |
+----+-------------+
9 rows in set (0.01 sec)

mysql> SELECT user_id, score FROM test1;
+---------+-------+
| user_id | score |
+---------+-------+
| 1       |   100 |
| 2       |    10 |
| 3       |    30 |
| 4       |    60 |
| 5       |    80 |
| 6       |    70 |
| 7       |    20 |
| 8       |    40 |
| 9       |    50 |
+---------+-------+
9 rows in set (0.00 sec)

mysql> SELECT user_id, score FROM test2;
+---------+-------+
| user_id | score |
+---------+-------+
| 2       |    30 |
| 3       |    50 |
| 4       |    20 |
| 6       |    70 |
| 8       |    80 |
+---------+-------+
5 rows in set (0.01 sec)

テスト1とテスト２の結果の合計が100点以上のユーザ(片方のみ受験ユーザを含む)を抽出したいが、nullを考えていない悪い例。
mysql>    SELECT name, t1.score + t2.score total
    ->      FROM users u
    -> LEFT JOIN test1 t1
    ->        ON u.id = t1.user_id
    -> LEFT JOIN test2 t2
    ->        ON u.id = t2.user_id
    ->     WHERE t1.score + t2.score >= 100;
+----------+-------+
| name     | total |
+----------+-------+
| Hartmann |   140 |
| Litvyak  |   120 |
+----------+-------+
2 rows in set (0.01 sec)
100+NULL>=100はUNKNOWNなのでWilckeが抽出されない。

NULLを考慮した良い例。
mysql>    SELECT name, IFNULL(t1.score, 0) + IFNULL(t2.score, 0) total
    ->      FROM users u
    -> LEFT JOIN test1 t1
    ->        ON u.id = t1.user_id
    -> LEFT JOIN test2 t2
    ->        ON u.id = t2.user_id
    ->     WHERE IFNULL(t1.score, 0) + IFNULL(t2.score, 0) >= 100;
+----------+-------+
| name     | total |
+----------+-------+
| Wilcke   |   100 |
| Hartmann |   140 |
| Litvyak  |   120 |
+----------+-------+
3 rows in set (0.03 sec)

データが少なくて漏れがわかればSQLが悪いことに気がつきそうだけど、データも多くなってしまうと結果もそれなりに正しそうなので悪い例のようなSQLのままになりがちなので中尉！！
ラベル: ,